How to grant permissions that a user's role has the grant option for?

Question

I'm setting up a secure login system for a new version of my company's software directly on SQL Server 2017. I'm trying to have user account's CONNECT permission to any databases revoked until the users login to an initial authentication database with a set username/password, and use a login procedure to enable their account on a specific database, to which the client would then disconnect from the initial database and reconnect using their personal account, connecting to their target database.

A certificate was created in the initial database, and used to sign a procedure that calls the one shown below. The procedure shown below is not signed by any certificates. The certificate that was created was copied to the target database, and used to create a user, who was then added to a group, Certified, that has permissions to execute the procedure shown below, and has CONNECT WITH GRANT OPTION on the database. When the procedure below gets executed, the grant/revoke statement fails, saying that the grantor doesn't have the necessary permissions (or that the user/role was not found, depending on if I try to add an AS clause)

The granting/revoking of the connect permission is done through dynamic SQL in a stored procedure, in the target database (every database that's part of our software will have an identical procedure to do this job). It works if I run the code as a member of the sysadmin fixed role, but not when I am running it with my set authentication account. The procedure in the target database (ModifyUser) is called from another procedure in the initial database that is signed with a certificate, to which there is a user from the same certificate in the target database that is a member of the role Certified that has been granted CONNECT WITH GRANT OPTION, however whenever I run the procedure, the statement fails.

I've tried 3 versions, changing the content of the AS clause:

1. AS the certificate user, who is part of the database role, uCompCompID
2. AS the database role, that the certificate user is a part of, Certified
   • SQL Server documentation doesn't seem clear about which of these should be used when trying to grant a permission that a role has the grant option for
3. With no AS clause, so as to grant the permission as the user running the dynamic SQL
   • This seems to run the query as the authentication user, not the certificate user that is a member of the role that has the grant option for CONNECT

The ModifyUser procedure is short, so I'll include the whole thing below. This procedure is stored in the same database as the user I want to grant/revoke the CONNECT permission to, but is called for a different database signed by a certificate that matches a user created in this same database.

This is version 3, where there is no AS clause.

PROCEDURE [Authorization].[ModifyUser]
    @user nvarchar(128),
    @status bit
AS
BEGIN
    SET NOCOUNT ON

    IF @user IS NULL
        THROW 50002, 'Invalid argument: @user', 0
    IF LTRIM(RTRIM(@user)) = ''
        THROW 50002, 'Invalid argument: @user', 1

    IF @status IS NULL
        THROW 50002, 'Invalid argument: @status', 0

    DECLARE @statement nvarchar(200)
    IF @status = 1
        SET @statement = 'GRANT CONNECT TO ' + @user
    ELSE
        SET @statement = 'REVOKE CONNECT TO ' + @user

    EXEC (@statement)
END

The expected result would be that the CONNECT permission on the target user is changed, but the received result is always an error. The exact error depends on the version used:

1. Msg 15151, Level 16, State 1, Line 2 Cannot find the user 'uCompCompID', because it does not exist or you do not have permission.
2. Msg 15151, Level 16, State 1, Line 2 Cannot find the user 'Certified', because it does not exist or you do not have permission.
3. Msg 4613, Level 16, State 1, Line 2 Grantor does not have GRANT permission.

This is not an issue if I grant the permissions directly on the certificate user. However, I would like to keep the permissions in a role so that when I inevitably need to recreate the certificate after modifying one of the procedures involved in this system, I only need to worry about adding the new certificate user to the appropriate group, instead of needing to grant permissions to the recreated user every time a change is made that requires resigning any of the certificates.

Answer 1

First of all you should update your question because it's not clear in what databases did you create a cerificate and which procedure is signed an which is not:

The procedure in the target database (ModifyUser) is called from another procedure in the initial database that is signed with a certificate, to which there is a user from the same certificate in the target database that is a member of the role Certified that has been granted CONNECT WITH GRANT OPTION

From this it seems that only the procedure in the initial database is signed with a certificate, but the inner procedure (in the target database) is not, in this case only the outer procedure has permissions granted to a user created from certificate, that's why you get error N3

Grantor does not have GRANT permission

In other cases you get your error because you use execute as clause (this clause admits only user, not login!). When you use it, your proc is sandboxed within the database where it the procedure created, i.e. you cannot do nothing in another database even if you are sysadmin, server just cannot use the corresponding login (to search the corresponding user in other database) unless the database is trustworthy and in't owner has authenticate in another database.

Answer 2

I've resolved this issue for my case. To go to the solution, skip ahead to the horizontal line break.

To clarify my setup if my original post didn't do a good job, I have a database, Initial, that created a certificate, cCompCompID, which was used to sign a procedure in that same database. The certificate was then copied to another database, Target, where it was used to create a user, uCompCompID, that was then added to the role Certified. The role was granted permission to execute ModifyUser, as well as CONNECT WITH GRANT OPTION so that ModifyUser could be used to change the connect permission on other users, but only when called from the signed procedure in Initial (this is a property of Module Signing, which I am using to keep the system as secure as possible from unintended access to these procedures).

I wanted to keep the permissions to the role Certified and not on the user, because I know I'll need to modify the procedures in the future, and that would require the procedure in Initial be resigned, and I would need to create a new certificate to sign the procedure, and then copy the new certificate to the Target database, drop and recreate the user, and then I'd need to worry about granting the right permissions again. Keeping the permissions on the role simplifies the process. The certificate needs to be recreated, because everywhere I've seen, the recommended course of action is t drop the private key from certificates once use of it is done so as to prevent unintended use of them later.

---

The correct way to grant a permission that a role has the GRANT OPTION for is to use AS <role name> at the end of the grant statement. The reason this was not working correctly in case 2 in my original post is explained by the documentation on granting permissions on a database with an AS clause. When granting, revoking, or denying a permission as a database role, the user executing the statement must have:

ALTER permission on the role, membership in the db_securityadmin fixed database role, membership in the db_owner fixed database role, or membership in the sysadmin fixed server role.

The resolution to my issue is to grant the certificate user, uCompCompID ALTER permission on the Certified role, so that it will be able to grant or revoke the permission by using its role membership. Granting the user this permission does not pose a security threat, because ModifyUser is only granted the permissions granted to uCompCompID when it is called from the procedure in Initial that was signed by the same certificate that uCompCompID was created from. It will not have these permissions if it were invoked directly, or by any procedure not signed by that certificate, thanks to Module Signing (And since the private key is dropped from the certificate once the procedure in Initial is signed, there is no risk of it being signed to anything else to create a security threat without a much bigger hole elsewhere)

As @sepupic points out, it is also necessary for me to sign ModifyUser with the same certificate for the permissions to work. ModifyUser originally has its permissions because it was called from a different procedure that was signed with the certificate. When ModifyUser executes dynamic SQL without being signed, these extra permissions are removed until the dynamic SQL is completed. If ModifyUser is signed with the same certificate, then the dynamic SQL executed by ModifyUser will retain the permissions that were expected and required.

Thank you @BenThul and @sepupic for your help.