August 6, 2014 at 9:47 am
Hi All,
Just encountered something that I wasn't expected, in that a user who has an explicit deny on a column in a table was able to select it when referenced through a view in a schema they have the SELECT permission on. This seems to me to go against the principle that DENY overrides everything when it comes to permissions? Is this how it's meant to work?
Code is below:-
--create test user
CREATE USER TestDenyOnViewUser WITHOUT LOGIN
GO
--create test schema (authorization dbo - same owner as dbo schema so ownership chaining will apply)
CREATE SCHEMA TestDenyOnView AUTHORIZATION dbo
GO
--create test table
CREATE TABLE dbo.TestDenyOnView
(ID int IDENTITY(1,1) PRIMARY KEY
, DataToDenyAtColumnLevel varchar(50))
GO
--insert a row
INSERT INTO dbo.TestDenyOnView
(DataToDenyAtColumnLevel)
VALUES
('Some text')
GO
--create a view in our new schema to select from the test table
CREATE VIEW TestDenyOnView.vTestDenyOnView
AS
SELECT ID
, DataToDenyAtColumnLevel
FROM dbo.TestDenyOnView
GO
--grant select on the new schema to the new user
GRANT SELECT ON SCHEMA::TestDenyOnView TO TestDenyOnViewUser
GO
--grant select on the table to the new user
GRANT SELECT ON dbo.TestDenyOnView
TO TestDenyOnViewUser
--explicitly deny select on the column on the new table
DENY SELECT ON dbo.TestDenyOnView (DataToDenyAtColumnLevel)
TO TestDenyOnViewUser
GO
--switch to our new user
EXEC AS USER = 'TestDenyOnViewUser'
--select * from the new table; expect this to fail as we're selecting a column we are denied on
SELECT * FROM dbo.TestDenyOnView
--select the ID column only from the new table; expect this to work as we aren't touching the denied column
SELECT ID FROM dbo.TestDenyOnView
--select * from the view; expect this to fail as we're selecting the denied column, however this works through ownership chaining?
SELECT * FROM TestDenyOnView.vTestDenyOnView
--revert back to normal login
REVERT
--cleanup
DROP USER TestDenyOnViewUser
DROP VIEW TestDenyOnView.vTestDenyOnView
DROP TABLE dbo.TestDenyOnView
DROP SCHEMA TestDenyOnView
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
August 6, 2014 at 12:09 pm
Matthew Darwin (8/6/2014)
Hi All,Just encountered something that I wasn't expected, in that a user who has an explicit deny on a column in a table was able to select it when referenced through a view in a schema they have the SELECT permission on. This seems to me to go against the principle that DENY overrides everything when it comes to permissions? Is this how it's meant to work?
Code is below:-
--create test userCREATE USER TestDenyOnViewUser WITHOUT LOGIN
GO
--create test schema (authorization dbo - same owner as dbo schema so ownership chaining will apply)
CREATE SCHEMA TestDenyOnView AUTHORIZATION dbo
GO
--create test table
CREATE TABLE dbo.TestDenyOnView
(ID int IDENTITY(1,1) PRIMARY KEY
, DataToDenyAtColumnLevel varchar(50))
GO
--insert a row
INSERT INTO dbo.TestDenyOnView
(DataToDenyAtColumnLevel)
VALUES
('Some text')
GO
--create a view in our new schema to select from the test table
CREATE VIEW TestDenyOnView.vTestDenyOnView
AS
SELECT ID
, DataToDenyAtColumnLevel
FROM dbo.TestDenyOnView
GO
--grant select on the new schema to the new user
GRANT SELECT ON SCHEMA::TestDenyOnView TO TestDenyOnViewUser
GO
--grant select on the table to the new user
GRANT SELECT ON dbo.TestDenyOnView
TO TestDenyOnViewUser
--explicitly deny select on the column on the new table
DENY SELECT ON dbo.TestDenyOnView (DataToDenyAtColumnLevel)
TO TestDenyOnViewUser
GO
--switch to our new user
EXEC AS USER = 'TestDenyOnViewUser'
--select * from the new table; expect this to fail as we're selecting a column we are denied on
SELECT * FROM dbo.TestDenyOnView
--select the ID column only from the new table; expect this to work as we aren't touching the denied column
SELECT ID FROM dbo.TestDenyOnView
--select * from the view; expect this to fail as we're selecting the denied column, however this works through ownership chaining?
SELECT * FROM TestDenyOnView.vTestDenyOnView
--revert back to normal login
REVERT
--cleanup
DROP USER TestDenyOnViewUser
DROP VIEW TestDenyOnView.vTestDenyOnView
DROP TABLE dbo.TestDenyOnView
DROP SCHEMA TestDenyOnView
This is the same behavior you see in other places (most commonly stored procedures). The permission check was done when you selected the view. The user has permission to select from the view. It is assumed the user has permission to the underlying objects. Otherwise the sql engine would spend all of its time evaluating permissions on every single statement. What you describe is a common way to get around not being able to view a given piece of information. This behavior is very common for stored procedures. You deny update/delete permissions on a table but create a procedure that will perform these actions. Give execute permission to a user for this procedure and the user is now able to update/delete the base table but ONLY when using the procedure. Make sense?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2014 at 12:23 pm
I understood that ownership chaining worked when a user didn't have an explicit grant on an object, but not when they had an explicit deny. I very rarely use denies anyway; but I always thought they were the absolute catch all to prevent a user access to something.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
August 6, 2014 at 12:39 pm
Matthew Darwin (8/6/2014)
I understood that ownership chaining worked when a user didn't have an explicit grant on an object, but not when they had an explicit deny. I very rarely use denies anyway; but I always thought they were the absolute catch all to prevent a user access to something.
If that same user tried to select the column directly from the view it would through an exception but from the view it won't. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2014 at 2:35 pm
Hmm, it just seems to diminish the point of deny against simply not having permission, to me.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
August 6, 2014 at 2:38 pm
Matthew Darwin (8/6/2014)
Hmm, it just seems to diminish the point of deny against simply not having permission, to me.
In your example I have to say I rather agree. In general though it is quite useful. Of course if it denied it, you would get an exception but in this example that might actually be what you want to happen.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2014 at 2:56 pm
Yes that's what I was expecting. Going to have to work out another way around the problem, I think.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply