Practice: Enforcing Unified Audit Policies on the Current User

Overview

This practice shows how unified audit policies are enforced on the current user who executes the SQL statement.

Before starting any new practice, refer to the Practices Environment recommendations.

Step 1 : Create the users and a procedure

• Execute the /home/oracle/labs/M104781GC10/setup_audit_policies.sh script to create users and a procedure for this practice.
  

  
  $ cd /home/oracle/labs/M104781GC10
  $ /home/oracle/labs/M104781GC10/setup_audit_policies.sh
  ...
  Connected to:
  
  SQL> drop user u1 cascade;
  drop user u1 cascade
            *
  ERROR at line 1:
  ORA-01918: user 'U1' does not exist
  
  SQL> drop user u2 cascade;
  drop user u2 cascade
            *
  ERROR at line 1:
  ORA-01918: user 'U2' does not exist
  
  SQL> create user u1 identified by password;
  
  User created.
  
  SQL> grant create session, create procedure to u1;
  
  Grant succeeded.
  
  SQL> create user u2 identified by password;
  
  User created.
  
  SQL> grant select on hr.employees to u1, u2;
  
  Grant succeeded.
  
  SQL> grant create session to u2;
  
  Grant succeeded.
  
  SQL> grant select on unified_audit_trail to u1,u2;
  
  Grant succeeded.
  
  SQL>
  SQL> CREATE OR REPLACE PROCEDURE u1.procemp (employee_id IN NUMBER)
    2  AS
    3     v_emp_id  NUMBER:=employee_id;
    4     v_sal NUMBER;
    5  BEGIN
    6     SELECT salary INTO v_sal FROM hr.employees WHERE employee_id=v_emp_id;
    7     dbms_output.put_line('Salary is : '||v_sal || ' for Employee ID: '||v_emp_id);
    8  END procemp;
    9  /
  
  Procedure created.
  
  SQL>
  SQL> grant execute on u1.procemp to u2;
  
  Grant succeeded.
  
  SQL>
  SQL> exit
  
  $

Step 2 : Create and enable an audit policy

• In PDB21, create and enable an audit policy to audit any query on the HR.EMPLOYEES table executed by the login user named U2.

  
  $ sqlplus system@PDB21
  
  Copyright (c) 1982, 2019, Oracle.  All rights reserved.
  
  Enter password:
  
  SQL> CREATE AUDIT POLICY pol_emp ACTIONS select on hr.employees;
  
  Audit policy created.
  
  SQL> AUDIT POLICY pol_emp BY u2;
  
  Audit succeeded.
  
  SQL> 

Step 3 : Test

• Connect to PDB21 as U2 and execute the U1.PROCEMP procedure.

  
  SQL> CONNECT u2@PDB21
  Enter password:
  SQL> SET SERVEROUTPUT ON
  SQL> EXECUTE u1.procemp(206)
  Salary is : 8300 for Employee ID: 206
  
  PL/SQL procedure successfully completed.
  
  SQL> 

• Display the values in DBUSERNAME (the login user) and CURRENT_USER (the user who executed the procedure) from the unified audit trail.

  
  SQL> SELECT dbusername, current_user, action_name
       FROM   unified_audit_trail
       WHERE  unified_audit_policies = 'POL_EMP';
  
  no rows selected
  
  SQL> EXIT
  $

  Observe that the unified audit policy is enforced on the current user who executed the SQL statement, U1. Because only U2 is audited and U1 is the current user executing the query, there is no audit record generated that would give the auditor the impression that the statement is executed by the user who owned the top-level user session.