By Franck Pachot

.
12.1 introduced Unified Auditing where you define policies and then enable them. As with the traditional audit, you enable them for all users or for specific users. The unified auditing adds a syntax to audit all users except some listed ones. 12.2 adds a syntax to audit a group of users, defined by the role granted. This is the best way to enable a policy for a group of users, including those created later.

I create a simple policy, to audit logon and DBA role usage:


SQL> create audit policy DEMO_POLICY actions logon, roles DBA;
Audit POLICY created.

I create a new DBA user, USER1


SQL> create user USER1 identified by covfefe quota unlimited on USERS;
User USER1 created.
SQL> grant DBA to USER1;
Grant succeeded.

I want to enable the policy for this user because I want to audit all DBAs


SQL> audit policy DEMO_POLICY by USER1;
Audit succeeded.

I remove Audit records for this demo


SQL> exec dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>dbms_audit_mgmt.audit_trail_unified,use_last_arch_timestamp=>false);
PL/SQL procedure successfully completed.

Let’s connect with this user and see what is audited:


SQL> connect USER1/covfefe@//localhost/PDB1
Connected.
 
SQL> select audit_type,os_username,userhost,terminal,dbusername,action_name,unified_audit_policies,system_privilege_used,event_timestamp
  2   from unified_audit_trail where unified_audit_policies='DEMO_POLICY' order by event_timestamp;
 
AUDIT_TYPE  OS_USERNAME  USERHOST  TERMINAL  DBUSERNAME  ACTION_NAME  UNIFIED_AUDIT_POLICIES  SYSTEM_PRIVILEGE_USED  EVENT_TIMESTAMP
----------  -----------  --------  --------  ----------  -----------  ----------------------  ---------------------  ---------------
Standard    oracle       VM104     pts/0     USER1       LOGON        DEMO_POLICY             CREATE SESSION         04-JUN-17 04.22.51.865094000 PM
Standard    oracle       VM104     pts/0     USER1       SELECT       DEMO_POLICY             SELECT ANY DICTIONARY  04-JUN-17 04.22.51.948187000 PM

The logon and the select on dictionary table (possible here thanks to the DBA role) has been audited because the policy is enabled for this user.

We have a new DBA and we create a new user for him:


SQL> create user USER2 identified by covfefe quota unlimited on USERS;
User USER2 created.
SQL> grant DBA to USER2;
Grant succeeded.

He connects and check what is audited:


SQL> connect USER2/covfefe@//localhost/PDB1
Connected.
SQL> select audit_type,os_username,userhost,terminal,dbusername,action_name,unified_audit_policies,system_privilege_used,event_timestamp
  2   from unified_audit_trail where unified_audit_policies='DEMO_POLICY' order by event_timestamp;
 
AUDIT_TYPE  OS_USERNAME  USERHOST  TERMINAL  DBUSERNAME  ACTION_NAME  UNIFIED_AUDIT_POLICIES  SYSTEM_PRIVILEGE_USED  EVENT_TIMESTAMP
----------  -----------  --------  --------  ----------  -----------  ----------------------  ---------------------  ---------------
Standard    oracle       VM104     pts/0     USER1       LOGON        DEMO_POLICY             CREATE SESSION         04-JUN-17 04.22.51.865094000 PM
Standard    oracle       VM104     pts/0     USER1       SELECT       DEMO_POLICY             SELECT ANY DICTIONARY  04-JUN-17 04.22.51.948187000 PM
Standard    oracle       VM104     pts/0     USER1       SELECT       DEMO_POLICY             SELECT ANY DICTIONARY  04-JUN-17 04.22.52.132814000 PM

Nothing is audited for this user. The DBA role usage is audited, but only for USER1.

Of course, we can add an audit statement for each user created for a DBA:


SQL> audit policy DEMO_POLICY by USER2;
Audit succeeded.

Then his new activity is audited:


SQL> connect USER2/covfefe@//localhost/PDB1
Connected.
SQL> select audit_type,os_username,userhost,terminal,dbusername,action_name,unified_audit_policies,system_privilege_used,event_timestamp
  2   from unified_audit_trail where unified_audit_policies='DEMO_POLICY' order by event_timestamp;
 
AUDIT_TYPE  OS_USERNAME  USERHOST  TERMINAL  DBUSERNAME  ACTION_NAME  UNIFIED_AUDIT_POLICIES  SYSTEM_PRIVILEGE_USED  EVENT_TIMESTAMP
----------  -----------  --------  --------  ----------  -----------  ----------------------  ---------------------  ---------------
Standard    oracle       VM104     pts/0     USER1       LOGON        DEMO_POLICY             CREATE SESSION         04-JUN-17 04.22.51.865094000 PM
Standard    oracle       VM104     pts/0     USER1       SELECT       DEMO_POLICY             SELECT ANY DICTIONARY  04-JUN-17 04.22.51.948187000 PM
Standard    oracle       VM104     pts/0     USER1       SELECT       DEMO_POLICY             SELECT ANY DICTIONARY  04-JUN-17 04.22.52.132814000 PM
Standard    oracle       VM104     pts/0     USER2       LOGON        DEMO_POLICY             CREATE SESSION         04-JUN-17 04.22.52.338928000 PM

But for security reason, we would like to be sure that any new user having the DBA role granted is audited.
Let’s try something else


SQL> noaudit policy DEMO_POLICY by USER1,USER2;
Noaudit succeeded.

We can simply audit all users:


SQL> audit policy DEMO_POLICY;
Audit succeeded.

