(part 3 of my series of articles on security principles in Microsoft SQL Servers & Databases)
Security concept: Delegation of Authority
To be precise from the beginning: Delegation is a process or concept rather than a principle. But it is a particularly useful practice to keep in mind when designing any security concept and it is strongly connected to the discussed security principles like the Principle of Least Privilege and Separation of Duties as you will find out.
So, what is it about? Delegation (of authority) is the process to pass on certain permissions to other users, often temporarily, without raising their overall privileges to the same level as the delegating account.
There are two slightly different approaches. Delegation can be done either..
1) at the Identity level: allowing Identity A to be used utilized by identity B. Here B assumes A’s identity and powers/permissions. Examples in Operating Systems are the “Runas” and “sudo”-commands in Windows, respectively Unix/Linux.
Or
2) it can be done at the Authorization level: essentially by passing on a specified set of permissions via a form of Role Based Access Control (RBAC*) to another identity.
When you look at two-tier application architectures and the authentication flows, you will probably notice that delegation is somewhat a common pattern.
OAuth and on-behalf-of token for authentication
For example, OAuth is an open standard for access delegation which is also utilized in Azure Active Directory (AAD). Using the OAuth 2.0 On-Behalf-Of flow (OBO) applications can invoke a service API, which in turn needs to call another service API - like SQL Server.
Instead of using the invoking application’s identity, the delegated user’s identity (and permissions) is propagated through the request chain. To do this, the second-tier application authenticates to the resource for example a SQL database, with its own token, a so called “on-behalf-of” token that originated from the first-tier application..
This is an example of delegation at identity level.
Delegation of Authority in the SQL realm
In SQL Server there are multiple options available to implement Delegation.
Azure AD User’s creation
One scenario where delegation is being used under the covers is the following:
Assume, an AAD principal, such as the AAD admin account for Azure SQL, wants to create an AAD user in the database (statement: CREATE USER <AADPrincipalName> FROM EXTERNAL PROVIDER).
In this case, the managed service identity (MSI) which is assigned to Azure SQL server is required. Using this MSI, Azure SQL server sends the information about the AAD user which the AAD admin account wants to create as a user inside SQL to AAD graph (in future: MS graph) for verification. Therefore, the MSI (and not the AAD admin account) requires the proper permission in Azure AD (such as the “Directory Readers” role).
GRANT WITH GRANT OPTION
This one may be a less widely known possibility in SQL: it is possible to Grant a permission to Users and to allow them to pass on this permission. This is what the “WITH GRANT OPTION” of the GRANT statement is for.
In the below example, a User Shakti has been granted the various privileges, INSERT, UPDATE, DELETE and SELECT a Schema named “Sales”. On top of that, by using the WITH GRANT OPTION, she is allowed to pass on those permissions.
This is demoed by impersonating her account using the EXECUTE AS clause (btw: this is also an example of using delegation at the identity level) and now “being” Shakti we can grant permissions to anyone else, Jiao in this case.
EXECUTE AS and privilege bracketing for temporarily delegating permissions
A powerful and commonly used technique is the possibility to run stored procedures under a separate user account for just the task at hand, no matter who the original caller is, and by that means use the privileges of the impersonated account for the runtime of the stored procedure.
This is typically used to delegate tasks that otherwise require high privileges in SQL Server and by that helps to maintain the Principle of Least Privilege.
Strictly speaking, the delegation happens when access to the stored procedure is granted. The use of a stored procedure is technically a separate concept, referred to as “privilege bracketing” which is somewhat in the same space as just-in-time privileges (JIT). JIT is yet another technique which allows the use of certain elevated permissions for a certain period of time (aka “time bound”) only.
Privileged Identity Management in Azure offers these capabilities: What is Privileged Identity Management? - Azure AD | Microsoft Docs
See the below example: ALTER is the minimal (“least”) permission necessary to Update Statistics on tables. But instead of granting ALTER on each table or the whole database, Jiao will only get permission to run this stored procedure which in turn runs under elevated permissions that are assumed for the runtime of the procedure only.
Note
If you are auditing User’s activities (which in general you should, but this is another topic coming up), you need to be aware that since Impersonation has the effect that the “current user” is not the actual User who is acting, looking only at Server or Database Principal ID’s does not give the right picture. Luckily, SQL Auditing by design always captures the session_server_principal_name. This contains the name of the principal who is connected to the instance of SQL Server originally, no matter how many levels of impersonations are done. This is the same like using the SQL Function ORIGINAL_LOGIN(), which you should use when implementing custom Logging solutions.
Signing Modules for temporarily delegating permissions
There is an alternative option when using modules for delegation to EXECUTE AS: signing the module. In SQL Server, modules (such as stored procedures, functions and triggers) can be signed with an asymmetric key or a certificate (technically just another form of asymmetric key).
The trick is that this key or certificate can be mapped to a database user. And when the module is executed, and the signature has been verified, the module will inherit the permissions of the mapped user. And here is the difference to the EXECUTE AS-clause: the permissions of the certificate-mapped user will be added to the permissions of the original caller, not replace them. This is because the execution context actually does not change. Which leads to the second big difference: All the built-in functions that return login and user names return the name of the caller, not the certificate user name.
In the resources you will find a couple of links with various examples.
Here is a diagram of how this can be used in a simple example:
In this example, Ben alone only has SELECT on the table Orders, but does not have access to the OrderDetails-tables. Instead, the intention is, that he can only access this table by using the specifically prepared stored procedure ProcAccountforInternalCalc.
Only at runtime of this procedure, the permissions of the original caller, Ben, are extended with the permissions of another user, ProcAccountforInternalCalc, which has been specifically created for this use-case: to grant the SELECT permission on the table OrderDetails only when the stored procedure sel_OrderDetails_with_margin is being used. For that, this user is mapped to a certificate, Cert
_ProcAccountforInternalCalc, which has been used to sign the stored procedure.
Now, anyone who has permission to execute this procedure, will inherit the additional permissions of ProcAccountforInternalCalc and can then see the data from the table – using the business logic from the procedure only.
Note
Module signing should only ever be used to GRANT permissions, and not be used as a mechanism to enforce DENY, let alone REVOKE permissions.
Most SQL Server environments I have seen already use these concepts one way or the other. Delegation is an immensely powerful and useful technique: As you may realize from the last two examples, those techniques can also help ensuring the Principle of Least Privilege is adhered to. And it also can help implement Separation of Duties, which is the reason to talk about it specifically within the context of the general Security Principles.
The T-SQL code-examples are available as a download below this article.
Happy delegating
Andreas
Thank you to my Reviewer:
Mirek Sztajno, Senior Program Manager in SQL Security and expert in Authentication
Resources
- OAuth - Wikipedia
- Microsoft identity platform and OAuth2.0 On-Behalf-Of flow - Microsoft identity platform | Microsoft...
- Using Azure Active Directory with the ODBC Driver - SQL Server | Microsoft Docs
- Azure Active Directory service principal with Azure SQL - Azure SQL Database | Microsoft Docs
- Create Azure AD users using service principals - Azure SQL Database | Microsoft Docs
- Using Azure AD Privileged Identity Management for elevated access
- Azure role-based access control (Azure RBAC) and Privileged Identity Management - Azure Australia | ...
- GRANT WITH GRANT OPTION
- EXECUTE AS Clause
- ORIGINAL_LOGIN (Transact-SQL) - SQL Server | Microsoft Docs
- ADD SIGNATURE (Transact-SQL) - SQL Server | Microsoft Docs
- Signing Stored Procedures in SQL Server - ADO.NET | Microsoft Docs
- Module Signing (Database Engine) | Microsoft Docs
- Tutorial: Signing Stored Procedures with a Certificate - SQL Server | Microsoft Docs
- Wikipedia-Article on Delegation: https://en.wikipedia.org/wiki/Delegation_(computer_security)
- Wikipedia-Article on Privilege bracketing: https://en.wikipedia.org/wiki/Privilege_bracketing
- Article by a Community member and MVP on Packaging Permissions in Stored Procedures http://www.sommarskog.se/grantperm.html