DBA Hub

📋Steps in this guide1/6

Auditing Enhancements in Oracle Database 19c

This article gives an overview of the auditing enhancements in Oracle database 19c.

oracle 19cconfigurationintermediate
by OracleDba
47 views
1

Audit Top-Level Statements

When we are using PL/SQL APIs, a single call to a procedure may result in a large number of auditable actions. To reduce the number of entries in the audit trail, we can audit only top-level statements. In the case of a PL/SQL API, we would only audit the call to the procedure, rather than all the actions taken by the procedure when it runs. We connect to a privileged user and empty the audit trail. Don't do this if you care about the current contents of the audit trail. We create a test user. We create a test table called , and create a procedure called to add rows to the table. Both objects are owned by the user. We create two unified audit policies. They both audit all actions for the user, but second policy has the clause added. We enable the normal unified audit policy. We connect to the test user and execute the stored procedure to add a row into the table. We check the unified audit trail for rows related to the user. We have 10 rows of auditing, including the top-level call to the stored procedure, as well as the insert into the table. We stop auditing the normal policy and audit the top-level policy. We also clear down the audit trail. We connect to the test user and execute the stored procedure to add a row into the table. We check the unified audit trail for rows related to the user. We now have only 8 rows of auditing. We have audited the top-level call to the stored procedure, but not the insert into the table. When you are finished, disable and drop the audit policy.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

exec dbms_audit_mgmt.clean_audit_trail(dbms_audit_mgmt.audit_trail_unified,false);

-- drop user testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource to testuser1;
grant select_catalog_role to testuser1;

create table testuser1.t1 (
  id number
);

create or replace procedure testuser1.insert_t1 (p_id in number)
as
begin
  insert into t1 values (p_id);
end;
/

create audit policy testuser1_pol
  actions all
  when q'~ sys_context('userenv', 'session_user') = 'TESTUSER1' ~'
  evaluate per session;
  
create audit policy testuser1_toplevel_pol
  actions all
  when q'~ sys_context('userenv', 'session_user') = 'TESTUSER1' ~'
  evaluate per session
only toplevel
;

audit policy testuser1_pol;

conn testuser1/testuser1@//localhost:1521/pdb1

exec insert_t1(1);
commit;

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

set linesize 200
column event_timestamp format a30
column action_name format a13
column object_schema format a15
column object_name format a25

select event_timestamp,
       action_name,
       object_schema,
       object_name
from   unified_audit_trail
where  dbusername = 'TESTUSER1'
order by event_timestamp;

EVENT_TIMESTAMP                ACTION_NAME   OBJECT_SCHEMA   OBJECT_NAME
------------------------------ ------------- --------------- -------------------------
09-JAN-22 02.52.41.856739 PM   LOGON
09-JAN-22 02.52.41.864478 PM   ALTER SESSION
09-JAN-22 02.52.41.870481 PM   SELECT        SYS             DUAL
09-JAN-22 02.52.41.876372 PM   EXECUTE       SYS             DBMS_APPLICATION_INFO
09-JAN-22 02.52.41.881929 PM   COMMIT
09-JAN-22 02.52.41.886135 PM   COMMIT
09-JAN-22 02.52.41.889243 PM   INSERT        TESTUSER1       T1
09-JAN-22 02.52.41.890034 PM   EXECUTE       TESTUSER1       INSERT_T1
09-JAN-22 02.52.41.890959 PM   COMMIT
09-JAN-22 02.52.53.693288 PM   LOGOFF

10 rows selected.

SQL>

noaudit policy testuser1_pol;
audit policy testuser1_toplevel_pol;
exec dbms_audit_mgmt.clean_audit_trail(dbms_audit_mgmt.audit_trail_unified,false);

conn testuser1/testuser1@//localhost:1521/pdb1

exec insert_t1(1);
commit;

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

set linesize 200
column event_timestamp format a30
column action_name format a13
column object_schema format a15
column object_name format a25

select event_timestamp,
       action_name,
       object_schema,
       object_name
from   unified_audit_trail
where  dbusername = 'TESTUSER1'
order by event_timestamp;

