Object Tables Wrap-Up-Database Tables

Object tables are used to implement an object-relational model in Oracle. A single object table will create many physical database objects typically and add additional columns to your schema to manage everything. There is some amount of magic associated with object tables. Object views allow you to take advantage of the syntax and semantics of objects, while at the same time retaining complete control over the physical storage of the data and allowing for relational access to the underlying data. In that fashion, you can achieve the best of both the relational and object-relational worlds.

Blockchain Tables

Oracle contains several security features to keep your data safe and secure. You’re probably already familiar with most of these features such as passwords, database roles, privileges, encryption functions, and transparent data encryption. However, none of these features will prevent a person who has access to a table from making harmful data changes.

For example, say a disgruntled manager who has legitimate access to the PAYROLL table decides to update the table with unauthorized raises. Or a rogue DBA who has SYS privileges and can modify any table in the database decides to truncate the LEDGER table. Another source bad actors are criminals who have obtained access to the database through a phishing attack and now have access to a company’s financial data. These are just a few examples of how people with access to the database can modify it in a way that is harmful to the company.

Oracle blockchain technology detects and prevents illicit inserts, updates, and deletes to table data. A blockchain table is an immutable table that can only be inserted into. Once inserted, the data cannot be modified. Deletes are only allowed per the retention policy on inserted data. The idea is that you create an initial row of data, and then if you need to make changes to the data in that row, you insert a new row which is chained to the original row.

This way you have a history of how the data changed. What type of application would use a blockchain table? This technology is applicable whenyou need to maintain a tamper-resistant ledger of the current record of data along with an immutable history of transactions for that record.

Blockchain technology is transparently built into the Oracle database (as of release 19.10). It is available with all editions of Oracle. No extra license or option is required to create and use blockchain tables. In regard to selecting and viewing data, blockchain tables look like normal tables to the application. However, blockchain tables are different from normal tables in that they are immutable, insert-only tables. There are restrictions on deleting and updating data after it has been inserted into the blockchain table.

You specify the behavior of when data can be removed via the CREATE BLOCKCHAIN TABLE clauses. When creating a blockchain table, you must specify three clauses:
•\ Blockchain drop table clause
•\ Blockchain row retention clause
•\ Blockchain hash and data format clause

The prior clauses control how the data is protected after it has been inserted. Each of these clauses is detailed in the following subsections.
Blockchain Drop Table Clause

The blockchain drop table clause determines when the table can be dropped. The syntax for the drop table clause is as follows:
NO DROP [ UNTIL NUMBER DAYS IDLE ]

You can specify this clause in two ways:

•\ NO DROP without any other specification means that the blockchain table cannot ever be dropped. The only way to drop a blockchain table specified in this manner is to drop the schema that owns the table.

•\ NO DROP UNTIL NUMBER DAYS IDLE means that the blockchain table cannot be dropped until the latest inserted row is older than the UMBER DAYS IDLE number.

If you specify a NO DROP UNTIL 0 DAYS IDLE, then you can drop the table at any time (I’ll use this in an example coming up). You may want to do this if you’re testing and need to be able to drop the table before making it available for use.