This article is contributed. See the original author and article here.

Scenario: Two schemas: A and B. User test has access to everything under schema A, he does not have permissions under Schema B. Schema A and B were created by the same owner.

A view was created under schema A pointing to a select under schema B. Customer can’t query table under schema B, but once he does a select on the view under Schema A he is able to access data under schema B. (??) weird, is not?

 

 

 

 

 

------------------------------
--Master Database
CREATE LOGIN testUser1 
	WITH PASSWORD = 'Lalala!0000'
----Change to SQLDW
CREATE USER testUser1 FROM LOGIN testUser1
------------------------------------------
CREATE SCHEMA Schema_B;
go
CREATE SCHEMA Schema_A;
go
--------------------------------------
GRANT CREATE SCHEMA ON DATABASE :: [SQL_DW_database_name] TO testUser1 
 
GRANT SELECT, INSERT, DELETE, UPDATE, ALTER ON Schema::Schema_A TO  testUser1 
------------------------------------------
CREATE TABLE Schema_B.TestTbl
WITH(DISTRIBUTION=ROUND_ROBIN)    
AS    
	SELECT 1 AS ID, 100 AS VAL UNION ALL
	SELECT 2 AS ID, 200 AS VAL UNION ALL    
	SELECT 2 AS ID, 200 AS VAL
go
 
 
----------------------------------------
CREATE VIEW Schema_A.Bypass_VW 
AS -- runs successfully
SELECT * FROM Schema_B.TestTbl
 
go
 
-------------------------------------------------------------------------
--Log into SQLDW with the testUser1  ; --->executing as this user.
 
GO
 
SELECT * FROM Schema_B.TestTbl---> user does not have access
 
SELECT * FROM Schema_A.Bypass_VW -- runs successfully and fetches data from table not having select access to
 

 

 

 

 

 

Figure 1 and 2 exemplifies:

onwership_sche.png

Figure 1: Query view

 

onwership_sche_deny.png

Figure 2: Query table

 

Here is the documentation about this:

 

A user with ALTER permission on a schema can use ownership chaining to access securables in other schemas, including securables to which that user is explicitly denied access. This is because ownership chaining bypasses permissions checks on referenced objects when they are owned by the principal that owns the objects that refer to them. A user with ALTER permission on a schema can create procedures, synonyms, and views that are owned by the schema’s owner. Those objects will have access (via ownership chaining) to information in other schemas owned by the schema’s owner. When possible, you should avoid granting ALTER permission on a schema if the schema’s owner also owns other schemas.

For example, this issue may occur in the following scenarios. These scenarios assume that a user, referred as U1, has the ALTER permission on the S1 schema. The U1 user is denied to access a table object, referred as T1, in the schema S2. The S1 schema and the S2 schema are owned by the same owner.

The U1 user has the CREATE PROCEDURE permission on the database and the EXECUTE permission on the S1 schema. Therefore, the U1 user can create a stored procedure, and then access the denied object T1 in the stored procedure.

The U1 user has the CREATE SYNONYM permission on the database and the SELECT permission on the S1 schema. Therefore, the U1 user can create a synonym in the S1 schema for the denied object T1, and then access the denied object T1 by using the synonym.

The U1 user has the CREATE VIEW permission on the database and the SELECT permission on the S1 schema. Therefore, the U1 user can create a view in the S1 schema to query data from the denied object T1, and then access the denied object T1 by using the view

 

(https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver15)

 

Note this is the same across any version of SQL Server: SQLDB, SQLDW, SQL Server

Workaround:

I changed my demo based on the documentation. The point here is: there are  2 schemas with the same owner. So let’s change that: different schema owners.

 

List the  ownership and keep this information:

 

 

 

----Create a new login
--Master Database
CREATE LOGIN testowner 
	WITH PASSWORD = 'Lalala!0000'
--SQLDW
CREATE USER testowner FROM LOGIN testowner
-------------
--list objects ownership
SELECT 'OBJECT' AS entity_type  

    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name  

    ,name  

FROM sys.objects

 
---Keep the result of the permission. Once we change the ownership of the schema the permission per schema will be reset. ( if there is such information)
Select USER_NAME(principal_id),* from sys.schemas

--- I will change Schema_B as my schema permissions are only on Schema_A
ALTER AUTHORIZATION ON SCHEMA::Schema_B TO testowner;    

----------

--list objects ownership again. Check a new owner was added
SELECT 'OBJECT' AS entity_type  

    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name  

    ,name  

FROM sys.objects

 

 

 

And as figure 3 shows, the ownership chaining was solved:

 

onwership_sche_deny_works.png

 

Another way to solve this would include for example: Deny to the user select on the View(Schema_A.Bypass_VW ) or deny the select, like:

Deny select on Schema_A.Bypass_VW  TO testUser1  
Deny SELECT ON SCHEMA :: Schema_A TO testUser1 

 

Thanks Joao Polisel and Moises Romero for the advice on this.

 

That is it!

Liliam 

UK Engineer

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.