THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Piotr Rodak

if datepart(dw, getdate()) in (6, 7)
    use pubs;



Security – how to assign rights to database objects

A few days ago my team mate asked me to check out why one of the logins doesn’t have right to execute to a few stored procedures. Allegedly it had the right before. After a few minutes of digging in the source control it turned out that these procedures were scripted with explicit EXECUTE rights to them. However, some later updates to the code did not contain the rights scripted. Developers rarely pay too much attention to the security design of the code they write. It is not common that the code is implemented with least possible access level in mind. A classic example of this is TRUNCATE TABLE. While this is very handy command and can be very useful in certain scenarios, developers don’t realize that the user account executing it has to have some elevated rights.

I have seen many databases which implement explicit rights to objects. This is to some degree a legacy issue, as in SQL Server 2000 and before security model was completely different and more difficult to manage. The common practice at the time was to script rights to objects, and to make things simple everyone who had right to connect to database could do everything in it. Even recently I saw a table with explicit rights like this:

  1. create table dbo.sometable
  2. (
  3.     column1 int,
  4.     columnt2 char(1),
  5.     column3 varchar(255)
  6. )
  7. go
  8.  
  9. grant select on dbo.sometable to [public]
  10. grant delete on dbo.sometable to [public]
  11. grant insert on dbo.sometable to [public]
  12. grant update on dbo.sometable to [public]
  13.  
  14. go

This is definitely a bad practice. 

 

Let’s examine slightly different scenario, the one that I mentioned at the beginning of this post. Let’s analyse what options you have when you need to give user rights to execute a stored procedure but do don’t want to give rights to public role:

  1. Assign explicit rights for the user to the procedure.
  2. Assign explicit rights to a role and assign user to that role
  3. Create the procedure within a separate schema, assign user to specific role and assign rights for the schema to the role.

The above three options have their pros and cons. The first option is in most cases an aftermath of legacy design and while it is most granular, it is also hardest to manage and monitor.

Second option is also very granular, the only difference is after all that the rights are assigned to a database role. Main advantage of this solution is that you can assign more than one user to the database role and they will be automatically granted required access to the objects.

The third option requires some consideration regarding design of the database. You may need to change some code calling the procedures. You may still want to create a database role and assign rights for the schema to the role. This gives you best flexibility.

Let’s examine some of the effects each of these options brings in when implemented. I created script file that examines six scenarios you may come across in your database. This is definitely not a complete list, bear in mind.

First, let’s create test database and login that we will use to test the solutions. I will also create a sample table and stored procedure in the database.

  1. use master
  2. go
  3. create login RemoteLogin with password='Str()ngPwd', check_policy=off
  4. go
  5. create database testExecRights
  6. go
  7. use testExecRights
  8. go
  9. create user [RemoteUser] from login [RemoteLogin]
  10. go
  11. create table dbo.tTest(a int, b int)
  12. go
  13. create procedure dbo.pTestInsert(@a int, @b int)
  14. as
  15. begin
  16.     insert dbo.tTest(a, b) values(@a, @b)
  17. end
  18. go

The first scenario shows what happens when you create an user in the database, but you don’t assign any specific rights for it.

 

  1. ---Scenario 1: user has public right to the database
  2. execute as login='RemoteLogin'
  3. select suser_name()
  4. go
  5.  
  6.     begin try
  7.         ---should fail
  8.         exec dbo.pTestInsert 1, 1
  9.         return;
  10.     end try
  11.     begin catch
  12.         declare @errormsg nvarchar(2000)
  13.         set @errormsg = N'Scenario 1: Exception occured in ' + error_procedure() +
  14.             N': ' + error_message() + N' (' + convert(nvarchar, error_number()) + N')'
  15.         raiserror(@errormsg, 16, 1)
  16.     end catch

This code throws an exception, as expected:

Msg 50000, Level 16, State 1, Line 10
Scenario 1: Exception occured in pTestInsert: The EXECUTE permission was denied on the object 'pTestInsert', database 'testExecRights', schema 'dbo'. (229)

Let’s have a look what we’ll get when we assign the right to execute the stored procedure explicitly:

  1. ---Scenario 2: user has explicit right to execute the procedure
  2. ---this is executed at sa/dbo level
  3. grant execute on dbo.pTestInsert to [RemoteUser]
  4. go
  5. execute as login='RemoteLogin'
  6. select suser_name()
  7. go
  8.     begin try
  9.         ---should succeed
  10.         exec dbo.pTestInsert 1, 1
  11.         return;
  12.     end try
  13.     begin catch
  14.         declare @errormsg nvarchar(2000)
  15.         set @errormsg = N'Scenario 2: Exception occured in ' + error_procedure() + N': ' + error_message() + N' (' + convert(nvarchar, error_number()) + N')'
  16.         raiserror(@errormsg, 16, 1)
  17.     end catch
  18. go
  19. revert
  20. select suser_name()
  21. --revoke granted right
  22. revoke execute on dbo.pTestInsert to [RemoteUser]
  23. go

In line 3 in the above snippet you see the command to grant execute right on the stored procedure to the RemoteUser. Please note that the user still has no other rights – he can’t insert rows to the tTest table directly, he even can’t select from the table. Since the above snippet is part of the bigger script, at the end of it, in line 22 you can see command 'REVOKE’ to – revoke the right granted in the line 3. So, how the above code works? Yes, it works OK. It doesn’t throw exception because the permissions are right.

As I mentioned before, there were procedures which the remote login mysteriously lost ability to call. In my case, the issue was caused by a release script, which did not re-establish appropriate rights to them. There are two ways you can modify a procedure during release. One is obviously ALTER PROCEDURE, which does not change the security configuration of the procedure, the other is a sequence of DROP and CREATE statements. Scenarios 3 and 4 are shown in the below scripts.

  1. ---Scenario 3: user has explicit right to execute the procedure but the stored procedure is ALTERED
  2. --- in subsequent release
  3. go
  4. ---this is executed at sa/dbo level
  5. grant execute on dbo.pTestInsert to [RemoteUser]
  6. go
  7. go
  8. ---now ALTER the procedure
  9. alter procedure dbo.pTestInsert (@a int, @b int)
  10. as
  11. begin
  12.     insert dbo.tTest(a, b) values (@a, @a * @b)
  13. end
  14. go
  15. execute as login='RemoteLogin'
  16. select suser_name()
  17. go
  18.     begin try
  19.         ---should succeed
  20.         exec dbo.pTestInsert 1, 1
  21.         return;
  22.     end try
  23.     begin catch
  24.         declare @errormsg nvarchar(2000)
  25.         set @errormsg = N'Scenario 3: Exception occured in ' + error_procedure() + N': ' + error_message() + N' (' + convert(nvarchar, error_number()) + N')'
  26.         raiserror(@errormsg, 16, 1)
  27.     end catch
  28. go
  29. revert
  30. select suser_name()
  31.  
  32. go
  33. --revoke granted right
  34. revoke execute on dbo.pTestInsert to [RemoteUser]
  35. go

 

  1. ---Scenario 4: user has explicit right to execute the procedure but the stored procedure is
  2. ---DROPPED an CREATED in subsequent release
  3. go
  4. ---this is executed at sa/dbo level
  5. grant execute on dbo.pTestInsert to [RemoteUser]
  6. go
  7. go
  8. ---now DROP and CREATE the procedure
  9. drop procedure dbo.pTestInsert
  10. go
  11. create procedure dbo.pTestInsert (@a int, @b int)
  12. as
  13. begin
  14.     insert dbo.tTest(a, b) values (@a, @a * @b)
  15. end
  16. go
  17. execute as login='RemoteLogin'
  18. select suser_name()
  19. go
  20.     begin try
  21.         ---should fail
  22.         exec dbo.pTestInsert 1, 1
  23.         return;
  24.     end try
  25.     begin catch
  26.         declare @errormsg nvarchar(2000)
  27.         set @errormsg = N'Scenario 4: Exception occured in ' + error_procedure() + N': ' + error_message() + N' (' + convert(nvarchar, error_number()) + N')'
  28.         raiserror(@errormsg, 16, 1)
  29.     end catch
  30. go
  31. revert
  32. select suser_name()

Scenario 4 throws an exception as expected:

Msg 50000, Level 16, State 1, Line 9
Scenario 4: Exception occured in pTestInsert: The EXECUTE permission was denied on the object 'pTestInsert', database 'testExecRights', schema 'dbo'. (229)

Obviously there is an issue here. The release process must take care of the proper assignment of the rights. The problem is if the script consists of smaller parts prepared by developers, it may be virtually impossible to verify if the rights are scripted correctly in the release script. One can script all explicit permissions to objects before the release and then reconcile them with the permissions existing after the release. This also can be tricky, as sometimes the rights have to change, sometimes new objects don’t have properly scripted permissions etc. What if you have may users that need to perform certain operations on tables and views? In general, it looks like the idea of assigning rights one by one for all users and objects is bad in most scenarios. Imagine the amount of failure points if you have 3000+ objects in database and you change, say, a few hundred of them within the release. What can you do about it?

