生产环境中oracle dba权限检查和回收相关命令汇总

序号

类型

场景

检查语句

补充权限

0

回收之前检查

select grantee,privilege from dba_sys_privs where grantee in ('PRDUSER');

select grantee,granted_role from dba_role_privs where grantee in ('PRDUSER');

1

同义词

主要针对多用户场景

SELECT * FROM DBA_SYNONYMS WHERE OWNER IN ('PRDUSER');

grant create any synonym to PRDUSER;
GRANT DROP ANY SYNONYM TO PRDUSER;

2

物化视图

主要针对多用户场景

select owner, mview_name from dba_mviews where owner in ('PRDUSER')

grant ALTER ANY MATERIALIZED VIEW to PRDUSER;
grant CREATE ANY MATERIALIZED VIEW to PRDUSER;
grant DROP ANY MATERIALIZED VIEW to PRDUSER;

3

序列化

主要针对多用户场景

select sequence_owner, sequence_name from dba_sequences where sequence_owner in ('PRDUSER')

grant select any sequence to PRDUSER;

可能会在插入另一个用户表的时候，其某一个字段是使用sequence方式自增的，如果没有sequence查询权限，可能拿不到自增值

4

跨库表的互访增删改查

主要针对多用胡场景

如果有多用户，相互之间有增删改查的操作的话

grant SELECT ANY TABLE to PRDUSER;
grant INSERT ANY TABLE to PRDUSER;
grant UPDATE ANY TABLE to PRDUSER;
grant DELETE ANY TABLE to PRDUSER;
grant LOCK ANY TABLE to PRDUSER;
grant UNDER ANY TABLE to PRDUSER;
grant COMMENT ANY TABLE to PRDUSER;

如果有跨用户表truncate的话，1. 是 授权drop any table，但是这个风险太大了， 2. 是创建一个存储过程，授权存储过程的执行权限，执行 truncate操作。

5

存储过程debug

grant debug connect session to PRDUSER;

这个理论上不应该给吧，产品环境未来不允许debug操作。

6

系统字典视图无法访问

grant select any dictionary to PRDUSER；

7

删除会话的权限

创建一个删除会话的存储过程，授予执行权限

create or replace procedure kill_session ( v_sid number, v_serial number ) as v_varchar2 varchar2(100);

begin execute immediate 'ALTER SYSTEM KILL SESSION ''' || v_sid || ',' || v_serial || '''';

end;

/

grant execute on kill_session to PRDUSER;

8

基础授权

unlimited tablepsace

revoke dba之后，必做的一个动作

grant unlimited tablespace to PRDUSER;

9

其他授权

grant create session to PRDUSER;

grant create any procedure to PRDUSER;

grant execute any procedure to PRDUSER;

grant create table to PRDUSER;

grant drop any table to PRDUSER;

GRANT CREATE VIEW TO PRDUSER;

grant create any type to PRDUSER;

grant create trigger to PRDUSER;