Sql-server – Cross database reference select access with disabled login for DB user

permissionssql serversql-server-2008-r2

We have two databases (DB1, DB2) with the following view structure.

DB1:

T1, T2, T3, ….. Tn (Tables)
V1 (View consuming {T1, T2, T3 ….. Tn })

Inside DB2 we have to create a view V2 which will pull the data from DB1.dbo.V1

USE DB2;
CREATE VIEW dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic

Now the problem statement is, user created for the DB2 should not have any login access to DB1 but should be able to login on DB2 to consume the V2.

I don't want to disable the owner chain setting. Till now we have tried the below solution. But it enables the user to see the selected view (V1) inside DB1.

USE DB1;

CREATE View dbo.V1 AS SELECT * FROM T1; -- And some other tables according to business logic

CREATE USER testUser FOR LOGIN testUser;

GRANT SELECT ON dbo.V1 To testUser;

USE DB2;

CREATE View dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic

Please consider testUser is already present for DB2, I have just mapped it with the DB1 database.

This solution provides me the expected result. But it also allows testUser to login on the DB1 and fetch the data directly from DB1.dbo.V1. Is there any way to deny login access to DB1 but at the same time allowing select access to V2 created inside the DB2?

What we are trying to do over here is to create an user who will have a complete access to DB2. User should be able to fetch the data using the internal views as explained above. But should not able to login to DB1. In Linux/Unix based systems admin can create an internal user which can be used for the access definitions but can not be used for the logging in into system. We are looking solution to implement something similar in SQL.

If we don't grant select access to V1, server throws permission error:

The SELECT permission was denied on the object V1, database 'DB1', schema 'dbo'.

Best Answer

Make sure DB_CHAINING is ON.
Map TestUser Login to DB1 and DB2.
Grant SELECT on V2.
Don't grant SELECT on V1.

See Ownership Chains in the documentation.

