- 对象权限测试:
- 初始状态下,user2和user3都没有user1.t1的对象权限
- user1下存在t1表:
- duzz$user1@orcl>select * from t1;
- C1
- ----------
- 1111
- Elapsed: 00:00:00.00
- user2不能查询user1.t1:
- duzz$user2@orcl>select * from user1.t1;
- select * from user1.t1
- *
- ERROR at line 1:
- ORA-00942: table or view does not exist
- Elapsed: 00:00:00.00
- user3不能查询user1.t1:
- duzz$user3@orcl>select * from user1.t1;
- select * from user1.t1
- *
- ERROR at line 1:
- ORA-00942: table or view does not exist
- Elapsed: 00:00:00.01
- user1赋予user2在t1上的对象权限,同时允许user2将该权限赋予别的用户:
- duzz$user1@orcl>grant all on t1 to user2 with grant option;
- Grant succeeded.
- Elapsed: 00:00:00.00
- user2已经可以查询user1.t1,同时user2将user1.t1的对象权限赋予user3:
- duzz$user2@orcl>select * from user1.t1;
- C1
- ----------
- 1111
- Elapsed: 00:00:00.00
- duzz$user2@orcl>grant all on user1.t1 to user3;
- Grant succeeded.
- Elapsed: 00:00:00.00
- duzz$user2@orcl>
- user3也可以查询user1.t1了:
- duzz$user3@orcl>select * from user1.t1;
- C1
- ----------
- 1111
- Elapsed: 00:00:00.00
- user1尝试直接从user3那里回收权限,执行语句虽然没有报错,但是并没有实际作用:
- duzz$user1@orcl>revoke all on t1 from user3;
- Revoke succeeded.
- Elapsed: 00:00:00.00
- user3仍然能够查询user1.t1:
- duzz$user3@orcl>select * from user1.t1;
- C1
- ----------
- 1111
- Elapsed: 00:00:00.00
- user1这次开始从user2那里回收权限,
- duzz$user1@orcl>revoke all on t1 from user2;
- Revoke succeeded.
- Elapsed: 00:00:00.01
- user2权限被收回:
- duzz$user2@orcl>select * from user1.t1;
- select * from user1.t1
- *
- ERROR at line 1:
- ORA-00942: table or view does not exist
- Elapsed: 00:00:00.01
- 与此同时,user3的权限也被收回:
- duzz$user3@orcl>select * from user1.t1;
- select * from user1.t1
- *
- ERROR at line 1:
- ORA-00942: table or view does not exist
- Elapsed: 00:00:00.01
- 对于对象权限,虽然不能从非直接授予的用户回收,但是回收具有级联性。
- 系统权限测试:
- user1具有CREATE VIEW的系统权限,同时该权限可以grant给别的用户,user2和user3则没有该权限:
- duzz$user1@orcl>select * from session_privs;
- PRIVILEGE
- ---------------------------------------------------
- CREATE SESSION
- UNLIMITED TABLESPACE
- CREATE TABLE
- CREATE VIEW
- Elapsed: 00:00:00.00
- user2没有CREATE VIEW的系统权限:
- duzz$user2@orcl>select * from session_privs;
- PRIVILEGE
- ------------------------------------------------
- CREATE SESSION
- UNLIMITED TABLESPACE
- CREATE TABLE
- Elapsed: 00:00:00.01
- user3也没有CREATE VIEW的系统权限:
- duzz$user3@orcl>select * from session_privs;
- PRIVILEGE
- ------------------------------------------------
- CREATE SESSION
- UNLIMITED TABLESPACE
- CREATE TABLE
- Elapsed: 00:00:00.01
- user1将CREATE VIEW的权限赋予user2,同时赋予user2可以将该权限赋予其它用户的权限:
- duzz$user1@orcl>grant create view to user2 with admin option;
- Grant succeeded.
- Elapsed: 00:00:00.01
- user2获得CREATE VIEW的权限:
- duzz$user2@orcl>select * from session_privs;
- PRIVILEGE
- -------------------------------------------------
- CREATE SESSION
- UNLIMITED TABLESPACE
- CREATE TABLE
- CREATE VIEW
- Elapsed: 00:00:00.00
- user2将CREATE VIEW的权限赋予user3:
- duzz$user2@orcl>grant create view to user3;
- Grant succeeded.
- Elapsed: 00:00:00.00
- user3获得CREATE VIEW的权限:
- duzz$user3@orcl>select * from session_privs;
- PRIVILEGE
- --------------------------------------------------
- CREATE SESSION
- UNLIMITED TABLESPACE
- CREATE TABLE
- CREATE VIEW
- Elapsed: 00:00:00.01
- 不同于对象权限,系统权限可以直接从非直接授予的用户上收回。
- user1尝试直接从user3上回收CREATE VIEW的权限:
- duzz$user1@orcl>revoke create view from user3;
- Revoke succeeded.
- Elapsed: 00:00:00.01
- user3的CREATE VIEW的权限被收回:
- duzz$user3@orcl>select * from session_privs;
- PRIVILEGE
- ---------------------------------------------
- CREATE SESSION
- UNLIMITED TABLESPACE
- CREATE TABLE
- Elapsed: 00:00:00.01
- 测试系统权限是不是也像对象权限一样具有撤销级联性。
- user2再次将CREATE VIEW的权限赋予user3:
- duzz$user2@orcl>grant create view to user3;
- Grant succeeded.
- Elapsed: 00:00:00.01
- user1收回user2的CREATE VIEW的权限:
- duzz$user1@orcl>revoke create view from user2;
- Revoke succeeded.
- Elapsed: 00:00:00.00
- user2的CREATE VIEW的权限被收回:
- duzz$user2@orcl>select * from session_privs;
- PRIVILEGE
- ------------------------------------------------
- CREATE SESSION
- UNLIMITED TABLESPACE
- CREATE TABLE
- Elapsed: 00:00:00.00
- 不过,user3仍然拥有CREATE VIEW的权限:
- duzz$user3@orcl>select * from session_privs;
- PRIVILEGE
- -------------------------------------------------
- CREATE SESSION
- UNLIMITED TABLESPACE
- CREATE TABLE
- CREATE VIEW
- Elapsed: 00:00:00.00
- 对于系统权限,可以从非直接授予的用户收回权限,但是不具备回收级联性。
- 总结:
- 对于对象权限,虽然不能从非直接授予的用户回收,但是回收具有级联性。
- 对于系统权限,可以从非直接授予的用户收回权限,但是不具备回收级联性。