CODEWITHSUNDEEP
sqlsql-serverauthorization
Bulchsu
Bulchsu

Reputation: 700

What is a default behaviour when revoking permissions in SQL?

Imagine a following situation: a database admin creates new user. Let's call him user1. Admin grants privilege A to user1 with grant option.

GRANT A TO user1
WITH GRANT OPTION

Now user1 grants mentioned privilege to user2:

GRANT A TO user2
WITH GRANT OPTION

Let's suppose that admin revokes A from user1:

REVOKE A FROM user1

What happens with user2 granted permissions? Are they still working? Are they deleted? Does the behavior depend on a platform, so it may differ on Oracle, MS SQL, MySQL etc. ? I know that you can specify, such a behavior by using CASCADE keyword in MS SQL, but I've heard, other platforms delete child-grants on default, when the parent is revoked.

Upvotes: 0

Views: 560

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

In SQL Server revoking a permission from a principal who held that permission with grant option and who has granted that permission to other principals will fail with.

Msg 4611, Level 16, State 1, Line 16
To revoke or deny grantable privileges, specify the CASCADE option.

And to test this stuff on SQL Server create users without logins and use execute as to impersonate them and test the behavior and their effecitve permissions.

drop table if exists foo

create table foo(id int)
create user user1 without login 
create user user2 without login 

GRANT select on foo TO user1
WITH GRANT OPTION

execute as user='user1'
  select * from foo;
  GRANT select on foo TO user2
  WITH GRANT OPTION
revert

revoke select on foo to user1 --fails

go

revoke select on foo to user1 cascade

execute as user='user2'
  select * from foo; --fails
revert

Upvotes: 2

Related Questions