使用范围：
12c以上版本
pdb1
1.创建用户，角色，表
1.1 Create users.
create user wj identified by wj;
create user mqk identified by mqk;
create user zq identified by zq;
1.2 Create roles.
create role HR_MGR;
create role SALES_CLERK;
create role ANALYST;
1.3 Grant privileges.
grant create session to wj, mqk, zq;
grant select, update, delete, insert on hr.employees to HR_MGR;
grant HR_MGR to wj;
grant select on sh.sales to SALES_CLERK;
grant SALES_CLERK to mqk;
grant select any table to ANALYST;
grant ANALYST to zq;
2.定义权限抓取
2.1 定义所有用户都使用的权限抓取
exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( -
name => 'All_privs', -
description => 'All privs used', -
type => dbms_privilege_capture.g_database);
2.2 定义角色使用的权限抓取
exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( -
name => 'Role_privs', -
description => 'Privs used by HR_MGR, SALES_CLERK', -
type => dbms_privilege_capture.g_role, -
roles => role_name_list('HR_MGR', 'SALES_CLERK','ANALYST'));
2.3 定义上下文使用的权限抓取
exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( -
name => 'Special_capt', -
description => 'Special', -
type => dbms_privilege_capture.g_role_and_context, -
roles => role_name_list('SALES_CLERK'), -
condition => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'')=''MQK''');
3.启动抓取
3.1 启动权限抓取
exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'All_privs');
exit;
3.2 模拟权限使用
sqlplus wj/wj@localhost:1521/pdb1
select * from hr.employees where salary < 3000;
connect mqk/mqk@localhost:1521/pdb1
select * from sh.sales where amount_sold < 6.42 and cust_id = 6452;
connect zq/zq@localhost:1521/pdb1
select * from hr.employees where salary > 3000 and rownum <=5 order by salary desc;
3.5 查看对象权限的使用
COL username FORMAT A10
COL object_owner FORMAT A12
COL object_name FORMAT A30
COL obj_priv FORMAT A25
select username, object_owner, object_name, obj_priv
from dba_used_objprivs
where username in ('WJ', 'MQK')
and object_name in ('SALES' , 'EMPLOYEES');
USERNAME OBJECT_OWNER OBJECT_NAME OBJ_PRIV
---------- ------------ ------------------------------ -------------------------
WJ HR EMPLOYEES SELECT
MQK SH SALES SELECT
3.6 显示ZQ使用的权限，ZQ 或者analyst角色真的需要select any table权限吗
select username, used_role, sys_priv, object_name
FROM dba_used_privs
where username ='ZQ' and object_name = 'EMPLOYEES';
USERNAME USED_ROLE SYS_PRIV OBJECT_NAME
---------- -------------------- ------------------------------ --------------------
ZQ ANALYST SELECT ANY TABLE EMPLOYEES
3.7 显示系统权限的使用
select username, sys_priv
FROM dba_used_sysprivs
where username IN ('WJ', 'MQK', 'ZQ');
USERNAME SYS_PRIV
---------- ------------------------------
ZQ CREATE SESSION
WJ CREATE SESSION
MQK CREATE SESSION
ZQ SELECT ANY TABLE
3.8 显示通过角色使用的权限的使用路径
COL object_name FORMAT A12
COL path FORMAT A32
COL obj_priv FORMAT A10
select username, obj_priv, object_name, path
from dba_used_objprivs_path
where username IN ('WJ','MQK','ZQ')
and object_name IN ('SALES','EMPLOYEES');
USERNAME OBJ_PRIV OBJECT_NAME PATH
---------- ---------- ------------ --------------------------------
WJ SELECT EMPLOYEES GRANT_PATH('WJ', 'HR_MGR')
MQK SELECT SALES GRANT_PATH('MQK', 'SALES_CLERK')
3.9 显示未使用的权限
--WJ通过hr_mgr角色获得了 select,update,delete,insert 权限，他只使用了 select 权限
通过 DBA_UNUSED_PRIVS 查找未使用的权限
select username, sys_priv, obj_priv, object_name, path
from dba_unused_privs
where username='WJ'
and object_name = 'EMPLOYEES';
USERNAME SYS_PRIV OBJ_PRIV OBJECT_NAME PATH
---------- -------------------- ---------- ------------ --------------------------------
WJ UPDATE EMPLOYEES GRANT_PATH('WJ', 'HR_MGR')
WJ INSERT EMPLOYEES GRANT_PATH('WJ', 'HR_MGR')
WJ DELETE EMPLOYEES GRANT_PATH('WJ', 'HR_MGR')
4.启动通过角色使用的权限抓取
4.1 删除前面的抓取
exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => 'All_privs') ;
4.2 验证不在有前面抓取遗留数据
select username, sys_priv, obj_priv, object_name, path
from dba_unused_privs
where username='WJ';
no rows selected
4.3 启动角色权限抓取
exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'Role_privs')
4.4 jim和tom运行sql语句
connect WJ/wj@localhost:1521/pdb1
select * from hr.employees where salary < 3000;
connect mqk/mqk@localhost:1521/pdb1
select * from sh.sales where amount_sold < 6.42 and cust_id = 6452;
4.5 停止抓取
connect system/oracle@localhost:1521/pdb1
exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => 'Role_privs')
4.6 生成报告
exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name =>'Role_privs')
4.7 显示 hr_mgr和sales_clerk角色使用的对象权限
COL used_role FORMAT A14
select username, object_owner, object_name, obj_priv, used_role
from dba_used_objprivs
where used_role in ('HR_MGR', 'SALES_CLERK');
USERNAME OBJECT_OWNER OBJECT_NAME OBJ_PRIV USED_ROLE
---------- -------------------- -------------------- -------------------- --------------
WJ HR EMPLOYEES SELECT HR_MGR
MQK SH SALES SELECT SALES_CLERK
4.8 显示 hr_mgr和sales_clerk角色使用的系统权限
select username, sys_priv, used_role
from dba_used_sysprivs
where used_role in ('HR_MGR', 'SALES_CLERK');
no rows selected
4.9 显示hr_mgr角色未使用权限
COL username FORMAT A12
COL path FORMAT A32
COL object FORMAT A10
COL sys_priv FORMAT A10
COL obj_priv FORMAT A10
select sys_priv, obj_priv, object_name, path
from dba_unused_privs
where rolename IN ('HR_MGR', 'SALES_CLERK');
SYS_PRIV OBJ_PRIV OBJECT_NAME PATH
---------- ---------- -------------------- --------------------------------
DELETE EMPLOYEES GRANT_PATH('HR_MGR')
INSERT EMPLOYEES GRANT_PATH('HR_MGR')
UPDATE EMPLOYEES GRANT_PATH('HR_MGR')
5.启动上下文使用权限的抓取
5.1 删除前面的抓取
exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => 'Role_privs');
5.2 验证已经没有数据
select sys_priv, obj_priv, object_name, path
from dba_unused_privs
where rolename IN ('HR_MGR', 'SALES_CLERK');
5.3 启动上下文抓取
exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'Special_capt') ;
5.4 模拟权限的使用
connect wj/wj@localhost:1521/pdb1
select * from hr.employees where salary < 3000;
connect mqk/mqk@localhost:1521/pdb1
select * from sh.sales where amount_sold < 6.42 and cust_id = 6452;
5.5 停止抓取
connect system/oracle@localhost:1521/pdb1
exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name =>'Special_capt')
5.6 生成报告
exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => 'Special_capt')
5.7 显示对象权限的使用
COL username FORMAT A10
COL owner FORMAT A8
COL object FORMAT A16
COL obj_priv FORMAT A10
COL used_role FORMAT A14
select username, object_owner, object_name, obj_priv, used_role
from dba_used_objprivs
where username ='MQK' OR used_role='SALES_CLERK';
USERNAME OBJECT_OWNER OBJECT_NAME OBJ_PRIV USED_ROLE
---------- -------------------- -------------------- ---------- --------------
MQK SH SALES SELECT SALES_CLERK
5.8 检查系统权限的使用，应该没有记录，因为没有使用系统权限
select username, sys_priv from dba_used_sysprivs;
5.9 显示通过角色方式使用的权限的路径
COL object FORMAT A12
COL path FORMAT A32
COL obj_priv FORMAT A10
select username, obj_priv, object_name, path
from dba_used_objprivs_path
where username IN ('WJ','MQK')
and object_name IN ('SALES','EMPLOYEES');
USERNAME OBJ_PRIV OBJECT_NAME PATH
---------- ---------- -------------------- --------------------------------
MQK SELECT SALES GRANT_PATH('MQK', 'SALES_CLERK')
5.10 显示未使用权限，应该没有
select username, sys_priv, obj_priv, object_name, path
from dba_unused_privs
where username='TOM' OR rolename='SALES_CLERK';
no rows selected
6.删除抓取
6.1 列出所有要删除的抓取
COL name FORMAT A12
COL type FORMAT A12
COL enabled FORMAT A2
COL roles FORMAT A26
select name, type, enabled, roles, context
from dba_priv_captures;
NAME TYPE EN ROLES CONTEXT
------------------------------ ------------------------------ -- -------------------------- --------------------------------------------------
Special_capt ROLE_AND_CONTEXT N ROLE_ID_LIST(113) SYS_CONTEXT('USERENV','SESSION_USER')='MQK'