Our applications works with ADF as UI and Oracle Database 12c in the Backend.
As a regulation requirement,we have to mask few columns of the applications.The Entity Objects in the ADF are based on the Views.
We have tried addressing these Columns Masking using DBMS_REDACT Feature and while doing it, we face the below issues.
1. Have created the Policies on the Table on the Respective Columns.On the UI, the data was masked and shown properly.Upon editing and modifying any redacted/non-redacted column,during save, we receive "ORA-28081: Insufficient privileges - the command references a redacted object"
2. Have dropped the policies from the table and created the Policies on the View on the Respective Columns.Upon editing and modifying any redacted/non-redacted column,during save, we are able to save the values.
Now, its observed that, when the policy is created on the table,the view cannot update any of the columns of the underlying table which is redacted.
Do we have any workarounds to get out of this situation or Is there a way I can achieve what I wanted to. My requirement is, while the Policy would still be present in the table, I want to perform DML Operations on the redacted table through the view.
Below is what I do.
1. I create a policy on a table with the partial mask.
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'OFSLLPOC',
object_name => 'LOOKUPS',
column_name => 'LKC_DESC',
policy_name => 'redact_lkc_pm',
function_type => DBMS_REDACT.PARTIAL,
function_parameters=> 'VVVVVVVVVVVVVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVVVVVVVVVVVVV,X,1,15',
expression => '1=1',
enable=>true
);
END;
2. I have a view on the table on which Policy exists.
CREATE OR REPLACE VIEW UIX_LKC_FVW AS SELECT * FROM LOOKUPS;
3. Update uix_lkc_fvw set LKC_DESC='ABC' WHERE ROWNUM<2;
I get ORA-28081: Insufficient privileges - the command references a redacted object.
4. I drop the policy created in Step 1.
BEGIN
DBMS_REDACT.DROP_POLICY (object_schema=>'OFSLLPOC'
,object_name=>'LOOKUPS'
,policy_name=>'redact_lkc_pm');
END;
5. I create the policy on the view created in Step 2 with the partial Mask.
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'OFSLLPOC',
object_name => 'UIX_LKC_FVW',
column_name => 'LKC_DESC',
policy_name => 'redact_lkc_pm',
function_type => DBMS_REDACT.PARTIAL,
function_parameters=> 'VVVVVVVVVVVVVVVVVVVVVVVVVVVVVV,VVVVVVVVVVVVVVVVVVVVVVVVVVVVVV,X,1,15',
expression => '1=1',
enable=>true
);
END;
6. I update the underlying table of the view UIX_LKC_FVW.
UPDATE LOOKUPS SET LKC_DESC='ABC' WHERE ROWNUM<2;
1 Row updated.
The question is, while the Policy would still be present only in the table, I want to perform DML Operations on the redacted table through the view created based on the table.
In the example above, I want the policy to remain in LOOKUPS Table while I should be able to do DML Operations of the LOOKUPS table through UIX_LKC_FVW.