Re: Using Views for Security
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 05/18/05
- Previous message: DebHerman: "Re: Using Views for Security"
- In reply to: DebHerman: "Re: Using Views for Security"
- Next in thread: Magy Naumova: "Re: Using Views for Security"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 18 May 2005 07:50:58 -0500
> I'll have to think about the security implications.
A security consideration when cross-database chaining enabled is that you
must fully trust users that have permissions to create dbo-owned objects. A
user could access dbo-owned objects in other databases that have the same
owner by creating referencing dbo-owned objects.
> 2. I don't really want to give any access to the hr/finance database.
As previously mentioned in this thread, a user must have a security context
in order to access objects in a database. This can be accomplished by
adding the user to the database, granting a Windows group (of which the user
is a member) database access or enabling the guest user. However, direct
access to the underlying tables is not allowed unless you grant object
permissions.
> 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!
You can change object ownership with sp_changeobjectowner, although I don't
see how this will address your issues. The ownership chain is broken when
object owners map to different logins so permissions on the underlying
tables will be required
-- Hope this helps. Dan Guzman SQL Server MVP "DebHerman" <DebHerman@discussions.microsoft.com> wrote in message news:A178C46F-67C6-4611-BCF6-6E0EC37196CA@microsoft.com... > 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. >> > > > > > >> > > > > >> > > > > >> > > > > >> > > >> > > >> > >
- Previous message: DebHerman: "Re: Using Views for Security"
- In reply to: DebHerman: "Re: Using Views for Security"
- Next in thread: Magy Naumova: "Re: Using Views for Security"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
