Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 3 June 2016

Understand Grant Execute and Grant Select Permissions on SQL Server with Example

Consider  following Scenario

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you use?
A
GRANT SELECT ON Purchases.Suppliers TO ContosoUser
B
CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
WITH EXECUTE AS USER = 'dbo'
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser
C
CREATE PROCEDURE Purchases.PurgeInactiveSuppliers
AS
DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser
D
GRANT DELETE ON Purchases.Suppliers TO ContosoUser

Solution:

see sample table


Initial properties of ContosoUser see below



Initial State we can see user cant able to select table see below of that user




let see option A

  GRANT SELECT ON dbo.Suppliers TO ContosoUser 

if issue option A ContosoUser can only select that table so it cant use to delete
so it is not correct answer

 let see option B

it gives error


 let see option C


 After Execute SP of ContosoUser it is working fine see below


so OPTION C is the correct Answer

let see OPTION D


    GRANT DELETE ON dbo.Suppliers TO ContosoUser 

after that above command if run below commands

Delete from Suppliers where IsActive=0



If you execute it will throw message it needs additional select permission

 you need select permission on that table as see in option A

so option d is not correct answer it will be correct but you need to execute both OPTION A and OPTION D concurrently.

additional info if you need to get back permission you can use below T-SQL

    REVOKE SELECT ON dbo.Suppliers TO ContosoUser 

   REVOKE DELETE ON dbo.Suppliers TO ContosoUser 

   REVOKE EXECUTE ON dbo.PurgeInactiveSuppliers TO ContosoUser 



Also ContosoUser have only execute permission so ContosoUser user can't able to alter it




 Also you can see TBK2012Reader user can able to execute storedprocedure as see below

WITH EXECUTE AS CALLER clause

CREATE PROCEDURE dbo.PurgeInactiveSuppliersWITHCaller

        WITH EXECUTE AS CALLER

        as

    DELETE FROM dbo.Suppliers WHERE IsActive = 0

    GO

        GRANT EXEC ON dbo.PurgeInactiveSuppliersWITHCaller TO [TBK2012Reader]
GO





 Now TBK2012 Can able to remove that isactive field as see below





The EXECUTE AS clause can be added to stored procedures, functions, DML triggers, DDL triggers, queues as well as a stand alone clause to change the users context.  This clause is simply added to the code as follows:
CREATE PROCEDURE dbo.TestProcedure
WITH EXECUTE AS OWNER
  
There are basically five types of impersonation that can be used:
  • SELF - the specified user is the person creating or altering the module
  • CALLER - this will take on the permissions of the current user
  • OWNER - this will take on the permissions of the owner of the module being called
  • 'user_name' - a specific user
  • 'login_name' - a specific login

 CREATE PROCEDURE dbo.TestProcedure
 WITH EXECUTE AS OWNER

AS
BEGIN
 SQL Statement
END