Creating audit policies to audit privileges, actions and roles under specified conditions

In this recipe, you will create several unified audit policies.

Getting ready

To complete this recipe, you'll need two existing users:

• A user who has the audit_admin role (for example, jack)

• A user who has the create session privilege (for example, john)

Also, you should create the roles hr_role and oe_role as stated here and grant hr_role to the user john.

SQL> create role hr_role;
SQL> grant select any table, create table to hr_role;
SQL> grant insert on hr.departments to hr_role;
SQL> create role oe_role;
SQL> grant drop any table to oe_role;
SQL> grant select, update on oe.orders to oe_role;
SQL> grant oe_role to hr_role;
SQL> grant hr_role to john;

How to do it...

1. Connect to the database as a user who has the audit_admin role (for example, jack):

   $ sqlplus jack
   

2. Create audit policy my_policy1:

   SQL> CREATE AUDIT POLICY MY_POLICY1
   PRIVILEGES SELECT ANY TABLE
   ACTIONS CREATE TABLE...