Permissions: Execute Stored Procedure that references another Database

Question

Permissions: Execute Stored Procedure that references another Database

Jason Wisdom-380639

SSCrazy

Points: 2083
More actions
September 9, 2008 at 10:54 am

#125381

Hi,
I have a stored procedure. It selects from local tables and other-database tables (same server). It goes something like this
[use database1]
go
CREATE PROCEDURE myProc
AS
BEGIN
SELECT * FROM table1 t1 JOIN database2.dbo.table2 t2 ON t1.id=t2.id
END
go
The user (Windows Authentication) has db_owner privs on database1, and NO privs on database2.
The user cannot have privs on database2, as it is highly sensitive data.
However, the stored procedure on database1 has to be able to access the table on database2.
How can I do this??
Thank you greatly.
Jason

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Look up "Cross Database Ownership Chaining".

You may want to consider creating some views of the small amount of data they can get to in the second database and giving them permissions to these views rather than enabling the ownership chaining. It will probably be easier to make more secure. I would recommend doing that and then creating synonyms in the database they do have access to to keep things single on the development end.

Jason Wisdom-380639 SSCrazy Points: 2083 More actions · Answer 2

Michael Earl (9/9/2008)
Look up "Cross Database Ownership Chaining".

Yes my first thought was to create Synonyms and grant access to specific tables.

What are the risks with Cross Database Ownership Chaining?

Is it only that some rogue db_owner may create tables on Database 2 and gain permission that way? Or is there something else.

Thanks a bunch.

Jason

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

I honestly cannot remember. I do remember that MS decided to disable it by default way back between sp3 and sp4 on SQL 2000.

I think you would need to be able to create or modify a view or procedure in the one database that has the same owner as objects in the other database to exploit it as a security hole, but there may have been more to it than that. It is global - it applies to ALL databases on the server, not just the one you want to be able to link the security to.

Managing it with a couple of views and a security role in the second database will be a far more grandular level of security, so it will be safer. It also ensures you have an extra step to expose the data. With the ownership chaining if someone with permissions happens to query the sensitive data because they were not thinking, the users would not have access unless it was explicitly given to them. Not to mention you should be using schemas for your object owners and I would expect the schemas in the two databases to be different - or have you left everything in the dbo schema?

Jason Wisdom-380639 SSCrazy Points: 2083 More actions · Answer 4

Here is a blurb from Microsoft:

Risks Associated with Cross-Database Ownership Chaining
Microsoft recommends that you disable the cross-database ownership chaining option because of the actions that highly-privileged users can perform:
•Database owners and members of the db_ddladmin or the db_owners database roles can create objects that are 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 these users with data in all databases. To identify the members of the db_ddladmin and the db_owners roles in the current database, execute the following Transact-SQL commands:
exec sp_helprolemember 'db_ddladmin' exec sp_helprolemember 'db_owner'
•Users with 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 Microsoft recommends that you turn off cross-database ownership chaining for maximum security, there are some environments where you can fully trust your highly-privileged users; therefore, you can enable cross database ownership for specific databases to meet the requirements of specific applications.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

There you have it.

If you have faith in your users, it is ok to turn it on.

...and if a flying pig stops by...