revoke dba from user

无意间操作的时候,发现如果revoke dba from user,用户user的系统权限,sys_privs,是会丢失的,看看下面这个操作记录。

SQL> select * from dba_role_privs where grantee=’SCOTT’;

GRANTEE                        GRANTED_ROLE                   ADM DEF
—————————— —————————— — —
SCOTT                          RESOURCE                       NO  YES
SCOTT                          CONNECT                        NO  YES
//scott有两个角色,resource和connect
SQL> select * from dba_sys_privs where grantee=’SCOTT’;

GRANTEE                        PRIVILEGE                                ADM
—————————— —————————————- —
SCOTT                          UNLIMITED TABLESPACE                     NO
//scott有unlimited tablespace的权限。

//给scott一个dba的角色
SQL> grant dba to scott;

授权成功。

SQL> select * from dba_role_privs where grantee=’SCOTT’;

GRANTEE                        GRANTED_ROLE                   ADM DEF
—————————— —————————— — —
SCOTT                          RESOURCE                       NO  YES
SCOTT                          DBA                            NO  YES
SCOTT                          CONNECT                        NO  YES
//尝试revoke dba from scott
SQL> revoke dba from scott;

撤销成功。
查询之前的角色,依旧存在
SQL> select * from dba_role_privs where grantee=’SCOTT’;

GRANTEE                        GRANTED_ROLE                   ADM DEF
—————————— —————————— — —
SCOTT                          RESOURCE                       NO  YES
SCOTT                          CONNECT                        NO  YES
//查询原来的系统权限情况,发现unlimited tablespace权限丢失了…….
SQL> select * from dba_sys_privs where grantee=’SCOTT’;

未选定行
这件事情告诉我们:在做revoke 操作的时候,慎重。。。。。。。