Grant Select Permission on a View, But Not on Underlying Objects

Grant SELECT permission on a view, but not on underlying objects

Does the same user who owns the view also own the underlying tables? If not, the owner of the tables needs to grant the view owner permission WITH GRANT OPTION. If the same user owns both the tables and the view, then granting permission on the view should be sufficient.

Grant Select on a view not base table when base table is in a different database

As you state in one of your comments that the table in question is in a different database, then ownership chaining applies. I suspect there is a break in the chain somewhere - check that link for full details.

grant read access on a view but not on it's underlying tables from other databases

What I ended up doing:

1. Create an active directory group.
2. Add users to the AD group.
3. Create a login for the AD group mapped for the source DB and target DB.
4. Add the user on the target DB and give him permissions only for the requested views.
5. (Optional) Added the group on all the databases to deny select.

Couldn't find a solution for my original question without the AD group.

Do I need permissions for underlying tables when querying a view in SQL Server?

Just the View (A). Granting Select on the underlying tables will give them more access than they may need. The link below has some helpful info on ownership of the view\table and the affects it may have on your access.

Grant SELECT permission on a view, but not on underlying objects

Grant permission for a view that uses tables form other databases

You need to create the user in the other database (without granting any rights).

USE [DATABASE_A2]
GO
CREATE USER [User_B] FOR LOGIN [User_B]
GO

How to grant a user SELECT on a restricting view in Firebird

Since you do not want your user to have grants on the table, then it is the VIEW which should have, (or a selectable Stored Procedure, when user permissions are regulated with SPs). The user "invokes" the VIEW (or an SP) and then the VIEW invokes the table.

GRANT SELECT ON mytable TO VIEW myview

You have to grant table read rights to the view, not to the users. Read chapter 11.2.2. Statements for Granting Privileges in Firebird documentation at https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-security-auth-manage-users

View and table security conflict resolution

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.

---

Related Topics