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:
Figure 1: Query view
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
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:
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.
Recent Comments