5

I had an interesting observation on SQL Server security permissions.

My Setup is as below. I am trying to restrict user from updating tables under specific schema.

Problem statement is Why user is allowed to do inserts on table through view (of different schema) even though insert is explicitly denied on schema of table in view definition?

USE master
GO

CREATE LOGIN Login1
    WITH password = 'Admin@123'
GO

CREATE DATABASE TestDB
GO

USE TestDB
GO

CREATE user Login1
FROM LOGIN Login1
GO

CREATE SCHEMA sch1
GO

CREATE SCHEMA V
GO

CREATE TABLE sch1.table1 (
    id INT
    ,name VARCHAR(5)
    )
GO

CREATE VIEW v.view1
AS
SELECT *
FROM sch1.table1
GO

CREATE VIEW sch1.view2
AS
SELECT *
FROM sch1.table1
GO

--User can update all tables
ALTER ROLE [db_datawriter] ADD MEMBER [Login1] 
GO

--Except tables under this schema
DENY INSERT
    ON SCHEMA::[sch1]
    TO [Login1]
GO



--Open session with Login1
USE TestDB
GO

--Should not work, does not work.
INSERT sch1.table1
SELECT 1, 'A'
--The INSERT permission was denied on the object 'table1', database 'TestDB', schema 'sch1'.


--Should not work, does not work.
INSERT sch1.view2
SELECT 1, 'A'
--The INSERT permission was denied on the object 'view2', database 'TestDB', schema 'sch1'.


-- Works! - even though write is denied on underlying table?
INSERT v.view1
SELECT 1, 'A'
--(1 row affected)

1 Answer 1

3

Based on documentation, I would expect the insert through view to fail as well.

Requires UPDATE, INSERT, or DELETE permissions on the target table, depending on the action being performed.

But it works because of a concept called Ownership chaining. Official tutorial is here

When you have access to one object (in this case v.view1) and that object references a securable with the same owner (sch1.table1), then the permissions are not checked at all.

Since both objects have no explicit owner:

by default, schema-contained objects are owned by the schema owner

source

and schema owner can be found here

SELECT
    s.name AS schName, dp.name AS ownerUser
FROM sys.schemas AS s 
JOIN sys.database_principals AS dp
    ON dp.principal_id = s.principal_id
WHERE s.name IN (N'v', N'sch1')

enter image description here

You can break the ownership chain by changing the owner of the schema v like this:

ALTER AUTHORIZATION ON SCHEMA::v TO Login1

Then you'll get the DENY you expect.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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