locked
Is it possible to hide the source tables of views? RRS feed

  • Question

  • I'm deploying a warehouse and, in the classic set up, I'm trying to make sure users hit views of the warehouse and not the actual warehouse. I'm running into an issue where locking people out of the warehouse, locks people out of the views as well. The views and the warehouse are in separate databases. 

    I know this is how things are normally done: by giving users access to indexed views instead of actual warehouse tables. This is the first time I've had to do the work myself. What am I doing wrong?

    Thursday, September 1, 2016 4:49 PM

Answers

  • As I wrote, users can only see objects where they have permissions for, no others. Grant only select permissions for the views, then the user won't see any table.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by falcon00 Thursday, September 1, 2016 5:50 PM
    Thursday, September 1, 2016 5:12 PM
  • It is achievable with the setup you have if you enable DB_CHAINING for both databases:

    ALTER DATABASE db SET DB_CHAINING ON

    With this setting, ownership chaining extends across databases. If the databases are owned by the same login, and the tables and the views have the same owner, the users only needs SELECT permission on the views. You still need to create a user for them in both databases.

    Beware that this setting can incur a security risk. If there are more databases on the server with this setting enabled, a person who is db_owner in that database can access any data in the table database, by creating a user for the owner of the database in his database, and then create a stored procedure or a view that accesses tables in the table database. He then makes the other user owner of the procedure.

    A better option may be to have all in one database, but put views and tables in different schemas.

    • Marked as answer by falcon00 Tuesday, November 7, 2017 11:11 PM
    Thursday, September 1, 2016 9:20 PM
  • >a person who is db_owner in that database can access any data in the table database, by creating a user for the owner of the database in his database

    Yes, but only if

    1) the chained databases are not owned by 'sa' (because you can't create a user for sa)

    2) The user has the ability to connect to the target database.  Cross Database Ownership Chaining does not grant database access.

    Here's a repro to play with:

    /*
    use master
    drop database dw
    drop database sandbox
    drop database badUserDb
    drop login someUser
    drop login badUser
    drop login adminUser
    */
    go
    
    create login adminUser with password='P@ssword'
    create login someUser with password='P@ssword'
    create login badUser with password='P@ssword'
    
    go
    create database dw
    create database sandbox
    create database badUserDb
    go
    alter authorization on database::badUserDb to badUser
    alter authorization on database::dw to adminUser
    alter authorization on database::sandbox to adminUser
    go
    alter database dw set db_chaining on
    alter database sandbox set db_chaining on
    alter database badUserDb set db_chaining on
    
    
    go
    
    use dw 
    create table dbo.t(id int)
    create user someUser for login someUser 
    create user badUser for login badUser
    go
    use sandbox 
    create user someUser for login someUser 
    grant select to someUser
    
    create synonym dbo.t for dw.dbo.t
    
    go
    use sandbox 
    go
    execute as login='someuser'
    
      select * from t --works
      --select * from dw.dbo.t --fails
    
    revert
    
    go
    
    use baduserdb
    
    execute as login='badUser'
      
      create user adminUser for login adminUser
      go
      create schema adminUserSchema 
      go
      alter authorization on schema::adminUserSchema to adminUser
      go
      create view adminUserSchema.t as select * from dw.dbo.t
      go
      select * from adminUserSchema.t
     
    
    revert

    David


    David http://blogs.msdn.com/b/dbrowne/



    Thursday, September 1, 2016 9:59 PM
  • 1) the chained databases are not owned by 'sa' (because you can't create a user for sa)

    Hm, if the bad guy's database is not owned by sa, but the good guy's is, then it's true. But if both databases are owned by sa, there is no need to trick with an extra user.

    2) The user has the ability to connect to the target database.  Cross Database Ownership Chaining does not grant database access.

    That's correct.

    • Marked as answer by falcon00 Tuesday, November 7, 2017 11:13 PM
    Thursday, September 1, 2016 10:10 PM

