Difference between object permission and system permission revocation in Oracle PL/SQL

Object permission test: <br/> in the Initial State, neither user2 nor user3 has the object permission of user1.t1. <br/> table T1 exists in user1: <br/> duzz $ user1 @ orcl> select * from T1; </P> <p> C1 <br/> ---------- <br/> 1111 </P> <p> elapsed: 00:00:00. 00 </P> <p> user2 cannot query user1.t1: <br/> duzz $ user2 @ orcl> select * From user1.t1; <br/> select * From user1.t1 <br/> * <br/> error at line 1: <br/> ORA-00942: table or view does not exist </P> <p> elapsed: 00:00:00. 00 </P> <p> user3 cannot query user1.t1: <br/> Duz Z $ user3 @ orcl> select * From user1.t1; <br/> select * From user1.t1 <br/> * <br/> error at line 1: <br/> ORA-00942: table or view does not exist </P> <p> elapsed: 00:00:00. 01 </P> <p> user1 grants user2 the object permission on T1 and allows user2 to grant this permission to other users: <br/> duzz $ user1 @ orcl> grant all on T1 to user2 with grant option; </P> <p> grant succeeded. </P> <p> elapsed: 00:00:00. 00 </P> <p> user2 can query user1.t1, and user2 grants user1.t1 object permissions to user3: <br/> duzz $ use R2 @ orcl> select * From user1.t1; </P> <p> C1 <br/> ---------- <br/> 1111 </P> <p> elapsed: 00:00:00. 00 <br/> duzz $ user2 @ orcl> grant all on user1.t1 to user3; </P> <p> grant succeeded. </P> <p> elapsed: 00:00:00. 00 <br/> duzz $ user2 @ orcl> </P> <p> user3 can also query user1.t1: <br/> duzz $ user3 @ orcl> select * From user1.t1; </P> <p> C1 <br/> ---------- <br/> 1111 </P> <p> elapsed: 00:00:00. 00 </P> <p> user1 tries to revoke permissions directly from user3. Although the execution statement does not report an error Practical functions: <br/> duzz $ user1 @ orcl> revoke all on T1 from user3; </P> <p> revoke succeeded. </P> <p> elapsed: 00:00:00. 00 </P> <p> user3 can still query user1.t1: <br/> duzz $ user3 @ orcl> select * From user1.t1; </P> <p> C1 <br/> ---------- <br/> 1111 </P> <p> elapsed: 00:00:00. 00 </P> <p> user1 revokes permissions from user2 this time. <br/> duzz $ user1 @ orcl> revoke all on T1 from user2; </P> <p> revoke succeeded. </P> <p> elapsed: 00:00:00. 01 </P> <p> user2 permission revoked: <br/> Duz Z $ user2 @ orcl> select * From user1.t1; <br/> select * From user1.t1 <br/> * <br/> error at line 1: <br/> ORA-00942: table or view does not exist </P> <p> elapsed: 00:00:00. 01 </P> <p> user3 permissions are also revoked: <br/> duzz $ user3 @ orcl> select * From user1.t1; <br/> select * From user1.t1 <br/> * <br/> error at line 1: <br/> ORA-00942: table or view does not exist </P> <p> elapsed: 00:00:00. 01 </P> <p> although object permissions cannot be revoked from non-directly granted users Cascade. </P> <p> system permission test: <br/> user1 has the create view system permission, and can grant this permission to other users. user2 and user3 do not have this permission: <br/> duzz $ user1 @ orcl> select * From session_privs; </P> <p> privilege <br/> ------------------------------------------------- <br/> Create session <br/> unlimited tablespace <br/> Create Table <br/> Create view </P> <p> elapsed: 00:00:00. 00 </P> <p> user2 has no system permission for create view: <br/> duzz $ user2 @ orcl> select * From session_privs; </P> <p> PR Ivilege <br/> -------------------------------------------- <br/> Create session <br/> unlimited tablespace <br/> Create Table </P> <p> elapsed: 00:00:00. 01 </P> <p> user3 does not have the system permission to create view: <br/> duzz $ user3 @ orcl> select * From session_privs; </P> <p> privilege <br/> ---------------------------------------------- <br/> Create session <br/> unlimited tablespace <br/> Create Table </P> <p> elapsed: 00:00:00. 01 </P> <P> user1 grant the create view permission to user2 and grant user2 the permission to other users: <br/> duzz $ user1 @ orcl> grant create view to user2 with admin option; </P> <p> grant succeeded. </P> <p> elapsed: 00:00:00. 01 </P> <p> user2 permission to create view: <br/> duzz $ user2 @ orcl> select * From session_privs; </P> <p> privilege <br/> ----------------------------------------------- <br/> Create session <br/> unlimited tablespace <br/> Create Table <br/> Create v Iew </P> <p> elapsed: 00:00:00. 00 </P> <p> user2 grant the create view permission to user3: <br/> duzz $ user2 @ orcl> grant create view to user3; </P> <p> grant succeeded. </P> <p> elapsed: 00:00:00. 00 </P> <p> user3: Get the create view permission: <br/> duzz $ user3 @ orcl> select * From session_privs; </P> <p> privilege <br/> ------------------------------------------------ <br/> Create session <br/> unlimited tablespace <br/> Create Table <br/> Create view </P> <P> elapsed: 00:00:00. 01 </P> <p> different from object permissions, system permissions can be directly revoked from users not directly granted. <Br/> user1 tries to revoke the create view permission from user3: <br/> duzz $ user1 @ orcl> revoke create view from user3; </P> <p> revoke succeeded. </P> <p> elapsed: 00:00:00. 01 </P> <p> the create view permission of user3 is revoked: <br/> duzz $ user3 @ orcl> select * From session_privs; </P> <p> privilege <br/> ------------------------------------------- <br/> Create session <br/> unlimited tablespace <br/> Create Table </P> <p> elapsed: 00:00:00. 01 </P> <p> test whether system permissions are similar to objects. The same permissions have the ability to revoke cascading. <Br/> user2 again granted the create view permission to user3: <br/> duzz $ user2 @ orcl> grant create view to user3; </P> <p> grant succeeded. </P> <p> elapsed: 00:00:00. 01 </P> <p> user1 revokes the create view permission of user2: <br/> duzz $ user1 @ orcl> revoke create view from user2; </P> <p> revoke succeeded. </P> <p> elapsed: 00:00:00. 00 </P> <p> the create view permission of user2 is revoked: <br/> duzz $ user2 @ orcl> select * From session_privs; </P> <p> privilege <br/> ------------------------- ----------------------- <Br/> Create session <br/> unlimited tablespace <br/> Create Table </P> <p> elapsed: 00:00:00. 00 </P> <p> however, user3 still has the create view permission: <br/> duzz $ user3 @ orcl> select * From session_privs; </P> <p> privilege <br/> ----------------------------------------------- <br/> Create session <br/> unlimited tablespace <br/> Create Table <br/> Create view </P> <p> elapsed: 00:00:00. 00 </P> <p> for system permissions The user revokes the permission, but does not have the permission to be recycled. </P> <p> conclusion: <br/> although object permissions cannot be revoked from non-directly granted users, revocation is cascade. <Br/> for system permissions, You can revoke permissions from non-directly granted users, but do not have the permission to revoke cascade. <Br/>