How to mask data in HRMS or Any Other Data?

What is Oracle Data Redaction with Examples (Mask your sensitive Data)

There are many cases when data needs to be masked to hide some or all of the data for users working with data.

Oracle Data Redaction is one of the new features introduced in Oracle Database Oracle 12c and back-ported to 11.2.0.4 via patch set(Some examples of implementing Data Redaction using DBMS_REDACT (Doc ID 1588270.1)). This new feature is part of the Advanced Security option and enables the protection of data shown to the user in real-time, without requiring changes to the application.Oracle Data Redaction enables you to mask (redact) data that is returned from queries issued by applications.

Data masking (also known as data scrambling and data anonymization) is the process of replacing sensitive information copied from production databases to test non-production databases with realistic, but scrubbed, data based on masking rules. Data masking is ideal for virtually any situation when confidential or regulated data needs to be shared with non-production users. 

This can be done using Data Redaction in Oracle.Let's see we will look at how these can be used to hide data or mask data.Oracle allow you to mask sensitive data and display only a portion or completely randomize the result. In Oracle, Data Redaction allows you to control every aspect of query execution, so it can be based on user, role, weekday, time, IP, etc. 

Oracle Data Redaction provides the ability to masking data, typically sensitive data in real-time like credit card, Aadhar card details etc. before displaying with application. For example, in a call center, the agent able to view sensitive data in application because of the data they getting from PROD. Those Exposition may lead to privacy regulation and the corresponding user into risk.

Oracle Advanced Security Data Redaction is on-the-fly redaction, i.e., masking of sensitive data in query results prior to display by applications, as shown in the following illustration. It enables consistent redaction of database columns across application modules accessing the same data.

You can redact column data by using one of the following methods:

Full redaction. You redact all of the contents of the column data. The redacted value returned to the querying application user depends on the data type of the column. For example, columns of the NUMBER data type are redacted with a zero (0), and character data types are redacted with a single space.

Partial redaction. You redact a portion of the column data. For example, you can redact a Social Security number with asterisks (*), except for the last 4 digits.

Regular expressions. You can use regular expressions to look for patterns of data to redact. For example, you can use regular expressions to redact email addresses, which can have varying character lengths. It is designed for use with character data only.

Random redaction. The redacted data presented to the querying application user appears as randomly generated values each time it is displayed, depending on the data type of the column.

No redaction. The None redaction type option enables you to test the internal operation of your redaction policies, with no effect on the results of queries against tables with policies defined on them. You can use this option to test the redaction policy definitions before applying them to a production environment.

Oracle Database applies the redaction at runtime when users access the data (that is, at query-execution time). This solution works well in a production system. During the time that the data is being redacted, all of the data processing is performed normally, and the back-end referential integrity constraints are preserved.

Masking Data in Oracle with Data Redaction:

To mask data, use the function below to create a data redaction policy.

DBMS_REDACT.ADD_POLICY ( 

 object_schema               IN VARCHAR2 := NULL, 

 object_name                  IN VARCHAR2 := NULL, 

 policy_name                  IN VARCHAR2, 

 policy_description         IN VARCHAR2 := NULL, 

 column_name                IN VARCHAR2 := NULL, 

 column_description       IN VARCHAR2 := NULL, 

 function_type                 IN BINARY_INTEGER := DBMS_REDACT.FULL, 

 function_parameters      IN VARCHAR2 := NULL, 

 expression                      IN VARCHAR2, 

 enable                             IN BOOLEAN := TRUE, 

 regexp_pattern                IN VARCHAR2 := NULL, 

 regexp_replace_string    IN VARCHAR2 := NULL, 

 regexp_position              IN BINARY_INTEGER :=1, 

 regexp_occurrence         IN BINARY_INTEGER :=0, 

 regexp_match_parameter      IN VARCHAR2 := NULL); 

Important Procedures related to DBMS_REDACT

Procedure	Description
DBMS_REDACT.ADD_POLICY	Adds a Data Redaction policy to a table or view
DBMS_REDACT.ALTER_POLICY	Modifies a Data Redaction policy
DBMS_REDACT.APPLY_POLICY_EXPR_TO_COL	Applies a Data Redaction policy expression to a table or view column
DBMS_REDACT.CREATE_POLICY_EXPRESSION	Creates a Data Redaction policy expression
DBMS_REDACT.DISABLE_POLICY	Disables a Data Redaction policy
DBMS_REDACT.DROP_POLICY	Drops a Data Redaction policy
DBMS_REDACT.DROP_POLICY_EXPRESSION	Drops a Data Redaction policy expression
DBMS_REDACT.ENABLE_POLICY	Enables a Data Redaction policy
DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES	Globally updates the full redaction value for a given data type. You must restart the database instance before the updated values can be used.
DBMS_REDACT.UPDATE_POLICY_EXPRESSION	Updates a Data Redaction policy expression

