一、原因: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:N
2、查看默认的 ORACLE_SID
$ echo $ORACLE_SID
erp
$ sqlplus / as sysdba
3、切换 ORACLE_SID
$ export ORACLE_SID=orcl
$ echo $ORACLE_SID
orcl
$ sqlplus / as sysdba
4、查看当前实例下开放使用的用户
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 YES
6、查询开放的用户具有哪些 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 NO
7、回收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 NO
8、重新授权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 NO
9、最后通过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