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
b. Create a user for auditing
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
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)
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.