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
  1. 查看当前系统 ORACLE_SID(windows)
    依次打开【控制面板】—【系统安全】—【管理工具】—【服务】
    查找跟OracleService开头的相关服务,比如OracleServiceORCL、OracleServiceCRM等,有几个这样的服务就有几个实例
  2. 切换ORACLE_SID(linux)
$ echo $ORACLE_SID
orcl
$ export ORACLE_SID=crm
$ echo $ORACLE_SID
crm
$ sqlplus / as sysdba
  1. 切换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.
  1. 统计每个用户具有哪些角色权限(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
  1. 统计每个用户具有哪些系统权限(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

.
2. 重新授权必要权限

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.
  1. 确认权限
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
  1. 其他危险的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
  1. 回收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.
  1. 确认权限
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
  1. 查看是否有dblink,避免因权限回收,导致跨库出现异常
SQL> select * from dba_objects where object_type like '%LINK%';

5. 结束

  • 2
    点赞
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值