SQL Server GRANT/DENY on column and table level
-- run separate steps, one number at a time | |
-- 01 create database | |
USE master; | |
GO | |
IF DB_ID('test_security') IS NULL | |
EXEC sys.sp_executesql N'CREATE DATABASE test_security;'; | |
GO | |
-- 02 create test user; we don't need login for it | |
USE test_security; | |
GO | |
IF USER_ID('testUser') IS NULL | |
EXEC sys.sp_executesql N'CREATE USER testUser WITHOUT LOGIN;'; | |
GO | |
-- 03 create test table with few rows of data | |
IF OBJECT_ID(N'dbo.TestTable', N'U') IS NOT NULL | |
EXEC sys.sp_executesql N'DROP TABLE dbo.testTable;' | |
GO | |
CREATE TABLE dbo.TestTable ( | |
id TINYINT, | |
code CHAR(3), | |
info VARCHAR(10) | |
) | |
GO | |
INSERT INTO dbo.TestTable( | |
id, code, info | |
) | |
VALUES | |
(10, 'A01', 'Code A01'), | |
(20, 'A02', 'Code A02'), | |
(30, 'A03', 'Code A03'), | |
(40, 'A04', 'Code A04'), | |
(50, 'A05', 'Code A05') | |
; | |
-- 04 check if we can see the data | |
SELECT * FROM dbo.TestTable; | |
-- 05 check if testUser user can see the data | |
EXECUTE AS USER = 'testUser'; | |
GO | |
SELECT * FROM dbo.TestTable; | |
-- we should see error | |
-- The SELECT permission was denied on the object 'TestTable', database 'test_security', schema 'dbo'. | |
-- 06 switch back to admin, grant SELECT to testUser and try again | |
REVERT | |
GRANT SELECT ON dbo.TestTable TO testUser; | |
GO | |
EXECUTE AS USER = 'testUser'; | |
GO | |
SELECT * FROM dbo.TestTable; | |
-- now it works | |
-- 07 switch back to admin | |
REVERT | |
-- 08 REVOKE SELECT privilege from testUser, | |
-- GRANT access to 'id' and 'code' columns for testUser | |
REVOKE SELECT ON dbo.TestTable FROM testUser; | |
GO | |
GRANT SELECT ON dbo.TestTable(id, code) TO testUser; | |
GO | |
-- 09 test | |
EXECUTE AS USER = 'testUser'; | |
GO | |
-- that should throw the error | |
-- The SELECT permission was denied on the column 'info' of the object 'TestTable', database 'test_security', schema 'dbo'. | |
SELECT * FROM dbo.TestTable; | |
-- 10 but this should work | |
SELECT id, code FROM dbo.TestTable; | |
-- 11 switch back to admin and test DENY/GRANT behavior | |
REVERT | |
REVOKE SELECT ON dbo.TestTable(id, code) FROM testUser; | |
GO | |
DENY SELECT ON dbo.TestTable TO testUser; | |
GO | |
GRANT SELECT ON dbo.TestTable(id, code) TO testUser; | |
GO | |
-- 12 test | |
EXECUTE AS USER = 'testUser'; | |
GO | |
-- that should throw the error | |
-- The SELECT permission was denied on the column 'info' of the object 'TestTable', database 'test_security', schema 'dbo'. | |
SELECT * FROM dbo.TestTable; | |
-- this should work | |
SELECT id, code FROM dbo.TestTable; | |
-- so: GRANT takes precedence over DENY | |
-- 13 switch back to admin and DENY one more time | |
REVERT | |
DENY SELECT ON dbo.TestTable TO testUser; | |
GO | |
-- 14 test again | |
EXECUTE AS USER = 'testUser'; | |
GO | |
-- that should throw the error | |
-- The SELECT permission was denied on the column 'info' of the object 'TestTable', database 'test_security', schema 'dbo'. | |
SELECT * FROM dbo.TestTable; | |
-- this should throw the error too | |
SELECT id, code FROM dbo.TestTable; | |
-- so: looks like it's important if we do GRANT or DENY first; one more test | |
-- 15 switch back to admin, clean up permissions | |
REVERT | |
REVOKE SELECT ON dbo.TestTable FROM testUser; | |
REVOKE SELECT ON dbo.TestTable(id, code) FROM testUser; | |
GO | |
-- 16 first do GRANT, then DENY | |
GRANT SELECT ON dbo.TestTable(id, code) TO testUser; | |
DENY SELECT ON dbo.TestTable TO testUser; | |
-- 17 test again | |
EXECUTE AS USER = 'testUser'; | |
GO | |
-- that should throw the error | |
-- The SELECT permission was denied on the column 'info' of the object 'TestTable', database 'test_security', schema 'dbo'. | |
SELECT * FROM dbo.TestTable; | |
-- this should throw the error too | |
SELECT id, code FROM dbo.TestTable; | |
-- 18 switch back to admin and clean up | |
REVERT | |
USE master; | |
GO | |
DROP DATABASE test_security; | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment