Thursday, September 3, 2015

Granting permission on tables - individual tables or schema?

When there is a requirement for granting permission on tables for database users, common or known way of doing it is, granting explicit permission on individual objects for users. But, if database objects are schema-based objects and properly organized, then most appropriate way of managing permission is, granting permission on schema rather than granting permission on tables (or any other objects). The main reason for this is, any granted permission on a schema will by implicitly applied to all objects defined under the schema.

Example, if there are two tables called Product and ProductCategory and they have been created under Production schema, granting SELECT permission on Production schema is totally enough for granting SELECT permission to Product and ProductCategory tables.

Here is a test code;

/*
Creating a login and user called Jack
*/

USE master;
GO

CREATE LOGIN Jack WITH PASSWORD = 'Pa$$w0rd'
 , CHECK_POLICY = OFF;
GO

USE AdventureWorks2014;
GO

CREATE USER Jack FOR LOGIN Jack;
GO

-- Check and see whether Jack has permission on HumanResources schema
-- Login as Jack and see permissions
EXECUTE AS USER = 'Jack';
GO

SELECT * FROM HumanResources.Department;
GO

REVERT;
GO

-- Granting permission to the schema
GRANT SELECT ON SCHEMA::HumanResources TO Jack;
GO

-- Deny permisson on one table for testing
DENY SELECT ON HumanResources.Employee TO Jack;
GO

-- Login as Jack and see permissions
EXECUTE AS USER = 'Jack';
GO

-- Jack can executes this
SELECT * FROM HumanResources.Department;
GO

-- And this too
SELECT * FROM HumanResources.JobCandidate;
GO

-- But not this as we have explicity denied permission
SELECT * FROM HumanResources.Employee;
GO

REVERT;
GO

-- Cleaning up
DROP USER Jack;
GO
DROP LOGIN Jack;
GO


No comments: