How to create an audit policy based on user profile? Example using a context
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