I have received many questions regarding an
earlier post on
SQL Injection that I made; the most common question being: what
permissions do I set to be safe?
The 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.
thank you for your permission help worked for me SQL 2005
Adam(4 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:
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(4 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.
Bill Elliot(4 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!
Search My Site
My Company Giveaways
About Me
Adam Lewis
25 years old
Middletown, Ohio
Software Engineer
