Sql-server – How does security context propagate between schemas within a database

permissionsschemasql-server-2008view

We have a SQL Server 2008 database that contains two schema's. The owners of these schema's have been granted full SELECT permissions to all tables within each others ownership.

I have created a user ID and have granted it access to only SQL Views created in both of these schema's but not the underlying tables. In the example below I've created two views, one in each schema, that select data from one table in one of the schema's.

If I login as the schema owner (either testdata or testcntrl) it works regardless of the schema I execute the view from.

If I execute the View as my new user in the schema that the same table exists in (in the example testdata) the select is successful, but if I try from the other schema (testctrl) the select fails giving me the following permission error: The SELECT permission was denied on the object 'UserData', database 'DEV_DATABASE', schema 'TESTDATA'..

How does security propagate between schema's within a single database? Why is this successful from one schema but not the other?

Here is a rough example of what I am trying to do, please let me know if I can explain this better, as for Im not remotely close to a dba.

USE [DEV_DATABASE]

-- Create tables and insert dummy data
CREATE TABLE [DEV_DATABASE].[TESTDATA].[UserData]
    (
        COL1 CHAR(10),
        COL2 CHAR(10),
        COL3 CHAR(10),
        COL4 CHAR(10),
        COL5 CHAR(10)
    );

 INSERT INTO [DEV_DATABASE].[TESTDATA].[UserData] VALUES ('1','2','3','4',5');
 INSERT INTO [DEV_DATABASE].[TESTDATA].[UserData] VALUES ('11','22','33','44',55');


CREATE TABLE [DEV_DATABASE].[TESTCTRL].[ControlData]
 (
        CTRL1 CHAR(10),
        CTRL2 CHAR(10),
        CTRL3 CHAR(10),
        CTRL4 CHAR(10),
        CTRL5 CHAR(10)
    );

 INSERT INTO [DEV_DATABASE].[TESTCTRL].[ControlData] VALUES ('a','b','c','d',e');
 INSERT INTO [DEV_DATABASE].[TESTCTRL].[ControlData] VALUES ('aa','bb','cc','dd',ee');


-- Create views
CREATE VIEW [TESTDATA].[DATA_TEST_VIEW] AS 
SELECT 
 COL1, COL2, COL3
FROM  [TESTDATA].[UserData]

CREATE VIEW [TESTCTRL].[CTRL_TEST_VIEW] AS 
SELECT 
 COL1, COL2, COL3
FROM  [TESTDATA].[UserData]  


-- permissions
DENY SELECT ON [TESTDATA].[UserData] to JOEUSER;
GRANT SELECT ON [TESTDATA].[DATA_TEST_VIEW] to JOEUSER;    
GRANT SELECT ON [TESTCRL].[CTRL_TEST_VIEW]  to JOEUSER;

EXECUTE AS USER = 'JOEUSER';
-- tests
SELECT * FROM [TESTDATA].[UserData];
-- fails as expected

SELECT * FROM  [TESTDATA].[DATA_TEST_VIEW];
-- passes as expected

SELECT * FROM  [TESTCRL].[CTRL_TEST_VIEW];
-- fails, and should pass?

REVERT;

Error message received:

SELECT * FROM  [TESTCRL].[CTRL_TEST_VIEW];
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'UserData', database 'DEV_DATABASE', schema 'TESTDATA'.

Best Answer

Thanks to the OP's clarification of the error, I'm going to post my comment as a suggested answer.

In SQL Server, permissions are checked only when ownership changes.

Consider the following tables:

Table SchemaA.Table1, owned by Alice.
View SchemaA.View1, owned by Alice, depends on Schema1.Table1.
View SchemaB.View1, owned by Bob, depends on Schema1.View1.

Consider these situations:

Alice attempts to select from SchemaA.Table1:

No change in ownership so permissions are not checked on the table. Alice is allowed access.
Alice attempts to select from SchemaA.View1:

No change in ownership so permissions are not checked on the view or on the table. Alice is allowed access.
Bob attempts to select from SchemaA.Table1:

There is a change in ownership on SchemaA.Table1 (Bob is attempting an operation on Alice's object) so Bob's permissions are checked on SchemaA.Table1.
Bob attempts to select from SchemaA.View1:

This is a change in ownership on SchemaA.View1 (Bob is attempting an operation on Alice's object) so Bob's permissions are checked on SchemaA.View1. There is no change in ownership from SchemaA.View1 to SchemaA.Table1 so Bob's permissions are not checked on SchemaA.Table1.
Bob attempts to select from SchemaB.View1.

There is no change in ownership on SchemaB.View1 (Bob is attempting an operation on Bob's object) so permissions are not checked on SchemaB.View1. There is a change in ownership from SchemaB.View1 to SchemaA.View1 so Bob's permissions (not Alice's) are checked on SchemaA.View1. There is no change in ownership from SchemaA.View1 to SchemaA.Table1 so Bob's permissions are not checked on SchemaA.Table1.

So, in the OP's sitation, the view [TESTCTRL].[CTRL_TEST_VIEW] depends upon the table [TESTDATA].[UserData]. The view and the table have different owners so there is a change in ownership (something some SQL Server books call a "break in the ownership chain"). To query the view, JoeUser needs to be granted the appropriate permissions to both the view and the table.

Related Solutions

Sql-server – Giving selective permissions in a database

You'll have to grant them appropriate permissions at the database level to indicate what they should be able to do. Then grant ALTER permission (or make them the owner) on the schemas in which they can do these things.

GRANT CREATE TABLE, CREATE PROCEDURE TO [AD\dbusersgroup_limitedrights]
GRANT ALTER ON SCHEMA::[DIS] TO [AD\dbusersgroup_limitedrights]

Sql-server – How to add a user with access to a single view

Please don't use the UI for this. It's a confusing mess.

It sounds to me like what you want is to create a user in a database, for a specific login, who only has permissions to select from one view. So, since you already have the login created:

USE your_db;
GO
CREATE USER username FROM LOGIN username;
GO
GRANT SELECT ON dbo.MyViewName TO username;
GO

EDIT here is an example of a script that will lead to the error you mention.

First, create some table in the unrelated_db:

CREATE DATABASE unrelated_db;
GO
USE unrelated_db;
GO
CREATE TABLE dbo.foo(bar INT);
GO

Now create a relatively restricted login:

USE [master];
GO
CREATE LOGIN username WITH PASSWORD='foo', CHECK_POLICY = OFF;
GO

Now create a database where the view will live, and add the login as a user:

CREATE DATABASE velojason;
GO
USE velojason;
GO
CREATE USER username FROM LOGIN username;
GO

Now create a function that will reference the table in the other database, and a synonym to the other table:

CREATE FUNCTION dbo.checkbar()
RETURNS INT
AS 
BEGIN
    RETURN 
    (
      SELECT TOP (1) bar 
        FROM unrelated_db.dbo.foo 
        ORDER BY bar
    );
END
GO
CREATE SYNONYM dbo.foo FOR unrelated_db.dbo.foo;
GO

Now create a local table:

CREATE TABLE dbo.PaymentDetails
(
  PaymentID INT
);
GO

Now create a view that references the table, the function and the synonym, and grant SELECT to username:

CREATE VIEW dbo.SomeView
AS
  SELECT 
    p.PaymentID, 
    x = dbo.checkbar(), -- function that pulls from other DB
    y = (SELECT bar FROM dbo.foo) -- synonym to other DB
    FROM dbo.PaymentDetails AS p;
GO
GRANT SELECT ON dbo.SomeView TO username;
GO

Now try to execute as username and select only the local column from the view:

EXECUTE AS USER = 'username';
GO
  -- even though I don't reference any of the columns 
  -- in the other DB, I am denied SELECT on the view:
SELECT PaymentID FROM dbo.SomeView;
GO
REVERT;
GO

Result:

Msg 916, Level 14, State 1, Line 3
The server principal "username" is not able to access the database "unrelated_db" under the current security context.

Now change the view to not reference any external objects, and run the above SELECT again, and it works:

ALTER VIEW dbo.SomeView
AS
  SELECT 
    p.PaymentID 
    --x = dbo.checkbar(),
    --y = (SELECT bar FROM dbo.foo)
    FROM dbo.PaymentDetails AS p;
GO

Short of showing us the scripts for the Payment Details, Account Details and MyView objects, maybe you can let us know if this query returns any results. You can find references to various objects through the catalog view sys.sql_expression_dependencies, but this view is not perfect - I believe it depends on all the views being refreshed (in the case where views reference other views, for example, or underlying schema has changed) in order to be accurate.

DECLARE 
  @dbname   SYSNAME = N'unrelated_db',
  @viewname SYSNAME = N'dbo.SomeView';

SELECT DISTINCT 
    [This object] = 
    OBJECT_SCHEMA_NAME([referencing_id]) 
      + '.' + OBJECT_NAME([referencing_id]), 
    [references this object] = 
    OBJECT_SCHEMA_NAME([referenced_id]) 
      + '.' + OBJECT_NAME([referenced_id]), 
    [and touches this database] = referenced_database_name,
    [and is a(n)] = o.type_desc,
    [if synonym, it references] = s.base_object_name
FROM sys.sql_expression_dependencies AS d
LEFT OUTER JOIN sys.objects AS o
ON o.[object_id] = d.referenced_id
LEFT OUTER JOIN sys.synonyms AS s
ON d.referenced_id = s.[object_id]
AND s.base_object_name LIKE '%[' + @dbname + ']%'
WHERE OBJECT_ID(@viewname) IN (
        referenced_id, 
        referencing_id, 
        (SELECT referencing_id FROM sys.sql_expression_dependencies 
        WHERE referenced_database_name = @dbname)
) OR referenced_database_name = @dbname;

SQL Server isn't just going to try to access unrelated_db for the fun of it... there must be some tie to that database from the view you're trying to use. Unfortunately if we can't see the view definition and more details about the objects it touches, all we can do is speculate. The two main things I can think of are synonyms or functions that use three-part names, but seeing the actual scripts will give us a much better idea instead of guessing. :-)

You may also want to check sys.dm_sql_referenced_entities, however this function returns nothing useful in the example above.

Best Answer

Related Solutions

Sql-server – Giving selective permissions in a database

Sql-server – How to add a user with access to a single view

Related Question