dba_fga_audit_trail contains columns not audited (Doc ID 2960903.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

On : 19.3.0.0.0 version, Linux x86

The problem only occurs on the customer environment. example:

-- Creata test table

Create table test_tbl (NAME varchar2(30),COM varchar2(50) not null, TYPE varchar2(50),ID number);

insert into test_tbl values ('aaaa','aaaaaa','aaaaaaaaa',1);
insert into test_tbl values ('bbbb','bbbbbb','bbbbbbbbb',2);
insert into test_tbl values ('cccc','cccccc','ccccccccc',3);
insert into test_tbl values ('dddd','dddddd','ddddddddd',4);
commit;

-- Create FGA policy

SQL> BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'SCOTT',
object_name => 'TEST_TBL',
policy_name => 'TEST_TBL_DML',
audit_column => 'NAME,COM',
enable => TRUE,
statement_types => 'UPDATE');
END;
/

-- check audit column

select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, POLICY_COLUMN
from DBA_AUDIT_POLICY_COLUMNS
order by OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, POLICY_COLUMN;

OBJECT_SCH OBJECT_NAME POLICY_NAME POLICY_COLUMN
---------- ---------------------------------- ------------------------------ ------------------------------
SCOTT TEST_TBL TEST_TBL_DML COM
SCOTT TEST_TBL TEST_TBL_DML NAME

-- Run update

update test_tbl set NAME='aaaabb' where NAME='aaaa';
update test_tbl set TYPE='null' where ID=1;

-- check record

select OBJECT_NAME,STATEMENT_TYPE,EXTENDED_TIMESTAMP,SQL_TEXT from dba_fga_audit_trail;

OBJECT_NAME STATEMENT_TYPE EXTENDED_TIMESTAMP SQL_TEXT
-------------------- -------------------- -------------------------------------- ----------
TEST_TBL UPDATE 08-JUN-23 01.44.47.949064 AM +00:00 update
test_tbl
2 set NAME
='aaaabb'
where NAME
='aaaa'

TEST_TBL UPDATE 08-JUN-23 01.45.09.833711 AM +00:00 update <--- should not be audited
test_tbl
2 set TYPE=
'null' where
ID=1

Changes

 None

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.