This was first published on https://blog.dbi-services.com/12cr2-auditing-all-users-with-a-role-granted (2017-06-04)
Republishing here for new followers. The content is related to the the versions available at the publication date
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 PMThe 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 PMBut 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 YESBut 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
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 YESThe 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.
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.
Hi Franck,
Though 12.1 does not have such smooth syntax for this particular case, we can use the AUDIT CONDITION clause for that with some tiny restrictions. Like:
Unfortunately, the SYS_SESSION_ROLES namespace defines whether a passed role is ENABLED in the session or not. It means that if a user has the DBA role granted but not ENABLED in the session, our policy does not fire. The 12.2 syntax works for both cases: audit policy DEMO_POLICY by users with granted roles DBA;The AUDIT CONDITION is also not so straightforward.
Yours faithfully, Mikhail Velikikh.
Thanks Mikhail, good workaround in < 12.2 Regards, Franck.