In most advance and complex databases, developers and DBAs faces double hop security issue, which usually DBAs granting the permissions to resolve the issue but actually they are putting database into high security risk.
To mitigate double hop issue, SQL Server uses certificate to sign the executable modules to make sure it has not altered by other user. It can be used to authenticate objects between servers or databases and replace ownership chaining as well (I will explain Replace Ownership Chaining in the next post.).
SQL Server can sign modules such as Stored Procedures, Functions, DML Triggers and Assemblies.
Scenario: There is an user called “John”, John has permission to execute a stored procedure called “USP_GetSales” but John does not and strictly should not have “Select” permission on the “dbo.Sales” table, due to confidentiality.
Resolution: By signing USP_GetSales stored procedure, John can execute the module without having “Select” permission on “dbo.Sales” table. To sign the stored procedure:
- Create Certificate
- Sign Stored Procedure by Certificate
- Create User from the Certificate
For mentioned scenario, below scripts can resolve the double hop issue within the database.
Best practice is to remove the private key from the certificate or asymmetric key to prevent other users sign modified module.
Author: Hamid Jabarpour Fard