Home » Blog » Permission Delegation Done Right – Role Based Delegation

Permission Delegation Done Right – Role Based Delegation

2014-02-18 - General, Security

Introduction

Yesterday we looked at using roles to prevent the problem caused by cascading revokes when delegating permission management using grantable grants. Today I would like to take this a step further and show you how to use roles for permission management without requiring grantable grants at all.

Example of Role Based Permission Delegation

To try out role based permission delegation we need to first create a setup similar to the one we used yesterday:

[sql] CREATE LOGIN TestLogin1 WITH PASSWORD='********', CHECK_POLICY = OFF;
CREATE LOGIN TestLogin2 WITH PASSWORD='********', CHECK_POLICY = OFF;

CREATE USER TestUser1 FOR LOGIN TestLogin1;
CREATE USER TestUser2 FOR LOGIN TestLogin2;

CREATE ROLE TestRole1;
GO
CREATE SCHEMA TestSchema1;
GO
CREATE TABLE TestSchema1.tst(id INT);
INSERT INTO TestSchema1.tst VALUES(42);
[/sql]

We again have two users, a role and a table. But this time we are not going to add TestUser1 to the role and grant the grantable SELECT permission to the role. Instead we are going to grant just the SELECT permission to the role, not grantable. We will also grant ALTER on the role to TestUser1:

[sql] GRANT SELECT ON OBJECT::TestSchema1.tst TO TestRole1;
GRANT ALTER ON ROLE::TestRole1 TO TestUser1;
[/sql]

This does two things: First, any member of TestRole1 has now the SELECT privilege on our table. Second, because of the ALTER permission, TestUser1 can now add new members to the role and also remove current members from it. Let's see that in action:

TestUser1 was able to add TestUser2 to the role. With that TestUser2 can now successfully access the table.

Just as easy, TestUser1 can remove members from the role:

Removing TestUser2 form TestRole1 takes the SELECT privilege on the table away again.

This setup has two main advantages. The first is that revoking the ALTER permission from TestUser1 does not in any way mess with the current role members:

In this example TestUser1 first added TestUser2 back to the role. Then the ALTER privilege was revoked from TestUser1. In spite of that, TestUser2 is still a member of the role and still has access to the table.

The second advantage is that using roles makes managing groups of permissions a lot simpler. A single permission by itself is seldom adequate. A principal usually needs access to several securables and might need different types of access privileges for each securable. Instead of granting several grantable permissions to a delegate and then have the delegate grant all those permissions to every principal in need, we can just select the required permissions once and grant them to a role. From then on every principal that needs those permissions can simply be added to the role.

Summary

Today's and yesterday's post showed two different ways of using roles to allow for permission management delegation. Yesterday we granted a grantable permission to the role and used the role as a layer of separation between the delegate and the grantees. Today's method did not rely on grantable grants at all but instead showed how roles can be used to simplify permission management and allow for easy delegation. Which method is the appropriate one to use on depends on the situation, but today's solution is my favorite. It reduces permission clutter and permission duplication. Instead of granting the same set of permissions to many grantees, we need to grant those permissions only once - to the role. That alone makes permission management - and its delegation - a lot simpler.