I have a SQL Server database with a schema called [TopSecret]. I have a database role called [CLevel].
How can I restrict [TopSecret] permissions to only members of the [CLevel] role?
Note: sysadmin and database owners may see the [TopSecret] schema and this is OK, but no other user or role should
CodePudding user response：
If you DENY SELECT to the role PUBLIC which everyone is in, and make the role CLevel the owner of the schema CLevel, then only CLevel members, the database owner, sysadmin members and members of the special db_owner, and db_datareader roles will be able to read the data.
EG:
create schema clevel
go
create user fred without login
create user alice without login
create role Clevel
alter role Clevel add member alice
deny select on schema::CLevel TO public
create table clevel.secret(id int)
alter authorization on schema::clevel to clevel
go
execute as user='alice'
select * from clevel.secret --succeeds
revert
execute as user='fred'
select * from clevel.secret --fails
revert
The DENY doesn't affect CLevel members because object ownership supresses permission checks.
CodePudding user response：
The normal method would be to only grant access to the CLevel schema only to the CLevel role. This would require not granting anybody else access to the objects in the schema. This means not using database roles like db_owner, db_datareader, etc. This takes discipline.
A more forceful method is to use deny. One possible method is to create two roles with mutually exclusive membership, perhaps [CLevel] and [NotCLevel]. The deny will have priority if a user is in both groups. NotCLevel must have all users that have access to the database but are not in CLevel. Using a database level role access will not automatically grant access to CLevel because deny takes priority. Use something like the following for all schema permissions possible.
GRANT SELECT ON SCHEMA :: CLevel TO Clevel
DENY SELECT ON SCHEMA :: CLevel TO NotClevel
DENY has no effect on the owner or members of sysadmin because permission checks are skipped. Otherwise, a deny once issued could never be revoked. The deny does work on members of db_owner if they are not the owner or a member of sysadmin.
It might be hard to manage. A login trigger perhaps can automatically add users to the NotCLevel role if not in either NotCLevel and CLevel.