2

I'm talking about SQL Server in particular. I suppose a user tried it and got an error (you don't have select permissions on hidden underlying view B not explicitly mentioned in query). The main view A joins a table and another view (view B) together, and they don't have permission for view B.

I'm not in charge of permissions for the database, but I'm curious how it works.

Do you need to grant permissions to every object built underneath the views? Or just the view itself? Or either one?

I thought one of the purposes of views was a snapshot of underlying objects that you DON'T want to give full permissions for. That's what is unusual to me.

  • Re: Do you need to grant permissions to every object built underneath the views? Or just the view itself? Or either one? A: You should only have to grant access to both views and not the underlying tables or any underlying objects below. – rvsc48 Jul 6 '16 at 15:20
  • Hmm... so for some reason, on a view built on top of views and tables, you DO need to grant permissions to the underlying views, but NOT the underlying tables. Is that correct? – user45867 Jul 6 '16 at 19:43
5

Do you need to grant permissions to every object built underneath the views? Or just the view itself? Or either one?

Permissions need to be granted to the person executing the query for every object referenced by the view. Except if they are owned by the view owner. In that eventuality Ownership Chains come into play.

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

The below creates two tables. T1 (owned by TestUser1) and T2 (owned by TestUser2) and a view that references both tables and is owned by TestUser1.

A third user is granted SELECT permissions on the view but none of the underlying objects. Selecting from the view initially fails for them. However it succeeds after they are granted select permissions on T2. There is no need to grant them permissions on T1 as this is owned by the same user as the view.

Setup

CREATE USER TestUser1 WITHOUT LOGIN;
CREATE USER TestUser2 WITHOUT LOGIN;
CREATE USER TestUser3 WITHOUT LOGIN;

CREATE TABLE dbo.T1(C INT);

ALTER AUTHORIZATION ON dbo.T1 TO TestUser1; 


CREATE TABLE dbo.T2(C INT);

ALTER AUTHORIZATION ON dbo.T2 TO TestUser2; 

GO

CREATE VIEW dbo.V
AS
SELECT T1.C
FROM dbo.T1 CROSS JOIN dbo.T2

GO

ALTER AUTHORIZATION ON dbo.V TO TestUser1;

GRANT SELECT ON dbo.V TO TestUser3;

Test 1 (Fails)

EXECUTE AS USER='TestUser3';

SELECT *
FROM dbo.V;

REVERT;

The SELECT permission was denied on the object 'T2', database 'Foo', schema 'dbo'.

Test 2 (Succeeds)

GRANT SELECT ON dbo.T2 TO TestUser3    

EXECUTE AS USER='TestUser3';

SELECT *
FROM dbo.V;

REVERT;

Cleanup

DROP TABLE dbo.T1, dbo.T2
DROP VIEW dbo.V
DROP USER TestUser1
DROP USER TestUser2
DROP USER TestUser3
  • That makes sense, technically speaking. Logically it is not intuitive but maybe the system was created due to some security preferences or rationale at some point. – user45867 Jul 7 '16 at 14:51
0

It's the owner of the view that needs permissions to the underlying table, not the person querying the view. If you're getting a permission denied message when querying the view, it might be because the view owner doesn't have the appropriate permission to one of the underlying tables.

  • This isn't correct. Ownership chaining means that permissions arent checked if the objects have the same owner. But if they have different owners granting permissions to the view owner instead of the person executing the query wont help. – Martin Smith Jul 6 '16 at 16:35
  • Can you clarify that last sentence? – dpw Jul 6 '16 at 16:51
  • Submitted an answer to clarify it. – Martin Smith Jul 7 '16 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.