Basically, what it does is that, because V1 and V2 has the same owner (that's mandatory) and because DB_CHAINING is ON, when TestUser will SELECT V2, permissions will not be checked on V1, so you don't need to grant it any permission.

TestUser will be able to retrieve data from V2 but SELECT on V1 will be denied.

Double-check that DB_CHAINING is ON by executing:

SELECT name, is_db_chaining_on 
FROM sys.databases;

If it's not the case, set it with:

ALTER DATABASE DBX SET DB_CHAINING ON

...and make sure both views have the same owner.

You can try following code to validate that it's working:

Connect as sysadmin and create Databases DB1 and DB2

USE master
GO
CREATE DATABASE DB1;
GO
CREATE DATABASE DB2;
GO

in DB1:

Create a sample table TB1

Insert a value in TB1 table (this is only because I dont like empty result set)

Create a sample view selecting all rows in TB1 table

USE DB1
GO
CREATE TABLE TB1 (id int);
GO
INSERT INTO TB1 VALUES(42);
GO
CREATE VIEW V1 AS SELECT * FROM TB1;
GO

in DB2:

Create a sample view V2 selecting all rows in DB1.dbo.V1 view

USE DB2
GO
CREATE VIEW V2 AS SELECT * FROM DB1.dbo.V1;
GO

Create TestUser Login

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

in DB1:

Create TestUser User mapped to TestUser Login

USE [DB1]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO

in DB2:

Create TestUser User mapped to TestUser Login

Grant SELECT on V2 view to TestUser user

USE [DB2]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
GRANT SELECT ON V2 TO TestUser;

Connect with TestUser Login

In DB2:

Try to select from V2

USE DB2
GO
SELECT * FROM V2;

This should fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.

Connect as sysadmin

Enable DB_CHAINING on DB1

USE master
GO
ALTER DATABASE DB1 SET DB_CHAINING ON;
GO

Enable DB_CHAINING on DB2

ALTER DATABASE DB2 SET DB_CHAINING ON;
GO

Connect with TestUser Login

In DB2:

Try to Select From V2 again, that time it should succeed

USE DB2
GO
SELECT * FROM V2;

In DB1:

Try to Select From V1

USE DB1
GO
SELECT * FROM V1;
GO

As we didnt grant TestUser any permission this will fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.

Related Solutions

Sql-server – How to add a user with access to a single view

Please don't use the UI for this. It's a confusing mess.

It sounds to me like what you want is to create a user in a database, for a specific login, who only has permissions to select from one view. So, since you already have the login created:

USE your_db;
GO
CREATE USER username FROM LOGIN username;
GO
GRANT SELECT ON dbo.MyViewName TO username;
GO

EDIT here is an example of a script that will lead to the error you mention.

First, create some table in the unrelated_db:

CREATE DATABASE unrelated_db;
GO
USE unrelated_db;
GO
CREATE TABLE dbo.foo(bar INT);
GO

Now create a relatively restricted login:

USE [master];
GO
CREATE LOGIN username WITH PASSWORD='foo', CHECK_POLICY = OFF;
GO

Now create a database where the view will live, and add the login as a user:

CREATE DATABASE velojason;
GO
USE velojason;
GO
CREATE USER username FROM LOGIN username;
GO

Now create a function that will reference the table in the other database, and a synonym to the other table:

CREATE FUNCTION dbo.checkbar()
RETURNS INT
AS 
BEGIN
    RETURN 
    (
      SELECT TOP (1) bar 
        FROM unrelated_db.dbo.foo 
        ORDER BY bar
    );
END
GO
CREATE SYNONYM dbo.foo FOR unrelated_db.dbo.foo;
GO

Now create a local table:

CREATE TABLE dbo.PaymentDetails
(
  PaymentID INT
);
GO

Now create a view that references the table, the function and the synonym, and grant SELECT to username:

CREATE VIEW dbo.SomeView
AS
  SELECT 
    p.PaymentID, 
    x = dbo.checkbar(), -- function that pulls from other DB
    y = (SELECT bar FROM dbo.foo) -- synonym to other DB
    FROM dbo.PaymentDetails AS p;
GO
GRANT SELECT ON dbo.SomeView TO username;
GO

Now try to execute as username and select only the local column from the view:

EXECUTE AS USER = 'username';
GO
  -- even though I don't reference any of the columns 
  -- in the other DB, I am denied SELECT on the view:
SELECT PaymentID FROM dbo.SomeView;
GO
REVERT;
GO

Result:

Msg 916, Level 14, State 1, Line 3
The server principal "username" is not able to access the database "unrelated_db" under the current security context.

Now change the view to not reference any external objects, and run the above SELECT again, and it works:

ALTER VIEW dbo.SomeView
AS
  SELECT 
    p.PaymentID 
    --x = dbo.checkbar(),
    --y = (SELECT bar FROM dbo.foo)
    FROM dbo.PaymentDetails AS p;
GO

Short of showing us the scripts for the Payment Details, Account Details and MyView objects, maybe you can let us know if this query returns any results. You can find references to various objects through the catalog view sys.sql_expression_dependencies, but this view is not perfect - I believe it depends on all the views being refreshed (in the case where views reference other views, for example, or underlying schema has changed) in order to be accurate.

DECLARE 
  @dbname   SYSNAME = N'unrelated_db',
  @viewname SYSNAME = N'dbo.SomeView';

SELECT DISTINCT 
    [This object] = 
    OBJECT_SCHEMA_NAME([referencing_id]) 
      + '.' + OBJECT_NAME([referencing_id]), 
    [references this object] = 
    OBJECT_SCHEMA_NAME([referenced_id]) 
      + '.' + OBJECT_NAME([referenced_id]), 
    [and touches this database] = referenced_database_name,
    [and is a(n)] = o.type_desc,
    [if synonym, it references] = s.base_object_name
FROM sys.sql_expression_dependencies AS d
LEFT OUTER JOIN sys.objects AS o
ON o.[object_id] = d.referenced_id
LEFT OUTER JOIN sys.synonyms AS s
ON d.referenced_id = s.[object_id]
AND s.base_object_name LIKE '%[' + @dbname + ']%'
WHERE OBJECT_ID(@viewname) IN (
        referenced_id, 
        referencing_id, 
        (SELECT referencing_id FROM sys.sql_expression_dependencies 
        WHERE referenced_database_name = @dbname)
) OR referenced_database_name = @dbname;

SQL Server isn't just going to try to access unrelated_db for the fun of it... there must be some tie to that database from the view you're trying to use. Unfortunately if we can't see the view definition and more details about the objects it touches, all we can do is speculate. The two main things I can think of are synonyms or functions that use three-part names, but seeing the actual scripts will give us a much better idea instead of guessing. :-)

You may also want to check sys.dm_sql_referenced_entities, however this function returns nothing useful in the example above.

Sql-server – SQL Server Login to access tables of three different database from single database

Cross-database ownership chaining is the easier solution. But as Oliver noted, it does come with some security exposures.

If you want to avoid the cross-database ownership chaining approach you can, to quote Erland Sommarskog: "When you need to write a stored procedure that accesses data in another database, you can arrange permissions by signing your procedure with a certificate that exists in both databases."

Erland has a lengthy article on the subject. I am giving you the link to the specific issue of cross database permissions using a certificate: http://www.sommarskog.se/grantperm.html#certcrossdb

This is safer, but it is more work.

Best Answer

Related Solutions

Sql-server – How to add a user with access to a single view

Sql-server – SQL Server Login to access tables of three different database from single database

Related Question