Server level permissions for developers–why you should read books

rodak.p@gmail.com — Mon, 02 Jan 2012 03:05:00 GMT

It is quite difficult recently for me to find some time to write a new post, so I don’t seem to be leading the rankings of the most frequent bloggers. I rarely recently have opportunity to lay my hands on the code too, so natural sources of ‘inspiration’ are less often. Hopefully this will change in 2012 and I will have more opportunities to write something useful.

In a big enterprise the roles of server users are usually more distinguished than in smaller companies. You have DBAs, developers and business users. Each of these roles has generally different goals and different rights when it comes to connecting to the database servers. Large organizations tend to implement service oriented approach to processes, which means that various teams are responsible for executing various tasks ordered by other teams. In the case of developers and DBAs, the latter are responsible for all maintenance and management tasks on servers which are ‘owned’ by developers. Such organisation of work often results in sealing off developers from the maintenance related activities, such as creating databases or snapshots, running traces, viewing server state and troubleshooting blocks and deadlocks. This approach unfortunately, while imposes more order to the server utilization and prevents accidental failures, tends to slow down the development process, sometimes preventing any work from being done by significant number of developers for time anything from few minutes to few hours. This is a waste of time and money of course. In my case, development team wanted to have right to kill user sessions on development servers. You know, sometimes there is a process that takes a lot of time and locks, or there is an open transaction keeping schema locks all over the place and and the owner of it is gone for the day without committing it.

Problem is, that to be able to kill a session, you have to be a sysadmin, processadmin, or have ALTER ANY CONNECTION right granted to you at server level. Obviously, in certain environments these permissions are not the ones DBAs want to give to ~~random people~~ developers. If you GRANT ALTER ANY CONNECTION to a login, this login can kill some important sessions, not only the runaway ones. It may break audit, scheduled maintenance, backups, other teams ETL processes – just imagine your mailbox – it ain’t gonna look pretty. It would be much better to be able to grant login right to kill only spids belonging to certain users or working on certain databases. How can you eat the cake and have the cake?

I remembered that I had read about this idea in one of the books, namely Expert SQL Server 2005 Development by Adam Machanic, Lara Rubbelke and Hugo Kornelis. This is an excellent book, you should really read it if you haven’t yet done so. There is a chapter in the book, discussing the security features introduced in SQL Server 2005. These features allow architects to design more versatile and complex applications without jeopardizing security requirements. As you know, the security model in SQL Server 2005 has been completely revamped, for example the notion of the schema was separated from the ownership and database user. There are more features, one of most important probably is the ability to sign stored procedures with certificates and to create logins and users from the certificates. The discussion about security architecture and certificates is huge and going beyond scope of this post. One of the starting points you can check out is the MSDN article "Securing SQL Server”.

In short, if you were to grant server wide elevated rights to certain groups of users, how would you do it? Here’s a little shopping list:

Create a certificate
Create a login from the certificate
Grant ALTER ANY CONNECTION to the login
If you want to have the procedure in database other than master, you must copy the certificate to this database. You do it with backup-restore technique.
Create a user for the login
Create stored procedure that will issue KILL command
Sign the procedure with the certificate
Grant EXECUTE on the procedure to developers.

Here are some highlights of the solution. I attach the testing code at the end of the post as usual.

Sample code for creating certificate and the login is as follows.

 ---create certificate that the procedure will be signed with
create certificate kill_session_certificate
    encryption by password = '1410SomeReallyStrongPassword2011!'
    with subject = 'Enable KILL through procedure'
go
---create login that will be granted right to kill sessions
create login kill_login from certificate kill_session_certificate
go
 
--licence to kill
grant ALTER ANY CONNECTION to kill_login
 

This is the stored procedure developers can call to kill sessions.

 create procedure killsession(@sessionid int)
as
begin
    /* Piotr Rodak: procedure calls KILL command. Must be signed by certificate to work.*/
    print 'killsession: executing as ' + suser_name()
    declare @command varchar(300)
    set @command = 'kill ' + convert(varchar, @sessionid)
    print @command
    ---you can filter only sessions running on certain databases here.. 
    
    exec(@command)
    
    ---you can add logging here.. 
    print 'killed session ' + convert(varchar, @sessionid)
end
 

You can create the procedure in any database, but if you choose a database different than master you have to copy the certificate from master to the target database.

And here’s code to sign the procedure:

 ---sign the procedure with the certificate
add signature to killsession by certificate kill_session_certificate
with password ='1410SomeReallyStrongPassword2011!'
 

Since you are executing the command wrapped into a stored procedure, you have potential to implement additional audit, like grab execution plan or some other performance indicators of the server at the moment when the command is issued. This can be extremely helpful for troubleshooting reasons. I think this is pretty cool.

Here’s the testing script.

Enjoy.

Search results matching tags 'T-SQL', 'good practices', 'tips and tricks', and 'SQL Server 2008'

Server level permissions for developers–why you should read books