Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Security Best Practices - Dealing With User Name Changes Expand / Collapse
Author
Message
Posted Friday, January 11, 2013 1:23 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
Are there any Best Practices from Microsoft or anyone else on how best to deal with SQL Logins & DB Users when you have bname changes with your users.

For example, if SQL Logins and DB Users are created as firstname.lastname and the user Mary Jones (setup as mary.jones) gets married and changes to Mary Smith is it considered best to change the existing SQL Login & DB User from mary.jones to mary.smith or is it better to create a new SQL Login & DB User called mary.smith and then disable the mary.jones login & DB User?

BTW - I don't know if this kind of naming convention for logins & DB Users is considered bad practice but we do it because it then matches how users are setup within our primary application and the DB it uses and this allwsus to do things that would otherwise not be possibel (by matching Logins/DBUser with the app user).

Thanks


Kindest Regards,

Just say No to Facebook!
Post #1406246
Posted Friday, January 11, 2013 8:15 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:57 AM
Points: 369, Visits: 1,208
If that are windows logins, why granting them individually? You could create windows (AD) groups, and sql login for that group.
Managing rights will be much easier on the group level, and name changes won't affect you.
If you want some individuals to have different rights, create another group and put them in there.
If there would be as many groups as users (very, very unlikely), you are out of luck. You have to manage them as individual logins and in that case I would change the name of the login. That will not break internal dependencies and rights since they are based upon internal ID's (SID, sort of GUID), not the login name.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1406303
Posted Saturday, January 12, 2013 11:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 12, 2013 11:44 AM
Points: 7, Visits: 12
Vedran is right. If you manage security via AD Groups, all you need to do is remove the old login from the group - typically server team does this, and have the new login added to the domain group.
SQL Logins are typically meant for application logins. People generally are required to use their domain logins to connect to SQL.
I have also seen some people using same old domain login even after name change.
Post #1406398
Posted Saturday, January 12, 2013 10:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
AD Groups as SQL Logins and Database Users work great unless you need the User to have a default schema. In SQL 2012 the loop has been closed, but this is a 2008 forum.

@YSLGuru, I would look to rename them so they match the person's new name. Last name changes are pretty common and I rename Database Users and Server Logins all the time to keep up. Are you storing the Database User name in any audit tables? If the Database User name is used to maintain an audit trail then you may have a bit more to consider.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1406448
Posted Sunday, January 13, 2013 11:48 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 1,970, Visits: 2,909
The person's login id is often some combination of last name and first name, so you need to change the name there.

However, internally you should assign each person/user a unique id, and use that id in all tables that are not extremely short term (data is used for less than one day then discarded).

IOW, "mary.jones" may be part of the person's login, but should not be stored in internal tables such as usage history, etc.. Instead "mary.jones" would be, say, id#1875431, and that id would be used in all internal and permanent tables. Looking up id#1875431 yields the name "mary jones" ... until she changes her name, when it yields some other name, but the name change does not require any changes to any historical table data.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1406498
Posted Monday, January 14, 2013 3:26 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
Vedran Kesegic (1/11/2013)
If that are windows logins, why granting them individually? You could create windows (AD) groups, and sql login for that group.
Managing rights will be much easier on the group level, and name changes won't affect you.
If you want some individuals to have different rights, create another group and put them in there.
If there would be as many groups as users (very, very unlikely), you are out of luck. You have to manage them as individual logins and in that case I would change the name of the login. That will not break internal dependencies and rights since they are based upon internal ID's (SID, sort of GUID), not the login name.


We are working within the bounds of the application (our primary accounting software which uses a SQL Server DB for data storage) and so our choices of authentication type are limited to SQL Security only. NT Authentication will not work with the apps own proprietary security. As is common the app developers created their own security scheme instead of using what was already provided in SQL Server.

Within the aps security mechanism each user does have a unique numeric ID that is never seen (at least not from within the app) and so changes there do not matter. Where the name change is an issue comes into play when we are trying to sync into the apps processes (via T-SQL) and capture information that the app should but does not such as data change auditing. By assigning users with logins that match their name (which is how the application admins set up users within the app) we are able to do things that even the aps maker had previously said was not realistic. Thanks to native SQL items like ORIGINAL_LOGIN we can link up process to users that the app does not naturally provide.

If we had our users setup per the software vendors specs then every user would be connecting in as the login that owns the DB. That means that 50 users connections all come in as the same ID and it becomes impossible to determine who is doing what unless the apps native security functionality provides a way to do this and it doesn't. So while the use of names to logins via standard SQL Security is not the best choice under ideal circumstances its what we have to work with for now.


Thanks for posting


Kindest Regards,

Just say No to Facebook!
Post #1406963
Posted Monday, January 14, 2013 3:30 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
ScottPletcher (1/13/2013)
The person's login id is often some combination of last name and first name, so you need to change the name there.

However, internally you should assign each person/user a unique id, and use that id in all tables that are not extremely short term (data is used for less than one day then discarded).

IOW, "mary.jones" may be part of the person's login, but should not be stored in internal tables such as usage history, etc.. Instead "mary.jones" would be, say, id#1875431, and that id would be used in all internal and permanent tables. Looking up id#1875431 yields the name "mary jones" ... until she changes her name, when it yields some other name, but the name change does not require any changes to any historical table data.


The tables within the DB the application uses to store user info does assign unique numeric value to each user. The name consisting of first & last is for front end uses/purpose.


Kindest Regards,

Just say No to Facebook!
Post #1406964
Posted Monday, January 14, 2013 3:56 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 1,970, Visits: 2,909
Yes, I would change the specific SQL user login to match the person's name, if all/part of the name itself is being used as part of the login.

Use roles, where possible, to assign permissions to logins, as that will simplify management of permissions.

Have a script that "converts" the old login to the new login.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1406973
Posted Monday, January 14, 2013 3:59 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
opc.three (1/12/2013)
AD Groups as SQL Logins and Database Users work great unless you need the User to have a default schema. In SQL 2012 the loop has been closed, but this is a 2008 forum.

@YSLGuru, I would look to rename them so they match the person's new name. Last name changes are pretty common and I rename Database Users and Server Logins all the time to keep up. Are you storing the Database User name in any audit tables? If the Database User name is used to maintain an audit trail then you may have a bit more to consider.


The schema that the app assumes for everything is 'dbo'. As far as auditing storage goes we are storing the name (as text) when auditing something that the app does not manage itself. For example, when someone makes a configuration change to an Account Number in our apps chart of accounts we capture the data before the change along with the date/time and name/ID of the user making said change. We don't store the SQL Login/DB User (as setup within SQL Server), just the user ID info the app has. We use the SQL Login & DB Users to identofy what SPID belongs to whom when monitoring. Else every SPID would be listed as dbo and we'd have no idea who is doing what.

Thanks


Kindest Regards,

Just say No to Facebook!
Post #1406974
Posted Monday, January 14, 2013 4:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
YSLGuru (1/14/2013)
opc.three (1/12/2013)
AD Groups as SQL Logins and Database Users work great unless you need the User to have a default schema. In SQL 2012 the loop has been closed, but this is a 2008 forum.

@YSLGuru, I would look to rename them so they match the person's new name. Last name changes are pretty common and I rename Database Users and Server Logins all the time to keep up. Are you storing the Database User name in any audit tables? If the Database User name is used to maintain an audit trail then you may have a bit more to consider.


The schema that the app assumes for everything is 'dbo'.

If by 'assume' you meant that the app does not schema-qualify all object references and instead relies heavily on the default schema setup for each Database User then moving to AD Groups will not work for you until you move to SQL 2012.

As far as auditing storage goes we are storing the name (as text) when auditing something that the app does not manage itself. For example, when someone makes a configuration change to an Account Number in our apps chart of accounts we capture the data before the change along with the date/time and name/ID of the user making said change. We don't store the SQL Login/DB User (as setup within SQL Server), just the user ID info the app has. We use the SQL Login & DB Users to identofy what SPID belongs to whom when monitoring. Else every SPID would be listed as dbo and we'd have no idea who is doing what.

Thanks

In light of this info I would say there is no reason why you should not rename the Database Users and Server Logins to match the person's real, new, name.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1406978
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse