回收权限

1.经理要求回收dba权限从用户中
2.根据需要赋予用户相应的权限（我认为这些用户需要resource,connect,imp,exp,unlimited tablespce）
3.把新建的角色赋予用户

以上是要求。

以下是我的操作步骤及方
1.因为不确定resource是否具有unlimited tablespace.所以首先查看resource具有哪些权限,
用到DBA_SYS_PRIVS,GRANTEE为角色名。

SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO

已选择8行。

发现resource这个系统角色并没有unlimited tablespace权限。

2.创建pubrole，并赋给resource,connect,unlimited tablespace

SQL> create role pubrole;

角色已创建。

SQL> grant resource,connect to pubrole;

授权成功。

SQL> grant unlimited tablespace to pubrole;
grant unlimited tablespace to pubrole
*
第 1 行出现错误:
ORA-01931: 无法将 UNLIMITED TABLESPACE 授予角色

网上找资料，关于ora-01931的解释是unlimited tablespace权限只能给user直接grant而不能grant给role,DBA、RESOURCE角色并不含有Unlimited Tablespace权限，但是当把角色赋予USER时，会隐含得赋予用户Unlimited Tablespace权限，当revoke时也默认得收回。但是显式的Unlimited Tablespace权限只能赋给USER，不能赋给ROLE。
上面已经grant resouce to pubrole, 我们查证一下，是否是自动给了unlimited tablespace给user
SQL> create user a identified by helloworld;

用户已创建。

SQL> grant resource to a;

授权成功。

SQL> select * from dba_sys_privs where grantee='A';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
A UNLIMITED TABLESPACE NO

3.把imp ,exp的导入功能赋给pubrole

SQL> grant IMP_FULL_DATABASE,exp_full_database to pubrole;

授权成功。

4.查询出所有具有dba权限的user,并生成revoke dba语句（spool xxx.txt 文件导出）
SQL> select 'revoke dba from '||grantee||';' from dba_role_privs where granted_role='DBA' AND GRANTEE NOT IN ('SYS','SYSMAN','SYSTEM');

'REVOKEDBAFROM'||GRANTEE||';'
-----------------------------------------------
revoke dba from RDE;
revoke dba from GB4DTEM;
revoke dba from GB4DT2;
revoke dba from GBDT;
revoke dba from DR_DT;
revoke dba from WEBAPP_DT;
revoke dba from CARAUDIT;
revoke dba from SAMTESTDT;
revoke dba from PHILRTEM;
revoke dba from ICCRT;
revoke dba from PHILRT_PRD;

5.再生成一个grant pubrol to user的语句
SQL> select 'grant pubrole to '||grantee||';' from dba_role_privs where granted_
role='DBA' AND GRANTEE NOT IN ('SYS','SYSMAN','SYSTEM');

'GRANTPUBROLETO'||GRANTEE||';'
------------------------------------------------
grant pubrole to RDE;
grant pubrole to GB4DTEM;
grant pubrole to GB4DT2;
grant pubrole to GBDT;
grant pubrole to DR_DT;
grant pubrole to WEBAPP_DT;
grant pubrole to CARAUDIT;
grant pubrole to SAMTESTDT;
grant pubrole to PHILRTEM;
grant pubrole to ICCRT;
grant pubrole to PHILRT_PRD;

6.先执行第4步生成的revoke再执行grant.就ok了.

7.找个user查看一下它的sys privls和role,发现并没有unlimited tablespace权限，可能是由于先赋给了pubrole然后再赋给user就没了。

SQL> select * from dba_sys_privs where grantee='RDE';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---

8.重新生成grant unlimited tablespace给user

SQL> select 'grant UNLIMITED TABLESPACE to '||grantee||';' from dba_role_privs where granted_
role='PUBROLE' AND GRANTEE NOT IN ('SYS','SYSMAN','SYSTEM');

9.重新在rde中新建表可以成功了，不会报quota不够的问题。


附一些查看权限的视图


1.查看用户系统权限：
dba_sys_privs;
all_sys_privs;
user_sys_privs;

2.查看用户对象权限：
dba_tab_privs;
all_tab_privs;
user_tab_privs;
4.查看所有角色：
dba_roles;
5.查看用户所拥有的角色：
dba_role_privs;
user_role_privs;

查询user的quota

DBA_TS_QUOTAS