All replies

  • The views and the warehouse are in separate databases.

    What's that good for?

    In this case the user must have SELECT permissions on the views AND on the base tables in the other database, because by default cross database ownership chaining don't work.

    Keep all objects (base tables + view) in one database, then the users only need SELECT permissions on the views; they even don't need to "see" the tables.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, September 1, 2016 4:55 PM
  • "What's that good for?" I don't know, you tell me. I'm not a DBA. All I know is that when I have users, some of whom are SQL proficient and use SSMS, I don't want them to be able to actually see the tables when they expand that database or open things up in Tableau. It will cause mass confusion, not to mention there will be name collision when I have a table and a view named the same.  

    Thursday, September 1, 2016 5:00 PM
  • As I wrote, users can only see objects where they have permissions for, no others. Grant only select permissions for the views, then the user won't see any table.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by falcon00 Thursday, September 1, 2016 5:50 PM
    Thursday, September 1, 2016 5:12 PM
  • It is achievable with the setup you have if you enable DB_CHAINING for both databases:

    ALTER DATABASE db SET DB_CHAINING ON

    With this setting, ownership chaining extends across databases. If the databases are owned by the same login, and the tables and the views have the same owner, the users only needs SELECT permission on the views. You still need to create a user for them in both databases.

    Beware that this setting can incur a security risk. If there are more databases on the server with this setting enabled, a person who is db_owner in that database can access any data in the table database, by creating a user for the owner of the database in his database, and then create a stored procedure or a view that accesses tables in the table database. He then makes the other user owner of the procedure.

    A better option may be to have all in one database, but put views and tables in different schemas.

    • Marked as answer by falcon00 Tuesday, November 7, 2017 11:11 PM
    Thursday, September 1, 2016 9:20 PM
  • >a person who is db_owner in that database can access any data in the table database, by creating a user for the owner of the database in his database

    Yes, but only if

    1) the chained databases are not owned by 'sa' (because you can't create a user for sa)

    2) The user has the ability to connect to the target database.  Cross Database Ownership Chaining does not grant database access.

    Here's a repro to play with:

    /*
    use master
    drop database dw
    drop database sandbox
    drop database badUserDb
    drop login someUser
    drop login badUser
    drop login adminUser
    */
    go
    
    create login adminUser with password='P@ssword'
    create login someUser with password='P@ssword'
    create login badUser with password='P@ssword'
    
    go
    create database dw
    create database sandbox
    create database badUserDb
    go
    alter authorization on database::badUserDb to badUser
    alter authorization on database::dw to adminUser
    alter authorization on database::sandbox to adminUser
    go
    alter database dw set db_chaining on
    alter database sandbox set db_chaining on
    alter database badUserDb set db_chaining on
    
    
    go
    
    use dw 
    create table dbo.t(id int)
    create user someUser for login someUser 
    create user badUser for login badUser
    go
    use sandbox 
    create user someUser for login someUser 
    grant select to someUser
    
    create synonym dbo.t for dw.dbo.t
    
    go
    use sandbox 
    go
    execute as login='someuser'
    
      select * from t --works
      --select * from dw.dbo.t --fails
    
    revert
    
    go
    
    use baduserdb
    
    execute as login='badUser'
      
      create user adminUser for login adminUser
      go
      create schema adminUserSchema 
      go
      alter authorization on schema::adminUserSchema to adminUser
      go
      create view adminUserSchema.t as select * from dw.dbo.t
      go
      select * from adminUserSchema.t
     
    
    revert

    David


    David http://blogs.msdn.com/b/dbrowne/



    Thursday, September 1, 2016 9:59 PM
  • 1) the chained databases are not owned by 'sa' (because you can't create a user for sa)

    Hm, if the bad guy's database is not owned by sa, but the good guy's is, then it's true. But if both databases are owned by sa, there is no need to trick with an extra user.

    2) The user has the ability to connect to the target database.  Cross Database Ownership Chaining does not grant database access.

    That's correct.

    • Marked as answer by falcon00 Tuesday, November 7, 2017 11:13 PM
    Thursday, September 1, 2016 10:10 PM