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.

  • @mustaccio No, this is not a duplicate of that other question / answer as that situation was all within the same database, and this question is about spanning databases. By default that is not allowed. One would have to enable cross-database ownership chaining, and that is a huge security hole to open for such a narrow need. – Solomon Rutzky Dec 15 '17 at 21:16
  • @SolomonRutzky, I wouldn't call DB_CHAINING a "huge security hole". In typical production environments where only sysadmin role members can create objects, it's a non-issue. That said, it should be used carefully in cases where non-sysadmin role members have control permissions on schemas other than those they own. – Dan Guzman Dec 15 '17 at 23:43
  • @DanGuzman "Trust me, everything will always go according to plan" is not an effective strategy. By that logic, there's almost no risk in setting TRUSTWORTHY ON or having the application log in as sa. DB Ownership Chaining and TRUSTWORTHY exist mainly due to being the only solution at the time. But now, even if not a huge risk, DB Chaining is certainly an unnecessary risk since Module Signing is not that difficult. And if one relies on DB chaining and then uses Dynamic SQL, they're more likely to set TRUSTWORTHY ON to fix it, whereas with Module Signing it wouldn't have broken. – Solomon Rutzky Dec 17 '17 at 17:20
  • @SolomonRutzky, I would have suggested module signing if the question were about a module instead of a view. My thinking is that DB_CHAINING is no more risky than intra-database ownership chaining when the objects should have been in the same database anyway. – Dan Guzman Dec 17 '17 at 17:54
  • @DanGuzman Why assume that "the objects should have been in the same database anyway"? The O.P. has only indicated the opposite since they want to keep the DB access separated. That the O.P. is using a View is why I suggested a TVF instead of a Stored Procedure, but that doesn't mean continuing to use a View is the best course of action. It's common to suggest modifying structure and/or approach when it makes sense to do, as is the case here. Still, I added an optional wrapper View to my answer. And, given that it is most common for "dbo" to own everything, yes, DB_CHAINING is quite risky. – Solomon Rutzky Dec 17 '17 at 20:14

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;
  • 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.? – Chris Aldrich May 30 at 20:01
  • @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). – Solomon Rutzky May 30 at 20:09

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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