How to allow a user to select data through a view but not restrict access to underlying table

SQL Server Administration
1

I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source

How can I do the following: AS A Test_user

SELECT * FROM DB2.dbo.Test_view --Should work

SELECT * FROM DB1.dbo.Source --Should Not work

2
  1. Do not grant public access to the restricted view(s)
  2. Do create a database role to cover your use case
  3. Add your user to the new database role
  4. grant the new database role execute permission on the view and any permissions needed on any underlying tables or views
3

Thank you for your reply.
I created a login 'test_login'
I created a user 'test_user' for the login 'test_login'
I created a role named 'test' and added 'test_user' to that role.
I granted select and execute to the role 'test' on the view.

BUT still the following is not working

SELECT * FROM DB2.dbo.Test_view --Not working --This should work

SELECT * FROM DB1.dbo.Source --Should Not work --This is fine

4

Wouldn't you run into cross-database ownership chaining issues unless you create the user/role in the database where the base table is located (DB1 in the example)?

I can think of two possibilities - neither of which I particularly like:

a) Enable cross-database ownership chaining. It is not a recommended practice and opens up a security hole.

b) Grant public access to login 'Test_user' in DB1 as well, create a view in that database. Then use that view to select from in the view that you create in the DB2 database. Like this:

USE DB1
GO
CREATE USER [Test_user] FOR LOGIN [Test_user]
GO

CREATE VIEW dbo.DB1Test_View
AS
	SELECT * FROM dbo.[Source];
GO

GRANT SELECT ON dbo.DB1Test_View TO [Test_user]
GO

USE DB2
GO

CREATE VIEW dbo.Test_View
AS 
	SELECT * FROM DB1.dbo.DB1Test_View
GO

GRANT SELECT ON dbo.Test_View TO [Test_user]
GO

Given a choice I would opt for #2. If you want to enable cross-database ownership chaining, investigate and understand the security risks thoroughly before doing it.

5

Sorry, missed the x-db thing. you're correct.

however I avoid granting permissions to users at all costs. Use db roles and windows groups:

  1. create a role
  2. add windows groups as role members
  3. grant permissions to the role

that way, you abstract away both the users and the permissions. They meet in the role

6

Thank you for your response. We ended up as below:

USE db1
GO
-- Step 3
CREATE VIEW VW1
AS
SELECT *
FROM TBL1
-- Step 4
GRANT SELECT ON VW1 TO test_user

USE db2
GO
--Step 6
CREATE VIEW v2
AS
SELECT * FROM VW1
7

How can this work? V2 refers to VW1 but VW1 is in a different database?