All we need is an easy explanation of the problem, so here it is.
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 = '[email protected]' 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)
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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
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')
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