Daily Archives: 01.05.2026

How to create an audit policy based on user profile? Example using a context   Recently updated !

This entry was posted in Audit ideas Oracle on by

The customer wanted to have some audit policies that would be applied only to users who have a certain profile – lets say the profile APP_PROFILE.

Using unified auditing, it is possible to enable a policy for certain roles using AUDIT POLICY policy_name BY USERS WITH GRANTED ROLES role_name;

And you can use context on audit policies using CREATE AUDIT POLICY policy_name ACTIONS <action_list> WHEN q'!SYS_CONTEXT('<context_name>','<context_attr>') = <value>!' EVALUATE BY SESSION;

The user profile is unfortunately not automatically available in the default USERENV context.

The way to have audit policy on users with a certain profile, needs to go via a logon trigger, which either sets a ROLE to a user, or sets a CONTEXT.

I’ve decided on the latter option. For this I need few steps – this is done inside the PDB:

1. Create a context – AUDIT_CONTEXT -, which must say what program is allowed to set it

CREATE OR REPLACE CONTEXT audit_context USING audsys.proc_set_audit_flag;

2. Create the procedure referenced above – AUDSYS.PROC_SET_AUDIT_FLAG, that sets the context

CREATE OR REPLACE PROCEDURE audsys.proc_set_audit_flag IS
    v_profile VARCHAR2(128);
  BEGIN
    SELECT profile INTO v_profile
      FROM dba_users
     WHERE username = SYS_CONTEXT('USERENV', 'SESSION_USER');

    IF v_profile = 'APP_PROFILE' THEN
    dbms_output.put_line('Set context AUDIT Y');
      DBMS_SESSION.SET_CONTEXT('AUDIT_CONTEXT', 'AUDIT', 'Y');
    ELSE
      dbms_output.put_line('Set context AUDIT N');
      DBMS_SESSION.SET_CONTEXT('AUDIT_CONTEXT', 'AUDIT', 'N');
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_SESSION.SET_CONTEXT('AUDIT_CONTEXT', 'AUDIT', 'N');
END proc_set_audit_flag;
/

3. Grant access to the procedure

The procedure will be called by low privilege users. So the procedure needs access to DBA_USERS, this is done via an intermediary role ROLE_SET_AUDIT_FLAG; and any user needs to be able to execute the procedure, so it needs to be available to PUBLIC.

CREATE ROLE IF NOT EXISTS role_set_audit_flag;
GRANT select ON dba_users TO role_set_audit_flag;
GRANT role_set_audit_flag TO PROCEDURE audsys.proc_set_audit_flag;
GRANT execute ON audsys.proc_set_audit_flag TO public;

4. Create a logon trigger – TRIGGER_SET_AUDIT_CONTEXT – calling this procedure

CREATE OR REPLACE TRIGGER trigger_set_audit_context
  AFTER LOGON ON DATABASE
BEGIN
  audsys.proc_set_audit_flag;
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/

5. Use this construct within an audit policy – AUDIT_APP_USERS

CREATE AUDIT POLICY audit_app_users 
ACTIONS PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE 
WHEN q'!SYS_CONTEXT('AUDIT_CONTEXT', 'AUDIT') = 'Y'!' EVALUATE PER SESSION;

AUDIT POLICY audit_app_users;

Conclusion

Now, when I connect with a user having the APP_USER profile, I can see I’ve the right context

MIGUEL@ANJO.PDB1> SELECT * FROM SESSION_CONTEXT;

       NAMESPACE    ATTRIBUTE    VALUE
________________ ____________ ________
AUDIT_CONTEXT      AUDIT      Y

1 row selected.

And on the audit trail we can now see the entries from that policy:

SYS@ANJO.PDB1> select  EVENT_TIMESTAMP, DBUSERNAME, UNIFIED_AUDIT_POLICIES from audsys.unified_audit_trail order by 1 desc fetch first 1 rows only;

                  EVENT_TIMESTAMP    DBUSERNAME            UNIFIED_AUDIT_POLICIES                                                                            
_________________________________ _____________ _________________________ 
29-APR-2026 16:03:46.841520000    MIGUEL           AUDIT_APP_USERS