My Oracle Support Banner

DELETE Privilege Disappeared After Changed The Tablespace Of FGA_LOG$ by DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION (Doc ID 2547215.1)

Last updated on FEBRUARY 19, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

SQL> create user <user> identified by <password>;

User created.

SQL> grant create session to <user>;

Grant succeeded.

SQL> grant delete on fga_log$ to <user>;

Grant succeeded.

SQL> conn <user>/<password>
Connected.
SQL> delete from sys.fga_log$;

0 rows deleted.

SQL> rollback;

Rollback complete.

SQL> conn / as sysdba
Connected.
SQL> col table_name for a15
col tablespace_name for a15
select table_name,tablespace_name from dba_tables
where table_name in ('AUD$','FGA_LOG$');
SQL> SQL> 2
TABLE_NAME TABLESPACE_NAME
--------------- ---------------
FGA_LOG$ SYSTEM
AUD$ SYSTEM

SQL> BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'SYSAUX');
END;
/
2 3 4 5 6

PL/SQL procedure successfully completed.

SQL>
SQL> conn <user>/<password>
Connected.
SQL> delete from sys.fga_log$;
delete from sys.fga_log$
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn / as sysdba
Connected.
SQL> grant delete on aud$ to <user>;

Grant succeeded.

SQL> conn <user>/<password>
Connected.
SQL> delete from sys.aud$;

1347 rows deleted.

SQL> rollback;

Rollback complete.

SQL> conn / as sysdba
Connected.
SQL> BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'SYSAUX');
END;
/
2 3 4 5 6

PL/SQL procedure successfully completed.

SQL>
SQL> select table_name,tablespace_name from dba_tables
where table_name in ('AUD$','FGA_LOG$'); 2

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
FGA_LOG$ SYSAUX
AUD$ SYSAUX

SQL> conn <user>/<password>
Connected.
SQL> delete from sys.aud$;

1349 rows deleted.

SQL> rollback;

Rollback complete.

 

Changes

 Executed DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION to change the tablespace of FGA_LOG$.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


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