DBA Hub

📋Steps in this guide1/2

Auditing Enhancements in Oracle Database 23ai/26ai

This post describes some of the auditing enhancements in Oracle database 23ai/26ai.

oracle 23configurationintermediate
by OracleDba
22 views
1

Desupport of Traditional Auditing

Traditional auditing was deprecated in Oracle 21c, and has been desupported in Oracle 23ai/26ai. Make sure you are using Unified Auditing . You can read about handling the transition to unified auditing here .
2

Audit Individual Columns for Tables and Views

In Oracle 23ai/26ai we can create audit policies on individual columns of tables and views, which allows us to thin out the contents of the audit trail by ignoring actions that don't affect the columns of interest. For a table or view column we can audit the following actions, as described here . To demonstrate this we create a test table. We connect to a privileged user and create a new audit policy. We want to audit updates on or , and queries of . Notice we supply a comma-separated list of columns the audited action applies to. We check the audit trail for actions against the table, and we can see there are no actions audited. We perform some operations against the test table, some of which are auditable actions. We check the audit trail. Notice only those actions on the specified columns were audited. The query of the ID column, and the updates to the COL3 column were not audited. For more information see: - Object Actions That Can Be Audited - Example: Auditing an Action on a Table Column - Handling the Desupport of Traditional Auditing Hope this helps. Regards Tim...

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
117
118
119
120
ALL, ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, SELECT, UPDATE

conn testuser1/testuser1@//localhost:1521/freepdb1

drop table if exists audit_test_tab purge;

create table audit_test_tab (
  id  number generated always as identity,
  col1 varchar2(10),
  col2 varchar2(10),
  col3 varchar2(10)
);

insert into audit_test_tab (col1, col2) values ('apple', 'banana');
commit;

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

noaudit policy test_audit_policy;
drop audit policy test_audit_policy;

create audit policy test_audit_policy
  actions update(col1, col2) on testuser1.audit_test_tab,
          select(col2) on testuser1.audit_test_tab
  container = current;

audit policy test_audit_policy;

column event_timestamp format a30
column dbusername format a10
column action_name format a20
column object_schema format a10
column object_name format a20
column sql_text format a40

select event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name,
       sql_text
from   unified_audit_trail
where  object_name = 'AUDIT_TEST_TAB'
order BY event_timestamp;

no rows selected

SQL>

conn testuser1/testuser1@//localhost:1521/freepdb1

-- Not audited.
insert into audit_test_tab (col1, col2) values ('apple2', 'banana2');

update audit_test_tab
set    col3 = 'pear'
where  col3 is null;

commit;

select id from audit_test_tab;

        ID
----------
         1
         2

SQL>


-- Audited.
update audit_test_tab
set    col1 = 'apple1'
where  col1 = 'apple';

update audit_test_tab
set    col2 = 'banana1'
where  col2 = 'banana';

select col2 from audit_test_tab;

COL2
----------
banana1
banana2

SQL>

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

column event_timestamp format a30
column dbusername format a10
column action_name format a20
column object_schema format a10
column object_name format a20
column sql_text format a40

select event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name,
       sql_text
from   unified_audit_trail
where  object_name = 'AUDIT_TEST_TAB'
order BY event_timestamp;

EVENT_TIMESTAMP                DBUSERNAME ACTION_NAME          OBJECT_SCH OBJECT_NAME          SQL_TEXT
------------------------------ ---------- -------------------- ---------- -------------------- ----------------------------------------
14-JUN-23 19.31.17.231940 PM   TESTUSER1  UPDATE               TESTUSER1  AUDIT_TEST_TAB       update audit_test_tab
                                                                                               set    col1 = 'apple1'
                                                                                               where  col1 = 'apple'

14-JUN-23 19.31.17.248862 PM   TESTUSER1  UPDATE               TESTUSER1  AUDIT_TEST_TAB       update audit_test_tab
                                                                                               set    col2 = 'banana1'
                                                                                               where  col2 = 'banana'

14-JUN-23 19.31.17.252646 PM   TESTUSER1  SELECT               TESTUSER1  AUDIT_TEST_TAB       select col2 from audit_test_tab

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!