It supports the following column data types:

NUMBER, BINARY_FLOAT, BINARY_DOUBLE, CHAR, VARCHAR2, NCHAR, NCLOB, NVARCHAR2, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, BLOB and CLOB.

How to check Redaction policies OR View Existing Data Redaction Policies:

To view existing policies, you can use the code below:

select * from redaction_policies;

SELECT * FROM REDACTION_COLUMNS;

We need to make sure the respective user like apps (in my case redact user) has access to the DBMS_REDACT package.

GRANT EXECUTE ON sys.dbms_redact TO apps/redact;

Example: Let's create a user

$sqlplus / as sysdba

SQL>CREATE USER REDACT IDENTIFIED BY MyPwd;

SQL>GRANT CREATE SESSION TO REDACT;

SQL>ALTER USER REDACT quota unlimited on SYSTEM;

SQL>GRANT CREATE ANY TABLE TO REDACT;

SQL>grant execute on sys.dbms_redact to redact;

SQL>conn redact/MyPwd

Let's create a table

SQL>CREATE TABLE REDACTT

(cust_id     NUMBER NOT NULL

,name        varchar2(20)

,credit_card NUMBER(16)

,exp_date    DATE

,credit_limit NUMBER(6,2)

,email_adr    varchar2(20)

);

 

SQL>INSERT INTO REDACTT

VALUES (8714,'John Smith'    ,6283723982745971,'01-FEB-2017',49.50,'john.smith@gmail.com');

SQL>INSERT INTO REDACTT

VALUES (8743,'Paul McCartney',8995984092832971,'01-APR-2017',150.00,'paul@oracle.com');

SQL>INSERT INTO REDACTT

VALUES (8345,'George Lennon' ,9345945634845567,'01-FEB-2017',19.99,'glennon@qualogy.com');

SQL>INSERT INTO REDACTT

VALUES (9019,'Ricky Star'    ,2383834982340341,'01-JUN-2017',100.00,'star@aol.com');

SQL>set pagesize 200;

SQL>set linesize 300;

SQL>COLUMN credit_card format 0000000000000000

SQL>COLUMN EMAILADR    format a20

SQL>select * from redact.redactt;

 

CUST_ID  NAME      CREDIT_CARD   EXP_DATE  CREDIT_LIMIT  EMAIL_ADR

----------  --------------     -----------------             -----------       ---------------  --------------------

8714     John Smith    6283723982745971   01-FEB-2017    49.5   john.smith@gmail.com

8743     Paul McCar   8995984092832971   01-APR-2017   150     paul@oracle.com

8345     George Le     9345945634845567   01-FEB-2017    19.99  glennon@qualogy.com

9019     Ricky Star     2383834982340341   01-JUN-2017    100     star@aol.com

FULL REDACTION: Add a new policy

SQL>begin

DBMS_REDACT.ADD_POLICY ( 

 object_schema              =>'REDACT', 

 object_name                 =>'REDACTT', 

 policy_name                 =>'REDACT_CARD_POLICY',

 column_name               =>'CUST_ID', 

 function_type               =>DBMS_REDACT.FULL, 

 expression                    =>'1=1'); 

end;

/

Note: Expression of "1=1" means the redaction will always take place.

Check again data mask or not

select * from redact.redactt;

Alter an Existing Policy: Full unmask the data

We can also amend the policy so it does not affect the schema owner. The following example uses the SYS_CONTEXT function in the EXPRESSION parameter to determine the current user, making the application of the redaction policy conditional.

SQL>begin

DBMS_REDACT.ALTER_POLICY ( 

 object_schema              =>'REDACT', 

 object_name                 =>'REDACTT', 

 policy_name                 =>'REDACT_CARD_POLICY',

 column_name               =>'CUST_ID',

 action                            =>DBMS_REDACT.modify_expression,

 expression                     =>'sys_context(''userenv'',''session_user'') != ''REDACT''');

end;

/

Check data unmask or not---

SQL>select * from redact.redactt;

Drop a Data Redaction Policy:

To drop the policy, you can use the code below:

