Home » Blog » The Unexpected Security Implications for Role Owners

The Unexpected Security Implications for Role Owners

2014-03-03 - General, Security, Security Pitfalls

Introduction

On Friday, I wrote about the ownership concept. I demonstrated how to change the owner on a securable and that the owner has unrestricted access to its securables. One of the securable types that allow an owner to be specified is the database role. As with any other securable, the owner of a role has unrestricted access to that role. However, what exactly does that mean, and are there any hidden implications that you might not expect at first?

Role Ownership Example

To see what the security implications are, we need to first create a database role. To start out, create two database users with this statement batch:

[sql] CREATE LOGIN TestLogin1 WITH PASSWORD='********',CHECK_POLICY=OFF;
CREATE USER TestUser1 FROM LOGIN TestLogin1;
CREATE LOGIN TestLogin2 WITH PASSWORD='********',CHECK_POLICY=OFF;
CREATE USER TestUser2 FROM LOGIN TestLogin2;
[/sql]

With the users in place, executing the following two statements will create the role TestRole1, set TestUser1 as the owner and then add TestUser2 as role member to it.

[sql] CREATE ROLE TestRole1 AUTHORIZATION TestUser1;
ALTER ROLE TestRole1 ADD MEMBER TestUser2;
[/sql]

Finally, we also need a securable. A schema and table should do:

[sql] CREATE SCHEMA TestSchema1;
GO
CREATE TABLE TestSchema1.tst(id INT);
GRANT SELECT ON SCHEMA::TestSchema1 TO TestRole1;
[/sql]

Just to confirm that we did not cause any unexpected ownership changes on those two objects, let us execute this query:

[sql] SELECT T.name AS table_name,USER_NAME(T.principal_id) AS table_owner_name,s.name AS schema_name,USER_NAME(S.principal_id) AS schema_owner_name
FROM sys.tables AS T
JOIN sys.schemas AS S
ON T.schema_id = S.schema_id
WHERE T.name = 'tst';
[/sql]

That query produces this result, showing that the schema and the table are both owned by dbo.

Remember, a NULL principal_id on the table means that the table is owned by the schema owner.

Now let use actually look into the implied permissions that TestUser1 might have attained through the ownership of TestRole1. First we should check if TestUser1 is now considered a member of the role. We know, a direct membership check would come back negative, as TestUser1 was never actually added to the role. However, the permissions granted to the role might transfer to TestUser1 like it would for a real member. Let's see:

That is not the case. The role owner does not magically inherit permissions that were granted to the role. For the role's permissions to apply, the owner would also have to be a regular role member.

Next let us check, if the owner of the role attains any permissions directly on the role members. For that check we can use this query:

And again, no ugly surprises. TestUser1 does not have any permission on the role member TestUser2. This is encouraging. No unexpectedly implicated permission due to the concept of a role owner.

However, there is one thing that you do need to be aware of. The owner has unrestricted access to the role itself:

Unrestricted access includes in particular the ability to add new members to the role. With that, there is nothing stopping role owners from adding themselves to their role like this:

While the example in this post is based on a database role, the same behavior can be observed with server roles.

Summary

The permissions granted to a role do not automatically transfer to the role owner. However, role owners do have the ability to add themselves as member to their role and attaining the role's permission that way. Make sure to keep this in mind when selecting role owners and, more importantly, when auditing your permissions.