Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'.
share|improve this question
    
What is the exact error message of the failure? It will state the object name and schema name of the database object where access is denied. I am guessing it will be on object 'UserData' in schema 'TESTDATA' because permissions are checked only when ownership changes. If the two schemas are owned by different database users then the ownership changes from view [TESTCTRL].[CTRL_TEST_VIEW] to table [TESTDATA].[UserData]. This means that JOEUSER needs permissions on both the view and the table. –  Greenstone Walker Aug 7 '13 at 1:26
    
The error is Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object 'UserData', database 'DEV_DATABASE', schema 'TESTDATA'. –  ProfessionalAmateur Aug 7 '13 at 1:49

1 Answer 1

up vote 1 down vote accepted

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:

  1. Alice attempts to select from SchemaA.Table1:

    No change in ownership so permissions are not checked on the table. Alice is allowed access.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

share|improve this answer
    
Thanks. So the main issue is the table has columns with sensitive data in it, SSN, Addresses, Phone's, etc that we want to prevent the end user from querying against. The view we created (in TESTCTRL) does not bring back these columns from UserData table in TESTDATA schema. Is there a way to secure columns in a manner other than what we tried? This is a small example, we have probably 100 views accessing 100+ tables, so going column by column is not feasible from a long term maintenance perspective. –  ProfessionalAmateur Aug 8 '13 at 3:24
    

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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