{"id":1129,"date":"2026-05-01T16:06:11","date_gmt":"2026-05-01T14:06:11","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=1129"},"modified":"2026-04-29T16:09:27","modified_gmt":"2026-04-29T14:09:27","slug":"how-to-create-an-audit-policy-based-on-user-profile-example-using-a-context","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2026\/05\/01\/how-to-create-an-audit-policy-based-on-user-profile-example-using-a-context\/","title":{"rendered":"How to create an audit policy based on user profile? Example using a context"},"content":{"rendered":"\n

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<\/code>. <\/p>\n\n\n\n

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;<\/code><\/p>\n\n\n\n

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;<\/code><\/p>\n\n\n\n

The user profile is unfortunately not automatically available in the default USERENV context. <\/p>\n\n\n\n

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.<\/p>\n\n\n\n

I’ve decided on the latter option. For this I need few steps – this is done inside the PDB:<\/p>\n\n\n\n

1. Create a context – AUDIT_CONTEXT<\/code> -, which must say what program is allowed to set it<\/h2>\n\n\n
\nCREATE OR REPLACE CONTEXT audit_context USING audsys.proc_set_audit_flag;\n<\/pre><\/div>\n\n\n
2. Create the procedure referenced above – AUDSYS.PROC_SET_AUDIT_FLAG<\/code>, that sets the context<\/h2>\n\n\n
\nCREATE OR REPLACE PROCEDURE audsys.proc_set_audit_flag IS\n    v_profile VARCHAR2(128);\n  BEGIN\n    SELECT profile INTO v_profile\n      FROM dba_users\n     WHERE username = SYS_CONTEXT('USERENV', 'SESSION_USER');\n\n    IF v_profile = 'APP_PROFILE' THEN\n    dbms_output.put_line('Set context AUDIT Y');\n      DBMS_SESSION.SET_CONTEXT('AUDIT_CONTEXT', 'AUDIT', 'Y');\n    ELSE\n      dbms_output.put_line('Set context AUDIT N');\n      DBMS_SESSION.SET_CONTEXT('AUDIT_CONTEXT', 'AUDIT', 'N');\n    END IF;\n  EXCEPTION\n    WHEN OTHERS THEN\n      DBMS_SESSION.SET_CONTEXT('AUDIT_CONTEXT', 'AUDIT', 'N');\nEND proc_set_audit_flag;\n\/\n<\/pre><\/div>\n\n\n
3. Grant access to the procedure <\/h2>\n\n\n\n
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<\/code>; and any user needs to be able to execute the procedure, so it needs to be available to PUBLIC<\/code>.<\/p>\n\n\n
\nCREATE ROLE IF NOT EXISTS role_set_audit_flag;\nGRANT select ON dba_users TO role_set_audit_flag;\nGRANT role_set_audit_flag TO PROCEDURE audsys.proc_set_audit_flag;\nGRANT execute ON audsys.proc_set_audit_flag TO public;\n<\/pre><\/div>\n\n\n
4. Create a logon trigger – TRIGGER_SET_AUDIT_CONTEXT<\/code> – calling this procedure<\/h2>\n\n\n
\nCREATE OR REPLACE TRIGGER trigger_set_audit_context\n  AFTER LOGON ON DATABASE\nBEGIN\n  audsys.proc_set_audit_flag;\nEXCEPTION\n  WHEN OTHERS THEN NULL;\nEND;\n\/\n<\/pre><\/div>\n\n\n
5. Use this construct within an audit policy – AUDIT_APP_USERS<\/code><\/h2>\n\n\n
\nCREATE AUDIT POLICY audit_app_users \nACTIONS PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE \nWHEN q'!SYS_CONTEXT('AUDIT_CONTEXT', 'AUDIT') = 'Y'!' EVALUATE PER SESSION;\n\nAUDIT POLICY audit_app_users;\n<\/pre><\/div>\n\n\n
Conclusion<\/h2>\n\n\n\n
Now, when I connect with a user having the APP_USER profile, I can see I’ve the right context<\/p>\n\n\n
\nMIGUEL@ANJO.PDB1> SELECT * FROM SESSION_CONTEXT;\n\n       NAMESPACE    ATTRIBUTE    VALUE\n________________ ____________ ________\nAUDIT_CONTEXT      AUDIT      Y\n\n1 row selected.\n<\/pre><\/div>\n\n\n
And on the audit trail we can now see the entries from that policy:<\/p>\n\n\n
\nSYS@ANJO.PDB1> select  EVENT_TIMESTAMP, DBUSERNAME, UNIFIED_AUDIT_POLICIES from audsys.unified_audit_trail order by 1 desc fetch first 1 rows only;\n\n                  EVENT_TIMESTAMP    DBUSERNAME            UNIFIED_AUDIT_POLICIES                                                                            \n_________________________________ _____________ _________________________ \n29-APR-2026 16:03:46.841520000    MIGUEL           AUDIT_APP_USERS \n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,21,6],"tags":[],"class_list":{"0":"post-1129","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-audit","7":"category-ideas","8":"category-oracle","9":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1129","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=1129"}],"version-history":[{"count":3,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1129\/revisions"}],"predecessor-version":[{"id":1132,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1129\/revisions\/1132"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=1129"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=1129"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=1129"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}