mysql oracle dba权限_Oracle数据库DBA权限回收操作参考

1. 基本操作指令

1、查看当前系统 ORACLE_SID(linux)

# su - oracle

$ cat /etc/oratab

orcl:/oracle/app/oracle/product/11.2.0/dbhome_1:N

crm:/oracle/app/oracle/product/11.2.0/dbhome_1:N

2、查看当前系统 ORACLE_SID(windows)

依次打开【控制面板】—【系统安全】—【管理工具】—【服务】

查找跟OracleService开头的相关服务，比如OracleServiceORCL、OracleServiceCRM等，有几个这样的服务就有几个实例

3、切换ORACLE_SID(linux)

$ echo $ORACLE_SID

orcl

$ export ORACLE_SID=crm

$ echo $ORACLE_SID

crm

$ sqlplus / as sysdba

4、切换ORACLE_SID(windows)

C:\Users\sqluser> sqlplus sys/passwd@crm as sysdba

或者

C:\Users\sqluser> set oracle_sid=crm

C:\Users\sqluser> sqlplus /nolog

SQL> connect /as sysdba 或 SQL> connect sys/passwd@crm as sysdba

SQL> select name from v$database; 或 SQL> select instance_name from v$instance;

2. 权限回收准备工作

备注：先执行如下语句，筛选是否具有DBA权限的用户，如果没有(除sys/system用户外)，之后的操作可忽略。

SQL> select * from dba_role_privs where GRANTED_ROLE= 'DBA';

GRANTEE GRANTED_ROLE ADM DEF

------------------------------ ------------------------------ --- ---

SYS DBA YES YES

SYSTEM DBA YES YES

1、统计各个实例下开放使用的用户

SQL> select username from dba_users where account_status='OPEN';

USERNAME

------------------------------

SYS

SYSTEM

ERP

3 rows selected.

2、统计每个用户具有哪些角色权限(dba_role)，注意用户名要大写，以用户名ERP举例如下

SQL> select * from dba_role_privs where GRANTEE= 'ERP';

GRANTEE GRANTED_ROLE ADM DEF

------------------------------ ------------------------------ --- ---

ERP DBA NO YES

ERP RESOURCE NO YES

ERP CONNECT NO YES

3、统计每个用户具有哪些系统权限(dba_sys)，注意用户名要大写，以用户名ERP举例如下

SQL> select * from dba_sys_privs where GRANTEE='ERP';

GRANTEE PRIVILEGE ADM

------------------------------ ---------------------------------------- ---

ERP CREATE ANY SYNONYM NO

ERP UNLIMITED TABLESPACE NO

ERP CREATE SESSION NO

3. 操作回收DBA权限

1、回收dba权限

SQL> revoke dba from ERP;

Revoke succeeded

.

重新授权必要权限

SQL> grant connect,resource to ERP;

grant create view to ERP;

grant create public synonym to ERP;

grant drop public synonym to ERP;

grant unlimited tablespace to ERP;

Grant succeeded.

3、确认权限

SQL> select * from dba_role_privs where GRANTEE= 'ERP';

GRANTEE GRANTED_ROLE ADM DEF

------------------------------ ------------------------------ --- ---

ERP CONNECT NO YES

ERP RESOURCE NO YES

SQL> select * from dba_sys_privs where GRANTEE='ERP';

GRANTEE PRIVILEGE ADM

------------------------------ ---------------------------------------- ---

ERP CREATE VIEW NO

ERP DROP PUBLIC SYNONYM NO

ERP CREATE PUBLIC SYNONYM NO

ERP UNLIMITED TABLESPACE NO

4、其他危险的dba角色权限的回收，特别是以DROP ANY、UPDATE ANY、ALTER ANY、ADMINISTER开头的权限，要注意判断并根据情况回收，这里以DROP ANY TABLE举例

SQL> revoke DROP ANY TABLE from ERP;

Revoke succeeded.

4. 注意事项

备注：如果ADM列显示为YES表示该权限拥有WITH ADMIN OPTION(针对系统权限)或WITH GRANT OPTION(针对对象权限)，需要对其权限进行回收操作，并重新授权。

举例如下：

1、查询ERP用户具有哪些角色权限(dba_role)

SQL> select * from dba_role_privs where GRANTEE='ERP';

GRANTEE GRANTED_ROLE ADM DEF

------------------------------ ------------------------------ --- ---

ERP CONNECT YES YES

ERP AQ_USER_ROLE YES YES

ERP RESOURCE NO YES

2、回收ADM列为YES的权限，并重新授权

SQL> revoke connect from ERP;

Revoke succeeded.

SQL> revoke AQ_USER_ROLE from ERP;

Revoke succeeded.

SQL> grant connect to ERP;

Grant succeeded.

3、确认权限

SQL> select * from dba_role_privs where GRANTEE='ERP';

GRANTEE GRANTED_ROLE ADM DEF

------------------------------ ------------------------------ --- ---

ERP CONNECT NO YES

ERP AQ_USER_ROLE NO YES

ERP RESOURCE NO YES

4、查看是否有dblink，避免因权限回收，导致跨库出现异常

SQL> select * from dba_objects where object_type like '%LINK%';

5. 结束