Column GRANT overrides DENY Table, Avoiding with Common Criteria Compliance

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.

-- Create a login and add user to AdventureWorks

CREATE LOGIN [Yeshan]

WITH PASSWORD = 'Pa$$w0rd'

, CHECK_POLICY = OFF

GO

USE [AdventureWorks]

GO

CREATE USER [Yeshan] FOR LOGIN [Yeshan]

WITH DEFAULT_SCHEMA = [dbo]

GO

-- Denies permissions to user on Production table

DENY SELECT ON Production.Product TO [Yeshan]

-- Set the Execution Context to user and see

-- whether he can access the table

EXECUTE AS USER = 'Yeshan'

SELECT * FROM Production.Product

REVERT

-- The error "The SELECT permission was denied on..."

-- is thrown.

-- Grant permission to user on two columns

GRANT SELECT ON OBJECT::Production.Product

(ProductID, Name) TO [Yeshan]

-- Execute the SELECT statement again on all columns

EXECUTE AS USER = 'Yeshan'

SELECT * FROM Production.Product

REVERT

-- Still no permission

-- Now lets see whether user can see the granted columns

EXECUTE AS USER = 'Yeshan'

SELECT ProductID, Name FROM Production.Product

REVERT

-- Required result comes

-- Column GRANT overrides DENY TABLE

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.

sp_configure 'common criteria compliance enabled', 1

GO

RECONFIGURE

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.