Results 1 to 3 of 3

Thread: secure view

  1. #1
    Join Date
    Apr 2003

    Unanswered: secure view

    I am trying to create a filtered, secure view of another table, without having to grant read access to the originating table. I have not found a way to do this.


    select * from otherdb.dbo.table as t1
    WHERE t1.userid = USER

    If USER only has access to my_view, he will only see selective columns in t1. However, it seems that for USER to SELECT from my_view, USER has to also have SELECT privileges for otherdb.dbo.table. I wish for users to have access to my_view, but not have select access to otherdb.dbo.table and thus have a restricted view of otherdb.dbo.table.

    Is there any way to implement security like this with views?

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    You can do it, but you have to have all the objects in the same database. In SQL Server there is the concept of Ownership Chaining. If an owner (in this case dbo) wants to grant you select rights to a view, he does not need to grant permissions on any sub-views or tables. If a user (call him A) creates a view based on another user's (B) table, then select permission on A's view will require select permission on B's table.

    Now, here is where it gets into muddy water. You are now going from DB to DB. There is no hard link in the name of an owner in one DB vs. another. dbo could be mapped to someone other than sa in any database, so keeping the Ownership Chain across databases can provide you with some problems in security as the owner of one DB can peek at the other's data at will.

    In short, yes, you will have to grant select permission on the base table to the users (or guest/public).

    Is there a reason it is not one database?

  3. #3
    Join Date
    Apr 2003
    So, it will work if all the tables are in one DB? (eg, user has select on view, but not select on tables in the view)

    It doesn't necessarily have to be in one db, as the tables are read-only and aren't updated often.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts