0

Access Control setup on Tables inside dbo schema.

We are Denying select permission on some tables inside dbo schema as these tables have some critical data.

Issue: Even when Select query fails on the object. A user having create view grants can create a view on the same object.

Sample queries:

Select * from dbo.TABLE1 -- Fails as user does not have access.

Create view USER.VIEW1 as (Select * from dbo.TABLE1) -- this goes through.

How can I restrict this from happening?

0

I haven't tested this on Azure, but on my local SQL Server 2016, I cannot reproduce what you're seeing. Perhaps the target user has more rights than you think? Do they have membership in a role that is giving them access?

Anyway, the testbed I used to test this is:

CREATE TABLE dbo.SomeTable
(
    SomeTable_id int NOT NULL
        CONSTRAINT SomeTable_pk
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
) ON [PRIMARY];

CREATE USER UserA WITHOUT LOGIN;
DENY SELECT ON dbo.SomeTable TO UserA;
GRANT CREATE VIEW TO UserA;
GRANT CREATE SCHEMA TO UserA;


EXECUTE AS USER = N'UserA'; --anything past this point is executed using UserA's permissions

--fails with SELECT permission denied
SELECT *
FROM dbo.SomeTable;
GO

--this works due to the GRANT CREATE SCHEMA statement above
CREATE SCHEMA UserA;
GO

--this works due to the GRANT CREATE VIEW statement above
CREATE VIEW UserA.SomeTableView
AS
SELECT *
FROM dbo.SomeTable;
GO

--this fails since the user who created the view doesn't have rights to the underlying table.
SELECT *
FROM UserA.SomeTableView;
DROP VIEW UserA.SomeTableView;

--revert back to "me" instead of UserA
REVERT

UserA is able to create the view, but they cannot actually select from it. The error returned is:

Msg 229, Level 14, State 5, Line 27
The SELECT permission was denied on the object 'SomeTable', database 'MaxTest', schema 'dbo'.

  • My Apologies for a very delayed response. Authorization was a problem here. I had created the roles with authorization db_owner (which is by default), after restricting this, i was able to achieve the access controls. Something like this: Create ROLE MYROLE AUTHORIZATION db_datareader. – Dhruv Saraswat Aug 28 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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