Re: Using Views for Security

From: DebHerman (DebHerman_at_discussions.microsoft.com)
Date: 05/18/05 

Date: Wed, 18 May 2005 01:41:01 -0700

Thanks so much.
That would explain why in my preliminary tests I was sure I had this
working. Now, the object owners are different login accounts even though
they are both the dbo user. I'll have to think about the security
implications.
My reason for doing this is two fold.
1. The database in question is our hr/finance database. We develop
additional applications that access this database. I want to keep this
database clean of any additional views or other objects other than those that
ship with the hr/finance system. This is of course for maintenance reasons.
So I have created a seperate database that includes all views into this
database. It also lets us see all object that are not part of the original
database very easily.
2. I don't really want to give any access to the hr/finance database.

I've solved my first objective but not the second. If I decide to proceed, I
believe there is a sp that will change the owner of an object. Do you know
what that is. Thanks so much for you time!

"Magy Naumova" wrote:

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

Relevant Pages

  • Re: Using Views for Security
    ... A security consideration when cross-database chaining enabled is that you ... must fully trust users that have permissions to create dbo-owned objects. ... in order to access objects in a database. ... It is on cross-database ownership. ...
    (microsoft.public.sqlserver.security)
  • Re: Stored proc select denied error
    ... > You problem has to do with cross-database ownership chaining. ... On the first database the user has exec rights to a stored ... That stored procedure selects * from two tables with a join. ...
    (microsoft.public.sqlserver.programming)
  • Re: Viewing object owner in SQL 2005 - ownership chaining
    ... The server principal "HFDB_2_0_readonly" is not able to access the database "Hotfix_data" under the current security context. ... To add on to Erland's response, the user needs a security context in both databases, even if no permissions are granted. ... I'm trying to set up Ownership Chaining. ... When I set up both a table and view from one logon and granted permissions to the view it didn't work, evidently because "creating user" is not equivalent to OWNER. ...
    (microsoft.public.sqlserver.security)
  • Re: current security context is not trusted (cross db ownership ch
    ... ALTER DATABASE only in the specific cases where needed. ... The instance of SQL Server will recognize this setting when the cross ... db ownership chaining server option is 0. ...
    (microsoft.public.sqlserver.security)
  • Re: current security context is not trusted (cross db ownership ch
    ... The instance of SQL Server will recognize this setting when the cross ... database option is ignored when 'cross db ownership chaining' of sp_configure is set to 1. ...
    (microsoft.public.sqlserver.security)