TRUSTWORTHY ON to let sproc execute across Dbs

  • 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.

  • 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]

  • 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/

  • 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...

  • 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]

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

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