3

I'm currently dealing with some GRANT options using Oracle Database Express Edition 11g. Consider the following small code example where some users grant some privileges to other users:

-- User A
GRANT Select, Insert, Update, Delete ON T TO B,C WITH GRANT OPTION ;
-- User B
GRANT Select ON T TO C WITH GRANT OPTION ;
GRANT Insert ON T TO C ;
-- USer C
GRANT Select, Insert, Update ON T TO D ;

User A is the creator of Table T and performs the following REVOKE operation.

Now REVOKE Update ON T FROM C is performed. Since no constraint is specified, the REVOKE operation should either cancel, because otherwise there would be an abandoned UPDATE privilege at D, or delete the privileges of both C and D.

Now my question is: Is the REVOKE statement actually cancelled or removes both C and D privileges? Or in other words, is the result after executing that revoke statement that both C and D still have the UPDATE privilege or not?

Thanks in advance.

Patrick Weiß
  • 436
  • 9
  • 23

1 Answers1

3

Revoke object privilege

If user has granted the privilege to other users or roles, then the database also revokes the privilege from those other users or roles.

The correct REVOKE statement is:

  REVOKE object_priv [(column1, column2..)] ON [schema.]object 
         FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]

There is no RESTRICT in Oracle. The RESTRICT exists in PostgresSQL, MariaDB, etc.

However I think your intended way is just REVOKE Update ON T FROM C executed from A user. After that there is no any error and users C and D do NOT have privilege to update T.

dcieslak
  • 2,697
  • 1
  • 12
  • 19
  • You're absolutely right, there is no `RESTRICT` in Oracle. So you mean that the Oracle default behavior is `CASCADE`, right? – Patrick Weiß Dec 16 '15 at 09:10
  • 1
    Yes, we can say `CASCADE` is default for `WITH GRANT OPTION` grants... The other story is with granting system privileges `WITH ADMIN OPTION`. These ones will not be revoked. – dcieslak Dec 16 '15 at 11:07