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:
Post a Comment