Home » Blog » Permission Delegation Done Right – Preventing Cascading Revokes

Permission Delegation Done Right – Preventing Cascading Revokes

2014-02-17 - General, Security

Introduction

Over the last few weeks I have written quite a few posts about grantable grants. For example the introductory article about how GRANT ... WITH GRANT OPTION can help with security management delegation. But then there was the other article on how REVOKE CASCADE can make security management delegation questionable at best, as the CASCADE keyword is required when revoking a grantable grant. That causes all the "grant"-work that a security principal did while it held the grantable grant to be undone if the permissions needs to be removed from that principal.

Today I want to show you, how you can use grantable grants while avoiding the problems that come with the CASCADE keyword.

Using Roles for Grantable Grants

To get around the cascading problem we can use roles in combination with grantable grants. If you need a quick refresher on roles, check out my posts on database roles and server roles.

To see how roles can help alleviate the cascade problem let's look at an example. First we need a securable, a role and two principals:

[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;
ALTER ROLE TestRole1 ADD MEMBER TestUser1;
GO
CREATE SCHEMA TestSchema1;
GO
CREATE TABLE TestSchema1.tst(id INT);
INSERT INTO TestSchema1.tst VALUES(42);
[/sql]

The above code also added the principal TestUser1 as a member to the role. Now instead of granting a grantable permission to our principal directly, we are going to grant it to the role. As the principal TestUser1 is a member of that role, it inherits the grantable permission. However, as shown in Using GRANT ... WITH GRANT OPTION on roles, TestUser1 now needs to use the AS TestRole1 postfix when trying to grant the permission to another principal:

As you can see, TestUser1 is able to grant the permission to other principals. If we now need to revoke that right, we can just drop TestUser1 from the role:

After the drop, TestUser1 cannot access the table anymore (and consequently cannot grant access to it anymore either). However, TestUser2's ability to access the table was not affected.

Summary

You can use roles in conjunction with grantable grants to get the benefits of permission delegation without the disadvantages that come from the requirement to use CASCADE when revoking a grantable permission.