Applying security on a column set
Security can be applied to a column set just like applying security to other columns, but permissions on sparse columns may affect the data retrieval from the column set. Let’s do some testing on.
First, let’s grant SELECT permission to all sparse columns and try to retrieve data from the column set. You need to have a separate account for this test. If you do not have an additional account, create a login and a user as User1. Let us try to retrieve data with User1 permissions.
Code 1: Retrieving and updating data with User1’s account
— Set the execution context to the user User1
EXECUTE AS USER = ‘User1’
— select statement 1
SELECT Gender, Telephone, MonthlyIncome, Comments FROM Customers
— select statement 2
SELECT AllSparseColumns FROM Customers
— select statement 3
UPDATE dbo.Customers
SET Gender = 1
WHERE Id = 3
— select statement 4
UPDATE dbo.Customers
SET [AllSparseColumns] = ‘<Telephone>777225656</Telephone><Comments>Test msg</Comments><Gender>1</Gender>’
WHERE Id = 3
REVERT
Code 2: Granting SELECT permission on sparse columns to User1 and execute Code 1
— Grant select permission to all sparse columns
GRANT SELECT (Gender, Telephone, MonthlyIncome, Comments) ON OBJECT::dbo.Customers TO User1
— Execute the code 1:
— select statement 1 – will success
— select statement 2 – will fail
— select statement 3 – will fail
— select statement 4 – will fail
— Remove SELECT permission from User1
REVOKE SELECT (Id, Gender, Telephone, MonthlyIncome, Comments) ON OBJECT::dbo.Customers TO User1
Though we have granted SELECT permission to all sparse columns, users cannot retrieve data from the column set. It requires explicit SELECT permission. But if we grant both SELECT and UPDATE permissions on sparse columns, User1 will be able to access the column set. But User1 will not be able to update the column set.
Code 3: Granting SELECT and UPDATE permission on sparse columns to User1 and execute Code 1
— Grant select permission to all sparse columns
GRANT SELECT, UPDATE (Gender, Telephone, MonthlyIncome, Comments) ON OBJECT::dbo.Customers TO User1
— Execute the code 1
— select statement 1 – will success
— select statement 2 – will success
— update statement 3 – will success
— update statement 4 – will fail
— Remove SELECT, and UPDATE permissions from User1
REVOKE SELECT, UPDATE (Id, Gender, Telephone, MonthlyIncome, Comments) ON OBJECT::dbo.Customers TO User1
Now let us grant SELECT on the column set and try to access sparse columns.
Code 4: Granting SELECT permission on the column set to User1 and execute Code 1
— Grant select permission to the column set
GRANT SELECT (AllSparseColumns) ON OBJECT::dbo.Customers TO User1
— Execute the code 1
— select statement 1 – will fail
— select statement 2 – will success
— update statement 3 – will fail
— update statement 4 – will fail
— Remove SELECT permission from User1
REVOKE SELECT (AllSparseColumnss) ON OBJECT::dbo.Customers TO User1
Just like the code in Code 3, if we grant both SELECT and UPDATE permissions on column set to User1, SELECT statement 2 will be successful. In addition to that, User1 will be able to execute the UPDATE statement on the column set but not on the sparse columns. See the Code 5.
Code 5: Granting SELECT and UPDATE permissions on the column set to User1 and execute Code 1
— Grant select and update permissions to the column set
GRANT SELECT, UPDATE (AllSparseColumns) ON OBJECT::dbo.Customers TO User1
— Execute the code 1
— select statement 1 – will success
— select statement 2 – will success
— update statement 3 – will fail
— update statement 4 – will success
— Remove SELECT and UPDATE permission from User1
REVOKE SELECT, UPDATE (AllSparseColumnss) ON OBJECT::dbo.Customers TO User1
Now let us test how DENY permissions are propagated. Let us grant SELECT permission on the sparse column and deny SELECT on column set. As you expect, User1 will be able to access all sparse columns not the column set. Deny SELECT permission on the column set has NO effect no sparse columns.
Code 6: Granting SELECT permissions on sparse columns and denying SELECT on the column set to User1 and execute Code 1
— Grant SELECT permission on sparse columns
GRANT SELECT (Id, Gender, Telephone, MonthlyIncome, Comments) ON OBJECT::dbo.Customers TO User1
— Deny SELECT permission on the column set
DENY SELECT (AllSparseColumns) ON OBJECT::dbo.Customers TO User1
— Execute the code 1
— select statement 1 – will success
— select statement 2 – will fail
— update statement 3 – will fail
— update statement 4 – will fail
REVOKE ALL ON OBJECT::dbo.Customers TO User1
GO
But when the SELECT permission on sparse columns is denied, it propagates to the column set. See code 7. User1 will not be able to access the column set even though we have granted SELECT permission on the column set.
Code 7: Denying SELECT permissions on sparse columns and granting SELECT on the column set to User1 and execute Code 1
— Deny SELECT permission on sparse columns
DENY SELECT (Id, Gender, Telephone, MonthlyIncome, Comments) ON OBJECT::dbo.Customers TO User1
— Grant SELECT permission on the column set
GRANT SELECT (AllSparseColumns) ON OBJECT::dbo.Customers TO User1
— Execute the code 1
— select statement 1 – will fail
— select statement 2 – will fail
— update statement 3 – will fail
— update statement 4 – will fail
REVOKE ALL ON OBJECT::dbo.Customers TO User1
GO
Hope you learnt something from this. I would appreciate your comments on this article, please send your comments to dinesh@sqlserveruniverse.com.
]]>