View and table security conflict resolution

Question

I have a SQL Server 2017 instance with 5 databases on it. A, B, C, D, E. Database A holds nothing but schemas and views. There is one schema for each of the subsequent databases on the instance (A, B, C, D, E) and each schema has 50 - 150 views it owns.

I need the ability to give user(s) select permissions to all the views in database A but deny select permissions to ALL of the tables directly.

The test conditions should look like this.

SELECT * FROM [B].dbo.[any_table] (DENIED)
SELECT * FROM [A].[B].some_view (WORKS)

Since schema A - E are all owned by dbo I assumed I could just do the following:

USE A
GO

CREATE ROLE db_viewreader
GO

GRANT SELECT ON SCHEMA::A TO db_viewreader (repeat for b, c, d, e)
GO

CREATE USER testuser WITHOUT LOGIN
GO
ALTER ROLE db_viewreader ADD MEMBER testuser
GO

EXECUTE AS USER = 'testuser'

SELECT *
FROM [A].[B].some_view

I cannot get results from the view or the table. I THINK I understand why I am not getting results querying the tables since the user I created does not exist in database B - E. Is that the same reason I cannot call a view that accesses data in those underlying tables? If so Is there any way I can create a user that can utilize the views but not the tables they sit on top of?

Answer 1

One can allow SELECT on views without permissions on underlying tables with ownership chaining. As long as all the objects involved are owned by the same security principal, ownership chaining will allow users granted permissions on the view to use it regardless of permissions on the tables referenced by the view.

There are additional considerations to use ownership chaining with objects in different databases.

• The DB_CHAINING option must be turned on for the databases involved or the server-level cross db ownership chaining option turned on:

  ALTER DATABASE A SET DB_CHAINING ON;
  ALTER DATABASE B SET DB_CHAINING ON;
  ALTER DATABASE C SET DB_CHAINING ON;
  ALTER DATABASE D SET DB_CHAINING ON;
  ALTER DATABASE E SET DB_CHAINING ON;
  

• The user must be a server-level principal and added to each database (or guest user enabled in other databases):

  CREATE LOGIN testuser WITH PASSWORD = 'n3$(s(+#BB4--';
  USE A;CREATE USER testuser;ALTER ROLE db_viewreader ADD MEMBER testuser;
  USE B;CREATE USER testuser;
  USE C;CREATE USER testuser;
  USE D;CREATE USER testuser;
  USE E;CREATE USER testuser;
  

• The object owner, typically inherited from the object's schema AUTHORIZATION, must be the same security principal for all objects involved. In the case of the dbo user, this is the database owner login:

  ALTER AUTHORIZATION ON DATABASE::A TO DatabaseOwnerLogin;
  ALTER AUTHORIZATION ON DATABASE::B TO DatabaseOwnerLogin;
  ALTER AUTHORIZATION ON DATABASE::C TO DatabaseOwnerLogin;
  ALTER AUTHORIZATION ON DATABASE::D TO DatabaseOwnerLogin;
  ALTER AUTHORIZATION ON DATABASE::E TO DatabaseOwnerLogin;
  

Note that there is no need for an explict DENY on the tables unless you've granted the user permissions on the table directly or via role membership. Users have no object permissions by default unless granted.

To test permissions, you'll need to impersonate the server level principal rather than database user since the db user will otherwise be sandboxed in the context database (unless the database is TRUSTWORTHY):

USE A;
GO
EXECUTE AS LOGIN = 'testuser';
GO
SELECT *
FROM [A].[B].some_view;
GO
REVERT;
GO

Note that ownership chaining across databases is turned off by default for the reasons stated in the documentation excerpt below. This may not be a concern when only sysadmin role members can create database objects in production databases, and of course, review code before deployment.

Database owners and members of the db_ddladmin or the db_owners database roles can create objects that are owned by other users. These objects can potentially target objects in other databases. This means that if you enable cross-database ownership chaining, you must fully trust these users with data in all databases.