On Adopting the Mindset of an Enterprise DBA
	The Mindset of the Enterprise DBA: Creating and Applying Standards to Our Work
	The Mindset of the Enterprise DBA: 8 Ways To Centralize Your Work
	The Mindset of the Enterprise DBA: Harnessing the Power of Automation
	The Mindset of the Enterprise DBA: Delegating Work

Introduction

At this point in the series, we’ve made tremendous progress in our journey towards adopting the mindset of the enterprise DBA. We’ve streamlined and documented our work, and used the powerful tools of automation to let machines handle as much of our job’s daily mundane tasks as possible. But even if we did this to the greatest degree possible, there will still be work that we must do manually. What about that pesky application that holds open locks at times, causing massive blocking chains to form, resulting in one of the application support personnel standing in front of our desk needing our help to find the culprit and kill it? Certainly we can’t, even if we wanted to, automate something as tricky as figuring out which process to terminate, but are we really destined to continue having to spend our time on repetitive tasks such as this?

Thankfully, the answer is no. Instead, we can delegate.

Delegation

‘Delegation’ simply means that we hand off work to others that we don’t have the time to do ourselves. It could be that we have junior members of the team who could handle some of the more straightforward work (but don’t turn them into cogs in the machine, it’s a wonderful way to kill their desire to learn). But a far better approach is what I like to call “Self Service”. As DBAs we handle a large amount of requests coming in from other groups asking us to do something; perhaps it’s setting up a user account, or taking a backup of a database before a release, or seeing what processes are running on the server that could explain the slowness a team is seeing (and subsequently killing off some long running process that’s gone into the netherworld).

As a development DBA, I found myself inundated with such requests. Because we refused to let the developers have sysadmin access (much to their dismay of course), they were required to come to us for all sorts of problems. For example, if they needed to have an ad-hoc backup taken before applying a large amount of code, they had to put in a request ticket and wait for us to take care of it. Needless to say, this led to us (well, actually me, I was the only DBA on the team) being labeled as a constant bottleneck, and perhaps not without justification. More often than not, I had to stop whatever I was doing and handle the request right away, since delay meant that we had developers sitting idle. This cut into my time for learning, planning, and creating efficiencies. Very quickly I determined I had to make a choice: either I had to give up and let most of my time be consumed by these urgent (but not important) tasks, or I had to find a way to let these folks help themselves.

Why We Fear Delegation

I asked myself, “What’s the worst that could happen if I give these folks the rights to take their own backups?”

Well, I very quickly listed two:

They could take a backup on a drive other than the designated backup space, thereby cluttering things up at a minimum and possibly causing larger problems (ever try and take a full backup of a terabyte database to your C drive?).

They could take a backup without specifying the COPY_ONLY option, thereby breaking my differential chain and making any subsequent restores difficult.

And this didn’t include a whole host of ways they might not take the backup in line with my standardized maintenance routines.

And that’s when it hit me: I was afraid of delegating my work because I would have no control over how they did it.

Call me a control freak all you want, but it’s a DBA’s job to keep their server environment running smoothly, and while I don’t have any problem letting other DBAs pitch in, we all know that it is rare to find a developer who has the same mindset and experience as a DBA. So, I quickly dismissed any idea of giving them the rights and some documentation, even had they been prepared to devote the time to learning the skills.

There is, however, a way to make some delegation possible. SQL Server’s Permission system is sophisticated enough to allow specific permissions to be used by a process for the right purpose, in such a way that it cannot be exploited for mischief.

How To Delegate Safely

When we think about it, the ability to delegate processes such as doing an ad-ho backup safely hinges on us being able to let others do their own work, but ensure that is done in the same exact way we would.

Think for a moment and let that really sink in.

You’re a good DBA, so you could well have got some good cognitive dissonance going on right now, because we’ve been burned so badly from giving people rights beyond what they needed, and developed a rock solid gatekeeper mentality on top of that. So there’s probably a little voice in your head right now, saying “Don’t do it! Anyone else will find a way to mess it up for sure!”

Stop. Read what I said again, and do it slowly.

If we can make sure beyond a doubt that we could let them do the work, but only in the exact same way we would, then what’s the harm? Picture this: we hand them a black box, with two input knobs on one side, where they enter in some parameters for the work they need to get done. Maybe they need to create a SQL Server login on a server because a legacy application requires that each new user has its own login to the database (developers, please take note, this is a terrible approach, please don’t do it). So, they input a user name, and hit the magic GO button. Then, the little box starts jumping around and making great whiz-bangy noises, puffing out smoke, wheezing, and finally spitting out a piece of paper with a note saying “Your user has been created, their new password is ‘xxxxxxxxxx’, please send this to them.”

People asking us to do work for them really don’t care about how it’s done, only that it’s completed properly and promptly. They could care less about the innards of what’s going on, so the idea of that black box really won’t scare them. As long as they get what they want, and they have control over when it happens, they’ll be happy (or at least if they aren’t you have a solid ground on which to call them unreasonable).

