3

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.

1
  • Have you enabled Cross database ownership chaining? Commented Jun 3, 2019 at 14:06

1 Answer 1

4

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];
2
  • Hi Solomon Rutzky, late to the party, but hopw you read comments still :) I have a question to this method: If the procedure is callling a view that again is calling a synonym, and that synonym is pointing to a table in the second database (I know), will this still work? I think I read an article of yours somewhere saying that it did not work with views? And what happens if the procedure changes? Do we have to add the signature to it once again? (I can see it being difficult to manage with developers doing changes all the time). Thx in advance.
    – GHauan
    Commented Sep 22, 2022 at 11:31
  • @GHauan HI there. Well, you can't sign views, but you are signing the proc that is selecting from a view, so there is a chance that might work. Certainly worth trying out (excellent question!). And yes, if ALTER is called on any code that has been signed, then the signature will be dropped, even if there are no actual changes. As you suggested, the "altered" procs will need to be signed again. A bit cumbersome, but hopefully that can be integrated into an automated process. Commented Sep 28, 2022 at 5:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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