Site icon IT Tutorial

Data Redaction ( dbms_redact ) in Oracle Database -2

Mehmet Salih Deveci

Hi,

I will continue to explain Data Redaction in Oracle Database in this article.

 

If you don’t know what is the Data Redaction, you should read following article.

https://ittutorial.org/data-redaction-in-oracle-database-1/

 

 

Unauthorized users see the same data differently and Redacted, therefore Oracle Data Redaction is a very powerful feature. Because companies have a lot of problems due to security weaknesses nowadays.

 

For example; Call centers call you and ask your mother maiden name’s only some characters for Security. Because Call Center Staff shouldn’t also see private data and they should also see as Redacted like * and x character

 

 

 

Let’s continue to make new examples to understand this subject very well.

Table name and its column definition are like following.

 

Kart_bilgileri–> Card info table

Musteri_id —> Customer id

kart_no —> Card no

kart_string —> card no string type

bitiş_tarihi —> Expiry date

guvenlik_no —> Security Code or no

 

 

CREATE TABLE mehmet.kart_bilgileri (
id NUMBER NOT NULL,
musteri_id NUMBER NOT NULL,
kart_no NUMBER NOT NULL,
kart_string VARCHAR2(19) NOT NULL,
bitis_tarihi DATE NOT NULL,
guvenlik_no NUMBER NOT NULL,
gecerlilik_tarihi DATE,
CONSTRAINT mehmet.kart_bilgileri_pk PRIMARY KEY (id)
);

INSERT INTO mehmet.kart_bilgileri VALUES (1, 1000, 2013201320132013, '2013-2013-2013-2013', TRUNC(ADD_MONTHS(SYSDATE,12)), 123, NULL);
INSERT INTO mehmet.kart_bilgileri VALUES (2, 1001, 1989198919891989, '1989-1989-1989-1989', TRUNC(ADD_MONTHS(SYSDATE,12)), 234, NULL);
INSERT INTO mehmet.kart_bilgileri VALUES (3, 1002, 2018201820182018, '2018-2018-2018-2018', TRUNC(ADD_MONTHS(SYSDATE,12)), 345, NULL);
INSERT INTO mehmet.kart_bilgileri VALUES (4, 1003, 1453145314531453, '1453-1453-1453-1453', TRUNC(ADD_MONTHS(SYSDATE,12)), 456, NULL);
INSERT INTO mehmet.kart_bilgileri VALUES (5, 1004, 5710571057105710, '5710-5710-5710-5710', TRUNC(ADD_MONTHS(SYSDATE,12)), 567, NULL);
COMMIT;

When query the related table, result will be like following.

 

Let’s go to perform Full redaction as follows in the Kart_no ( card no ) column.

 

BEGIN
DBMS_REDACT.add_policy(
object_schema => 'mehmet',
object_name => 'mehmet.kart_bilgileri',
column_name => 'kart_no',
policy_name => 'redact_kart_info',
function_type => DBMS_REDACT.full,
expression => '1=1'
);
END;
/

When query the table with Mehmet user, Data seems properly not different or Redacted.

 

But When query the table with Salih user, Data seems  different or Redacted like following.

 

 

Then let’s go to perform Partial redaction as follows in the Kart_no ( card no ) column.

 

BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'mehmet',
object_name => 'mehmet.kart_bilgileri',
policy_name => 'redact_kart_info',
action => DBMS_REDACT.modify_column,
column_name => 'kart_no',
function_type => DBMS_REDACT.partial,
function_parameters => '1,1,12'
);
END;
/

When query the table with Mehmet user, Data seems properly not different or Redacted.

 

 

 

 

But When query the table with Salih user, Data seems  different or Redacted like following.

 

We can perform Partial redaction as follows in the Kart_string ( card no string type ) column with # character like following.

 

BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'mehmet',
object_name => 'mehmet.kart_bilgileri',
policy_name => 'redact_kart_info',
action => DBMS_REDACT.add_column,
column_name => 'kart_string',
function_type => DBMS_REDACT.partial,
function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,#,1,12'
);
END;
/

 

 

When query the table with Mehmet user, Data seems properly not different or Redacted.

 

 

But When query the table with Salih user, Data seems  different or Redacted like following.

 

 

or If you want to see Random redaction example, you can do it with following code to change guvenlik_no ( security code ) column

BEGIN
DBMS_REDACT.alter_policy (
object_schema => 'mehmet',
object_name => 'kart_bilgileri',
policy_name => 'redact_card_info',
action => DBMS_REDACT.add_column,
column_name => 'guvenlik_no',
function_type => DBMS_REDACT.RANDOM
);
END;
/

 

 

When query the table with Mehmet user, Data seems properly not different or Redacted.

 

But When query the table with Salih user, Data seems  different or Redacted like following.

 

 

Don’t forget that in order to use this feature, your license must be Enterprise Edition and you must also have an Advanced Security Option license.

 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

Exit mobile version