SQL>begin

DBMS_REDACT.DROP_POLICY ( 

 object_schema               =>'REDACT', 

 object_name                  =>'REDACTT', 

 policy_name                  =>'REDACT_CARD_POLICY');

end;

/

PARTIAL REDACTION: Partial Redaction on number values 

In Partial Redaction, the first 12 digits of the credit card numbers are masked with 0. We can mention the function_parameters to achieve this results.

Example 1:

BEGIN

 DBMS_REDACT.ADD_POLICY (

   object_schema       => 'REDACT',

   object_name          => 'REDACTT',

   column_name        => 'CREDIT_CARD',

   policy_name          => 'REDACT_CARD_POLICY',

   function_type        => DBMS_REDACT.PARTIAL,

   function_parameters => '0,1,12',

   expression             => '1=1'

 );

END;

/

Example 2:

Partial redaction can also be done on a date. Here we keep the year, but hide the rest of the date:

BEGIN

 DBMS_REDACT.ADD_POLICY (

   object_schema             => 'REDACT',

   object_name                => 'REDACTT',

   column_name              => 'EXP_DATE',

   policy_name                => 'REDACT_CARD_POLICY',

   function_type               => DBMS_REDACT.PARTIAL,

   function_parameters    => 'm1d1Y',

   expression                    => '1=1' );

END;

/

RANDOM REDACTION:

In Random Redaction the card numbers are transformed to entirely different card numbers as random number.

BEGIN

 DBMS_REDACT.ADD_POLICY (

   object_schema       => 'REDACT',

   object_name          => 'REDACTT',

   column_name        => 'CREDIT_CARD',

   policy_name          => 'REDACT_CARD_POLICY',

   function_type        => DBMS_REDACT.RANDOM,

   expression             => '1=1'

 );

END;

/

OR 

BEGIN

   DBMS_REDACT.ALTER_POLICY(

     object_schema       => 'REDACT',

     object_name          => 'REDACTT',

     column_name        => 'CREDIT_CARD',

     policy_name          => 'REDACT_CARD_POLICY',

     action                     => DBMS_REDACT.modify_column,

     function_type        => DBMS_REDACT.random);

END;

/

REGULAR EXPRESSION:

In this example the customer email address is getting transformed to xxxxxx@domain.com using the regular expression redaction.

BEGIN

    DBMS_REDACT.ADD_POLICY (

           object_schema         => 'REDACT',

           object_name            => 'REDACTT',

           policy_name            => 'REDACT_CARD_POLICY',

           column_name          => 'EMAIL_ADR',

           function_type          => DBMS_REDACT.REGEXP,

           expression               => '1=1',

           enable                      => TRUE,

           regexp_pattern        => '(.){2,}\@([[:alnum:]])',

           regexp_replace_string  => 'xxxxxxx@\2',

           regexp_position            => '1',

           regexp_occurrence       => '0',

           regexp_match_parameter  => 'i'

   );

END;

/

OR

BEGIN

 DBMS_REDACT.ADD_POLICY (

   object_schema           => 'REDACT',

   object_name              => 'REDACTT',

   column_name            => 'EMAIL_ADR',

   policy_name              => 'REDACT_CARD_POLICY',

   function_type            => DBMS_REDACT.REGEXP,

   regexp_pattern          => DBMS_REDACT.RE_PATTERN_EMAIL_ADDRESS,

   regexp_replace_string   => DBMS_REDACT.RE_REDACT_EMAIL_NAME,

   regexp_position             => DBMS_REDACT.RE_BEGINNING,

   regexp_occurrence         => DBMS_REDACT.RE_ALL,

   expression                      => '1=1'

 );

END;

/

Exceptions:

If you have the EXEMPT REDACTION POLICY system privilege, data is never redacted.Redaction is also never used in the following situations:

connections as SYS

database replication

backup and restore

export and import

SYS and DBA privileged schema over DBMS_REDACT

The sys and dba privileged schemas can view the details of columns masked using DBMS_REDACT.

$sqlplus / as sysdba

SQL>set pagesize 200;

SQL>set linesize 300;

SQL>select * from redact.redactt;

Create an APPS READONLY user:

There are 2 ways to achieve this:

1) Create a user and grant select option to that user.
This will have to be done individually for all users that you want to have as read only.

2) Create a readonly role and assign users this role.
This will have to be done once and then assign this role to the users.

1. Create a user and grant select option to that user.

Step 1: Connect as sysdba and create the database user to be used for apps read only schema.

