12cR2 auditing all users with a role granted

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.

 

Cet article 12cR2 auditing all users with a role granted est apparu en premier sur Blog dbi services.