Setting Secure MS SQL Permissions
Posted Wednesday, July 23, 2008 9:32:07 AMThe answer is simple really, give users as few permissions as possible while still giving them access to what they need. Now, how to do this is another matter but... here is what I recommend.
| Step 1: No public roles Don't give your user any role in the database other than the all inclusive "public" role. All others could give them access to tables that they have no need for and that could be exploited. |
 |
| Step 2: Give user limited access Now that you've removed all global permissions for the user you need to set what control they will have on specific tables. Be sure to set permissions for only the control they actually need. |
 |
| Step 3: Peace of Mind In theory step 1 and 2 should be good enough to limit permissions on a user, but... if you've been SQL Injected and are fed up with it then Step 3 can give you peace of mind about being attacked again (at least from this particular type of attack.) Explicitly denying the user access to the sysobjects and syscolumns tables will prevent anyone using SQL Injection from getting the names of your tables and columns, and without that information they cannot execute any update statements against your database. |
 |
| SQL Injection - Are you safe? (Wednesday, June 25, 2008 9:43:08 PM) |
Categories: Most Popular, Technical
View Comments
There are 5 comments on this article
NARENDRA DAS (11 years ago)
how many user can connect to web ms-sql server and my-sql server in both dedicated and non dedicated server at a time minimum and maximum
Matt (14 years ago)
thank you for your permission help worked for me SQL 2005
Adam (16 years ago)
Bill,
I just realized I never answered your MS SQL 2005 Question. The answer is: no. The rules are the same. MS SQL 2005 handles permissions a lot 'better' than 2000, but the basic rules still apply.
You can explicitly deny access to any table or object by executing this code:
I just realized I never answered your MS SQL 2005 Question. The answer is: no. The rules are the same. MS SQL 2005 handles permissions a lot 'better' than 2000, but the basic rules still apply.
You can explicitly deny access to any table or object by executing this code:
DENY SELECT ON [TABLE] to [USERNAME]Obviously you'd also want to deny INSERT, UPDATE, and DELETE as well, but the same syntax applies.
Adam (16 years ago)
Bill,
The only possible caustic effect on denying access to the sysobjects and syscolumns table is if the user you've denied access needs to see them. For example:
If you were to log into SQL via Enterprise Manager (or any other MS SQL front end application) as the denied user you wouldn't be able to see or edit anything.
It is rare that this is problem because most people access and edit the database from an administrator level not an "end user" level.
The only possible caustic effect on denying access to the sysobjects and syscolumns table is if the user you've denied access needs to see them. For example:
If you were to log into SQL via Enterprise Manager (or any other MS SQL front end application) as the denied user you wouldn't be able to see or edit anything.
It is rare that this is problem because most people access and edit the database from an administrator level not an "end user" level.
Bill Elliot (16 years ago)
Hi, these two articles are great - and it makes perfect sense to deny access to the sysobjects and syscolumns tables in order to prevent these types of attacks. Are there any caustic repercussions from denying access to these tables?
Also, your examples appear to be of MS SQL 2000 installations - are the rules much different for 2k5? How does one explicitly deny access to the sysobjects and syscolumns tables in 2k5?
Thanks again!
Also, your examples appear to be of MS SQL 2000 installations - are the rules much different for 2k5? How does one explicitly deny access to the sysobjects and syscolumns tables in 2k5?
Thanks again!