Implicit Permissions Due to Ownership Chaining or Scopes in SQL Server

Problem

I have audited for permissions on my databases because users seem to be accessing the tables, but I don’t see permissions which give them such rights. I’ve gone through every Windows group that has access to my SQL Server and into the database, but with no success. How are the users accessing these tables?

Solution

Likely the issue is due to implied permissions. The first thing to check are roles which give implied permission. Two other areas to check are ownership chaining and permissions which propogate down from a higher level, called a scope. First, let’s consider ownership chaining.

Ownership Chaining

If two objects have the same owner within a database, and one object references the other, an ownership chain forms. In this case, SQL Server will only check permission on the referring object. It won’t check permission on the referred to object. For instance, a view references a table. Both have the same owner. If a given user has the ability to SELECT against the view, SQL Server won’t check to see if the user has SELECT against the table as well, so long as the user is going against the view. Now, if the user is going against the table directly, SQL Server will check.

Here’s an example that works with SQL Server 2005/2008.

USE MSSQLTips;
GO

CREATE USER TestOwnershipChaining WITHOUT LOGIN;
GO

CREATE TABLE dbo.SomeTable (TableID INT);
GO

CREATE VIEW dbo.SomeView AS SELECT TableID FROM dbo.SomeTable;
GO

CREATE ROLE SomeRole
GO

GRANT SELECT ON dbo.SomeView TO SomeRole;
GO

EXEC sp_addrolemember ‘SomeRole’, ‘TestOwnershipChaining’;
GO

— This will work because of ownership chaining. Permission is checked
— on the view. It is not checked on the table
EXECUTE AS USER = ‘TestOwnershipChaining’;
GO

SELECT TableID FROM dbo.SomeView;
GO

REVERT;
GO

— This will fail. There isn’t an ownership chain since the table is
— being hit directly. The user doesn’t have permission against the table.
EXECUTE AS USER = ‘TestOwnershipChaining’;
GO

SELECT TableID FROM dbo.SomeTable;
GO

REVERT;
GO

Through a Higher Scope

In SQL Server 2005, the concept of securables was introduced, which is basically anything that can have a permission assigned. Also introduced were securables that were also containers, called scopes. The server scope contains database scopes. The database scope contains schema scopes and the schema scope contains objects like tables, views, stored procedures, etc. If a permission is at a scope level, it will propogate down to lower scopes and objects within that particular scope (or within the lower scopes) as well. So, for instance, if a role has the ability to SELECT against the dbo schema, it also has the ability to SELECT against every table and view in that scope. For instance:

— Revoke permission against the view
REVOKE SELECT ON dbo.SomeView FROM SomeRole;
GO

— Grant the permission at a higher scope
GRANT SELECT ON SCHEMA::dbo TO SomeRole;

— This will work because of the permission at the scope level
EXECUTE AS USER = ‘TestOwnershipChaining’;
GO

SELECT TableID FROM dbo.SomeView;
GO

REVERT;
GO

— Now this will work too, because of that schema level SELECT permission
EXECUTE AS USER = ‘TestOwnershipChaining’;
GO

SELECT TableID FROM dbo.SomeTable;
GO

REVERT;
GO

Auditing Permissions Due to Ownership Chaining

Unfortunately, this cannot be done. You might use a 3rd party tool to try and map dependencies (sysdepends, sys.sql_expression_dependencies), but these sometimes miss all the dependencies, depending on the order in which objects were created. Therefore, this is a matter of due diligence and understanding the code well.

Auditing Permissions Due to Scopes

Now, you also need to audit permissions at the higher scopes, too. So for sys.database_permissions, you’ll need to audit permissions against the database level and the schema level. For sys.server_permissions, you’ll have to check if such permissions have been granted as well. Merely relying on permissions at the object level will mean you will miss something.

Next Steps

Read these related tips:

Resources

Community

Subscribe