TRUSTWORTHY ON to let sproc execute across Dbs

Question

TRUSTWORTHY ON to let sproc execute across Dbs

Maxer

SSCrazy Eights

Points: 8964
More actions
December 19, 2016 at 9:06 am

#324130

OK.... this problem again 🙂
Setup:
Database: AllOurFinancialData
We have an external group who is doing some work for us, they need access to:
SELECT *
FROM AllOurFinancialData.NOTdbo.SuperSecretFinancials
WHERE DeptCode = '12345'
nothing else.
I tried doing the view in a NEW database:
ExternalGroupDb
SELECT *
from AllOurFinancialData.NOTdbo.SuperSecretFinancials
WHERE DeptCode = '12345'
That of course didn't work:
The SELECT permission was denied on the object 'SuperSecretFinancials', database 'AllOurFinancialData', schema 'NOTdbo'.
So, I said no big I'll use Stored Procedure Execute as Owner: our DBA owns both databases.
However, that fails because TRUSTHWORTHY was not set to on for ExternalGroupDb.
I turned Trustworthy on, works great...but.....
IS that the only real option I have? *Not doing cross database ownership chaining, freaks me out...
I could sign the stored procedure with a cert but...that's a PITA....
Why can't I get that view to work?
I don't fully understand that? I tried reading through this, but I think I am a bit slow today (up late with cranky baby last night) http://www.sommarskog.se/grantperm.html
I didn't see a way to get a view working in that article?
I have user ExternalGroupUser in both databases.
That user has SELECT rights on the view I created in the ExternalGroupDb and only public rights to 'SuperSecretFinancials'.
I'm missing something here...yes?
Thanks!
*EDIT: DBA owns both databases, so... same owner there.

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

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

The alternatives without moving data are:

1) EXECUTE AS + TRUSTWORTHY. OK, *if* ExternalGroupDb is owned by an SQL login that has no permissions granted, except one: AUTHENTICATE in the other database *and* only trusted people are db_owner (or can create user in ExternalGroupDb.)

2) Enable DB-chaining. This can also be a security risk, if there are unrelated databases that also are enabled for chaining, and people with permissions to create objects in the other databases also has access to these two database.

3) Certificate signing. No security risks at all, and what I would use in this case, as it appears to be a one-off.

Then you can of course set up some form of replication of the data to be exposed elsewhere to avoid the permission problems.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

Maxer (12/19/2016)
I could sign the stored procedure with a cert but...that's a PITA....

That's probably the safest option, and when you do it once, it becomes quite easy after that. I regularly use a certificate for this kind of cross database query in a stored proc.

https://www.mssqltips.com/sqlservertip/3336/how-to-use-module-signing-for-sql-server-security/

Maxer SSCrazy Eights Points: 8964 More actions · Answer 3

Thanks that is most helpful.

Wanted to make sure I didn't miss some simple option or way around it, etc...

Same for VIEWS I assume, and a VIEW can't do Execute As so...

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

Yes, for views database chaining are the only option. However, if you want the interface of a view, you could use a multi-statement function and wrap a view around it.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]