rajesh@10GR2> create table emp 2 as 3 select empno, 4 ename, 5 deptno, 6 dbms_random.random as ssn 7 from scott.emp; Table created. Elapsed: 00:00:00.26 rajesh@10GR2> create or replace function foo(p_schema in varchar2,p_object_name in varchar2) 2 return varchar2 3 as 4 begin 5 if ( p_schema = user ) then 6 return '1 = 1'; 7 else 8 return '1=0'; 9 end if; 10 end; 11 / Function created. Elapsed: 00:00:00.09 rajesh@10GR2>
Now, lets say that we need to hide this SSN details from others. This can be done by using new features available dbms_rls API in Oracle 10g database.
rajesh@10GR2> begin 2 dbms_rls.add_policy( 3 object_schema =>user, 4 object_name =>'EMP', 5 policy_name => 'SSN_HIDING', 6 function_schema => user, 7 policy_function =>'FOO', 8 statement_types =>'SELECT', 9 sec_relevant_cols => 'SSN', 10 sec_relevant_cols_opt => dbms_rls.all_rows 11 ); 12 end; 13 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.04 rajesh@10GR2> rajesh@10GR2> select * from emp; EMPNO ENAME DEPTNO SSN ---------- ---------- ---------- ---------- 7369 SMITH 20 407740162 7499 ALLEN 30 -128160633 7521 WARD 30 378242413 7566 JONES 20 -542663968 7654 MARTIN 30 1929020028 7698 BLAKE 30 408643855 7782 CLARK 10 -569575368 7788 SCOTT 20 1718524881 7839 KING 10 -2.139E+09 7844 TURNER 30 -1.033E+09 7876 ADAMS 20 -607357748 7900 JAMES 30 2099341193 7902 FORD 20 1979593316 7934 MILLER 10 -615781088 14 rows selected. Elapsed: 00:00:00.01 rajesh@10GR2> rajesh@10GR2> connect scott/tiger Connected. scott@10GR2> scott@10GR2> select * from rajesh.emp; EMPNO ENAME DEPTNO SSN ---------- ---------- ---------- ---------- 7369 SMITH 20 7499 ALLEN 30 7521 WARD 30 7566 JONES 20 7654 MARTIN 30 7698 BLAKE 30 7782 CLARK 10 7788 SCOTT 20 7839 KING 10 7844 TURNER 30 7876 ADAMS 20 7900 JAMES 30 7902 FORD 20 7934 MILLER 10 14 rows selected. Elapsed: 00:00:00.07 scott@10GR2> scott@10GR2>
how can we mask a column data for credit card with only last 3 digits visible.
ReplyDeletei.e
********345
@Anonymous: Does this helps you?
ReplyDeleterajesh@ORA10GR2>
rajesh@ORA10GR2> create table t
2 nologging
3 as
4 select object_name,owner,to_char(object_id,'fm0000000000') as object_id
5 from all_objects
6 where rownum <5;
Table created.
Elapsed: 00:00:00.11
rajesh@ORA10GR2>
rajesh@ORA10GR2> create or replace view t_vw
2 as
3 select t.*, 'xxx-xxxx-'||substr(object_id,-3) as fn_object_id
4 from t;
View created.
Elapsed: 00:00:00.00
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from t_vw;
OBJECT_NAME OWNER OBJECT_ID FN_OBJECT_ID
------------------------------ ---------- ----------- ------------
ICOL$ SYS 0000000020 xxx-xxxx-020
I_USER1 SYS 0000000044 xxx-xxxx-044
CON$ SYS 0000000028 xxx-xxxx-028
UNDO$ SYS 0000000015 xxx-xxxx-015
Elapsed: 00:00:00.03
rajesh@ORA10GR2>
rajesh@ORA10GR2> create or replace function foo
2 (p_schema_name in varchar2,p_object_name in varchar2)
3 return varchar2
4 as
5 begin
6 if (p_schema_name = USER) then
7 return '1=1';
8 else
9 return '1=0';
10 end if;
11 end foo;
12 /
Function created.
Elapsed: 00:00:00.04
rajesh@ORA10GR2>
rajesh@ORA10GR2> begin
2 dbms_rls.add_policy(
3 object_schema =>user,
4 object_name =>'T_VW',
5 policy_name =>'T_POLICY',
6 function_schema =>user,
7 policy_function =>'FOO',
8 statement_types =>'SELECT',
9 sec_relevant_cols =>'OBJECT_ID',
10 sec_relevant_cols_opt =>dbms_rls.all_rows);
11 end;
12 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from t_vw;
OBJECT_NAME OWNER OBJECT_ID FN_OBJECT_ID
------------------------------ ---------- ----------- ------------
ICOL$ SYS 0000000020 xxx-xxxx-020
I_USER1 SYS 0000000044 xxx-xxxx-044
CON$ SYS 0000000028 xxx-xxxx-028
UNDO$ SYS 0000000015 xxx-xxxx-015
Elapsed: 00:00:00.04
rajesh@ORA10GR2>
rajesh@ORA10GR2> grant select on t_vw to scott;
Grant succeeded.
Elapsed: 00:00:00.01
rajesh@ORA10GR2>
rajesh@ORA10GR2> connect scott/tiger
Connected.
scott@ORA10GR2>
scott@ORA10GR2> select * from rajesh.t_vw;
OBJECT_NAME OWNER O FN_OBJECT_ID
------------------------------ ---------- - ------------
ICOL$ SYS xxx-xxxx-020
I_USER1 SYS xxx-xxxx-044
CON$ SYS xxx-xxxx-028
UNDO$ SYS xxx-xxxx-015
Elapsed: 00:00:00.03
scott@ORA10GR2>
scott@ORA10GR2> select object_name,
2 owner,
3 nvl(object_id,fn_object_id) as object_id
4 from rajesh.t_vw;
OBJECT_NAME OWNER OBJECT_ID
------------------------------ ---------- ------------
ICOL$ SYS xxx-xxxx-020
I_USER1 SYS xxx-xxxx-044
CON$ SYS xxx-xxxx-028
UNDO$ SYS xxx-xxxx-015
Elapsed: 00:00:00.03
scott@ORA10GR2>
scott@ORA10GR2>
First of all thanks for this wonderful post. I have one question.
ReplyDelete1. One live database let's say LIVE.
2. Two different schema live_read and vpn_user.
3. Now when i am creating a policy so that vpn_user cannot see specific columns, that policy is automatically applies to live_read also. I want that policy to effect only vpn_user.
what is the difference between returning '1=1' and returning null ??
ReplyDelete@Anonymous:
ReplyDeletelook at the place where you posted the same question.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7573153772528#6706612900346523838
and how to mask pseudocolumn ROWNUM
ReplyDeleteVPD applied to realy columns and not on pseudocolumn.
DeleteHi I too want to know if column level masking can be leveraged to apply column masking on different columns for different users
ReplyDeletethen define a seperate policy for each column with its own masking condition for user specific.
DeleteThank you for this post.
ReplyDeleteI want to learn data masking using VPD. Please suggest some good videos on youtube.