Tutorial: Auditing Nondatabase Users

Auditing nondatabase users who are typical application service accounts is crucial. They are identified in the database using the CLIENT_IDENTIFIER attribute.

Step 1: Create the User Accounts and Ensure the User OE Is Active

You must first create users and ensure that the user OE is active.

  1. Log in to a PDB as user SYS with the SYSDBA administrative privilege.

    sqlplus sys@pdb_name as sysdba
Enter password: password

    To find the available PDBs in a CDB, log in to the CDB root container and then query the PDB_NAME column of the DBA_PDBS data dictionary view. To check the current container, run the show con_name command.

  2. Create the local user policy_admin, who will create the fine-grained audit policy.

    CREATE USER policy_admin IDENTIFIED BY password;
GRANT CREATE SESSION, AUDIT_ADMIN TO policy_admin;

    Replace password with a password that is secure.

  3. Create the local user account auditor, who will check the audit trail for this policy.

    CREATE USER policy_auditor IDENTIFIED BY password;
GRANT CREATE SESSION, AUDIT_VIEWER TO policy_auditor;

  4. The sample user OE will also be used in this tutorial, so query the DBA_USERS data dictionary view to ensure that OE is not locked or expired.

    SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'OE';

The account status should be OPEN. If the DBA_USERS view lists user OE as locked and expired, log in as user SYSTEM and then enter the following statement to unlock the OE account and create a new password:

ALTER USER OE ACCOUNT UNLOCK IDENTIFIED BY password;

Replace password with a password that is secure. For greater security, do not give the OE account the same password from previous releases of Oracle Database.

Related Topics

Step 2: Create the Unified Audit Policy

Next, you are ready to create the unified audit policy.

  1. Connect to the PDB as user policy_admin.

    CONNECT policy_admin@pdb_name
Enter password: password

  2. Create the following policy:

    CREATE AUDIT POLICY orders_unified_audpol
  ACTIONS INSERT ON OE.ORDERS, UPDATE ON OE.ORDERS, DELETE ON OE.ORDERS, SELECT ON OE.ORDERS
  WHEN 'SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'') = ''robert'''
    EVALUATE PER STATEMENT;

AUDIT POLICY orders_unified_audpol;

In this example, the AUDIT_CONDITION parameter assumes that the nondatabase user is named robert. The policy will monitor any INSERT, UPDATE, DELETE, and SELECT statements that robert will attempt. Remember that the user’s CLIENT_IDENTITIFER setting that you enter in the policy is case sensitive and that the policy only recognizes the case used for the identity that you specify here. In other words, later on, if the user session is set to Robert or ROBERT, the policy’s condition will not be satisfied.

Step 3: Test the Policy

To test the policy, use OE must try to select from the OE.ORDERS table.

A unified auditing policy takes effect in the next user session for the users who are being audited. So, before their audit records can be captured, the users must connect to the database after the policy has been created.

  1. Connect as user OE and then select from the OE.ORDERS table.

    CONNECT OE@pdb_name
Enter password: password

SELECT COUNT(*) FROM ORDERS;

    The following output appears:

      COUNT(*)

----------
       105

  2. Connect as user policy_auditor and then check if any audit records were generated.

    CONNECT policy_auditor@pdb_name
Enter password: password

col dbusername format a10
col client_identifier format a20
col sql_text format a29

SELECT DBUSERNAME, CLIENT_IDENTIFIER, SQL_TEXT FROM UNIFIED_AUDIT_TRAIL
 WHERE SQL_TEXT LIKE '%FROM ORDERS%';

    The following output appears:

    no rows selected

  3. Reconnect as user OE, set the client identifier to robert, and then reselect from the OE.ORDERS table.

    CONNECT OE@pdb_name
Enter password: password

EXEC DBMS_SESSION.SET_IDENTIFIER('robert');

SELECT COUNT(*) FROM ORDERS;

    The following output should appear:

      COUNT(*)

----------
       105

  4. Reconnect as user auditor and then check the audit trail again.

    CONNECT policy_auditor@pdb_name
Enter password: password

SELECT DBUSERNAME, CLIENT_IDENTIFIER, SQL_TEXT FROM UNIFIED_AUDIT_TRAIL
 WHERE SQL_TEXT LIKE '%FROM ORDERS%';

This time, because robert has queried the OE.ORDERS table, the audit trail captures their actions:

DBUSERNAME CLIENT_IDENTIFIER SQL_TEXT

---------- ----------------- ----------------------------
OE         robert            SELECT COUNT(*) FROM ORDERS;

Step 4: Remove the Components of This Tutorial

If you no longer need the components of this tutorial, then you can remove them.

  1. Connect as user policy_admin, and then manually disable and drop the orders_unified_audpol policy.

    CONNECT policy_admin@pdb_name
Enter password: password

NOAUDIT POLICY orders_unified_audpol;
DROP AUDIT policy orders_unified_audpol;

    (Unified audit policies reside in the SYS schema, not the schema of the user who created them.)

  2. Connect to SQL*Plus as user SYSTEM.

    CONNECT SYSTEM@pdb_name
Enter password: password

  3. Drop users policy_admin and policy_auditor.

    DROP USER policy_admin;
DROP USER policy_auditor;

  4. If you want, lock and expire OE, unless other users want to use this account:

    ALTER USER OE PASSWORD EXPIRE ACCOUNT LOCK;