Column Masking

One of the issues that we are facing is identity theft.
We store lot of sensitive information in the database  and expect ( or pray ) for it to be secure .

The data  can be stored in encrypted tablespace .
TDE works at the tablespace level  , ie , if someone steals the datafile ,
they can't see the data in the raw datafile  if it is encrypted .

But from sqlplus ( any other app )  , the columns will be visible .
Here is one way to mask the sensitive columns .


SQL> show user
USER is "ZAHIR"

SQL> create table employee as select object_id ssn , object_name ename from all_objects ;

Table created.

SQL> desc employee
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SSN                                       NOT NULL NUMBER
 ENAME                                     NOT NULL VARCHAR2(30)

SQL> CREATE OR REPLACE
PACKAGE sec_data_pkg
AS
FUNCTION mask_ssn(
    p_owner IN VARCHAR2,
    p_name  IN VARCHAR2 )
  RETURN VARCHAR2;
END sec_data_pkg;
/ 

Package created.

SQL>
CREATE OR REPLACE
  2  PACKAGE BODY sec_data_pkg
  3  AS
  4  FUNCTION mask_ssn(
  5      p_owner IN VARCHAR2,
  6      p_name  IN VARCHAR2 )
  7    RETURN VARCHAR2
  8  AS
  9  BEGIN
 10    IF sys_context( 'userenv', 'session_user' ) = 'ZAHIR' THEN
 11      RETURN NULL;
 12    ELSE
 13      RETURN '1=0';
 14    END IF;
 15  END;
 16    END sec_data_pkg;
 17
 18  / 

Package body created.

SQL>   BEGIN
  2           DBMS_RLS.ADD_POLICY(object_schema=>user, object_name=>'EMPLOYEE',
  3                               policy_name=>'SECURE_SSN',
  4                               function_schema=>user,
  5                               policy_function=>'sec_data_pkg.mask_ssn',
  6                               sec_relevant_cols=>'ssn',
  7                               sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
  8     END;
  9     /
                            
PL/SQL procedure successfully completed.

SQL>  create  synonym empl for employee;

Synonym created.

SQL> grant select on empl to public;

Grant succeeded.

SQL> show user
USER is "ZAHIR"

SQL>  select * from zahir.empl where rownum <= 10;

       SSN ENAME
---------- ------------------------------
       100 ORA$BASE
       116 DUAL
       117 DUAL
       280 MAP_OBJECT
       365 SYSTEM_PRIVILEGE_MAP
       367 SYSTEM_PRIVILEGE_MAP
       368 TABLE_PRIVILEGE_MAP
       370 TABLE_PRIVILEGE_MAP
       371 STMT_AUDIT_OPTION_MAP
       373 STMT_AUDIT_OPTION_MAP

10 rows selected.


Let us see , if any other signs on to the database and issues the same SELECT .
The values in the column SSN will not be visible , as per our policy (  sec_relevant_cols=>'ssn')


SQL> conn scott/tiger
Connected.

SQL> select * from zahir.empl where rownum <= 10;

       SSN ENAME
---------- ------------------------------
           ORA$BASE
           DUAL
           DUAL
           MAP_OBJECT
           SYSTEM_PRIVILEGE_MAP
           SYSTEM_PRIVILEGE_MAP
           TABLE_PRIVILEGE_MAP
           TABLE_PRIVILEGE_MAP
           STMT_AUDIT_OPTION_MAP
           STMT_AUDIT_OPTION_MAP

10 rows selected.

Please send your comments / suggestions .

Update :

I have updated this post for this question 

Column Masking was interesting, however how do we show only the last 4 digit of SSN if we want to. Can you please brief something on PCI compliance

We would use a view to display  the last 4 digits of SSN.

SQL> show user
USER is "ZAHIR"

SQL> create or replace view emp_v as
select ssn , 'xxx-xxx-'||substr(ssn,-4) formatted_ssn ,
ename from employee ; 

View created.

SQL> select * from emp_v where rownum < 5;

       SSN SSN_FORMATTE ENAME
---------- ------------ ------------------------------
 123456889 xxx-xxx-6889 ORA$BASE
 123456905 xxx-xxx-6905 DUAL
 123456906 xxx-xxx-6906 DUAL
 123457069 xxx-xxx-7069 MAP_OBJECT


SQL> exec DBMS_RLS.DROP_POLICY(object_schema=>user,object_name=>'EMPLOYEE' ,policy_name=>'SECURE_SSN') ;

PL/SQL procedure successfully completed.

SQL>
BEGIN
             DBMS_RLS.ADD_POLICY(object_schema=>user, object_name=>'EMP_V',
                                 policy_name=>'SECURE_SSN',
                                 function_schema=>user,
                                 policy_function=>'sec_data_pkg.mask_ssn',
                                 sec_relevant_cols=>'ssn',
                                 sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
       END;
       /
SQL> grant select on emp_v to scott;

Grant succeeded.


SQL> BEGIN
  2               DBMS_RLS.ADD_POLICY(object_schema=>user, object_name=>'EMP_V',
  3                                   policy_name=>'SECURE_SSN',
  4                                   function_schema=>user,
  5                                   policy_function=>'sec_data_pkg.mask_ssn',
  6                                   sec_relevant_cols=>'ssn',
  7                                   sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
  8         END;
  9         /

PL/SQL procedure successfully completed.

SQL> select * from zahir.emp_v where rownum < 5;

       SSN SSN_FORMATTE ENAME
---------- ------------ ------------------------------
 123456889 xxx-xxx-6889 ORA$BASE
 123456905 xxx-xxx-6905 DUAL
 123456906 xxx-xxx-6906 DUAL
 123457069 xxx-xxx-7069 MAP_OBJECT

SQL> conn scott/tiger
Connected.
SQL>  select * from zahir.emp_v where rownum < 5;

       SSN SSN_FORMATTE ENAME
---------- ------------ ------------------------------
           xxx-xxx-6889 ORA$BASE
           xxx-xxx-6905 DUAL
           xxx-xxx-6906 DUAL
           xxx-xxx-7069 MAP_OBJECT
 
SQL> select COALESCE (to_char(ssn) , formatted_ssn  ) ssn ,ename from zahir.emp_v where rownum < 5;

SSN                                      ENAME
---------------------------------------- ------------------------------
xxx-xxx-6889                             ORA$BASE
xxx-xxx-6905                             DUAL
xxx-xxx-6906                             DUAL
xxx-xxx-7069                             MAP_OBJECT