一、原因：oracle在回收某用户DBA角色时，会同时收回该用户的UNLIMITED TABLESPACE权限。 二、结果：导致用户无UNLIMITED TABLESPACE权限造成最终造成业务中断 三、解决：回收DBA角色时，需要重新将必要的权限授权给对应用户
1、查看当前系统 ORACLE_SID
# su - oracle $ cat /etc/oratab erp:/oracle/app/oracle/product/11.2.0/dbhome_1:N orcl:/oracle/app/oracle/product/11.2.0/dbhome_1:N2、查看默认的 ORACLE_SID
$ echo $ORACLE_SID erp $ sqlplus / as sysdba3、切换 ORACLE_SID
$ export ORACLE_SID=orcl $ echo $ORACLE_SID orcl $ sqlplus / as sysdba4、查看当前实例下开放使用的用户
SQL> select username from dba_users where account_status='OPEN'; USERNAME ------------------------------ SYS SYSTEM BACKUP CRM 4 rows selected.5、查询开放的用户具有哪些 dba_role 权限（注意CRM要大写）
SQL> select * from dba_role_privs where GRANTEE='CRM' ; GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- CRM RESOURCE NO YES CRM CONNECT NO YES CRM DBA NO YES6、查询开放的用户具有哪些 dba_sys 权限
SQL> select * from dba_sys_privs where GRANTEE='CRM'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- CRM CREATE PUBLIC SYNONYM NO CRM CREATE VIEW NO CRM DROP PUBLIC SYNONYM NO CRM UNLIMITED TABLESPACE NO7、回收DBA权限，再次查看CRM具有哪些 dba_role 权限：
SQL> revoke dba from CRM; Revoke succeeded. SQL> select * from dba_role_privs where GRANTEE='CRM'; GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- CRM RESOURCE NO YES CRM CONNECT NO YES再次查看CRM具有哪些 dba_sys 权限（对比发现unlimited tablespace权限也被回收）
SQL> select * from dba_sys_privs where GRANTEE='CRM'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- CRM CREATE PUBLIC SYNONYM NO CRM CREATE VIEW NO CRM DROP PUBLIC SYNONYM NO8、重新授权unlimited tablespace权限，再次查询相关权限
SQL> grant unlimited tablespace to CRM; Grant succeeded. SQL> select * from dba_role_privs where GRANTEE='CRM'; GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- CRM RESOURCE NO YES CRM CONNECT NO YES SQL> select * from dba_sys_privs where GRANTEE='CRM'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- CRM CREATE PUBLIC SYNONYM NO CRM CREATE VIEW NO CRM DROP PUBLIC SYNONYM NO CRM UNLIMITED TABLESPACE NO9、最后通过sqlplus验证连接正常；查看业务日志正常；
注：在不影响业务使用的情况下，尽最大可能回收DBA相关的权限，尤其是生产环境，切记权限不可混乱使用，以免造成数据丢失，无法挽回。 connect权限简介：
connect 权限：分配给普通用户； 该权限具有： alter session —— 修改会话； create cluster —— 创建聚簇； create database link —— 创建数据库连接； create sequence —— 创建序列； create session —— 创建会话； create synonym —— 创建同义词； create view —— 创建视图；resource 权限简介：
resource 权限：分配给设计人员； 该权限具有： create cluster —— 创建聚簇； create procedure —— 创建过程； create sequence —— 创建序列； create table —— 创建表； create trigger —— 创建触发器； create type —— 建类型；符哪些权限为DBA权限：
select * from dba_sys_privs where grantee = 'DBA' order by privilege; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA ADMINISTER ANY SQL TUNING SET YES DBA ADMINISTER DATABASE TRIGGER YES DBA ADMINISTER RESOURCE MANAGER YES DBA ADMINISTER SQL MANAGEMENT OBJECT YES DBA ADMINISTER SQL TUNING SET YES DBA ADVISOR YES DBA ALTER ANY ASSEMBLY YES DBA ALTER ANY CLUSTER YES DBA ALTER ANY CUBE YES DBA ALTER ANY CUBE DIMENSION YES DBA ALTER ANY DIMENSION YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA ALTER ANY EDITION YES DBA ALTER ANY EVALUATION CONTEXT YES DBA ALTER ANY INDEX YES DBA ALTER ANY INDEXTYPE YES DBA ALTER ANY LIBRARY YES DBA ALTER ANY MATERIALIZED VIEW YES DBA ALTER ANY MINING MODEL YES DBA ALTER ANY OPERATOR YES DBA ALTER ANY OUTLINE YES DBA ALTER ANY PROCEDURE YES DBA ALTER ANY ROLE YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA ALTER ANY RULE YES DBA ALTER ANY RULE SET YES DBA ALTER ANY SEQUENCE YES DBA ALTER ANY SQL PROFILE YES DBA ALTER ANY TABLE YES DBA ALTER ANY TRIGGER YES DBA ALTER ANY TYPE YES DBA ALTER DATABASE YES DBA ALTER PROFILE YES DBA ALTER RESOURCE COST YES DBA ALTER ROLLBACK SEGMENT YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA ALTER SESSION YES DBA ALTER SYSTEM YES DBA ALTER TABLESPACE YES DBA ALTER USER YES DBA ANALYZE ANY YES DBA ANALYZE ANY DICTIONARY YES DBA AUDIT ANY YES DBA AUDIT SYSTEM YES DBA BACKUP ANY TABLE YES DBA BECOME USER YES DBA CHANGE NOTIFICATION YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA COMMENT ANY MINING MODEL YES DBA COMMENT ANY TABLE YES DBA CREATE ANY ASSEMBLY YES DBA CREATE ANY CLUSTER YES DBA CREATE ANY CONTEXT YES DBA CREATE ANY CUBE YES DBA CREATE ANY CUBE BUILD PROCESS YES DBA CREATE ANY CUBE DIMENSION YES DBA CREATE ANY DIMENSION YES DBA CREATE ANY DIRECTORY YES DBA CREATE ANY EDITION YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA CREATE ANY EVALUATION CONTEXT YES DBA CREATE ANY INDEX YES DBA CREATE ANY INDEXTYPE YES DBA CREATE ANY JOB YES DBA CREATE ANY LIBRARY YES DBA CREATE ANY MATERIALIZED VIEW YES DBA CREATE ANY MEASURE FOLDER YES DBA CREATE ANY MINING MODEL YES DBA CREATE ANY OPERATOR YES DBA CREATE ANY OUTLINE YES DBA CREATE ANY PROCEDURE YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA CREATE ANY RULE YES DBA CREATE ANY RULE SET YES DBA CREATE ANY SEQUENCE YES DBA CREATE ANY SQL PROFILE YES DBA CREATE ANY SYNONYM YES DBA CREATE ANY TABLE YES DBA CREATE ANY TRIGGER YES DBA CREATE ANY TYPE YES DBA CREATE ANY VIEW YES DBA CREATE ASSEMBLY YES DBA CREATE CLUSTER YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA CREATE CUBE YES DBA CREATE CUBE BUILD PROCESS YES DBA CREATE CUBE DIMENSION YES DBA CREATE DATABASE LINK YES DBA CREATE DIMENSION YES DBA CREATE EVALUATION CONTEXT YES DBA CREATE EXTERNAL JOB YES DBA CREATE INDEXTYPE YES DBA CREATE JOB YES DBA CREATE LIBRARY YES DBA CREATE MATERIALIZED VIEW YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA CREATE MEASURE FOLDER YES DBA CREATE MINING MODEL YES DBA CREATE OPERATOR YES DBA CREATE PROCEDURE YES DBA CREATE PROFILE YES DBA CREATE PUBLIC DATABASE LINK YES DBA CREATE PUBLIC SYNONYM YES DBA CREATE ROLE YES DBA CREATE ROLLBACK SEGMENT YES DBA CREATE RULE YES DBA CREATE RULE SET YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA CREATE SEQUENCE YES DBA CREATE SESSION YES DBA CREATE SYNONYM YES DBA CREATE TABLE YES DBA CREATE TABLESPACE YES DBA CREATE TRIGGER YES DBA CREATE TYPE YES DBA CREATE USER YES DBA CREATE VIEW YES DBA DEBUG ANY PROCEDURE YES DBA DEBUG CONNECT SESSION YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA DELETE ANY CUBE DIMENSION YES DBA DELETE ANY MEASURE FOLDER YES DBA DELETE ANY TABLE YES DBA DEQUEUE ANY QUEUE YES DBA DROP ANY ASSEMBLY YES DBA DROP ANY CLUSTER YES DBA DROP ANY CONTEXT YES DBA DROP ANY CUBE YES DBA DROP ANY CUBE BUILD PROCESS YES DBA DROP ANY CUBE DIMENSION YES DBA DROP ANY DIMENSION YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA DROP ANY DIRECTORY YES DBA DROP ANY EDITION YES DBA DROP ANY EVALUATION CONTEXT YES DBA DROP ANY INDEX YES DBA DROP ANY INDEXTYPE YES DBA DROP ANY LIBRARY YES DBA DROP ANY MATERIALIZED VIEW YES DBA DROP ANY MEASURE FOLDER YES DBA DROP ANY MINING MODEL YES DBA DROP ANY OPERATOR YES DBA DROP ANY OUTLINE YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA DROP ANY PROCEDURE YES DBA DROP ANY ROLE YES DBA DROP ANY RULE YES DBA DROP ANY RULE SET YES DBA DROP ANY SEQUENCE YES DBA DROP ANY SQL PROFILE YES DBA DROP ANY SYNONYM YES DBA DROP ANY TABLE YES DBA DROP ANY TRIGGER YES DBA DROP ANY TYPE YES DBA DROP ANY VIEW YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA DROP PROFILE YES DBA DROP PUBLIC DATABASE LINK YES DBA DROP PUBLIC SYNONYM YES DBA DROP ROLLBACK SEGMENT YES DBA DROP TABLESPACE YES DBA DROP USER YES DBA ENQUEUE ANY QUEUE YES DBA EXECUTE ANY ASSEMBLY YES DBA EXECUTE ANY CLASS YES DBA EXECUTE ANY EVALUATION CONTEXT YES DBA EXECUTE ANY INDEXTYPE YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA EXECUTE ANY LIBRARY YES DBA EXECUTE ANY OPERATOR YES DBA EXECUTE ANY PROCEDURE YES DBA EXECUTE ANY PROGRAM YES DBA EXECUTE ANY RULE YES DBA EXECUTE ANY RULE SET YES DBA EXECUTE ANY TYPE YES DBA EXECUTE ASSEMBLY YES DBA EXPORT FULL DATABASE YES DBA FLASHBACK ANY TABLE YES DBA FLASHBACK ARCHIVE ADMINISTER YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA FORCE ANY TRANSACTION YES DBA FORCE TRANSACTION YES DBA GLOBAL QUERY REWRITE YES DBA GRANT ANY OBJECT PRIVILEGE YES DBA GRANT ANY PRIVILEGE YES DBA GRANT ANY ROLE YES DBA IMPORT FULL DATABASE YES DBA INSERT ANY CUBE DIMENSION YES DBA INSERT ANY MEASURE FOLDER YES DBA INSERT ANY TABLE YES DBA LOCK ANY TABLE YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA MANAGE ANY FILE GROUP YES DBA MANAGE ANY QUEUE YES DBA MANAGE FILE GROUP YES DBA MANAGE SCHEDULER YES DBA MANAGE TABLESPACE YES DBA MERGE ANY VIEW YES DBA ON COMMIT REFRESH YES DBA QUERY REWRITE YES DBA READ ANY FILE GROUP YES DBA RESTRICTED SESSION YES DBA RESUMABLE YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA SELECT ANY CUBE YES DBA SELECT ANY CUBE DIMENSION YES DBA SELECT ANY DICTIONARY YES DBA SELECT ANY MINING MODEL YES DBA SELECT ANY SEQUENCE YES DBA SELECT ANY TABLE YES DBA SELECT ANY TRANSACTION YES DBA UNDER ANY TABLE YES DBA UNDER ANY TYPE YES DBA UNDER ANY VIEW YES DBA UPDATE ANY CUBE YES GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- DBA UPDATE ANY CUBE BUILD PROCESS YES DBA UPDATE ANY CUBE DIMENSION YES DBA UPDATE ANY TABLE