CODEWITHSUNDEEP
sql-server
David
David

Reputation: 25

Create permission to see view results without permission to access tables behind it

I'm trying to create a view in MSSQL2008 that provides some specific details from system tables, as the users of this view don't have access to the system tables themselves. Is there a way to give provide permission for them to see that data via the view but not access it directly from the table? Like giving the view a specific user to execute under regardless of who is calling the view (providing that user has permission to select from the view).

I've found permissions to select from the view, select from the table, but not the in-between that I'm looking for.

Upvotes: 0

Views: 874

Answers (1)

Thom A
Thom A

Reputation: 95561

As I mentioned (in my deleted comment), provided that the 2 objects, in this case the TABLE and VIEW, have the same owner then a USER using the object can make use of ownership chaining. So if the USER has access to SELECT from the VIEW but not the TABLE, when they query the VIEW they will be given implicit permission to be able to query the table, as they are owned by the same user.

This chaining can continue on afterwards. For example, a PROCEDURE that references a VIEW, that references 3 TABLEs and then INSERTs into a 4th. The USER might only have access to use the PROCEDURE, but if all the objects are owned by the same USER, then the permissions will be implicitly given to both SELECT and INSERT within the context of the PROCEDURE.

This is explained in more detail in the documentation.

As a simple example, see the below, where the user SomeUser successfully manages to SELECT from the VIEW, despite the explicit DENY on the underlying table. In this case, on my instance, both objects are owned by the dbo user:

--Create table
CREATE TABLE dbo.SomeTable (SomeID int IDENTITY,
                            SomeDate datetime DEFAULT GETDATE());
GO
--Some Rows
INSERT INTO dbo.SomeTable
DEFAULT VALUES;
GO 25
--Create view
CREATE VIEW dbo.SomeView AS
    SELECT SomeDate
    FROM dbo.SomeTable;
GO
--See Owners
SELECT o.[name] AS ObjectName, dp.[name] AS OwningUser
FROM sys.objects o
     JOIN sys.schemas s ON s.schema_id = o.schema_id
     JOIN sys.database_principals dp ON o.principal_id = dp.principal_id
                                     OR (o.principal_id IS NULL AND s.principal_id = dp.principal_id)
WHERE o.[name] IN (N'SomeTable','SomeView');
GO
--Create User
CREATE USER SomeUser WITHOUT LOGIN;
GO
--Grant SELECT on View, DENY on Table
GRANT SELECT ON dbo.SomeView TO SomeUser;
DENY SELECT ON dbo.SomeTable TO SomeUser;
GO
--Switch User Context
EXECUTE AS USER = 'SomeUser';
GO
--Run Select
SELECT *
FROM dbo.SomeView;
GO
--Revert context
REVERT
GO
--Clean up
DROP USER SomeUser;
DROP VIEW dbo.SomeView;
DROP TABLE dbo.SomeTable;

Upvotes: 2

Related Questions