ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Immutable tables are intended to provide an extremely tamper resistant means of storing relational data in a form wherein it can be accessed using SQL.
Immutable tables intended for use in an environment where it is required that an audit trail could potentially be tampered with my insertion but that once a record was inserted it
would not be possible to alter or delete it except within the date constraints imposed as part of the NO DROP and NO DELETE clauses.
Data Dictionary Objects
ALL_SEGMENTS
PROCEDUREBLOCKCHAININFO$
DBA_TABLES
ALL_TAB_COLS
CDB_SEGMENTS
DBMS_BLOCKCHAIN_TABLE_LIB
ALL_TABLES
CDB_TAB_COLS
USER_SEGMENTS
BLOCKCHAIN_TABLE$
CDB_TABLES
USER_TAB_COLS
BLOCKCHAIN_TABLE_CHAIN$
DBA_SEGMENTS
USER_TABLES
BLOCKCHAIN_TABLE_EPOCH$
DBA_TAB_COLS
Exceptions
Error Code
Reason
ORA-05715
operation not allowed on the blockchain or immutable table
ORA-05723
drop blockchain or immutable table <table_name> not allowed
ORA-05729
blockchain or immutable table cannot be created in root container
First Available
21c, back-ported to 19.11
System Privileges
ALTER ANY TABLE
CREATE ANY TABLE
DROP ANY TABLE
ALTER TABLE
CREATE TABLE
DROP TABLE
General (new 21c)
Block Change table
CREATE IMMUTABLE TABLE <schema_name>.<table_name>(
<column_name> <column_data_type>)
NO DROP [UNTIL <integer> DAYS IDLE]
[sharing_clause]
[memoptimize_clause]
[relational_properties];
CREATE IMMUTABLE TABLE uwclass.audit1(
tx_id INTEGER,
tx_date DATE,
tx_value NUMBER(10,2))
NO DROP UNTIL 31 DAYS IDLE
NO DELETE UNTIL 31 DAYS AFTER INSERT
TABLESPACE uwdata;
Table created.
Block Change table with LOCKED
CREATE IMMUTABLE TABLE <schema_name>.<table_name>(
<column_name> <column_data_type>)
NO DROP [UNTIL <integer> DAYS IDLE]
NO DELETE UNTIL <integer> DAYS AFTER INSERT [LOCKED]
[sharing_clause]
[memoptimize_clause]
[relational_properties];
CREATE IMMUTABLE TABLE uwclass.audit2(
tx_id INTEGER,
tx_date DATE,
tx_value NUMBER(10,2))
NO DROP UNTIL 31 DAYS IDLE
NO DELETE UNTIL 31 DAYS AFTER INSERT LOCKED
TABLESPACE uwdata;
Table created.
Immutable table with range partitioning
This was enabled in 20c but is not available in 21.1.
CREATE IMMUTABLE TABLE <schema_name>.<table_name>(
<column_name> <column_data_type>)
NO DROP UNTIL
NO DELETE LOCKED
PARTITION BY ....;
CREATE IMMUTABLE TABLE
uwclass.audit_partitioned(
tx_id NUMBER,
tx_date DATE,
grantor VARCHAR2(60),
grantee VARCHAR2(60))
NO DROP UNTIL 60 DAYS IDLE
NO DELETE UNTIL 31 DAYS AFTER INSERT
PARTITION BY RANGE(trans_date)
(
PARTITION p1 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')),
PARTITION p2 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')),
PARTITION p3 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')),
PARTITION p4 VALUES LESS THAN (TO_DATE('01-01-2021','dd-mm-yyyy')));
Table created.
Immutable Table Reverse Engineered
-- reformatted for legibility
SELECT dbms_metadata.get_ddl('TABLE', 'AUDIT2','UWCLASS') FROM dual;
-- two very important things to consider with
respect to the above metadata
-- first, omits the fact that it is an immutable table ... compare with
the
-- blockchain table which explicitly identifies itself.
-- the second difference is the lack of an appended anonymous PL/SQL block
-- perhaps this is an omission and perhaps there isn't any PL/SQL code
-- our suspicion is that it is an omission and dbms_metatadata needs a
fix.
SELECT * FROM blockchain_table_chain$;
no rows selected
SELECT obj#, epoch#, pdb_guid, owner, object_name
FROM sys.blockchain_table_epoch$ bte, sys.dba_objects do
WHERE bte.obj# = do.object_id;
CREATE IMMUTABLE TABLE audit0 (
tx_id INTEGER,
tx_date DATE,
tx_value NUMBER(10,2))
NO DROP UNTIL 31 DAYS IDLE
NO DELETE LOCKED;
CREATE IMMUTABLE TABLE audit0 (
*
ERROR at line 1:
ORA-05729: blockchain or immutable table cannot be created in root container
ALTER SESSION SET CONTAINER=PDBDEV;
CREATE IMMUTABLE TABLE uwclass.audit0(
tx_id INTEGER,
tx_date DATE,
tx_value NUMBER(10,2))
NO DROP UNTIL 31 DAYS IDLE
NO DELETE LOCKED;
Table created.
SQL> desc uwclass.audit0 Name Null? Type
----------------------------- -------- --------------------
TX_ID NUMBER(38)
TX_DATE DATE
TX_VALUE NUMBER(10,2)
SELECT column_id, column_name, data_type, hidden_column, virtual_column, user_generated
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'AUDIT0'
ORDER BY user_generated, column_id;
COLUMN_ID COLUMN_NAME DATA_TYPE HID VIR USE
---------- ------------------------- ---------------------------- --- --- ---
ORABCTAB_CREATION_TIME$ TIMESTAMP(6) WITH TIME ZONE YES NO NO
ORABCTAB_SIGNATURE_CERT$ RAW YES NO NO
ORABCTAB_SIGNATURE_ALG$ NUMBER YES NO NO
ORABCTAB_INST_ID$ NUMBER YES NO NO
ORABCTAB_CHAIN_ID$ NUMBER YES NO NO
ORABCTAB_SEQ_NUM$ NUMBER YES NO NO
ORABCTAB_SPARE$ RAW YES NO NO
ORABCTAB_USER_NUMBER$ NUMBER YES NO NO
ORABCTAB_HASH$ RAW YES NO NO
ORABCTAB_SIGNATURE$ RAW YES NO NO 1 TX_ID NUMBER NO NO YES 2 TX_DATE DATE NO NO YES 3 TX_VALUE NUMBER NO NO YES
INSERT INTO uwclass.audit0
(tx_id, tx_date, tx_value)
VALUES
(1, SYSDATE, 100);
UPDATE audit0 SET tx_value = 200;
UPDATE audit0 SET tx_value = 200
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table
DELETE FROM audit0;
DELETE FROM audit0
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table
TRUNCATE TABLE audit0;
TRUNCATE TABLE audit0
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table
DROP TABLE audit0;
DROP TABLE audit0;
*
ERROR at line 1:
ORA-05723: drop blockchain or immutable table AUDIT0 not allowed
Immutable Table Queries
TAB$ Query
SELECT so.name, st.spare7
FROM sys.obj$ so, sys.tab$ st
WHERE so.obj# = st.obj#
AND ctime > sysdate-1/48
