Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Recently, we had a stored proc request that required access across multiple databases. Of course this is not uncommon to have but in our case, earlier we used service accounts and this service account had read/write permissions to all the databases.

But now we changed all permissions to windows sign on AD group and the users in the AD group do not have all permissions to all the databases.

Example:

  • AD group UG1 has 10 users who are users on Database A.
  • Stored procedure PROCA resides in Database A but has selects on the tables that belongs to Database B and Database C.
  • Execute permissions have been granted to the stored proc PROCA and the AD group UG1 as users (with no other permissions) has been created on Database B and Database C.
  • The database owners of the DatabaseA, DatabaseB and DatabaseC are under login XXX.

As we all know, such permissions across databases can be accomplished by

  • Option 1 : Switching on Database Cross chains ON all the databases, or
  • Option 2 : Giving the AD group UG1 required individual object permissions on the databases (B and C) or
  • Option 3 : Using Trustworthy db options on DatabaseA and changing the stored proc PROCA to include execute as “dbo” user so that the permissions can impersonate.
  • Option 4: Using certficates

My question is what would be the best solution with respect to security and keeping it simple and safe?

Please note that none of the developers are database owners of any of the databases and elevated permissions are only assigned to DBA’s and hence the chances of someone hacking or SQL injecting or impersonating as sysadmin is “none”.

But again, I would like to know what your take would be in such scenarios? I prefer option 3.

share|improve this question

migrated from stackoverflow.com Jun 15 '12 at 15:08

This question came from our site for professional and enthusiast programmers.

    
From your mention of AD I assume you are talking about SQL Server. Is that correct? What version of SQL Server and Windows are you using? –  Nick Chammas Jun 15 '12 at 15:39
    
SQL 2008 and Windows 2008. Thanks for your help –  DBAuser Jun 15 '12 at 15:43
add comment

1 Answer 1

up vote 2 down vote accepted

Option 1 is the worst of all.. would not use that..

Option 2 a lot of administrative effort..

Option 3 (see last sentece :) )

Option 4 recently I had to think about how to secure databases.. My solution was this one. there is great article on security here. Even thought it was a bit complicated to use Certificates at beginning (never done with them anything before) I am very happy of this choice. I grant permissions to Certificate Login/User. I sign procedures by that certificate and everything works. I use the same system to grant permissions for dynamic SQL (so if there is sp_executesql I grant minimum rights to necessary tables to Certificate User, so caller of proc can not see tables and i do not have to make Execute as Owner or smthng). And the same to grant rights to use Asymmetric Keys. And I always know WHO was the caller of procedure (no Execute As), even though its not very important in my case..

Its a bit cumbersome to sign procedures during development time (trying to find good way around that). Currently I am searching procedure text (DDL trigger) for use of other database and if I find it, I print out warning, that procedure possibly has to be signed..

Another thing I miss is that its not possible to sign functions or views (there must be a good reason) so you cant use them to access other DB..

Last thing I would like to say- I would prefer to solve as many problems I possibly can with minimum variety of patterns.. So if you extensively using "Execute As" and proxy users then maybe Trustworthy option is better choice.

share|improve this answer
    
Thanks. Good information. I agree with the certificate authentication but to use the certificate, you need to use the password which means it is open again and anyone could use this in their code, correct? Also, I would think this solely depends on the types of business and applications that are used. If an environment and the security is built with the needs for developers with minimal coding change to the existing code, such solutions often are difficult to get implemented. Thanks once again for your time –  DBAuser Jul 18 '12 at 13:59
    
No, password is not required. You can encrypt certificate by master key (DMK) and to SIGN modules you need control rights on certificate. Usage of signed modules doesn't change- the only difference is that you sign module every time if you change it. –  Jānis Jul 18 '12 at 14:25
    
Sorry. You are right. I was confusing this with application role security. –  DBAuser Jul 18 '12 at 15:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.