Now, if only there was a way we could let people do work inside SQL Server without granting explicit roles to their login to do so; some way for us to let people execute only certain pieces of code, without letting them run T-SQL code willy-nilly.

By now, this should be sounding awfully familiar.

Stored Procedures To The Rescue

If you thought to yourself, “Self! This sounds an awful lot like how we use stored procedures to let applications or users only run defined queries without letting them run amok in the data.” then congratulations, you’ve hit the nail on the head.

If you’re starting to think that I’m nuts, bear with me. If you’re starting to see the light, keep reading anyway.

If done correctly, we can use stored procedures to safely delegate our work to others in exactly the same way. Yes, things are a little more complex when doing admin level work, but going back to our automation mindset we can build tools that allow us to easily give folks the ability to get work done without bothering us at all. We could even save time by using them ourselves.

But before we get into the nuances of permissions, let’s be clear on something: you must be able to clearly define and replicate, in code, the exact steps you would normally take for this to work. If there is a weakness here, it’s that if we don’t write airtight T-SQL, problems can crop up.

Let’s take an example. For the moment, assume that when the user is executing the stored procedure, they have the rights that are necessary to execute the work; in this case, to take a backup of the database. The stored procedure takes only one input parameter, which is the name of the database to be backed up. You might think that all we need to do is construct a simple BACKUP DATABASE statement that uses the name of the database they gave. Well, we could, but what about:

Checking to make sure the user requesting the backup is allowed to back up that particular database? Maybe we want to check they are a member of some role in the given database, or perhaps we built a table containing permissions of who is allowed to backup what. We don’t want someone taking a backup of the ‘master’ database!

Defining a policy for retention of backups, so that people don’t fill up your backup volume and leave things there for all eternity. It might be simple enough to say that they’re allowed only one backup at a given time (when they take one it overwrites anything existing), but we still have to know that, and code for it.

Making sure all options necessary are specified, such as COPY_ONLY, or using whatever third party product we have installed, such as Litespeed or SQLBackup.

Making sure the database name is actually valid, and that someone isn’t either accidentally or maliciously trying to pull a ‘Bobby Tables’ on us.

As you can see, we need to be cautious and methodical when defining all the rules required. All these are possible with properly written code, but it’s important to make sure we know these upfront.

About Those Permissions

Let’s assume you have your logic down cold and the procedure coded up. Now comes the tricky part: how do we give the executor the rights to do the work in the stored procedure? You see, because a lot of the work we are trying to delegate involves T-SQL outside the scope of regular old DML (INSERT, UPDATE, DELETE, SELECT), it’s not quite as simple as granting them EXECUTE rights on the procedure and going on our way. Assuming you have a basic understanding of how ownership chaining works (and if you don’t, stop now and go read Erland Sommarskog’s excellent article on the subject, I’ve found none better), you’re also aware that only DML is in the bounds of this normal method of stored procedure permission checking. So, we can’t just give the user permission to run the stored procedure, because when the line that actually runs the BACKUP DATABASE command executes, SQL is going to realize that they don’t have DBO or BACKUP DATABASE rights and halt the action.

So what’s a DBA to do? We can’t give them explicit rights to backup the database, because then they could circumvent our stored procedure and do things as they please.

Thankfully, there are two obvious ways we can get around this issue without doing anything complicated. One is easier, but comes with some problems, while the other is a little harder to setup but is more robust in the long run.

EXECUTE AS

First, we can use the EXECUTE AS clause in defining our stored procedures. What this does is tell SQL that as soon as we start running this code, we want to switch context to the user specified for the duration of the stored procedure. In other words, if we specify something like “EXECUTE AS DBO”, then all code in that stored procedure will execute as if the user was, in fact, the ‘DBO’ user. This means that we can effectively grant additional rights and privileges without giving them to the user directly.

Mind you, using “EXECUTE AS DBO” is probably overkill. Instead we should create a user without login, with only the minimum necessary privileges required to complete the work. For example, if we are trying to allow a user to back up a database, give them the BACKUP DATABASE right. This way we are still following the principle of least privilege. We can also allow for server level work using the EXECUTE AS OWNER clause, as well as setting the database’s TRUSTWORTHY bit. This allows the code to execute server level operations in the context of the login that owns the database (and all privileges it has).

However, this approach has some pretty significant downsides. For example, if our code uses the standard “USERNAME” or “SUSERSNAME” functions, it will return the name of the user defined in the “EXECUTE AS” clause (or in the case of the second function, the name of the server level principal associated with the user). Instead, we must remember to use the ORIGINAL_LOGIN function, which will return the name of the original calling user. In addition, we must be very careful in controlling access to the database where we create this code if we choose to set the TRUSTWORTHY bit to on. This is because once that bit it set, any and all code residing in that database is explicitly trusted and can access resources outside the database. For example, a malicious user could create a stored procedure with “EXECUTE AS OWNER”, and if the database is owned by the ‘sa’ principal, execute any code they please as the sysadmin user. So if you go this route, my suggestion would be to make sure only DBAs have access to create or modify objects in the database, and don’t accept user written code (at least without thoroughly reviewing it).

