Bug in oracle database- manipulating the last_ddl in oracle database

on

 Was trying to figure out a way to crack the newly introduced blockchain tables. While doing so found some serious issue in oracle database while creating the table. When we create the table the three columns(created,last_ddl, timestamp) in the dba_tables get updated with the current sysdate. 

The problem here is, if you set your fixed_date parameter, the table creation takes the sysdate from what we set in fixed date.


SQL> select sysdate from dual;

SYSDATE
---------
09-FEB-21

SQL>  ALTER SYSTEM SET FIXED_DATE='2021-02-10';

System altered.

SQL> create  table oracledbaarena1 (d date, amt number);

Table created.

SQL> col OWNER for a10
SQL> col OBJECT_NAME for a15
SQL> select OWNER,OBJECT_NAME,CREATED,LAST_DDL_TIME from dba_objects where object_name like 'ORACLEDBAARENA1';

OWNER      OBJECT_NAME     CREATED   LAST_DDL_
---------- --------------- --------- ---------
SYS        ORACLEDBAARENA1 10-FEB-21 10-FEB-21

SQL> SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) from ORACLEDBAARENA1;
SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) from ORACLEDBAARENA1
       *
ERROR at line 1:
ORA-01405: fetched column value is NULL

SQL> insert into oracledbaarena1 values (sysdate,1);

1 row created.

SQL> SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) from ORACLEDBAARENA1;

SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN))
---------------------------------------------------------------------------
09-FEB-21 06.28.04.000000000 PM

SQL>
SQL> !date
Tue Feb  9 18:30:51 UTC 2021

From the above you can see that, even though today's date is Feb 9 but since I have set my FIXED_DATE to FEB 10 the table creation date set to Feb 10. So this makes the insert statement in a table older than the table itself.
Anyone can use this to modify the oracle database objects by modifying the last_ddl date.
Hopefully, Oracle fixes this issue.

Comments

Post a Comment