$sqlplus / as sysdba
SQL> create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;
User created.
SQL> grant connect to appsro;
Grant succeeded.
SQL> grant select any table to appsro;
Grant succeeded.

Step 2: Connect with the appsro user and run the SQL commands:
SQL> conn appsro
Enter password:
Connected.
SQL> show user
USER is "APPSRO"

Step 3: Try to access an apps schema table
SQL> select count(*) from apps.po_headers_all;
COUNT(*)
----------
132206

Step 4: Try to update an apps schema table
SQL> update apps.po_headers_all set attribute1 = '1' where po_header_id = 126;
update apps.po_headers_all set attribute1 = '1' where po_header_id = 126
*
ERROR at line 1:
ORA-01031: insufficient privileges

2. Create a read only role and assign to users this role.
Step 1: Connect as sysdba and create the database user to be used for apps read only schema.

SQL> create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;
User created.
SQL> grant connect to appsro;
Grant succeeded.
SQL> create role role_readonly;
Role created.
SQL> grant select any table to role_readonly;
Grant succeeded.
SQL> grant role_readonly to appsro;
Grant succeeded.

Step 2: Connect with the appsro user and run the SQL commands:
SQL> conn appsro
Enter password:
Connected.
SQL> show user
USER is "APPSRO"

Step 3: Try to access an apps schema table
SQL> select count(*) from apps.po_headers_all;
COUNT(*)
----------
132206

Step 4: Try to update an apps schema table
SQL> update apps.po_headers_all set attribute1 = '1' where po_header_id = 126;
update apps.po_headers_all set attribute1 = '1' where po_header_id = 126
*
ERROR at line 1:
ORA-01031: insufficient privileges

Example: Grant another user the 'read only' role

SQL> create user apps_readonly identified by apps_readonly;
User created.
SQL> grant connect to apps_readonly;
Grant succeeded.
SQL> grant role_readonly to apps_readonly;
Grant succeeded.
SQL> conn apps_readonly
Enter password:
Connected.
SQL> show user
USER is "APPS_READONLY"
SQL> select count(*) from apps.fnd_concurrent_requests;
COUNT(*)
----------
75724
SQL> select count(*) from apps.po_headers_all;
COUNT(*)
----------
132206
SQL> update apps.po_headers_all set attribute1 = '1' where po_header_id = 126;
update apps.po_headers_all set attribute1 = '1' where po_header_id = 126
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> exit;

You are done. Now your users can use “appsro” schema to have the read only access to Apps Data.

Prevent users from login into a database within defined period.

Description:

It shows how to set up the database security so that users cannot log into the database outside defined time-window. Here we are using Event Triggers.

Steps to be followed :

Step 1 : Create a user (or existing user) and check, whether is able to connect.

SQL> connect username/*****
Connected.

Now we able to connect, afterthat login as sys user and create a trigger .

Step 2 : Create an event trigger that allow the user to login the database only on Monday to Friday included 9 AM to 6 PM.

Except DBA Privilege user, remaining users connection occurs only within the time frame.

SQL> create or replace trigger prevent_logon_database_trigger after logon on database
begin
if (to_char(sysdate,'D') not between '2' and '6') ------(2 for monday and 6 for friday)
or (to_char(sysdate, 'HH24')  between '09' and '18') 

-----or (to_char(sysdate, 'HH24') not between '09' and '18') 

then RAISE_APPLICATION_ERROR(-20001, 'Please login on your given date');
end if;
end;
/

Trigger created.

OR

SQL>create or replace trigger prevent_logon_database_trigger after logon on database 
declare
current_hour pls_integer;
restricted_user boolean;
restricted_time boolean;
begin
restricted_user := user = 'USERNAME';
current_hour    := extract(hour from systimestamp);
restricted_time := (current_hour < 18 or current_hour >= 9);
if restricted_user and restricted_time 

then raise_application_error(-20001, 'You can''t come in!');
end if;
end ;
/

Trigger created.

Step 3 : Now check whether you are able to connect to the database.

Example : It’s Saturday normal user cannot be able to log into the database. We have created trigger for all the users to login into the database on Monday and Friday included 9AM to 6PM. So the error throws .

SQL> connect username/*****

ERROR:

ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not allowed to log into database now.
ORA-06512: at line 3
Warning: You are no longer connected to ORACLE.

Step 4 : Disable trigger.

SQL>Drop trigger prevent_logon_database_trigger;

Trigger dropped.