locked
Grant select on a view but not to base tables RRS feed

  • Question

  • Hi all,


    There is a view in DB1 which is selecting rows from somw tables in DB2.
    I'd like to grant select access to the view, but not direct access to the base tables.
    Is it possible?


    I have granted select access to the view and public security privileges to a user. There is a result:
    Msg 229, Level 14, State 5, Line 1
     The SELECT permission was denied on the object 'TableName', database 'DB2', schema 'SchemaName'.

    The both DBs are on the same SQL-instanz and have the same owner..


    how can i make it work?


    Thank you for your time
    Evgeniya

    Tuesday, October 11, 2011 12:56 PM

Answers

  • Zenya

    An user that runs that VIEW exists in DB1 and DB2?

    http://www.sommarskog.se/grantperm.html#ownershipchaining

    There is paragraph named Cross-Database Access 



    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Crazy Adam Tuesday, October 11, 2011 2:38 PM
    • Marked as answer by Stephanie Lv Tuesday, October 18, 2011 9:22 AM
    Tuesday, October 11, 2011 1:15 PM
  • @Adam:

    If i do the way you suggest,  it will work.

    But i want that the user has no access to tables, so to table [Foo].[dbo].[Boogers] in your example...

    Is it possible?

    Yes and no. Initially, you described a use-case where there is a view in "DB1" selecting records from a table in "DB2". In this situation, no, I don't believe you can get around giving the user access in the other database. However, if you created the view in "DB2", you could grant the user access to just the view without also granting select rights to the view's source table(s). For example (building on my earlier sample):

    USE [Foo]
    GO
    REVOKE SELECT ON dbo.Boogers FROM [test_user];
    GO
    CREATE VIEW dbo.Snots
     AS
    SELECT * FROM dbo.Boogers;
    GO
    GRANT SELECT ON dbo.Snots TO [test_user];
    GO
    
    

    Now, test the permissions with our trusty "test_user":

    USE [Foo]
    GO
    --Boom! No select from dbo.Boogers for you!
    SELECT * FROM dbo.Boogers;
    
    --Ah, you get a nice result set.
    SELECT * FROM dbo.Snots;
    GO
    

    Listen, I know this alternative isn't what you're asking about, and I'm sure you have your reasons. Stored procedures allow you a little more flexibility in this regard (e.g., using the "execute as" clause). Erland's suggestion about the link Uri provided is good advice.

    Good luck bro.

    ~CA


    Adam
    • Proposed as answer by LearnerSql Monday, October 17, 2011 6:31 AM
    • Marked as answer by Stephanie Lv Tuesday, October 18, 2011 9:22 AM
    Wednesday, October 12, 2011 2:45 PM

All replies

  • Zenya

    An user that runs that VIEW exists in DB1 and DB2?

    http://www.sommarskog.se/grantperm.html#ownershipchaining

    There is paragraph named Cross-Database Access 



    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 11, 2011 1:15 PM
  • Zenya

    An user that runs that VIEW exists in DB1 and DB2?

    http://www.sommarskog.se/grantperm.html#ownershipchaining

    There is paragraph named Cross-Database Access 



    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Crazy Adam Tuesday, October 11, 2011 2:38 PM
    • Marked as answer by Stephanie Lv Tuesday, October 18, 2011 9:22 AM
    Tuesday, October 11, 2011 1:15 PM
  • Hey Evgeniya,

    Uri is right. Just to add to his answer, SQL Server Books Online (BoL) has clear guidance on this subject; look under "Cross-database permissions". If you look in BoL, you'll see the following:

    "...If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database."

    --Create Databases "Foo" and "Bar"
    CREATE DATABASE [Foo]
    GO
    CREATE DATABASE [Bar]
    GO
    
    --Create a base table in "Foo" named "dbo.Boogers"
    USE [Foo]
    GO
    CREATE TABLE [dbo].[Boogers] (ID INT IDENTITY(1,1));
    GO
    
    --Create a view in "Bar" named "dbo.Snots"
    USE [Bar]
    GO
    CREATE VIEW [dbo].[Snots]
     AS
    SELECT * FROM [Foo].[dbo].[Boogers];
    GO
    
    --Create your test login
    CREATE LOGIN [test_user] WITH PASSWORD = '@TestUser@', DEFAULT_DATABASE = [Bar];
    
    --Create a user in "Bar" for your test login and grant it rights to the view
    CREATE USER [test_user] FROM LOGIN [test_user];
    GRANT SELECT ON [dbo].[Snots] TO [test_user];
    GO
    
    --Create a user in "Foo" for your test login and grant it rights to the table
    USE [Foo]
    GO
    CREATE USER [test_user] FROM LOGIN [test_user];
    GRANT SELECT ON [dbo].[Boogers] TO [test_user];
    GO
    

    Now, start a second session to this instance logging in as "test_user". Once you've done that, query the view:

    SELECT * FROM [dbo].[Snots]
    GO
    

    It should work :-)

    ~CA


    Adam
    Tuesday, October 11, 2011 2:45 PM
  • Hi,

     

    Thank you for reply..

     

    @Uri:

    Yes,  the user exists in both DBs. But he has no read access to tables because right this i want to avoid.

     

    @Adam:

    If i do the way you suggest,  it will work.

    But i want that the user has no access to tables, so to table [Foo].[dbo].[Boogers] in your example...

     

    Is it possible?

     

    Thank you

    Evgeniya

    Tuesday, October 11, 2011 3:59 PM
  • Yes,  the user exists in both DBs. But he has no read access to tables because right this i want to avoid.

    Did you check the link Uri gave you? There are two more conditions that needs to be fulfilled.

    Please also read about the caveats so that you have a fully understanding of the potential security holes.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, October 11, 2011 10:12 PM
  • @Adam:

    If i do the way you suggest,  it will work.

    But i want that the user has no access to tables, so to table [Foo].[dbo].[Boogers] in your example...

    Is it possible?

    Yes and no. Initially, you described a use-case where there is a view in "DB1" selecting records from a table in "DB2". In this situation, no, I don't believe you can get around giving the user access in the other database. However, if you created the view in "DB2", you could grant the user access to just the view without also granting select rights to the view's source table(s). For example (building on my earlier sample):

    USE [Foo]
    GO
    REVOKE SELECT ON dbo.Boogers FROM [test_user];
    GO
    CREATE VIEW dbo.Snots
     AS
    SELECT * FROM dbo.Boogers;
    GO
    GRANT SELECT ON dbo.Snots TO [test_user];
    GO
    
    

    Now, test the permissions with our trusty "test_user":

    USE [Foo]
    GO
    --Boom! No select from dbo.Boogers for you!
    SELECT * FROM dbo.Boogers;
    
    --Ah, you get a nice result set.
    SELECT * FROM dbo.Snots;
    GO
    

    Listen, I know this alternative isn't what you're asking about, and I'm sure you have your reasons. Stored procedures allow you a little more flexibility in this regard (e.g., using the "execute as" clause). Erland's suggestion about the link Uri provided is good advice.

    Good luck bro.

    ~CA


    Adam
    • Proposed as answer by LearnerSql Monday, October 17, 2011 6:31 AM
    • Marked as answer by Stephanie Lv Tuesday, October 18, 2011 9:22 AM
    Wednesday, October 12, 2011 2:45 PM