How To Audit All Access of Proxy Users with Unified Auditing

This article is intended to provide an example for how to audit access by proxy users using the Unified Auditing feature brought out in Oracle Enterprise Database 12c.  The sample also uses a condition in the policy so that only actions by the proxy user are recorded.

Note:  These steps are specific to Microsoft Windows operating systems.  They also will work on non-Windows systems without the need to stop the database service using net stop

1. Enable unified auditing
a. connect as a sysdba user

sqlplus sys/oracle as sysdba

b. Create a user for auditing

create user audit_admin identified by audit_admin;
grant CREATE SESSION, CREATE PROCEDURE, CREATE ROLE, INHERIT ANY PRIVILEGES, SELECT ANY DICTIONARY, AUDIT SYSTEM to audit_admin;
grant execute on sys.dbms_audit_mgmt to audit_admin;

c. Enable Unified Auditing

shutdown immediate
net stop OracleServiceDBSEC
lsnrctl stop

**********
On Windows:
cd %ORACLE_HOME%\BIN
ren orauniaud12.dll.dbl orauniaud12.dllOn Linux:
% cd $ORACLE_HOME/rdbms/lib
% make -f ins_rdbms.mk uniaud_on ioracle
*********

lsnrctl start
net start OracleServiceDBSEC
sqlplus sys/oracle as sysdba
select value from v$option where parameter = ‘Unified Auditing’;
TRUE

2. Create test user and test proxy user (These steps will use the scott/tiger schema for the test user)

CREATE USER appuser identified by usertest;
GRANT CREATE SESSION to usertest;
ALTER USER scott GRANT CONNECT THROUGH usertest;
connect usertest[scott]/usertest
select sys_context(‘userenv’,’current_user’) from dual;
SCOTT
select sys_context(‘userenv’,’proxy_user’) from dual;
appuser

3. Turn on Unified Auditing for the proxy user

connect audit_amin/audit_admin
create audit policy user_proxy_policy
actions all
when ‘SYS_CONTEXT(”USERENV”, ”PROXY_USER”) IS NOT NULL’
evaluate per statement;

AUDIT POLICY user_proxy_policy;
Audit succeeded

4. Test the audit for the proxy user

connect usertest[scott]/usertest
select * from scott.emp;

connect sys/oracle as sysdba
select count(*) from unified_audit_trail;

new audit records will be present. checking the audit entry shows the dbusername is scott and the dbproxy_username is usertest.

Leave a Reply

Your email address will not be published.