Saturday, May 22, 2010

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.

2 comments:

Avantha Siriwardana said...

Hi Dinesh
Small Ques.. why should we concern about the GRANT statement when the user has been DENIED access to the whole table?? is there any reasons???

Thanks

Dinesh Priyankara said...

I cannot think about a real scenario, but my friend's environment has such implementation, where user is in a certain group and group has DENIED permission set. In that case, if user needs to be given permission on set of columns, GRANT permission comes in handy. And SQL Server allows to do it!!!

So, if you need to remove that facility and stick into the industrial standard (CC), you have to go for the solution given.