SQL Security for your VB 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
Did you beat these people about the face and neck for doing something so ridiculously stupid?? Really, why would you give an application GOD rights to the ENTIRE SQL Server??
Mark Cabbage wrote:
I think the SA password idea sounds simplier,
And would get you fired, on the spot, in any real job. Who cares what the advantages are at that point...
Mark Cabbage wrote:
the advantage of being able to limit what data the user gets to
You would normally create several different user accounts in the database that the application uses appropriately. These user accounts would then get various permissions assigned to them in SQL Security, depending on the role of that user. Say there was a user created that was used only look at the data in a couple of tables. This user would get permissions to execute SELECT queries on just those tables. Anything it tried to do would be denied by SQL Server. Seriously, this is such a big topic, no one, or ten, forums posts is going to cover what your options are, and there is no one option that is best for every situation. Pick up a book on SQL Server and you'll find a couple of chapters on the subject. Pickup a book on SQL Server Security, and you'll find the entire book written on this very subject.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
Mark Cabbage wrote:
Some people say to use the SA password
Did you beat these people about the face and neck for doing something so ridiculously stupid?? Really, why would you give an application GOD rights to the ENTIRE SQL Server??
Mark Cabbage wrote:
I think the SA password idea sounds simplier,
And would get you fired, on the spot, in any real job. Who cares what the advantages are at that point...
Mark Cabbage wrote:
the advantage of being able to limit what data the user gets to
You would normally create several different user accounts in the database that the application uses appropriately. These user accounts would then get various permissions assigned to them in SQL Security, depending on the role of that user. Say there was a user created that was used only look at the data in a couple of tables. This user would get permissions to execute SELECT queries on just those tables. Anything it tried to do would be denied by SQL Server. Seriously, this is such a big topic, no one, or ten, forums posts is going to cover what your options are, and there is no one option that is best for every situation. Pick up a book on SQL Server and you'll find a couple of chapters on the subject. Pickup a book on SQL Server Security, and you'll find the entire book written on this very subject.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Hi Dave, Thanks for your reply. Your response seemed to suggest on using limited SQL accounts if I read it correctly. Is there any reason you didn't mention Active Directory accounts and groups ?? I appreciate this topic is massive. I'd appreciate if you knew of any good resources (online or book based) that covered this topic well from all view points. I need a balance between security and functionality. I really want to limit the customers a contractor can see to just them, and if I have numerous contractors it's too hard to use numerous hard coded accounts.
-
Hi Dave, Thanks for your reply. Your response seemed to suggest on using limited SQL accounts if I read it correctly. Is there any reason you didn't mention Active Directory accounts and groups ?? I appreciate this topic is massive. I'd appreciate if you knew of any good resources (online or book based) that covered this topic well from all view points. I need a balance between security and functionality. I really want to limit the customers a contractor can see to just them, and if I have numerous contractors it's too hard to use numerous hard coded accounts.
Mark Cabbage wrote:
Is there any reason you didn't mention Active Directory accounts and groups
Possible, but a PITA to administer. Imagine a company with 10,000 users...
Mark Cabbage wrote:
I really want to limit the customers a contractor can see to just them,
There's a couple of ways of doing this. Either your app checks a directory group for membership in some kind of "Contrator" group, or if SQL Windows Integrated security is turned on, SQL Server security can be setup to use that group instead of an SQL User Account. Any of the SQL Server Security books would cover this. Just go to Amazon and search for "SQL Server Security". Believe it or not, the SQL Server Programming books don't get into this stuff very deep at all.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
Mark Cabbage wrote:
Some people say to use the SA password
Did you beat these people about the face and neck for doing something so ridiculously stupid?? Really, why would you give an application GOD rights to the ENTIRE SQL Server??
Mark Cabbage wrote:
I think the SA password idea sounds simplier,
And would get you fired, on the spot, in any real job. Who cares what the advantages are at that point...
Mark Cabbage wrote:
the advantage of being able to limit what data the user gets to
You would normally create several different user accounts in the database that the application uses appropriately. These user accounts would then get various permissions assigned to them in SQL Security, depending on the role of that user. Say there was a user created that was used only look at the data in a couple of tables. This user would get permissions to execute SELECT queries on just those tables. Anything it tried to do would be denied by SQL Server. Seriously, this is such a big topic, no one, or ten, forums posts is going to cover what your options are, and there is no one option that is best for every situation. Pick up a book on SQL Server and you'll find a couple of chapters on the subject. Pickup a book on SQL Server Security, and you'll find the entire book written on this very subject.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Dave Kreskowiak wrote:
Mark Cabbage wrote: I think the SA password idea sounds simplier, And would get you fired, on the spot, in any real job.
I already mentioned this is a cross post of his. I kind of got snapped at about Active Directory and he didn't even mention using it.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
Mark Cabbage wrote:
Is there any reason you didn't mention Active Directory accounts and groups
Possible, but a PITA to administer. Imagine a company with 10,000 users...
Mark Cabbage wrote:
I really want to limit the customers a contractor can see to just them,
There's a couple of ways of doing this. Either your app checks a directory group for membership in some kind of "Contrator" group, or if SQL Windows Integrated security is turned on, SQL Server security can be setup to use that group instead of an SQL User Account. Any of the SQL Server Security books would cover this. Just go to Amazon and search for "SQL Server Security". Believe it or not, the SQL Server Programming books don't get into this stuff very deep at all.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Dave Kreskowiak wrote:
you didn't mention Active Directory accounts and groups Possible, but a PITA to administer. Imagine a company with 10,000 users...
I sure wouldn't want to do that. 10,000+ users, yikes :eek:
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
Dave Kreskowiak wrote:
you didn't mention Active Directory accounts and groups Possible, but a PITA to administer. Imagine a company with 10,000 users...
I sure wouldn't want to do that. 10,000+ users, yikes :eek:
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
I have! 30,000 desktops and 300,000+ users. Imagine making just one little mistake where noone can login! ;P
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
I have! 30,000 desktops and 300,000+ users. Imagine making just one little mistake where noone can login! ;P
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Dave Kreskowiak wrote:
Imagine making just one little mistake where noone can login!
Yep. That'd be a big oops for the day :->
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
Dave Kreskowiak wrote:
Imagine making just one little mistake where noone can login!
Yep. That'd be a big oops for the day :->
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
I know, from first hand experience. Whoops! :->
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007