Pitching Pebbles – Using Certificates to Grant Privileges

If you fill the jar with pebbles first, you'll never have room for the big rocks.

I’m sure you’ve heard the story by Stephen Covey about the professor that fills a jar with rocks, pebbles, and sand as a metaphor for time management and prioritization. I’d relate it here, but I think this video is much more fun.

Obviously this metaphor applies to all aspects of our lives, but we can also take heed of its lesson when it comes to our job. Too often, we as DBAs are so busy handling little tasks like managing users, migrating databases from this server to that one, checking job statuses, etc., that we don’t have time to focus on bigger things, like baselining, inventorying, performance tuning, and designing disaster recovery plans. Little tasks (aka the pebbles) might not take up much time in and of themselves, but sometimes they interrupt a development process or train of thought, which also hinders productivity and creativity. I’m a big proponent of empowering developers and other IT support personnel to do certain tasks themselves. Within reason, of course. Letting them handle some of the little tasks themselves frees us up for the bigger stuff.

I have a certain application that gets requests to add or remove users fairly regularly. The original request is sent to the application support team, they forward the requests to the DBA team to create the login and database user. We notify the support team once that’s been done so that they can complete user setups within the application. I’ve already put the create and drop logic into stored procedures, so again, the actual act of creating the logins doesn’t take much time. But since it is already scripted, it’s the perfect candidate for delegating to the support team.

There were a few options for allowing non-sysadmin users the ability to run my procedures.

• Grant them admin rights directly
• Modify the script to run as an admin login, grant the support team impersonate rights to that admin login
• Sign the stored procedures with a certificate

Obviously granting admin rights directly to the support team’s logins is not the ideal approach, so I wasn’t even going to entertain that idea. I played with the idea of using EXECUTE AS in the stored procedure, but where I kept getting hung up was granting IMPERSONATE to the support team. While the risk was relatively low that someone would abuse that privilege, it was still a risk. The third option, however, was not only effective, it was also highly secure.

Signing a stored procedure with a certificate is actually a very simple process. The first thing I had to do was create the certificate itself. I’m creating mine in master, since that’s where my procedure is.

USE master;
GO
CREATE CERTIFICATE [MyAppCert]
ENCRYPTION BY PASSWORD = 'BestPassw0rdEvar!'
WITH SUBJECT = 'Certificate for signing MyApp stored procedures',
START_DATE = '2011-12-01',
EXPIRY_DATE = '2025-12-31';
GO

Once the certificate has been created, I can create a login from the certificate and make that login a member of the sysadmin server role.

CREATE LOGIN [MyAppUserCreator]
FROM CERTIFICATE [MyAppCert];
EXEC sp_addsrvrolemember 'MyAppUserCreator', 'sysadmin'
GO

According to the principal of least privilege, I should have granted ALTER ANY LOGIN to MyAppUserCreator and then granted ALTER ANY USER in the database, rather than granting sysadmin. And I considered doing that. But while it wouldn’t have been a problem in Production, in our QA and Dev environments, we sometimes have several copies of this application’s database and we’d (read: I’d) need to make sure the login had a user in all these databases with the appropriate rights. In the end I decided to keep it simple.

Ok, so the next step was to create the stored procedure, which I had already done. I’ve simplified it here.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp_create_myapp_user] (@new_user varchar(100), @passwd varchar(100))
as
begin

    declare
        @db varchar(200),
        @sqlstmt nvarchar(4000),
        @ErrorMessage NVARCHAR(4000),
        @ErrorSeverity INT,
        @ErrorState INT;

    SET @db = 'MyDatabase'

    --create login

    BEGIN TRY

        print 'Creating login ' + @new_user + '...'
        set @sqlstmt = N'CREATE LOGIN ' + @new_user + ' WITH PASSWORD = ''' + @passwd + ''' , '
                        + 'DEFAULT_DATABASE = ' + @db + ';'
        exec sp_executesql @sqlstmt
        print '... login created!'

        print 'Creating user ' + @new_user + ' in database ' + @db + '...'
        set @sqlstmt = N'USE '+ @db +'; CREATE USER '+ @new_user + ' FOR LOGIN '+ @new_user + ';'
        exec sp_executesql @sqlstmt
        print '... user created!'

        print 'Adding user ' + @new_user + ' to MyAppReader role in database ' + @db + '...'
        set @sqlstmt = N'USE '+ @db +'; EXEC sp_addrolemember ''MyAppReader'', '''+ @new_user + ''';'
        exec sp_executesql @sqlstmt
        print '... user added to role!'

    END TRY
    BEGIN CATCH

        if error_number() = 15025
        begin
            print '... login ' + @new_user + ' already exists.'
        end
        else if error_number() = 15023
        begin
            print '... user '+@new_user+' already exists.'
        end
        else
        begin
            SELECT
                @ErrorMessage = ERROR_MESSAGE(),
                @ErrorSeverity = ERROR_SEVERITY(),
                @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );

        end

    END CATCH

END
GO

Notice it’s just an ordinary stored procedure. No fancy code, no EXECUTE AS, nothing. As it stands now, even if I granted a non-admin user execute permissions on the procedure, it wouldn’t work for them, since it would be running with their privileges. To make the procedure run with sysadmin privileges, we have to sign it with the certificate we created earlier.

ADD SIGNATURE TO OBJECT::[sp_create_myapp_user]
BY CERTIFICATE [MyAppCert]
WITH PASSWORD = 'BestPassw0rdEvar!';
GO

Now it’s just a matter of granting the support team users access to the procedure.

CREATE ROLE [MYAPP_SUPPORT_ROLE] AUTHORIZATION [dbo]
GO
grant execute on sp_create_myapp_user to MYAPP_SUPPORT_ROLE
CREATE USER NonAdminUser FOR LOGIN NonAdminLogin;
exec sp_addrolemember 'MYAPP_SUPPORT_ROLE', 'NonAdminUser';

And with that I’ve allowed non-admin users to perform a specific admin-level duty, without directly granting them any elevated privileges. I hope this gets you thinking about how you can get some of the pebbles out of your jar so you can focus on the bigger rocks.

PSA:  Please don’t actually throw stones at your developers.  No matter how much you think they deserve it.

Also recommended: