Determining Least Privilege Access Using Privilege Analysis

 

Overview

    Purpose

    This tutorial covers the steps required to capture privileges used by users during a short period of time to help you decide which privileges need to be revoked or kept.

    Time to Complete

    Approximately 25 minutes.

    Introduction

    The Privilege Analysis feature allows you to:

    1. Define a privilege capture
    2. Start a privilege analysis during a period of time
    3. Generate results about privileges and roles used and unused during the analysis period
    4. Compare used and unused privileges using views to decide which privileges and or roles need to be revoked or kept
    5. Delete capture analysis

Scenario

In this tutorial, you will perform three types of analysis:

  • Capture privileges used by all users
  • Capture privileges used through roles
  • Capture privileges used through contexts

Prerequisites

Before starting this tutorial, you should:

    • Oracle Database 12c should be installed.
    • You need a started database.

    The environment used in the development of this tutorial is as follows:

    • ORACLE_HOME: /u01/app/oracle/product/12.1.0
    • TNS Listener port: 1521
    • Container databases:
      • SID: cdb1
      • SID: cdb2
    • Pluggable databases (in cdb1):
      • pdb1
      • pdb2
    • Non-CDB SID: noncdb
 

Create Users, Roles, Tables and Grant Privileges and Roles

    Use SQL*Plus to connect as system to create the users JIM and TOM , roles, and grant privileges and or roles.

    Connect as SYSTEM:

    . oraenv

    [enter cdb1 at the prompt]

    sqlplus system/oracle@localhost:1521/pdb1

    Create users.

    create user jim identified by jim;
    create user tom identified by tom;
    create user ann identified by ann;

    Create roles.

    create role HR_MGR;
    create role SALES_CLERK;
    create role ANALYST;

    Grant privileges.

    grant create session to jim, tom, ann;

    grant select, update, delete, insert on hr.employees to HR_MGR;
    grant HR_MGR to JIM;

    grant select on sh.sales to SALES_CLERK;
    grant SALES_CLERK to TOM;

    grant select any table to ANALYST;
    grant ANALYST to ann;
 

