Securing System Tables

Securing System Tables

In light of the recent sql injection attack that iterates through system 
tables, we are tightening down security for our Application Logins.

I am not able to deny select on  views in the "sys" & "INFORMATION_SCHEMA 
"schemas for a role I have created called "DenySystemTableSelect".  I used 
this statement:

DENY SELECT ON SCHEMA::[sys] TO [DenySystemTableSelect]
GO

DENY SELECT ON SCHEMA::[INFORMATION_SCHEMA] TO [DenySystemTableSelect]
GO

The above doesn't work.


I AM able to deny access to specific views in the sys schema like so:

DENY SELECT ON [sys].[columns] TO DenySystemTableSelect
GO
DENY SELECT ON [sys].[tables] TO DenySystemTableSelect
GO
DENY SELECT ON [sys].[syscolumns] TO DenySystemTableSelect
GO
DENY SELECT ON [sys].[sysobjects] TO DenySystemTableSelect
GO
DENY SELECT ON [sys].[objects] TO DenySystemTableSelect
GO
DENY SELECT ON [sys].[syscomments] TO DenySystemTableSelect
GO

The above does work.



I am NOT able to deny access to specific views in the "INFORMATION_SCHEMA" 
schema using the following SQL:

DENY SELECT ON  INFORMATION_SCHEMA.COLUMNS To DenySystemTableSelect

DENY SELECT ON INFORMATION_SCHEMA.PARAMETERS To DenySystemTableSelect

DENY SELECT ON INFORMATION_SCHEMA.TABLES To DenySystemTableSelect

DENY SELECT ON INFORMATION_SCHEMA.VIEWS To DenySystemTableSelect

The above does not work.


Basically, all I want to do is deny select on the "sys" schema and the 
"INFORMATION_SCHEMA" schema.  

I'm fine with specifically denying select on key views in the sys schema.  
But why doesn't a DENY at the schema level work?  Perhaps it's because all 
logins require access to certain system views in order to function.

Also, why can't I get a DENY SELECT on the INFORMATION_SCHEMA to work at 
all, even when denying at the specific view level?

Any thoughts, suggestions would be appreciated.  Thanks!

date: Fri, 19 Sep 2008 05:15:00 -0700 author: Scott M

RE: Securing System Tables

What error are you getting?

I am able to deny select on the INFORMATION_SCHEMA no problem. 

We been having few issues with SQL Injection attack also; rather then 
locking out the system tables.  I got developers to fix their code to prevent 
the current bug thats going around.

Thanks.
-- 
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/


"Scott M" wrote:

> In light of the recent sql injection attack that iterates through system 
> tables, we are tightening down security for our Application Logins.
> 
> I am not able to deny select on  views in the "sys" & "INFORMATION_SCHEMA 
> "schemas for a role I have created called "DenySystemTableSelect".  I used 
> this statement:
> 
> DENY SELECT ON SCHEMA::[sys] TO [DenySystemTableSelect]
> GO
> 
> DENY SELECT ON SCHEMA::[INFORMATION_SCHEMA] TO [DenySystemTableSelect]
> GO
> 
> The above doesn't work.
> 
> 
> I AM able to deny access to specific views in the sys schema like so:
> 
> DENY SELECT ON [sys].[columns] TO DenySystemTableSelect
> GO
> DENY SELECT ON [sys].[tables] TO DenySystemTableSelect
> GO
> DENY SELECT ON [sys].[syscolumns] TO DenySystemTableSelect
> GO
> DENY SELECT ON [sys].[sysobjects] TO DenySystemTableSelect
> GO
> DENY SELECT ON [sys].[objects] TO DenySystemTableSelect
> GO
> DENY SELECT ON [sys].[syscomments] TO DenySystemTableSelect
> GO
> 
> The above does work.
> 
> 
> 
> I am NOT able to deny access to specific views in the "INFORMATION_SCHEMA" 
> schema using the following SQL:
> 
> DENY SELECT ON  INFORMATION_SCHEMA.COLUMNS To DenySystemTableSelect
> 
> DENY SELECT ON INFORMATION_SCHEMA.PARAMETERS To DenySystemTableSelect
> 
> DENY SELECT ON INFORMATION_SCHEMA.TABLES To DenySystemTableSelect
> 
> DENY SELECT ON INFORMATION_SCHEMA.VIEWS To DenySystemTableSelect
> 
> The above does not work.
> 
> 
> Basically, all I want to do is deny select on the "sys" schema and the 
> "INFORMATION_SCHEMA" schema.  
> 
> I'm fine with specifically denying select on key views in the sys schema.  
> But why doesn't a DENY at the schema level work?  Perhaps it's because all 
> logins require access to certain system views in order to function.
> 
> Also, why can't I get a DENY SELECT on the INFORMATION_SCHEMA to work at 
> all, even when denying at the specific view level?
> 
> Any thoughts, suggestions would be appreciated.  Thanks!
> 
> 
>

date: Fri, 19 Sep 2008 11:05:01 -0700 author: Mohit K. Gupta

