I am trying to allow a user to run a stored procedure while restricting them read/write access to the underlying tables. I am able to complete this with other stored procedures, but the one I am working with contains tables in a second database.
I have restricted the user from accessing the underlying tables in the database the stored procedure was created in (database ORMAN
), but the only way this stored procedure will run is if I give the user READ permissions to the entire second database that contains one of the tables called in the stored procedure (database PSMAN
).
This is the setup:
Databases:
ORMAN
PSMAN
User:
BI_User
ORMAN
database access, has access to execute stored procedure (BI_Datadownload
).PSMAN
database access, assigneddb_datareader
access.
Stored Procedure (BI_Datadownload
):
- Found in database
ORMAN
. - Calls table in another database
PSMAN
Any help would be appreciated.
Cheers.
Best Answer
This is easy to accomplish with Module Signing.
The concept is to create a certificate in the database that the user,
BI_User
, has access to (and contains the stored procedure),[ORMAN]
, which will then be used to sign the stored procedure,BI_DataDownload
, that has the functionality the are currently restricted from accessing.The certificate is then copied (public key only) into the restricted database,
[PSMAN]
, for the purpose of creating a local user from it. That local certificate-based user is then assigned whatever permission(s) are needed in order for theBI_DataDownload
stored procedure to complete successfully.The
BI_User
account still only has permission to connect to[ORMAN]
and to executeBI_DataDownload
. No permissions are being granted to that account (Login or User) at all. The permissions are granted only to the certificate-based user that exists in the[PSMAN]
database. The connection between theBI_DataDownload
stored procedure and the certificate-based user is (effectively) the public key of the certificate (which is why we only needed to copy that part to the[PSMAN]
database). And that public key is only loaded into the session's security context when a module that has been signed with that certificate is being executed. And that signature requires the certificate's password (in theADD SIGNATURE
statement), and is dropped if anyone changes even a single byte of the stored procedure (or the owner of it). Hence, very secure.The following illustrates this concept:
Main Setup
Test Current Situation (no extra permissions)
Module Signing Setup
Test with Module Signing