One of my friends was configuring security in his databases last week and I was helping him on it. The configuration is more on authorization, so, a bunch of GRANT and DENY statements. Yesterday he called me and explained a situation where users can execute SELECT statements on granted columns even though they have been denied for the table. I was confused. If users are denied to the table, how can they see values in columns, even though they are granted permission on columns. I thought, the rule of thumb is, DENY always overrides GRANT, isn’t it?
I tried to find reasons for this. Finally I managed to dig up reasons for this. By default, Column GRANT overrides DENY Table in SQL Server. I am not sure whether it is the default setting for all other DBMSs, but that is the normal behavior of SQL Server. Let me show you this behavior.
If this is not what you wish, you can enhance security to avoid it. It is done through enabling Common Criteria Compliance option. The Common Criteria for Information Technology Security Evaluation (abbreviated as Common Criteria or CC) is an international standard for computer security. SQL Server 2005 SP2 was evaluated against CC and now SQL Server is complied with Common Criteria Evaluation Assurance Level 4. Enabling this option in SQL Server, following Common Criteria are enforced;
Table DENY overrides Column GRANT
Viewing login statistics capability with sys.dm_exec_sessions
Residual Information Protection (RIP) compliance
What we need here is, the first criteria. Following code enables Common Criteria Compliance in SQL Server.
You may have to restart the services in order to set the run_value of Common Criteria Compliance Enabled option. If you do not see this option with sp_configure, enable show advanced option first and reconfigure.
Once it is enabled, Column GRANT does not override DENY Table.