The concept you want to understand is called "ownership chaining"
As long as the owner of the object and database remain the same, SQL Server will not re-evaluate the permissions of a user after the 1st object in the chain.
In your first example you denied USER1 access to TABLE1 but allowed access to VW_TABLE1. Since USER1 has access to VW_TABLE1 and the ownership chain has not been broken, they can see TABLE1 because permissions are not re-evaluated inside the VIEW.
In your second example I'm assuming the SEC schema must have a different owner which causes SQL Server to re-evaluate permissions when inside the VW_TABLE1 view.
In short, to fix this problem make sure all objects have the same owner
If you want users to select from the view, why are you granting to the table? By "revoke" do you mean explicitly revoke/deny? Deny will override grant so there's your problem... you should be able to accomplish this by adding grant to the view and not doing anything either way on the tables.
Here's a quick example where SELECT
has not been explicitly granted on the table, but has been on the view. The user can select from the view but not the table.
CREATE USER foo WITHOUT LOGIN;
GO
CREATE TABLE dbo.a(id INT);
CREATE TABLE dbo.b(id INT);
GO
CREATE VIEW dbo.v
AS
SELECT a.id FROM a INNER JOIN b ON a.id = b.id;
GO
GRANT SELECT ON dbo.v TO foo;
GO
EXECUTE AS USER = N'foo';
GO
-- works:
SELECT id FROM dbo.v;
GO
-- Msg 229, SELECT denied:
SELECT id FROM dbo.a;
GO
REVERT;
Note that this assumes foo
has not been granted elevated privileges through explicit permissions on the schema or database, or through role or group membership.
Since you are using tables in multiple databases (sorry I missed the end of that first sentence initially), you also may need explicit grants on the table(s) in the database where the view does not exist. In order to avoid granting select to the table(s), you could create a view in each database, and then join the views.
Create two databases and a login:
CREATE DATABASE d1;
GO
CREATE DATABASE d2;
GO
USE [master];
GO
CREATE LOGIN blat WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
In database d1
, create a user, then create a table and a simple view against that table. Grant select to the user only against the view:
USE d1;
GO
CREATE USER blat FROM LOGIN blat;
GO
CREATE TABLE dbo.t1(id INT);
GO
CREATE VIEW dbo.v1
AS
SELECT id FROM dbo.t1;
GO
GRANT SELECT ON dbo.v1 TO blat;
GO
Now, in the second database, create the user, then create another table and a view that joins that table to the view in d1
. Grant select only to the view.
USE d2;
GO
CREATE USER blat FROM LOGIN blat;
GO
CREATE TABLE dbo.t2(id INT);
GO
CREATE VIEW dbo.v2
AS
SELECT v1.id FROM dbo.t2
INNER JOIN d1.dbo.v1 AS v1
ON t2.id = v1.id;
GO
GRANT SELECT ON dbo.v2 TO blat;
GO
Now launch a new query window and change the credentials to be for the login blat
(EXECUTE AS
does not work here). Then run the following from the context of either database, and it should work fine:
SELECT id FROM d1.dbo.v2;
These should both yield Msg 229 errors:
SELECT id FROM d1.dbo.t1;
GO
SELECT id FROM d2.dbo.t2;
Results:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 't1', database 'd1', schema 'dbo'.
Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 't2', database 'd2', schema 'dbo'.
Best Answer
Based on one of the suggestions that Dan Guzman gave me in your answer, and after read this question I found a workaround to my problem.
Apparently, there is no way, in scenario as described, to grant to userX
SELECT
permission in the viewABC without grant also SELECT permission on tableA.The solution that I found was the following:
in schemaD I created a Table-Valued Function that return the same record set of viewABC - despite ownerX isn't owner of schemaA, he has
SELECT
permission on tableAthe
EXECUTE AS
clause was used to guarantee that any execution of the function will use ownerX permissions - in this context doesn't matter the permission that userX has on tableAto userX was granted
SELECT
permission in the created function - notEXECUTE
permission, since the function return a tableThe Table-Valued Function sample code:
Granting
SELECT
permission:Now userX can use this command to get data: