Tuesday, June 17, 2014

How Audit is working in distributed environment

On the Oracle database security documentation, there is a short paragraph on audit distributed environment:

Auditing is site autonomous. A database instance audits only the statements issued by directly connected users. A local Oracle Database node cannot audit actions that take place in a remote database.

What will be the audit output looks like if the session is via DBLINK?

Scenario preparation:

SQL> show user;
USER is "DONGHUA"

SQL> create user linkuser identified by ora123;

User created.

SQL> grant select any table to linkuser;

Grant succeeded.

SQL> grant create session to linkuser;

Grant succeeded.

SQL> audit all on t1;

Audit succeeded.

SQL> create database link orcl_link connect to linkuser identified by ora123 using 'orcl';

Database link created.

SQL> select count(*) from donghua.t1@orcl_link;

  COUNT(*)
----------
         1

Output:

image

Surprisingly, the session_user context kept through DBLink:

SQL> select sys_context('userenv','session_user') from dual@orcl_link;

SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
DONGHUA

SQL> select sys_context('userenv','session_user') from dual;

SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
DONGHUA