Certificate Signing

The other way we can enable the indirect granting of higher privileges is through what’s known as code signing. Basically, we create a stored procedure, cryptographically sign it with a certificate, then create a user or login from the certificate and grant it the rights it needs. When a user executes the procedure, any code that is contained within will execute with the additional rights of the certificate user, without any kind of the context switching we see when using EXECUTE AS. This means that we can still use the same USER_NAME or SUSER_SNAME functions and we will still get the actual user name, not the “proxy” user name. In addition, there’s no need for the TRUSTWORTHY bit to be set to enable server level rights; instead any code that is signed is trusted.

Here again, be cautious about only granting the rights truly required. If we only need database level rights, then a user can be created and rights assigned. If we need server level rights, then we can create a login and give it those rights.

In SQL Server 2012 and up, copying the certificates from database to database is easier, as you can use the CREATE CERTIFICATE…FROM BINARY syntax to recreate the certificates without backing them up.

This approach isn’t without its own caveats and disadvantages, which we’ll now explore.

For example, the behavior of code signing is a little finicky at times, such as when using nested stored procedure calls. If I execute stored procedure A, which is signed, and it in turn executes stored procedure B, which is not signed, then any privileges that come from the certificate user disappear. This can be resolved however, using something called “counter signing”. Basically, we add a special kind of signature to the second stored procedure, using the ADD COUNTER SIGNATURE statement. After running this, when the counter signed procedure is executed in a nested context, and the parent caller is signed, then the certificate rights still apply. Naturally we could also simply sign the child procedure in the same way as the parent, which would mean that certificate rights still apply whenever it is executed in any way, regardless of the call tree.

In addition, any time a stored procedure is altered, the signature must be reapplied (though in reality that means just adding one more line to the script used to generate the procedure; you are using version controlled scripts, right?). You could also handle signing using metadata, such as knowing that all objects in a given schema require signing, and having code that finds all qualifying objects and signs them.

Finally, we must be careful to ensure that only authorized personnel have access to the signing certificate. Otherwise, they could create a malicious procedure, sign it, and execute it with all the rights granted to the certificate login. This is still better than the case of having a TRUSTWORTHY database, since, for example, we can still allow others to create procedures within the database without letting them have control of the certificate.

Leveraging Community Resources

When planning for delegation of DBA duties, the maintenance of the permissions can often be a bit of work, especially if they are complex. Fortunately, there is an existing open source project to consider when applying the principles of delegation: the SQL Server Separation Of Duties Framework. Written by Laura Rubbelke, the framework makes it very easy to setup certificate signed procedures for delegating work. Intended for achieving separation of duty for DBAs (i.e. when we can’t allow DBAs to have sysdamin rights), it works equally well for delegating DBA level work to others. Basically, you place scripts for stored procedures in a folder in the installation package, and run a Powershell script to complete the installation. After prompting you for some information, the script will take care of all the certificate creation(s), granting of rights, and signing of the procedures. All you need to do is add a user to a role and they can execute the signed procedures, allowing them to carry out their work without bothering you.

That’s not to say you shouldn’t roll your own solution if you are so inclined. While I loved this framework, I chose to create my own for the purposes of learning about the nuances of code signing and permission delegation. It was a valuable exercise as I now feel quite comfortable in trusting my delegation system, having built it from the ground up myself. And trust is key, since we are allowing others to carry out actions with elevated permissions. Still, consider carefully the amount of time building your own will take and look hard at the existing community solutions. Often times the return on investment of building from scratch is far less and may not be worth it.

Conclusion

Delegation is key in freeing up the last bits of our time, so that we can pursue meaningful work as a DBA. While the thought of letting others do work for us may be frightening, especially when those doing the work are not DBAs, we must learn to let go of that fear. Once we recognize that we can allow others to complete administrative tasks without jeopardizing the health and security of our systems, we are free to enable a more self-service-centric model of operation. Certainly there may be some tasks we decide to keep for ourselves, and that’s fine, as long as it’s done intentionally and after careful consideration. Simply put: Don’t let irrational the fear of releasing control stop you from taking steps to maximize your freedom with your time at work.

A Final Note

We’ve now come to the end of our series on adopting the mindset of the enterprise DBA. We’ve learned how to standardize work, how to keep things in as few places as possible, how to automate our work on a daily basis, and finally how to let others do work for us. All four of these principles are key in taking your career as a DBA to the next level; without them you’ll likely find yourself mired in daily busy work, with little time for learning or more important / less urgent work. Thankfully with practice and deliberate action, anyone can embrace these concepts. I hope you have enjoyed the series, and would love to hear how you are working these ideas into your daily routines. Share them in the comments, or drop me a line at “josh at awanderingmind dot com”. And never stop moving yourself forward in your journey towards adopting the mindset of the enterprise DBA.