Re: Using Views for Security

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

Date: Wed, 18 May 2005 06:10:06 -0700

You are welcome Deb :)
You obviously had that working before because you didnt install SP3.
But be carefull of using accounts in that cross ownership, you have to trust
that account becouse he/she can use newlly attached databases o oter
databases in your server.
And the stored procedure is sp_changeobjectowner (again new in sp3) but why
do you want to use it?. It is a matter of your login accounts and enabling
config option for cross database chaining.

Magy Naumova
MCT, MCDBA

"DebHerman" wrote:

> 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: Please! Doesnt anyone know a better way to do this?
    ... account, they need to automatically be directed to the page to enter data ... session variable on the Account page. ... I assume here that you're checking a database when the user attempts to ... When a new user attempts to login or clicks to register, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Best way to parse file into db-type layout?
    ... That's an awful lot of columns to put into a database table, ... The rows are identified by Letter type and an account number (always the ... Off to search for dictionaries and databases now. ... > What you need is a clear appreciation of what you are trying to do ...
    (comp.lang.python)
  • Re: Help with WSS 3.0 Server Farm Config - Backend SQL 2005
    ... I had to use only "sharepoint" to get the ... What interest me though is that the database get created but fails after ... Virtual Server with DBSVR ... an account local to the WEBSVR) to create and access the SQL server, ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Relationships, forms and command buttons
    ... asking how to do a particular thing in terms of the database objects itself. ... If they change their bank account, ... tblIndividuals and tblCAFGAYEDons) with linking tables with an autonumber PK ...
    (microsoft.public.access.gettingstarted)
  • RE: Take This Value and Shove It
    ... because of the way I must acquire the loan account numbers and borrower ... To get that data into the Access database, and keep it updated, I have ... Your subform record source should be tblNotes or a query based on it. ...
    (microsoft.public.access.formscoding)