Oracle Data Redaction and JSON

You can use JavaScript Object Notation (JSON) on redacted data.

You can create is json constraints on table columns.

However, you cannot create an Oracle Data Redaction policy on a table column that has the is json constraint. If you attempt to do so, an ORA-28073 - The column column_name has an unsupported datatype error is raised. As a workaround solution, Oracle recommends that you create a relational view that uses the JSON_TABLE row source operator on top of the JSON object, and then apply the Data Redaction policy to this view.

The following example shows how you can use json_table to create a relational view and add a data redaction policy to this view.
```
create table tab1
        (c1 varchar2(4000) check (c1 is json))

insert into tab1 values
('{"id" : 4,
   "name" : "Large blue soda cup",
   "price" : 12,
   "tags" : ["large1", "green"],
   "phone" : "1-415-555-0100"}'
)

create or replace view json_view1 as
 select t.* from tab1,
  json_table(c1, '$' columns
       id number path '$.id',
       name varchar2(20) path '$.name',

       price number path '$.price',
       phone varchar2(20) path '$.phone') t ;
```
If the owner of the view queries column phone, then it is not redacted. Now, the redaction policy can be added on any of the columns in the view (such as phone, price, and so on).
You can create JSON duality views on top of redacted data.

However, there is a restriction to this ability: columns enabled for redaction cannot be primary keys and cannot be part of the Duality View Entity Tag (ETAG).

Related Topics