Define the Captures

    In this section, you will prepare the three types of captures.

     

    Define the capture of privileges used by all users.

      Define the capture of privileges used by all users. Use the DBMS_PRIVILEGE_CAPTURE package and the CREATE_CAPTURE procedure with the appropriate type of capture.

      exec SYS.DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE ( -
              name           =>  'All_privs', -
              description    =>  'All privs used', -
              type           =>   dbms_privilege_capture.g_database)

     

    Define the capture of privileges used through roles.

      Define a capture of privileges used by roles HR_MGR, ANALYST, and SALES_CLERK. Use the DBMS_PRIVILEGE_CAPTURE package and the CREATE_CAPTURE procedure with the appropriate type of capture and the list of roles analyzed.

      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'))

     

    Define the capture of privileges used through contexts.

      Define a capture of privileges used by the user TOM or by the specific role SALES_CLERK.

      Use the DBMS_PRIVILEGE_CAPTURE package and the CREATE_CAPTURE procedure with the appropriate type of capture, the list of roles analyzed and the context in which the analysis would take place once started.

      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'')=''TOM''')

     

    List the existing captures.

      Use the DBA_PRIV_CAPTURES view.
      Note: You may see different role ID results.
      COL name     FORMAT A12
      COL type     FORMAT A16
      COL enabled  FORMAT A2
      COL roles    FORMAT A24       COL context  FORMAT A43

      select name, type, enabled, roles, context
      from   dba_priv_captures;

 

Start Privilege Captures and Analyze

     

    Start and analyze the capture of privileges used by all users.

      Start capturing the privileges while users are performing their daily work using privileges. Use the ENABLE_CAPTURE procedure.

      exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'All_privs')

      exit
      [Note that you must exit SQL*Plus and then relaunch SQL*Plus in the next step, otherwise you may see unintended results in the privilege analysis views in later steps of this tutorial.]

      The users JIM, TOM, and ANN run SQL statements using privileges. JIM, and ANN who select rows from HR.EMPLOYEES table and TOM who selects rows from SH.SALES table.

      sqlplus jim/jim@localhost:1521/pdb1
      select * from hr.employees where salary < 3000;

      connect tom/tom@localhost:1521/pdb1
      select * from sh.sales where amount_sold < 6.42 and cust_id = 6452;

      connect ann/ann@localhost:1521/pdb1
      select * from hr.employees where salary > 3000 and rownum <=5 order by salary desc;

      exit
      [Note you must exit SQL*Plus and then relaunch SQL*Plus in the next step.]

      Stop capturing. Use the DISABLE_CAPTURE procedure.

      sqlplus system/oracle@localhost:1521/pdb1
      exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => 'All_privs')

      Generate the capture results. It may take a few minutes. Use the GENERATE_RESULT procedure.

      exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => 'All_privs')
      Display the object privileges used during the capture period. Use the DBA_USED_OBJPRIVS view.

      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 ('JIM', 'TOM')
      and    object_name in ('SALES' , 'EMPLOYEES');

      Display the privileges used by ANN. Use the DBA_USED_PRIVS view.  Does Ann, or the ANALYST role, really need the SELECT ANY TABLE privilege?

      select username, used_role, sys_priv, object_name FROM dba_used_privs
      where  username ='ANN' and object_name = 'EMPLOYEES';

      Display the system privileges used. Use the DBA_USED_SYSPRIVS view.

      select username, sys_priv FROM dba_used_sysprivs
      where  username IN ('JIM', 'TOM', 'ANN');

      Display the path of the privileges used if the privileges were granted to roles, and roles to users. Use the DBA_USED_OBJPRIVS_PATH view.

      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 ('TOM','JIM','ANN')
      and    object_name IN ('SALES','EMPLOYEES');

      JIM is granted select, update, delete, insert privileges on HR.EMPLOYEES table through HR_MGR role. He used only the SELECT privilege thus far.
      Use the DBA_UNUSED_PRIVS view to list the unused privileges. You can decide which of the unused privileges can be revoked if necessary.

      select username, sys_priv, obj_priv, object_name, path
      from   dba_unused_privs
      where  username='JIM'
      and object_name = 'EMPLOYEES';

     

    Start and analyze the capture of privileges used through roles.

      Delete the previous capture so as to remove all previous captured information from the views. Use the DROP_CAPTURE procedure.

      exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => 'All_privs')
      Verify that there is no data left from the All_privs capture. Use the DBA_UNUSED_PRIVS view. There should be no rows returned by the query.

      select username, sys_priv, obj_priv, object_name, path
      from   dba_unused_privs
      where  username='JIM';
      Start capturing the privileges while users are performing their daily work using roles.

      exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'Role_privs')

      The users JIM and TOM run SQL statements using privileges. JIM who selects rows from HR.EMPLOYEES table and TOM who selects rows from SH.SALES table.

      connect jim/jim@localhost:1521/pdb1
      select * from hr.employees where salary < 3000;

      connect tom/tom@localhost:1521/pdb1
      select * from sh.sales where amount_sold < 6.42 and cust_id = 6452;

      Stop capturing.

      connect system/oracle@localhost:1521/pdb1
      exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name => 'Role_privs')
      Generate the capture results.

      exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name =>'Role_privs')
      Display the object privileges used by the roles HR_MGR and SALES_CLERK during the capture period.

      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');

      Display the system privileges used by the roles HR_MGR and SALES_CLERK during the capture period.

      select username, sys_priv, used_role
      from dba_used_sysprivs
      where used_role in ('HR_MGR', 'SALES_CLERK');

      No rows are returned because no system privileges were used.

      HR_MGR is granted select, update, delete on HR.EMPLOYEES table. The role used by JIM during the capture period used the SELECT privilege.
      The unused privileges are visible in DBA_UNUSED_PRIVS view. You can decide which of the unused privileges or role can be revoked if necessary.

      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');

     

    Define the capture of privileges used through contexts.

      Delete the previous capture so as to remove all captured information from the views.



      exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => 'Role_privs')

      Verify that there is no data left from the Role_privs capture. There should be no rows returned by the query.

      select sys_priv, obj_priv, object_name, path
      from   dba_unused_privs
      where  rolename IN ('HR_MGR', 'SALES_CLERK');

      Start capturing the privileges while users are performing their daily work using privileges.

      exec SYS.DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE (name => 'Special_capt')

      The users JIM and TOM run SQL statements using privileges. JIM selects rows from HR.EMPLOYEES table and TOM selects rows from SH.SALES table.

      connect jim/jim@localhost:1521/pdb1
      select * from hr.employees where salary < 3000;

      connect tom/tom@localhost:1521/pdb1
      select * from sh.sales where amount_sold < 6.42 and cust_id = 6452;

      Stop capturing.

      connect system/oracle@localhost:1521/pdb1
      exec SYS.DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE (name =>'Special_capt')

      Generate the capture results.

      exec SYS.DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (name => 'Special_capt')
      Display the object privileges used during the capture period.

      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 ='TOM' OR used_role='SALES_CLERK';

      Check whether any system privileges were used. There should be no rows returned, because no system privileges were used.

      select username, sys_priv from dba_used_sysprivs;
      Display the path of the privileges used if the privileges were granted to roles, and roles to users. Use the DBA_USED_OBJPRIVS_PATH view.

      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 ('TOM','JIM')
      and    object_name IN ('SALES','EMPLOYEES');

      TOM is granted the select privilege on the SH.SALES table through SALES_CLERK role. He used the privilege.
      The unused privs are visible in DBA_UNUSED_PRIVS view.
      The query returns no rows, because there are no unused privileges. Therefore, there is no privilege that has been unnecessarily granted.



      select username, sys_priv, obj_priv, object_name, path
      from   dba_unused_privs
      where  username='TOM' OR rolename='SALES_CLERK';

 

Delete Captures

    List all captures to delete:

    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;

    Delete all captures.

    exec SYS.DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE (name => 'Special_capt')

 

Resetting Your Environment

    Delete users and roles.

    drop user jim cascade;
    drop user tom cascade;
    drop user ann cascade;

    drop role hr_mgr;
    drop role sales_clerk;
    drop role analyst;

 

Summary

    In this tutorial, you have learned how to:

    • Define a privilege capture for:
      • Privileges used by all users
      • Privileges used through roles
      • Privileges used through contexts
    • Start a privilege analysis during a period of time
    • Generate results about privileges and roles used and unused during the analysis period
    • Compare used and unused privileges using views to decide which privileges and / or roles need to be revoked or kept
    • Delete capture analysis

    Resources

    Credits

    • Lead Curriculum Developer: Dominique Jeunot
    • Other Contributors: Jean-Francois Verrier