Re: Securing System Tables

> We been having few issues with SQL Injection attack also; rather then
> locking out the system tables.  I got developers to fix their code to 
> prevent
> the current bug thats going around.

I agree it's best to first address the underlying security flaw. 
Disallowing meta-data access only mitigates risk after the injection has 
already occurred.

-- 
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Mohit K. Gupta"  wrote in message 
news:A51EF617-9DD2-4830-B864-34188E991DB2@microsoft.com...
> What error are you getting?
>
> I am able to deny select on the INFORMATION_SCHEMA no problem.
>
> We been having few issues with SQL Injection attack also; rather then
> locking out the system tables.  I got developers to fix their code to 
> prevent
> the current bug thats going around.
>
> Thanks.
> -- 
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
> http://sqllearnings.blogspot.com/
>
>
> "Scott M" wrote:
>
>> In light of the recent sql injection attack that iterates through system
>> tables, we are tightening down security for our Application Logins.
>>
>> I am not able to deny select on  views in the "sys" & "INFORMATION_SCHEMA
>> "schemas for a role I have created called "DenySystemTableSelect".  I 
>> used
>> this statement:
>>
>> DENY SELECT ON SCHEMA::[sys] TO [DenySystemTableSelect]
>> GO
>>
>> DENY SELECT ON SCHEMA::[INFORMATION_SCHEMA] TO [DenySystemTableSelect]
>> GO
>>
>> The above doesn't work.
>>
>>
>> I AM able to deny access to specific views in the sys schema like so:
>>
>> DENY SELECT ON [sys].[columns] TO DenySystemTableSelect
>> GO
>> DENY SELECT ON [sys].[tables] TO DenySystemTableSelect
>> GO
>> DENY SELECT ON [sys].[syscolumns] TO DenySystemTableSelect
>> GO
>> DENY SELECT ON [sys].[sysobjects] TO DenySystemTableSelect
>> GO
>> DENY SELECT ON [sys].[objects] TO DenySystemTableSelect
>> GO
>> DENY SELECT ON [sys].[syscomments] TO DenySystemTableSelect
>> GO
>>
>> The above does work.
>>
>>
>>
>> I am NOT able to deny access to specific views in the 
>> "INFORMATION_SCHEMA"
>> schema using the following SQL:
>>
>> DENY SELECT ON  INFORMATION_SCHEMA.COLUMNS To DenySystemTableSelect
>>
>> DENY SELECT ON INFORMATION_SCHEMA.PARAMETERS To DenySystemTableSelect
>>
>> DENY SELECT ON INFORMATION_SCHEMA.TABLES To DenySystemTableSelect
>>
>> DENY SELECT ON INFORMATION_SCHEMA.VIEWS To DenySystemTableSelect
>>
>> The above does not work.
>>
>>
>> Basically, all I want to do is deny select on the "sys" schema and the
>> "INFORMATION_SCHEMA" schema.
>>
>> I'm fine with specifically denying select on key views in the sys schema.
>> But why doesn't a DENY at the schema level work?  Perhaps it's because 
>> all
>> logins require access to certain system views in order to function.
>>
>> Also, why can't I get a DENY SELECT on the INFORMATION_SCHEMA to work at
>> all, even when denying at the specific view level?
>>
>> Any thoughts, suggestions would be appreciated.  Thanks!
>>
>>
>>

date: Sat, 20 Sep 2008 08:27:09 -0500 author: Dan Guzman

Re: Securing System Tables

Scott M (ScottM@discussions.microsoft.com) writes:
> In light of the recent sql injection attack that iterates through system 
> tables, we are tightening down security for our Application Logins.
> 
> I am not able to deny select on  views in the "sys" & "INFORMATION_SCHEMA 
> "schemas for a role I have created called "DenySystemTableSelect".  I used 
> this statement:

It may be related to that the sys schema lives in mssqlsystemresource.

In any case, I don't think it is a very good idea to pursue, as it
could break a lot of things. Nor is there any need to.

By default, in SQL 2005, a user may only see the definition of object
he has access to. If you grant a user access to an object, he also gets
VIEW DEFINITION permissions on the database.

If you want to hide all metadata for a user you can do

   DENY VIEW DEFINITION TO someuser

The user cannot read any metadata.

But it is not uncommon for application to run meta-data queries to find
out the columns of tables or parameters of stored procedures. They will
break.

Furthermore, what you are achieving is only security by obscurity. As long
as your code is open to SQL injection and your application login has
write access to the tables, you will still be open to SQL injection. The 
intruder will just have to blindly guess the table names. But intruders
often have plenty of time on their hands, so that is a small issue.

If you really want to protect yourself against SQL injection, first step
is to have application to use parameterised commands. A second step
would be to move to use stored procedures which would permit you to
revoke direct permissions on the tables. (Although it could be argued 
that stored procedures also falls in the security-by-obscurity category.)

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

date: Sat, 20 Sep 2008 06:35:22 -0700 author: Erland Sommarskog