Re: Using Views for Security

From: Magy Naumova (MagyNaumova_at_discussions.microsoft.com)
Date: 05/18/05

  • Next message: DebHerman: "Re: Using Views for Security" 
    Date: Wed, 18 May 2005 00:44:02 -0700

    Hi Deb,

    I understood your question. It is on cross-database ownership.
    Cross-database ownership chaining occurs when a source object depends on
    objects in other databases.

    Cross-database ownership chaining works in the same way as ownership
    chaining within a database. The only distinction is that an unbroken
    ownership chain is based on all objects being owned by the same login
    account, not the same database user name. This is because one login account
    can have different user names in different databases.

    In a cross-database ownership chain, if the source object in the source
    database and the target objects in the target databases are owned by the same
    login account, SQL Server does not check permissions on the target objects.

    For example, if two databases are owned by the same login account, the dbo
    users in these databases are mapped to the same login account. If
    cross-database ownership chaining is enabled for these databases, source
    objects in the dbo schema can access target objects in the dbo schema of both
    databases.
    Ownership chaining within a database is a useful application design
    technique; however, Microsoft does not recommend cross-database ownership
    chaining because of possible security risks
    Because of the security implications of cross-database ownership chaining,
    SQL Server 2000 SP3 allows you to turn cross-database ownership chaining on
    or off for each database.

    It is configurable only with SQL Server 2000 SP3.
    You can download Updated Books Online from the microsoft.com site and see
    the folowing topics in it for details what does it meen and how to configure:
    Using Ownership Chains
    Cross DB Ownership Chaining

    Gr :)
    Magy Naumova
    MCT, MCDBA

    "DebHerman" wrote:

    > The user is defined in both databases, the one with the view and the one with
    > the underlying tables in the view. I don't want to have to assing
    > permissions to the underlying tables, just the view.
    >
    > "Uri Dimant" wrote:
    >
    > > I see, you have to have the user defined in the database
    > > I'm affraid you cannot assign permissions to the user who does not exist in
    > > the database .
    > >
    > >
    > > "DebHerman" <DebHerman@discussions.microsoft.com> wrote in message
    > > news:03E78361-58D6-449D-8D10-A56DA3EA18A3@microsoft.com...
    > > > Thanks for the reply, but I don't believe this article addressed what I am
    > > > trying to do, or if it did, I didn't recognise it. My problem is when I
    > > try
    > > > to go across databases, I don't get the same results as within the same
    > > > database. I'm not really looking for row level security. I want to give
    > > > users access to a view but not the the underlying tables. This works in
    > > the
    > > > same database but not across databases.
    > > > "Uri Dimant" wrote:
    > > >
    > > > > Hi
    > > > > Read Vyas's article describing row level security
    > > > >
    > > > > http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
    > > > >
    > > > > "DebHerman" <DebHerman@discussions.microsoft.com> wrote in message
    > > > > news:373B1A28-B090-488A-89D1-1DFDF1FEA1FA@microsoft.com...
    > > > > > I am using SQL views to aid with security in my SQL Server
    > > implementation.
    > > > > > For example I create viewA as select field1, field2 from TAble1. I
    > > grant
    > > > > > select access to viewA to user1 but not access to the underlying
    > > table.
    > > > > As
    > > > > > long as the owner of viewA has permissions to the table user1 can
    > > access
    > > > > the
    > > > > > view but not the table. This works great. However I want to take
    > > this
    > > > > one
    > > > > > step further in that viewA acutally is created on tables in another
    > > > > database
    > > > > > on the same server. In this scenario I give select access to viewA to
    > > > > > User1. The owner of viewA rights to the table is the other database;
    > > he
    > > > > > even has dbo rights to the database. In this case user1 can't select
    > > from
    > > > > > viewA. User1 gets the error that he does not have rights to the table
    > > in
    > > > > the
    > > > > > other database. Is there something I am missing or is this not
    > > allowed. I
    > > > > > can't find any specific documentation regarding this. Thanks.
    > > > > >
    > > > >
    > > > >
    > > > >
    > >
    > >
    > >

  • Next message: DebHerman: "Re: Using Views for Security"

    Relevant Pages

    • Re: Cross-database ownership with different dbo
      ... But be aware that cross-database ownership chains and using guest ... Dejan Sarka, SQL Server MVP ... > I have granted access to database to user, ... > But I am for each of these databases and, logically, it would be ...
      (microsoft.public.sqlserver.security)
    • Re: Cross-database ownership with different dbo
      ... I have granted access to database to user, ... When tries to use this view, SQL Server answers " Server ... But I am for each of these databases and, logically, it would be nice, ... if SQL Server had used cross-database ownership feature without granting ...
      (microsoft.public.sqlserver.security)
    • Cross-database ownership with different dbo
      ... We have several databases within a single instance of SQL Server SP3a. ... Cross-database ownership is enabled for the instance. ... user does not have a corresponding login. ... In this database user [dbo] ...
      (microsoft.public.sqlserver.security)
    • Re: Security, Views, Multiple DBs
      ... the 'top' object is also mapped to the user that is the owner of the 'lower ... the same login, It's enough just to give your users permissions on the 'top' ... "Security Considerations for Cross-Database Ownership Chaining: ... highly-privileged users with data in other databases. ...
      (microsoft.public.sqlserver.security)