How to grant permissions to developers to grant permissions to users?

Question

Is there a way I can give developers permission to grant a user permissions over objects without giving them the option to create users or functions?

I'm trying to limit developers permissions, I recently found out that developers had db_owner permissions in dev and prod environments! So I'm doing my best to stop this madness.

Any good article about this matter?

Answer 1

I've found that the most dangerous aspect of the db_owner role is that if you issue a deny on a permissions, then the members of the role can grant it back to themselves. I've just started reading about this and I'm testing this

Create role db_ControlDatabase

grant control to db_ControlDatabase

deny backup database to db_ControleDatabase

alter role db_ControlDatabase add member TestUser

So far, I've found that the subject TestUser has permissions without being able to add or remove members of the fixed database roles. You should be able to deny whatever you need at this point like backup certificate, backup master key, etc.

Here is a list of permissions that can be denied or granted:

Answer 2

Setting permission on objects like stored procedures can be accomplished with "GRANT EXECUTE ON . to ;

However, you may also want to grant security rights at both the login and user level. You will want to determine and grant ONLY the necessary rights for the objects that require access (such as execution). Consider use of the "EXECUTE AS" capability which enables impersonation of another user to validate permissions that are required to execute the code WITHOUT having to grant all of the necessary rights to all of the underlying objects (e.g. tables). The EXECUTE AS can be added to stored procs, functions, triggers, etc.

Add to the code as follows right within the Stored Procedure: CREATE PROCEDURE dbo.MyProcedure WITH EXECUTE AS OWNER

In this case you are impersonating the owner of the module being called. You can also impersonate SELF, OR the user creating or altering the module OR... imperonate CALLER , which will enable to module to take on the permissionsof the current user, OR... impersonate OWNER, which will take on the permission of the owner of the procedure being called OR... impersonate 'user_name', which will impersonate a specific user OR... impersonate 'login_name' with will impersonate a specific login.

MOST of the time, you will only need to grant EXECUTE rights to stored procs and then rights are granted to all objects referenced within the stored proc.

In this way, you DO NO need to give implicit rights (example: to update data or call additional procs). Ownership chaining handles this for you. This is especially helpful for dynamic sql or if you need to create elevated security tasks such as CREATE TABLE. EXECUTE AS is a handy tool to consider for these.

This example may help clarify all of this:

Create a user called NoPrivUser with public access to a database (e.g. dbadb)

USE [master] GO CREATE LOGIN [NoPrivUser] WITH PASSWORD=N'ABC5%', DEFAULT_DATABASE=[dbadb], CHECK_EXPIRATION=ON, CHECK_POLICY=ON GO USE [DBAdb] GO CREATE USER [NoPrivUser] FOR LOGIN [NoPrivUser] GO

NOTE: CREATOR OR OWNER OF THIS PROCEDURE WILL REQUIRE CREATE TABLE RIGHTS within the target database.

use DBAdb go CREATE PROCEDURE dbo.MyProcedure WITH EXECUTE AS OWNER AS IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].MyTable') AND type in (N'U')) CREATE TABLE MyTable (PKid int, column1 char(10)) INSERT INTO MyTable VALUES (1,'ABCDEF')

GO

GRANT EXEC ON dbo.MyProcedure TO NoPrivUser; GO

-- Now log into your database server as NoPrivUser and run the following.

use dbadb go

EXEC dbo.MyProcedure

(1 row(s) affected)

Now try to select from the new table while logged on as NoPrivuser.

You will get the following:

select * from MyTable go

Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object 'MyTable', database 'DBAdb', schema 'dbo'.

That is expected since you only ran the procedure under the security context of Owner while logged on as NoPrivUser. NoPrivUser as no rights to actually read the table. Just to execute the procedure which creates and inserts the rows.

With the EXECUTE AS clause the stored procedure is run under the context of the object owner. This code successfully creates dbo.MyTable and rows are inserted successfully. In this example, the user "NoPrivUser" has absolutey no granted rights to modify the table, or read or modify any of the data in this table. It only takes on the rights needed to complete this specific task coded WITHIN the context of this procedure.

This method of creating stored procedures that can perform tasks that require elevated security rights without permanently assigning those rights come be very useful.

Answer 3

Owners of objects can grant permissions on those objects. Provided your developers don't need to grant things like CREATE TABLE rights, you might be able to give them ownership of the objects that you want them to grant permission on.

Answer 4

As said, if someone could hand out permissions, they could hand out permissions to themselves (or a dummy account). I'm not sure if there is a trick in SQL Server to provide "give user permissions less then me".

The way I would do it is with stored procedures.

Create a stored procedure that gives a specified user a specific right or set of rights (those rights are the ones that regular users are allowed to have). Then give the developers execute access to this stored procedure. In effect you use stored procedures to create a limited version of GRANT, while keeping the full GRANT command to yourself.

Answer 5

As Stefan said, giving them grant permissions would effectively give them all permissions, since if they want to do something all they have to do is grant themselves the permissions to do it.

Rather than considering the developers the enemy, though, you may want to consider giving the developers a second user account that's used to administer the database. It's pretty common not to give developers ANY permissions to production, at least on their development account.

Answer 6

You can make them members of the "db_securityadmin" database role

Answer 7

If someone can give someone else permissions, he can also give himself the permission to do what he wants. So what is this good for? Probably I don't understand your situation.