CODEWITHSUNDEEP
sql-serverdatabasesql-server-2008sql-server-2012schema
CM2K
CM2K

Reputation: 883

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

I want to grant read permissions for a user for a view which joins 2 tables from another database. I don't want to:

  1. give him permission on the database where the tables are or add him as an user there.
  2. Make him the owner of the view/schema due to the security exploits.
  3. I don't want to create first a table, or variations of a hack table with a truncate and a stored procedure which inserts the data on a trigger.

Can this be done somehow? Maybe there's something I missed and you guys might know.

I have read these posts but they didn't solve my problem: Grant SELECT permission on a view, but not on underlying objects

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

https://msdn.microsoft.com/en-us/library/ms188676.aspx

https://dba.stackexchange.com/questions/89632/sql-server-grant-select-access-to-a-user-in-a-view-and-not-in-its-tables

Thank you

Edit: The easiest solution I came up with, after some research, is activating the cross database ownership chainingoption on the database where I'm placing the views and granting the read permission to the users. This might be in contrast with my 2nd point of things I'm trying to avoid. Is this a good idea?

Upvotes: 1

Views: 5828

Answers (2)

CM2K
CM2K

Reputation: 883

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.

Upvotes: 0

David T. Macknet
David T. Macknet

Reputation: 3162

Give them a login to another database on the same server, and include only your single view, with the view pointing to your secure database. Don't give that login any access to anything but the database with the view in it, and only read access to that single view. Obviously, you'll have to fully qualify your table name (e.g., from SourceDB.dbo.SomeSecretTable).

Upvotes: 1

Related Questions