Re: Security, Views, Multiple DBs

• Previous message: Dejan Sarka: "Re: security based on value of X inside a table!"
• In reply to: Craig: "Security, Views, Multiple DBs"
• Next in thread: Dan Guzman: "Re: Security, Views, Multiple DBs"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Sun, 17 Aug 2003 00:13:58 +0200

The only requirement is that that the login that is mapped to the owner of
the 'top' object is also mapped to the user that is the owner of the 'lower
level' objects in the other DBs. The user accessing the 'top' object does
not need access to the other DB. The whole idea here is to prevent this
'direct' access.

If (as MS recommends) dbo owns all objects in all DBs and the DB owner is
the same login, It's enough just to give your users permissions on the 'top'
object.

You should be aware of the risks introduced by enabling cross database
ownership chaining.

>From BOL:

"Security Considerations for Cross-Database Ownership Chaining:
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. These risks are due to the
actions that highly-privileged users can perform:
Database owners and members of the db_ddladmin or db_owners database roles
can create objects owned by other users. These objects can potentially
target objects in other databases. This means that if you enable
cross-database ownership chaining, you must fully trust your
highly-privileged users with data in other databases.
Users with the CREATE DATABASE permission can create new databases and
attach existing databases. If cross-database ownership chaining is enabled,
these users can access objects in other databases from newly created or
attached databases.
Even though cross-database ownership chaining is not recommended, some
applications might require cross-database ownership chaining. Additionally,
there are some environments where you can fully trust your highly-privileged
users. For this reason, cross-database ownership chaining is configurable."

HTH

"Craig" <reply@newsgroup.only> wrote in message
news:04a001c36386$e815b4d0$a401280a@phx.gbl...
> We have multiple databases. One database contains views
> and stored procedures that access data on the other
> databases. I give permission to various users only on
> these views and stored procedures.
>
> We have decided to add a new SQL server (not NT
> authenticated) login to the server. This new login needs
> to get access to the tables in the various databases via
> the views and stored procedures.
>
> I know I need to add a user mapped to the login to the
> database with the views and stored procedures and give
> permissions to those views and stored procedures. In the
> past, I've also added a user for the login to the other
> databases, although I have given no access to any objects
> in the database. That way they can access the data via
> the view or stored procedure but not directly through the
> table.
>
> My question is, do I really need to add each new login to
> the secondary databases. Should I be able to add a guest
> account to the secondary databases and accomplish the same
> effect. After all, I do not wish to grant permission to
> any objects, I just need the login to map to something in
> the database.
>
> Any advise and help would be greatly appreciated.

• Previous message: Dejan Sarka: "Re: security based on value of X inside a table!"
• In reply to: Craig: "Security, Views, Multiple DBs"
• Next in thread: Dan Guzman: "Re: Security, Views, Multiple DBs"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]