First thing that you should do is to break explicit relationship of users and objects. You can do this by creating a database role which will serve as single point of security assignments. By having several roles in the database, you can tune the security fairly easily without having to rescript permissions for all users each time you need to make a change. Scenario 5 shows such database role example:

  1. ---Scenario 5: belongs to DATABASE ROLE with explicit right to execute the procedure
  2. ---and the stored procedure is DROPPED an CREATED in subsequent release
  3. go
  4. ---this is executed at sa/dbo level
  5. create role [pTestExecRightRole]
  6. go
  7. grant execute on dbo.pTestInsert to [pTestExecRightRole]
  8. go
  9. exec sp_addrolemember 'pTestExecRightRole', 'RemoteUser'
  10. go
  11. ---now DROP and CREATE the procedure
  12. drop procedure dbo.pTestInsert
  13. go
  14. create procedure dbo.pTestInsert (@a int, @b int)
  15. as
  16. begin
  17.     insert dbo.tTest(a, b) values (@a, @a * @b)
  18. end
  19. go
  20. execute as login='RemoteLogin'
  21. select suser_name()
  22. go
  23.     begin try
  24.         ---should succeed
  25.         exec dbo.pTestInsert 1, 1
  26.         return;
  27.     end try
  28.     begin catch
  29.         declare @errormsg nvarchar(2000)
  30.         set @errormsg = N'Scenario 5: Exception occured in ' + error_procedure() + N': ' + error_message() + N' (' + convert(nvarchar, error_number()) + N')'
  31.         raiserror(@errormsg, 16, 1)
  32.     end catch
  33. go
  34. revert
  35. select suser_name()
  36. go
  37. exec sp_droprolemember 'pTestExecRightRole', 'RemoteUser'
  38. go
  39. drop role [pTestExecRightRole]
  40. go

The issue with the above is that still, when an object is dropped and recreated, the rights assigned to it are lost. The script above throws the same exception again:

Msg 50000, Level 16, State 1, Line 9
Scenario 5: Exception occured in pTestInsert: The EXECUTE permission was denied on the object 'pTestInsert', database 'testExecRights', schema 'dbo'. (229)

To solve this you have to group objects in a similar way you do it with users. While database role is used as a container for usSers, schema  serves the same purpose for database objects. Scenario 6 shows this approach.

  1. ---Scenario 6: user belongs to DATABASE ROLE with explicit right to execute on the SCHEMA
  2. ---and the stored procedure is DROPPED an CREATED in subsequent release
  3. go
  4. ---this is executed at sa/dbo level
  5. create role [pTestExecRightRole]
  6. go
  7. create schema RemoteExec authorization dbo
  8. go
  9. grant execute on schema::RemoteExec to [pTestExecRightRole]
  10. go
  11. exec sp_addrolemember 'pTestExecRightRole', 'RemoteUser'
  12. go
  13. ---now DROP and CREATE the procedure
  14. drop procedure dbo.pTestInsert
  15. go
  16. create procedure RemoteExec.pTestInsert (@a int, @b int)
  17. as
  18. begin
  19.     insert dbo.tTest(a, b) values (@a, @a * @b)
  20. end
  21. go
  22. execute as login='RemoteLogin'
  23. select suser_name()
  24. go
  25.     begin try
  26.         ---should succeed
  27.         exec RemoteExec.pTestInsert 1, 1
  28.         return;
  29.     end try
  30.     begin catch
  31.         declare @errormsg nvarchar(2000)
  32.         set @errormsg = N'Scenario 6: Exception occured in ' + error_procedure() + N': ' + error_message() + N' (' + convert(nvarchar, error_number()) + N')'
  33.         raiserror(@errormsg, 16, 1)
  34.     end catch
  35. go
  36. revert
  37. select suser_name()
  38.  
  39. go

As you see, I created schema RemoteExec in line 7. I granted explicit right to execute on all objects belonging to the schema to the database role pTestExecRightRole. Subsequently I simulate release process by dropping existing procedure dbo.pTestInsert and creating it in the schema RemoteExec. As you see, I don’t apply any explicit rights to the stored procedure. The fact that it belongs to the RemoteExec schema is sufficient for it to be callable by the user belonging to pTestExecRightRole. You can easily add and remove procedures from the RemoteExec schema and they will automatically inherit any rights you have chosen to assign to them, without any manual coding. More importantly, the rights are not tied to the objects, so you will not loose required functionality after making changes to the objects.

As I said before, this approach has its consequences. You may need to modify some of the code that calls the stored procedures to explicitly reference schema, in some cases you need to modify the code of the stored procedures. If you work with older code the most likely schema it references is an implicit dbo – object names are used without any schema part. This may be OK and resolve at runtime, but in some cases it won’t especially if you move the procedure to a different schema. It is a good practice to explicitly reference schemas in your code to avoid any confusion and problems.

You can download the test script with all scenarios and cleanup code from this location. Enjoy.

Published Sunday, April 10, 2011 11:23 AM by Piotr Rodak

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Dewraj said:

Great article! thanks Piotr.

April 11, 2011 10:05 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement