November 3, 2010 at 9:13 pm
Comments posted to this topic are about the item One Time Passwords
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 4, 2010 at 1:17 am
This is an out of the box thinking to our traditional approach. It reminds me the concept of generating prepaid credit cards. We can generate card ourselves with certain amount that are valid for certain period and can safely use it while paying bills/booking tickets.
Thanks for the thought provoking editorial.
November 4, 2010 at 3:25 am
It would be very useful for those external suppliers who come in and install their software needing access just for the day. I usually create a sql login with sysadmin permissions and have to remember to remove/de-permission it rather than just giving them the sa password (which is generally never used as the admins are in a group which has the privileges on the servers)
November 4, 2010 at 5:45 am
I don't see the benefit of the one-time password in this context, if I want to access my stuff from a friend's computer, wouldn't I need to know ahead of time I was going to do that? Or do you first log into your account with your real password, to setup a one-time password, and then log out so you can re-login with the one-timer? 😉 If someone can enlighten me I would appreciate it.
From the DBA perspective it sounds like a user account that can be given and end date is the feature that is needed.
<><
Livin' down on the cube farm. Left, left, then a right.
November 4, 2010 at 6:48 am
You could easily set something like this up with a login trigger.
The problem would be getting and remembering the new password once it's issued, so the trigger would need to do something like send an SMS message to your cell phone with the new password for your next login attempt.
That makes it slightly less secure, but would still be much more secure than using sa from a workstation that might have a keylogger on it, or anything like that. Not as secure as certain biometric tests, but bypasses the need for specialized hardware that those make necessary.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 7:47 am
One possible solution for assigning a specific lifespan to database logins that need irregular access would be to create a reference table similar to the one below, and then schedule a job that runs hourly, executing an ALTER LOGIN <login_name> ENABLE | DISABLE. This would be for situations where you don't want a login's domain account or password to expire, but rather just to restrict their access to the database for a specific date range, thus allowing the DBA to pre-schedule these events to kick off automatically instead of attempting to manage it all manually. It would also keep sort of an audit trail of what irregular accounts had access and when. Perhaps you could have another encrypted column contains passwords for pre-scheduled resets as well. Forgive me if there is a similar feature built into SQL Server 2008.
create table login_constraint
(
primary key ( loginname, enable_date ),
loginname varchar(120) not null,
enable_date smalldatetime not null,
disable_date smalldatetime not null
)
loginname enable_date disable_date
-------------- ----------------- -------------------
consult4 2010/11/01 08:00AM 2010/11/05 06:00PM
mycorp\basmith 2010/04/15 12:00AM 2010/12/31 11:59PM
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 4, 2010 at 8:23 am
This could be done by adding two columns to your user table. They are:
LoginCount int null
AllowedLoginCount int null
If AllowedLoginCount is greater then zero, this is how many time they can log in. Each time they log in, you increment LoginCount by one. When LoginCount > AllowedLoginCount then that's it. If AllowedLoginCount is null or negative or some other magic number then they can log in an unlimited number of times.
The logic would be:
AllowLogin = AllowLoginCount IS NULL OR LoginCount < AllowedLoginCount
November 4, 2010 at 8:41 am
alan.paxton (11/4/2010)
This could be done by adding two columns to your user table. They are:LoginCount int null
AllowedLoginCount int null
If AllowedLoginCount is greater then zero, this is how many time they can log in. Each time they log in, you increment LoginCount by one. When LoginCount > AllowedLoginCount then that's it. If AllowedLoginCount is null or negative or some other magic number then they can log in an unlimited number of times.
The logic would be:
AllowLogin = AllowLoginCount IS NULL OR LoginCount < AllowedLoginCount
If I were doing something like that, I'd have a bit field that would explicitly allow unlimited logins. Makes it easier to figure out what's going on when the documentation is (inevitably) missing/lost/outdated/incomprehensible/ignored.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 8:45 am
alan.paxton (11/4/2010)
This could be done by adding two columns to your user table. They are:LoginCount int null
AllowedLoginCount int null
If AllowedLoginCount is greater then zero, this is how many time they can log in. Each time they log in, you increment LoginCount by one. When LoginCount > AllowedLoginCount then that's it. If AllowedLoginCount is null or negative or some other magic number then they can log in an unlimited number of times.
The logic would be:
AllowLogin = AllowLoginCount IS NULL OR LoginCount < AllowedLoginCount
Yes, if you want to limit login counts, then and updating those 2 columns, and the final login attempt denial, would be updated by a login trigger. Also in that case the trigger would handle the enable_date and disable_date check too. Denial by trigger would perhaps make ALTER <login_name> DISABLE not technically necessary, but I feel that a "disabled" login should be actually DISABLED at some point.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 4, 2010 at 10:24 am
What I like about this article is the suggested solutions to provide similar functionality.
Thanks all
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 4, 2010 at 10:31 am
CELKO (11/4/2010)
My favorite security device is the "bingo card password" -- if anyone know the real name, please tell me. I have seen it in use in Brazil for ATM access and implemented for applications here.You get a wallet card every so often with a square grid of numbers or letters on it. You put in your password and the application gives you a challenge that consists of a pair of numbers or letters.
You locate the two numbers on your bingo card. Your response is another pair that complete a rectangle in the grid. For example, with letters, consider a 5x5 grid:
a b c h j
d g f n r
o q e t v
i k u p w
s x l m z
The challenge (a, e) is met with (o, c). There are 25 *( 24 -4-4) = 400 different challenges on this small card. There are 25! possible grids for the 25 letters I used.
I've seen the same thing. Seems to make sense.
Would be awefully inconvenient if you lost yours whilst traveling or something like that. Also has the usual limits on standardization and implementation, especially for travel abroad and that kind of thing.
Probably worth it for the added security.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 10:45 am
If you want to see an interesting use of one time passwords check out GoToMyPC
From there documentation:
One-Time Passwords
The use of One-Time Passwords (OTPs) offers an additional level of security
that can protect you while you are using public terminals that may have
keyboard loggers or other keystroke-capture hardware or software. Our
One-Time Passwords system ensures that a discovered password is useless
to the person who discovers it.
One-Time Passwords are composed of six words, and each six-word combination
provides one-time access to a host PC. Password lists are numbered in reverse
order from highest to lowest and end in zero.
The use of One-Time Passwords enables you to generate a list of One-Time
Passwords that you will be prompted to enter after you enter your access code
each time you access your host PC.
You can read the full document from:
https://www.gotomypc.com/help/gtmpc_user_help.htm#using_gotomypc_features/one-time-passwords.htm
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply