SQL Security for your apps
-
Hi everyone, I was wondering how you all went about connecting to your databases from a security point of view. Some people say to use the SA password and embed security in your app with internal usernames/passwords, while others use Windows Security. I think the SA password idea sounds simplier, and has the advantage of being able to limit what data the user gets to (eg: which clients information they can see) HOWEVER it doesn't stop someone using the password with MS Access to bypass your app and get the data. So I was thinking the integrated security is best, and like any security, the use of groups would be best. My issue then becomes how to use those groups in SQL / VB.NET to enforce security, especially how would I enforce record level security (eg: which clients information they can see) Thoughts everyone ??
-
Hi everyone, I was wondering how you all went about connecting to your databases from a security point of view. Some people say to use the SA password and embed security in your app with internal usernames/passwords, while others use Windows Security. I think the SA password idea sounds simplier, and has the advantage of being able to limit what data the user gets to (eg: which clients information they can see) HOWEVER it doesn't stop someone using the password with MS Access to bypass your app and get the data. So I was thinking the integrated security is best, and like any security, the use of groups would be best. My issue then becomes how to use those groups in SQL / VB.NET to enforce security, especially how would I enforce record level security (eg: which clients information they can see) Thoughts everyone ??
Mark Cabbage wrote:
Some people say to use the SA password
I've heard from numerous others that you should never use the
sapassword in your app. Create a user account for your app, granting it only the necessary permissions that it requires to get it's job done."Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
Mark Cabbage wrote:
Some people say to use the SA password
I've heard from numerous others that you should never use the
sapassword in your app. Create a user account for your app, granting it only the necessary permissions that it requires to get it's job done."Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
Thanks for the reply Paul, however that doesn't solve the overall decision of using self-contained security accounts defined inside the account, or using Active Directory security.
-
Hi everyone, I was wondering how you all went about connecting to your databases from a security point of view. Some people say to use the SA password and embed security in your app with internal usernames/passwords, while others use Windows Security. I think the SA password idea sounds simplier, and has the advantage of being able to limit what data the user gets to (eg: which clients information they can see) HOWEVER it doesn't stop someone using the password with MS Access to bypass your app and get the data. So I was thinking the integrated security is best, and like any security, the use of groups would be best. My issue then becomes how to use those groups in SQL / VB.NET to enforce security, especially how would I enforce record level security (eg: which clients information they can see) Thoughts everyone ??
SQL Server's security model is pretty complicated. The credentials you supply to the server map to a login. This login can be authenticated using SQL Server, by supplying a username and password, or can be authenticated by Windows (either through local accounts or domain accounts, if the server is a member of an NT or Active Directory domain). You can grant both Windows user accounts and Windows security groups a login. You do not supply a username or password for Windows Authentication - it uses the Windows credentials of the user running the client application, and the groups that they are members of. To use different credentials you must run the application as a different user. By default, SQL Server authentication is disabled. The default configuration has an
salogin which is SQL Server-authenticated, and allows the Windows security groupBUILTIN\Administratorsto log in as well. Logins can be members of one or more server roles. These are fixed roles which have special permissions to manage or modify the server. All logins are members of thepublicserver role - this cannot be removed. The only permission this role has in SQL Server 2005 isVIEW ANY DATABASEwhich means that the login can see all the databases on the system - without this, it can only seemaster,tempdb, and any databases it owns.saandBUILTIN\Administratorsare members of thesysadminserver role, which has full control over the database server. This is why you should never usesain application code - it's simply far too privileged. The set of server roles is fixed and, apart from public in SQL Server 2005, their permissions cannot be changed. In SQL Server 2005, many of the permissions can be granted individually to logins, while in SQL Server 2000 these permissions can only be granted through membership of the appropriate server role. Just being granted a login to the server does not give any access to a database. The database must have a mapped user for the login. Again, the server roles do allow this to be overridden and in effect, members of the sysadmin server role have full control over every object in every database. Individual database users can be granted or denied permission to execute particular SQL statements on particular objects, and can also be granted permission to grant that permission to someone else. Again, there are database roles which p -
SQL Server's security model is pretty complicated. The credentials you supply to the server map to a login. This login can be authenticated using SQL Server, by supplying a username and password, or can be authenticated by Windows (either through local accounts or domain accounts, if the server is a member of an NT or Active Directory domain). You can grant both Windows user accounts and Windows security groups a login. You do not supply a username or password for Windows Authentication - it uses the Windows credentials of the user running the client application, and the groups that they are members of. To use different credentials you must run the application as a different user. By default, SQL Server authentication is disabled. The default configuration has an
salogin which is SQL Server-authenticated, and allows the Windows security groupBUILTIN\Administratorsto log in as well. Logins can be members of one or more server roles. These are fixed roles which have special permissions to manage or modify the server. All logins are members of thepublicserver role - this cannot be removed. The only permission this role has in SQL Server 2005 isVIEW ANY DATABASEwhich means that the login can see all the databases on the system - without this, it can only seemaster,tempdb, and any databases it owns.saandBUILTIN\Administratorsare members of thesysadminserver role, which has full control over the database server. This is why you should never usesain application code - it's simply far too privileged. The set of server roles is fixed and, apart from public in SQL Server 2005, their permissions cannot be changed. In SQL Server 2005, many of the permissions can be granted individually to logins, while in SQL Server 2000 these permissions can only be granted through membership of the appropriate server role. Just being granted a login to the server does not give any access to a database. The database must have a mapped user for the login. Again, the server roles do allow this to be overridden and in effect, members of the sysadmin server role have full control over every object in every database. Individual database users can be granted or denied permission to execute particular SQL statements on particular objects, and can also be granted permission to grant that permission to someone else. Again, there are database roles which pThanks so much for taking the time to elaborate on the topic. Mark