By tom


2017-12-15 18:55:33 8 Comments

I created view in database1 based on tables in database2. I gave SELECT permission to a user who has access only to database1. The user can't get this view to work because he doesn't have an account in database2. How can I solve this issue? I don't want to create an account in database2.

1 comments

@Solomon Rutzky 2017-12-15 19:02:10

This is easy to accomplish in a very secure way using Module Signing. This will be similar to the following two answers of mine, also here on DBA.StackExchange, that give examples of doing just this:

Stored procedure security with execute as, cross database queries, and module signing

Permissions in triggers when using cross database certificates

The difference for this particular question is that it deals with a View, and Views cannot be signed. So, You will need to change the View into a multi-statement Table-Valued Function (TVF) as those can be signed and can be accessed just like a View (well, for SELECT access).

The following example code shows doing exactly what is being requested in the question in that the Login / User "RestrictedUser" only has access to "DatabaseA" and is yet able to get data from "DatabaseB". This works only by selecting from this one TVF, and only due to it being signed.

Accomplishing this type of cross-database access while still using a View, and not giving the User any additional permissions, would require enabling Cross-Database Ownership Chaining. That is far less secure because it is completely open-ended for all objects between both Databases (it cannot be restricted to certain objects and/or Users). Module Signing allows just this one TVF to have the cross-DB access (the User doesn't have the permission, the TVF does), and Users that cannot SELECT from the TVF have no access to "DatabaseB" at all.

USE [master];

CREATE LOGIN [RestrictedUser] WITH PASSWORD = 'No way? Yes way!';
GO

---

USE [DatabaseA];

CREATE USER [RestrictedUser] FOR LOGIN [RestrictedUser];

GO
CREATE FUNCTION dbo.DataFromOtherDB()
RETURNS @Results TABLE ([SomeValue] INT)
AS
BEGIN
    INSERT INTO @Results ([SomeValue])
        SELECT [SomeValue]
        FROM   DatabaseB.dbo.LotsOfValues;

    RETURN;
END;
GO

GRANT SELECT ON dbo.[DataFromOtherDB] TO [RestrictedUser];
GO
---

USE [DatabaseB];

CREATE TABLE dbo.[LotsOfValues]
(
    [LotsOfValuesID] INT IDENTITY(1, 1) NOT NULL
        CONSTRAINT [PK_LotsOfValues] PRIMARY KEY,
    [SomeValue] INT
);

INSERT INTO dbo.[LotsOfValues] VALUES
    (1), (10), (100), (1000);
GO

---

USE [DatabaseA];

SELECT * FROM dbo.[DataFromOtherDB]();


EXECUTE AS LOGIN = 'RestrictedUser';

SELECT * FROM dbo.[DataFromOtherDB]();
/*
Msg 916, Level 14, State 1, Line XXXXX
The server principal "RestrictedUser" is not able to access
the database "DatabaseB" under the current security context.
*/

REVERT;

All of the steps above recreate the current situation: the User has access to DatabaseA, has permission to interact with an object in DatabaseA, but gets an error due to that object in DatabaseA accessing something in DatabaseB where the User does not have any access.

The steps below set up the Module Singing. It does the following:

  1. creates a Certificate in DatabaseA
  2. Signs the TVF with the Certificate
  3. Copies the Certificate (without the Private Key) to Database B
  4. Creates a User in DatabaseB from the Certificate
  5. Grants SELECT permission to the Table in DatabaseB to the Certificate-based User

Module Signing setup:

CREATE CERTIFICATE [AccessOtherDB]
    ENCRYPTION BY PASSWORD = 'SomePassword'
    WITH SUBJECT = 'Used for accessing other DB',
    EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE
    TO dbo.[DataFromOtherDB]
    BY CERTIFICATE [AccessOtherDB]
    WITH PASSWORD = 'SomePassword';

---
DECLARE @CertificatePublicKey NVARCHAR(MAX) =
            CONVERT(NVARCHAR(MAX), CERTENCODED(CERT_ID(N'AccessOtherDB')), 1);

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

EXEC (N'USE [DatabaseB];
CREATE CERTIFICATE [AccessOtherDB] FROM BINARY = ' + @CertificatePublicKey + N';');
---


EXEC (N'
USE [DatabaseB];
CREATE USER [AccessOtherDbUser] FROM CERTIFICATE [AccessOtherDB];

GRANT SELECT ON dbo.[LotsOfValues] TO [AccessOtherDbUser];
');

---



EXECUTE AS LOGIN = 'RestrictedUser';

SELECT * FROM dbo.[DataFromOtherDB]();
-- Success!!

SELECT * FROM [DatabaseB].[dbo].[LotsOfValues];
/*
Msg 916, Level 14, State 1, Line XXXXX
The server principal "RestrictedUser" is not able to access
the database "DatabaseB" under the current security context.
*/

REVERT;

IF ACCESS NEEDS TO BE THROUGH A VIEW, for whatever reason, then you can simply create a View that selects from the TVF shown above. And, in that situation, SELECT access does not need to be granted to the TVF, only to the View, as demonstrated below:

GO
CREATE VIEW dbo.[DataFromTVF]
AS
SELECT [SomeValue]
FROM   dbo.DataFromOtherDB();
GO

-- Remove direct access to the TVF as it is no longer needed:
REVOKE SELECT ON dbo.[DataFromOtherDB] FROM [RestrictedUser];

GRANT SELECT ON dbo.[DataFromTVF] TO [RestrictedUser];

And now to test it:

EXECUTE AS LOGIN = 'RestrictedUser';


SELECT * FROM dbo.[DataFromOtherDB]();
/*
Msg 229, Level 14, State 5, Line XXXXX
The SELECT permission was denied on the object 'DataFromOtherDB',
database 'DatabaseA', schema 'dbo'.
*/


SELECT * FROM [OwnershipChaining].[dbo].[LotsOfValues];
/*
Msg 916, Level 14, State 1, Line XXXXX
The server principal "RestrictedUser" is not able to access
the database "DatabaseB" under the current security context.
*/


SELECT * FROM dbo.[DataFromTVF];
-- Success!!


REVERT;

For more info on Module Signing, please visit: https://ModuleSigning.Info/

@Chris Aldrich 2018-05-30 20:01:36

Are the certs backed up as part of regular backups? Or are they stored elsewhere and require a file system backup as well? And what happens if you restore to a lower environment that may use different passwords, etc.?

@Solomon Rutzky 2018-05-30 20:09:38

@ChrisAldrich In the usage shown here, it is backed up with the DB since it is entirely held within the Database. If you use ALTER CERTIFICATE ... DROP PRIVATE KEY then the private key would be gone if you did not first back it up to a file using BACKUP CERTIFICATE. But, the public key is still in sys.certificates. And the public key does not need the password. Only using the private key to sign a module requires the password (which is the same across servers, unlike when protecting via master key).

Related Questions

Sponsored Content

3 Answered Questions

[SOLVED] Permission to create linked server

2 Answered Questions

1 Answered Questions

[SOLVED] What would cause "SELECT permission denied" for an object?

3 Answered Questions

2 Answered Questions

3 Answered Questions

1 Answered Questions

2 Answered Questions

Sponsored Content