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 .
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
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
