12c权限分析，帮助我们决定是否回收或保留哪些没有用的权限

郑全

使用到的包：DBMS_PRIVILEGE_CAPTURE
步骤：
   
    1.定义权限抓取
    2.启动权限分析
    3.对分析期间权限是否使用，生成一个报告
    4.比较权限的使用未用情况
    5.删除抓取分析

使用范围：
    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;
      
      exit
    3.3 停止抓取
       sqlplus system/oracle@localhost:1521/pdb1
       exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => 'All_privs')
   
    3.4 生成报告
       exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => 'All_privs')
     
    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'

   6.2 删除所有抓取
   exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => 'Special_capt')