Before You Begin
This 15-minute tutorial shows you how to audit top-level user activities in a database without collecting recursive user activity.
Background
Before Oracle Database 19c, the audit policies records user activities including both directly issued events and recursive SQL statements.
In Oracle Database 19c, auditing can exclude recursive SQL statements. Top-level statements are SQL statements that users directly issue. These statements can be important for both security and compliance. SQL statements run from within PL/SQL procedures or functions are not considered top-level because they may be less relevant for auditing purposes.
What Do You Need?
- Oracle Database 19c installed
- A database, either non-CDB or CDB with a PDB
- The
create_proc.sql
SQL script. Download the SQL script to the labs directory created on your server/home/oracle/labs
Create
The Table and The Procedure
- Execute the Oracle script
$ORACLE_HOME/demo/schema/human_resource/hr_main.sql
that creates theHR
schema inPDB1
in the tablespaceUSERS
. The password in the command is the password for theHR
schema.sqlplus system@PDB1 @$ORACLE_HOME/demo/schema/human_resources/hr_main.sql password users temp /tmp Enter password: password
- Query the
HR.EMPLOYEES
table.SELECT employee_id, salary FROM hr.employees;
Read the rows from theHR.EMPLOYEES
table. Read the result from theresult1
text file. - Create a procedure that allows the
HR
user to raise the employees’ salaries inPDB1
.@/home/oracle/labs/create_proc.sql
Read the result from theresult2
text file.
Create
and Enable The Audit Policy
- Create the security officer. The security officer is the one
responsible for managing audit policies.
CREATE USER auditor_admin IDENTIFIED BY password;
- Grant the security officer the
CREATE SESSION
system privilege and theAUDIT_ADMIN
role.GRANT create session, audit_admin TO auditor_admin;
- Connect to
PDB1
as auditor_admin.CONNECT auditor_admin@PDB1 Enter password: password
- Create an audit policy that audits any salary increase.
CREATE AUDIT POLICY pol_sal_increase ACTIONS UPDATE ON hr.employees;
- Enable the audit policy.
AUDIT POLICY pol_sal_increase WHENEVER SUCCESSFUL;
Audit
The User Activities Including Recursive Statements
- In another session, session2, connect as
HR
and increase the salary for employee ID 106 through theRAISE_SALARY
procedure.sqlplus hr@PDB1 Enter password: password
EXEC emp_admin.raise_salary(106,10)
- Still in session2, update the row directly and commit.
UPDATE hr.employees SET salary=salary*0.1 WHERE employee_id = 106;
COMMIT;
- Quit the session.
EXIT
Display
the
Activities Audited
- Verify from session1 that the update actions executed
through the PL/SQL procedure and directly by the
UPDATE
command are audited.SELECT action_name, object_name, sql_text FROM unified_audit_trail WHERE unified_audit_policies = 'POL_SAL_INCREASE';
Read the result from theresult3
text file. - Disable the audit policy.
NOAUDIT POLICY pol_sal_increase;
- Drop the audit policy.
DROP AUDIT POLICY pol_sal_increase;
Audit
the Top-Level Statements Only
- Create an audit policy that audits any salary increase
executed directly with an
UPDATE
command only.CREATE AUDIT POLICY pol_sal_increase_direct ACTIONS UPDATE ON hr.employees ONLY TOPLEVEL;
- Enable the audit policy.
AUDIT POLICY pol_sal_increase_direct WHENEVER SUCCESSFUL;
- In session2, connect as
HR
toPDB1
.sqlplus hr@PDB1 Enter password: password
- Increase the salary for employee ID 107 through the
RAISE_SALARY
procedure.EXEC emp_admin.raise_salary(107,30)
- Still in session2, update the row directly and commit.
UPDATE hr.employees SET salary=salary*0.1 WHERE employee_id = 107;
COMMIT;
- Quit the session.
EXIT
Display
The Top-Level Activities Audited
- Verify from session1 that the update actions executed
through the PL/SQL procedure are not audited.
SELECT action_name, object_name, sql_text FROM unified_audit_trail WHERE unified_audit_policies = 'POL_SAL_INCREASE_DIRECT'; ACTION_NAME OBJECT_NAME ------------ ------------ SQL_TEXT --------------------------------------------------------- UPDATE EMPLOYEES UPDATE hr.employees SET salary=salary*0.1 WHERE employee_id = 107
Observe that only the directUPDATE
statement is audited as this is the purpose of theONLY TOPLEVEL
clause of theCREATE AUDIT POLICY
command.
Clean
Up the Environment
- In session1, disable the audit policy.
NOAUDIT POLICY pol_sal_increase_direct;
- Drop the audit policy.
DROP AUDIT POLICY pol_sal_increase_direct;
- Connect as
SYSTEM
toPDB1
.CONNECT system@PDB1 Enter password: password
- Drop the
HR
schema.DROP USER hr CASCADE;
- Drop the
auditor_admin
user.DROP USER auditor_admin CASCADE;
- Quit the session.
EXIT