How to grant execute permissions to a stored procedure but not to the underlying databases

Question

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, assigned db_datareader access.

Stored Procedure (BI_Datadownload):

• Found in database ORMAN.
• Calls table in another database PSMAN

Any help would be appreciated.

Cheers.

Answer 1

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 the BI_DataDownload stored procedure to complete successfully.

The BI_User account still only has permission to connect to [ORMAN] and to execute BI_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 the BI_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 the ADD 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

USE [master];

CREATE DATABASE [ORMAN];
ALTER DATABASE [ORMAN] SET RECOVERY SIMPLE;

CREATE DATABASE [PSMAN];
ALTER DATABASE [PSMAN] SET RECOVERY SIMPLE;


CREATE LOGIN [BI_User] WITH PASSWORD = 'DBA.SE_239700';
GO


USE [PSMAN];
CREATE TABLE dbo.RemoteTable
(
  [RemoteTableID] INT NOT NULL IDENTITY(1, 1)
    CONSTRAINT [PK_RemoteTable] PRIMARY KEY,
  [SomeValue] NVARCHAR(50) NULL
);

INSERT INTO dbo.[RemoteTable] ([SomeValue]) VALUES (N'one');
INSERT INTO dbo.[RemoteTable] ([SomeValue]) VALUES (N'two');
GO


USE [ORMAN];
CREATE USER [BI_User] FOR LOGIN [BI_User];

GO
CREATE PROCEDURE dbo.[BI_DataDownload]
AS
SET NOCOUNT ON;

SELECT rt.[RemoteTableID], rt.[SomeValue]
FROM   [PSMAN].[dbo].[RemoteTable] rt;

GO

GRANT EXECUTE ON dbo.[BI_DataDownload] TO [BI_User];
GO

Test Current Situation (no extra permissions)

EXECUTE AS LOGIN = N'BI_User';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC dbo.[BI_DataDownload];
/*
Msg 916, Level 14, State 1, Procedure dbo.BI_DataDownload,
            Line XXXXX [Batch Start Line YYYYY]
The server principal "BI_User" is not able to access the database
       "PSMAN" under the current security context.
*/

REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

Module Signing Setup

-- Create Certificate and sign Stored Procedure with it:
CREATE CERTIFICATE [Permission$PSMAN]
    ENCRYPTION BY PASSWORD = 'SomePassword'
    WITH SUBJECT = 'Used for accessing PSMAN',
    EXPIRY_DATE = '2099-12-31';


ADD SIGNATURE
    TO dbo.[BI_DataDownload]
    BY CERTIFICATE [Permission$PSMAN]
    WITH PASSWORD = 'SomePassword';

---
-- Copy Certificate to [PSMAN] DB:
DECLARE @CertificatePublicKey NVARCHAR(MAX) =
            CONVERT(NVARCHAR(MAX), CERTENCODED(CERT_ID(N'Permission$PSMAN')), 1);

SELECT @CertificatePublicKey AS [Cert / PublicKey]; -- debug

EXEC (N'
USE [PSMAN];
CREATE CERTIFICATE [Permission$PSMAN] FROM BINARY = ' + @CertificatePublicKey + N';');
---


-- Create User in [PSMAN] DB to allow for extending permssions there:
EXEC (N'
USE [PSMAN];
CREATE USER [Permission$PSMAN] FROM CERTIFICATE [Permission$PSMAN];

GRANT SELECT ON dbo.[RemoteTable] TO [Permission$PSMAN];
');

GO

Test with Module Signing

EXECUTE AS LOGIN = N'BI_User';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC dbo.[BI_DataDownload];  -- SUCCESS!!!!!!!!
/*
RemoteTableID    SomeValue
1                one
2                two
*/


REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];