EVENT_TIMESTAMP                ACTION_NAME   OBJECT_SCHEMA   OBJECT_NAME
------------------------------ ------------- --------------- -------------------------
09-JAN-22 02.56.18.545875 PM   LOGON
09-JAN-22 02.56.18.556143 PM   SELECT        SYS             DUAL
09-JAN-22 02.56.18.561399 PM   EXECUTE       SYS             DBMS_APPLICATION_INFO
09-JAN-22 02.56.18.566822 PM   COMMIT
09-JAN-22 02.56.18.572013 PM   COMMIT
09-JAN-22 02.56.18.575647 PM   EXECUTE       TESTUSER1       INSERT_T1
09-JAN-22 02.56.18.576603 PM   COMMIT
09-JAN-22 02.56.23.354887 PM   LOGOFF

8 rows selected.

SQL>

noaudit policy testuser1_pol
noaudit policy testuser1_toplevel_pol;
drop audit policy testuser1_pol;
drop audit policy testuser1_toplevel_pol;
2

EVENT_TIMESTAMP_UTC Column Added to UNIFIED_AUDIT_TRAIL View

The column has been added to the view. The column should be included in the WHERE clause of queries against the view where possible, as it will allow partition pruning, resulting in better performance. Other best practices for querying the view are listed here .
3

Trace File Analyzer (TFA) and Audit

In Oracle 19c the Trace File Analyzer (TFA) supports several new Service Request Data Collections (SRDCs) including "dbaudit". You can find the full list here .

Code/Command (click line numbers to comment):

1
2
cd $ORACLE_HOME/ahf/oracle.ahf/bin
./tfactl diagcollect -srdc dbaudit
4

Enabling SYSLOG and Windows Event Viewer Captures for the Unified Audit Trail

In Oracle 19c we can write a subset of unified audit trail records to the operating system logging system. Two initialization parameters have been added to support this. - : Can be applied to individual containers (CDB or PDBs). - : (UNIX only) Can only be applied to the root container, allowing a common audit log for all containers. The allowable values vary depending on the operating system. The documentation on this feature is unclear, with some sections of the documentation contradicting others regarding allowable values and what needs to be set to make it work. I have only got it to work using the following process. Create an entry in the "/etc/rsyslog.conf" file for the logging you are planning to do, then restart SYSLOG. Set the parameter in the CDB and the in any container (CDB or PDB) that you want to be included in the SYSLOG auditing. Some audit messages will now be included in the "/var/log/messages" and the "/var/log/local0.notice.audit.log" files. At the time of writing, there doesn't seem to be a way to avoid getting the auditing in the "/var/log/messages" file. This is true up to and including Oracle 19.13 and 21.4. On Windows systems, simply set the initialization parameter in any container and restart the database.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Windows

UNIFIED_AUDIT_SYSTEMLOG = { TRUE | FALSE }

UNIX

UNIFIED_AUDIT_SYSTEMLOG = 'facility_clause.priority_clause'
UNIFIED_AUDIT_COMMON_SYSTEMLOG = 'facility_clause.priority_clause'

facility_clause ::= { USER | LOCAL[ 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 ] }

priority_clause::= { NOTICE | INFO | DEBUG | WARNING | ERR | CRIT | ALERT | EMERG }

cat >> /etc/rsyslog.conf <<EOF
local0.notice /var/log/local0.notice.audit.log
EOF

systemctl restart syslog

conn / as sysdba
alter system set unified_audit_common_systemlog='local0.notice' scope=spfile;
shutdown immediate;
startup;


conn / as sysdba
alter session set container=pdb1;
alter system set unified_audit_systemlog='local0.notice' scope=spfile;
shutdown immediate;
startup;
5

PDB_GUID Field Added For SYSLOG and Windows Event Viewer

The field has been added into the audit records written to SYSLOG and the Windows Event Viewer. This indicates which container performed the audited action. The full list of fields written to SYSLOG and the Windows Event Viewer are listed here here .
6

Oracle Database Vault Command Rules For Unified Audit Policies

For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!