But this is too much. Some applications constantly logon and logoff and we don’t want to have that in the audit trail.


SQL> noaudit policy DEMO_POLICY;
Noaudit succeeded.

We can still enable the policy for all users, and exempt those users we don’t want:


SQL> audit policy DEMO_POLICY except DEMO;
Audit succeeded.

Here is what is enabled, and this will audot all new users:


SQL> select * from audit_unified_enabled_policies;
 
USER_NAME  POLICY_NAME         ENABLED_OPT  ENABLED_OPTION  ENTITY_NAME  ENTITY_TYPE  SUCCESS  FAILURE
---------  -----------         -----------  --------------  -----------  -----------  -------  -------
DEMO       DEMO_POLICY         EXCEPT       EXCEPT USER     DEMO         USER         YES      YES
ALL USERS  ORA_SECURECONFIG    BY           BY USER         ALL USERS    USER         YES      YES
ALL USERS  ORA_LOGON_FAILURES  BY           BY USER         ALL USERS    USER         NO       YES

But once again, this is not what we want.


SQL> noaudit policy DEMO_POLICY by DEMO;
Noaudit succeeded.
 
SQL> select * from audit_unified_enabled_policies;
 
USER_NAME  POLICY_NAME         ENABLED_OPT  ENABLED_OPTION  ENTITY_NAME  ENTITY_TYPE  SUCCESS  FAILURE
---------  -----------         -----------  --------------  -----------  -----------  -------  -------
ALL USERS  ORA_SECURECONFIG    BY           BY USER         ALL USERS    USER         YES      YES
ALL USERS  ORA_LOGON_FAILURES  BY           BY USER         ALL USERS    USER         NO       YES

Audit all users to whom roles are granted directly

In 12cR2 we have the possibility to do exactly what we want: audit all users having the DBA role granted:


SQL> audit policy DEMO_POLICY by users with granted roles DBA;
Audit succeeded.

This enables the audit for all users for whom the DBA role has been directly granted:


SQL> select * from audit_unified_enabled_policies;
 
USER_NAME  POLICY_NAME         ENABLED_OPT  ENABLED_OPTION   ENTITY_NAME  ENTITY_TYPE  SUCCESS  FAILURE
---------  -----------         -----------  --------------   -----------  -----------  -------  -------
           DEMO_POLICY         INVALID      BY GRANTED ROLE  DBA          ROLE         YES      YES
ALL USERS  ORA_SECURECONFIG    BY           BY USER          ALL USERS    USER         YES      YES
ALL USERS  ORA_LOGON_FAILURES  BY           BY USER          ALL USERS    USER         NO       YES

The important thing is that a newly created user will be audited as long as he has the DBA role directly granted:


SQL> create user USER3 identified by covfefe quota unlimited on USERS;
User USER3 created.
SQL> grant DBA to USER3;
Grant succeeded.
 
SQL> connect USER3/covfefe@//localhost/PDB1
Connected.
SQL> select audit_type,os_username,userhost,terminal,dbusername,action_name,unified_audit_policies,system_privilege_used,event_timestamp
  2   from unified_audit_trail where unified_audit_policies='DEMO_POLICY' order by event_timestamp;
 
AUDIT_TYPE  OS_USERNAME  USERHOST  TERMINAL  DBUSERNAME  ACTION_NAME  UNIFIED_AUDIT_POLICIES  SYSTEM_PRIVILEGE_USED  EVENT_TIMESTAMP
----------  -----------  --------  --------  ----------  -----------  ----------------------  ---------------------  ---------------
Standard    oracle       VM104     pts/0     USER1       LOGON        DEMO_POLICY             CREATE SESSION         04-JUN-17 04.29.17.915217000 PM
Standard    oracle       VM104     pts/0     USER1       SELECT       DEMO_POLICY             SELECT ANY DICTIONARY  04-JUN-17 04.29.17.988151000 PM
Standard    oracle       VM104     pts/0     USER1       SELECT       DEMO_POLICY             SELECT ANY DICTIONARY  04-JUN-17 04.29.18.117258000 PM
Standard    oracle       VM104     pts/0     USER2       LOGON        DEMO_POLICY             CREATE SESSION         04-JUN-17 04.29.18.322716000 PM
Standard    oracle       VM104     pts/0     USER2       SELECT       DEMO_POLICY             SELECT ANY DICTIONARY  04-JUN-17 04.29.18.345351000 PM
Standard    oracle       VM104     pts/0     USER2       SELECT       DEMO_POLICY             SELECT ANY DICTIONARY  04-JUN-17 04.29.18.415117000 PM
Standard    oracle       VM104     pts/0     USER2       SELECT       DEMO_POLICY             SELECT ANY DICTIONARY  04-JUN-17 04.29.18.439656000 PM
Standard    oracle       VM104     pts/0     USER2       SELECT       DEMO_POLICY             SELECT ANY DICTIONARY  04-JUN-17 04.29.18.455274000 PM
Standard    oracle       VM104     pts/0     USER3       LOGON        DEMO_POLICY             CREATE SESSION         04-JUN-17 04.29.18.507496000 PM

This policy applies to all users having the DBA role, and gives the possibility to audit more than their DBA role usage: here I audit all login from users having the DBA role.

So what?

We don’t use roles only to group privileges to grant. A role is usually granted to define groups of users: DBAs, Application user, Read-only application users, etc. The Unified Auditing can define complex policies, combining the audit of actions, privileges, and roles. The 12.2 syntax allows enabling the policy to a specific group of users.

Post Views: 1,476
Thumbnail [60x60]
by
Oracle Team