cheng-lu lin, March 04, 2010 - 9:54 pm UTC
Based on your example, can you suggest a solution if I want to column masking SSN, hiredate for userA and column masking SSN, DEPTNO for userB? Thanks.
March 05, 2010 - 5:29 am UTC
set up a policy for each set of columns that has a different rule
not for each user, for each set of columns
ops$tkyte%ORA11GR2> drop user a cascade;
User dropped.
ops$tkyte%ORA11GR2> drop user b cascade;
User dropped.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t
2 as
3 select empno ssn, ename nm, hiredate, job, deptno
4 from scott.emp
5 where rownum <= 5
6 /
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace function foo( p_owner in varchar2, p_name in varchar2 ) return varchar2
2 as
3 begin
4 if sys_context( 'userenv', 'session_user' ) = 'A'
5 then
6 return '1=0';
7 else
8 return null;
9 end if;
10 end;
11 /
Function created.
ops$tkyte%ORA11GR2> BEGIN
2 DBMS_RLS.ADD_POLICY(object_schema=>user, object_name=>'T',
3 policy_name=>'SEC_SSN',
4 function_schema=>user,
5 policy_function=>'foo',
6 sec_relevant_cols=>'ssn',
7 sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
8 END;
9 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace function bar( p_owner in varchar2, p_name in varchar2 ) return varchar2
2 as
3 begin
4 if sys_context( 'userenv', 'session_user' ) = 'B'
5 then
6 return '1=0';
7 else
8 return null;
9 end if;
10 end;
11 /
Function created.
ops$tkyte%ORA11GR2> BEGIN
2 DBMS_RLS.ADD_POLICY(object_schema=>user, object_name=>'T',
3 policy_name=>'SEC_HIREDATE',
4 function_schema=>user,
5 policy_function=>'bar',
6 sec_relevant_cols=>'hiredate',
7 sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
8 END;
9 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> grant select on t to public;
Grant succeeded.
ops$tkyte%ORA11GR2> grant create session to a identified by a;
Grant succeeded.
ops$tkyte%ORA11GR2> grant create session to b identified by b;
Grant succeeded.
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> select * from ops$tkyte.t;
SSN NM HIREDATE JOB DEPTNO
---------- ---------- --------- --------- ----------
SMITH 17-DEC-80 CLERK 20
ALLEN 20-FEB-81 SALESMAN 30
WARD 22-FEB-81 SALESMAN 30
JONES 02-APR-81 MANAGER 20
MARTIN 28-SEP-81 SALESMAN 30
a%ORA11GR2> connect b/b
Connected.
b%ORA11GR2> select * from ops$tkyte.t;
SSN NM HIREDATE JOB DEPTNO
---------- ---------- --------- --------- ----------
7369 SMITH CLERK 20
7499 ALLEN SALESMAN 30
7521 WARD SALESMAN 30
7566 JONES MANAGER 20
7654 MARTIN SALESMAN 30
Follow-up to VPD Column Masking
George Corona, December 22, 2010 - 8:10 pm UTC
Tom,
I have a slightly different problem. I would like to mask SSN, but instead of NULL, I would like the output to be xxx-xx-1234, where '1234' is the last four digits of the SSN. Can this be accomplished using VPD?
Thanks
December 23, 2010 - 10:14 am UTC
that cannot be accomplished via VPD.
that would be something a VIEW can do - create a view that selects out SSN and 'xxx-xx-' || substr( ssn, 8 ) - put the VPD on the ssn column and users can select nvl( ssn, function_of_ssn )
Follow-up to VPD Column Masking
Rajeshwaran, Jeyabal, December 25, 2010 - 1:44 am UTC
Tom:
Is this what you are suggesting?
rajesh@10GR2> create table emp
2 as
3 select empno ,
4 ename ,
5 hiredate,
6 abs(dbms_random.random) as ssn
7 from scott.emp
8 /
Table created.
Elapsed: 00:00:00.31
rajesh@10GR2> create or replace view emp_vw
2 as
3 select empno,ename,hiredate,ssn,'xxx-xxx-'||substr(ssn,-4) as fn_ssn
4 from emp;
View created.
Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2> create or replace function foo(p_owner in varchar2,p_name in varchar2)
2 return varchar2
3 as
4 begin
5 if (p_owner = user) then
6 return '1=1';
7 else
8 return '1=0';
9 end if;
10 end;
11 /
Function created.
Elapsed: 00:00:00.12
rajesh@10GR2>
rajesh@10GR2> grant select on emp_vw to public;
Grant succeeded.
Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_rls.add_policy(object_schema=>user,
3 object_name =>'EMP_VW',
4 policy_name =>'EMP_VW_SSN',
5 function_schema=>user,
6 policy_function=>'FOO',
7 statement_types=>'SELECT',
8 sec_relevant_cols=>'SSN',
9 sec_relevant_cols_opt=>dbms_rls.all_rows);
10 end;
11 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select * from emp_vw;
EMPNO ENAME HIREDATE SSN FN_SSN
---------- ---------- --------- ---------- ------------
7369 SMITH 17-DEC-80 660816385 xxx-xxx-6385
7499 ALLEN 20-FEB-81 558288401 xxx-xxx-8401
7521 WARD 22-FEB-81 1349880355 xxx-xxx-0355
7566 JONES 02-APR-81 1212276157 xxx-xxx-6157
7654 MARTIN 28-SEP-81 1854915396 xxx-xxx-5396
7698 BLAKE 01-MAY-81 1087829822 xxx-xxx-9822
7782 CLARK 09-JUN-81 1356701610 xxx-xxx-1610
7788 SCOTT 09-DEC-82 1547708010 xxx-xxx-8010
7839 KING 17-NOV-81 1623701361 xxx-xxx-1361
7844 TURNER 08-SEP-81 16336872 xxx-xxx-6872
7876 ADAMS 12-JAN-83 395375669 xxx-xxx-5669
7900 JAMES 03-DEC-81 23865000 xxx-xxx-5000
7902 FORD 03-DEC-81 1731198072 xxx-xxx-8072
7934 MILLER 23-JAN-82 821387924 xxx-xxx-7924
14 rows selected.
Elapsed: 00:00:00.06
rajesh@10GR2>
rajesh@10GR2> connect scott/tiger
Connected.
scott@10GR2>
scott@10GR2>
scott@10GR2> select empno,ename,hiredate, nvl( to_char(ssn), fn_ssn) as ssn
2 from rajesh.emp_vw;
EMPNO ENAME HIREDATE SSN
---------- ---------- --------- ----------------------------------------
7369 SMITH 17-DEC-80 xxx-xxx-6385
7499 ALLEN 20-FEB-81 xxx-xxx-8401
7521 WARD 22-FEB-81 xxx-xxx-0355
7566 JONES 02-APR-81 xxx-xxx-6157
7654 MARTIN 28-SEP-81 xxx-xxx-5396
7698 BLAKE 01-MAY-81 xxx-xxx-9822
7782 CLARK 09-JUN-81 xxx-xxx-1610
7788 SCOTT 09-DEC-82 xxx-xxx-8010
7839 KING 17-NOV-81 xxx-xxx-1361
7844 TURNER 08-SEP-81 xxx-xxx-6872
7876 ADAMS 12-JAN-83 xxx-xxx-5669
7900 JAMES 03-DEC-81 xxx-xxx-5000
7902 FORD 03-DEC-81 xxx-xxx-8072
7934 MILLER 23-JAN-82 xxx-xxx-7924
14 rows selected.
Elapsed: 00:00:00.06
scott@10GR2>
scott@10GR2>
December 25, 2010 - 11:47 am UTC
yes
Ellaboration of Column Masking
Sunny J, March 18, 2011 - 6:37 am UTC
Well I agree with what you have said so far but would like some more clarification.
Havin read the manual, it suggest that if you need to use Column Masking, you would have to use the parameter 'sec_relevant_cols_opt' and
"Unlike regular VPD predicates, the masking condition that is generated by the policy function must be a simple boolean expression. "
I am assuming this means that the Policy function would return either a TRUE or a FALSE as opposed to the predicate as you have shown in the example in your earlier reply.
This means that column masking can be used to mask All or None of the values for a column.
Basically, I am trying to find a way to use Column Masking to display values of columns only for certain rows and should NULL for the others.
Which I believe isn't possible off-the-shelf.
1 Possible solution would be to use Views to display and use contect functions to process the values and display based on the value from the context.
Thanks in advance
March 18, 2011 - 8:21 am UTC
Basically, I am trying to find a way to use Column Masking to display values of
columns only for certain rows and should NULL for the others.that is exactly what this does.
ops$tkyte%ORA11GR2> create table t
2 as
3 select empno ssn, ename nm, hiredate, job, deptno
4 from scott.emp
5 where rownum <= 5
6 /
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace function foo( p_owner in varchar2, p_name in varchar2 ) return varchar2
2 as
3 begin
4 if sys_context( 'userenv', 'session_user' ) = 'OPS$TKYTE'
5 then
6 return null;
7 else
8 return 'hiredate <= to_date( ''01-mar-1981'', ''dd-mon-yyyy'' )';
9 end if;
10 end;
11 /
Function created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> BEGIN
2 DBMS_RLS.ADD_POLICY(object_schema=>user, object_name=>'T',
3 policy_name=>'SEC_SSN',
4 function_schema=>user,
5 policy_function=>'foo',
6 sec_relevant_cols=>'ssn',
7 sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
8 END;
9 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from t;
SSN NM HIREDATE JOB DEPTNO
---------- ---------- --------- --------- ----------
7369 SMITH 17-DEC-80 CLERK 20
7499 ALLEN 20-FEB-81 SALESMAN 30
7521 WARD 22-FEB-81 SALESMAN 30
7566 JONES 02-APR-81 MANAGER 20
7654 MARTIN 28-SEP-81 SALESMAN 30
ops$tkyte%ORA11GR2> grant all on t to public;
Grant succeeded.
ops$tkyte%ORA11GR2> connect scott/tiger
Connected.
scott%ORA11GR2> select * from ops$tkyte.t;
SSN NM HIREDATE JOB DEPTNO
---------- ---------- --------- --------- ----------
7369 SMITH 17-DEC-80 CLERK 20
7499 ALLEN 20-FEB-81 SALESMAN 30
7521 WARD 22-FEB-81 SALESMAN 30
JONES 02-APR-81 MANAGER 20
MARTIN 28-SEP-81 SALESMAN 30
thats Brilliant,..
Sunny J, March 18, 2011 - 9:29 am UTC
I'm gonna give this a go.
cheers.
A reader, March 29, 2011 - 11:55 am UTC
How can I achieve this masking if the client is a web application . For a db , all the connections are from a single app user ( aka Web Server ) ?
Do we have set the identity via dbms_application_info?
or is there any other way ?
mfz, March 30, 2011 - 9:46 am UTC
Thanks .
Oracle Forms updates SSN to null
Jerry, February 14, 2012 - 11:09 am UTC
I have implemented column masking, using a policy, as you described above. When I login to sqlplus as a user who is not allowed to see SSN, and select the SSN field from the person table, it appears as null. When I run a form as the same user, the SSN displays as null there, too. That's all good. But if I change a field on the form other than SSN, and commit, the SSN is changed to null in the database. I want the user to be able to enter an SSN (even if they can't see it once it is committed) but I don't want the form changing the SSN to null automatically. Any thoughts? Thanks in advance for your help.
February 14, 2012 - 12:21 pm UTC
It has been since 1995 that I last used forms...
As I recall however, there was an option somewhere to update just the modified fields....
but, I'll point you to the forums on otn.oracle.com, they would be better equipped to answer this
Update Changed Columns Only
Mark, February 17, 2012 - 11:20 am UTC
"Update Changed Columns Only"
is the block level property in Oracle Forms that Tom alluded to
Jerry, make sure you read the associated "Usage Notes" in the forms help
Re: Column masking for only certain rows
Prathap, December 09, 2015 - 3:47 pm UTC
Hi Tom,
I have a two tables with parent child relationship.
parent_table:
Caseid Partyid CaseName ...
---------------------------
abc 123 Case1
abc 456 Case2
xyz 789 Case3
Child_table:
Partyid Salary .....
--------------------
123 $1000
456 $500
789 $600
For "Restricted" users, I want to apply column masking on "xyz" caseid's rows. For "Unrestricted" users no column masking on any rows.
So, when I (as a restricted user) query both the tables, this should be the result.
parent_table:
Caseid Partyid CaseName ...
---------------------------
abc 123 Case1
abc 456 Case2
xyz 789 {NULL}
Child_table:
Partyid Salary .....
--------------------
123 $1000
456 $500
789 {NULL}
It works fine on the parent table where my policy function will return either "1=1" for "Unrestricted" users or caseid <> 'xyz' for "Restricted users"
But if the child table' policy function returns "partyid not in (select partyid from parent_table where caseid <> 'xyz')", I'm getting a ORA-28113: policy predicate has error. What am I doing wrong here?
I thought "not in" keyword is the culprit but it doesn't seem to be because "partyid not in ('789')" works fine.
December 10, 2015 - 2:04 pm UTC
Provide a full test case (and possibly log as a new question) and we'll take a look
To: Prathap from Hartford | on column masking
Rajeshwaran, Jeyabal, December 10, 2015 - 3:20 pm UTC
rajesh@ORA10G> set feedback off
rajesh@ORA10G> drop table c purge;
rajesh@ORA10G> drop table p purge;
rajesh@ORA10G>
rajesh@ORA10G> create table p(case_id varchar2(3),
2 party_id int primary key,
3 case_name varchar2(5));
rajesh@ORA10G>
rajesh@ORA10G> create table c(party_id references p ,
2 salary int);
rajesh@ORA10G>
rajesh@ORA10G> insert into p values('ABC',123,'CASE1') ;
rajesh@ORA10G> insert into p values('ABC',456,'CASE2') ;
rajesh@ORA10G> insert into p values('XYZ',789,'CASE3') ;
rajesh@ORA10G> insert into c values(123,1000);
rajesh@ORA10G> insert into c values(456,500);
rajesh@ORA10G> insert into c values(789,600);
rajesh@ORA10G> commit;
rajesh@ORA10G> set feedback on
rajesh@ORA10G>
rajesh@ORA10G> select * from p;
CAS PARTY_ID CASE_
--- ---------- -----
ABC 123 CASE1
ABC 456 CASE2
XYZ 789 CASE3
3 rows selected.
rajesh@ORA10G> select * from c;
PARTY_ID SALARY
---------- ----------
123 1000
456 500
789 600
3 rows selected.
rajesh@ORA10G> create or replace function foo1(p_owner varchar2,p_name varchar2)
2 return varchar2 as
3 begin
4 if user ='RAJESH' then
5 return '1=1';
6 else
7 return q'| case_id <> 'XYZ' |' ;
8 end if;
9 end;
10 /
Function created.
rajesh@ORA10G> begin
2 dbms_rls.add_policy(object_schema=>user,
3 object_name=>'P',
4 policy_name=>'P_PLCY',
5 function_schema=>user,
6 policy_function=>'foo1',
7 sec_relevant_cols=>'CASE_NAME',
8 sec_relevant_cols_opt=>dbms_rls.all_rows);
9 end;
10 /
PL/SQL procedure successfully completed.
rajesh@ORA10G> create or replace context ctx using foo2;
Context created.
rajesh@ORA10G> create or replace function foo2(p_owner varchar2,p_name varchar2)
2 return varchar2 as
3 l_prty_id int;
4 begin
5 select party_id into l_prty_id
6 from p where case_id ='XYZ';
7
8 dbms_session.set_context('CTX','X',l_prty_id);
9
10 if user ='RAJESH' then
11 return '1=1';
12 else
13 return q'| party_id <> sys_context('CTX','X') |' ;
14 end if;
15 end;
16 /
Function created.
rajesh@ORA10G>
rajesh@ORA10G> begin
2 dbms_rls.add_policy(object_schema=>user,
3 object_name=>'C',
4 policy_name=>'C_PLCY',
5 function_schema=>user,
6 policy_function=>'foo2',
7 sec_relevant_cols=>'SALARY',
8 sec_relevant_cols_opt=>dbms_rls.all_rows);
9 end;
10 /
PL/SQL procedure successfully completed.
rajesh@ORA10G> select * from p;
CAS PARTY_ID CASE_
--- ---------- -----
ABC 123 CASE1
ABC 456 CASE2
XYZ 789 CASE3
3 rows selected.
rajesh@ORA10G> select * from c;
PARTY_ID SALARY
---------- ----------
123 1000
456 500
789 600
3 rows selected.
rajesh@ORA10G> grant select on p to scott;
Grant succeeded.
rajesh@ORA10G> grant select on c to scott;
Grant succeeded.
rajesh@ORA10G>
rajesh@ORA10G> conn scott/tiger@ora10g
Connected.
scott@ORA10G> select * from rajesh.p;
CAS PARTY_ID CASE_
--- ---------- -----
ABC 123 CASE1
ABC 456 CASE2
XYZ 789
3 rows selected.
scott@ORA10G> select * from rajesh.c;
PARTY_ID SALARY
---------- ----------
123 1000
456 500
789
3 rows selected.
scott@ORA10G>
Excellent
Helena Marková, October 05, 2017 - 8:12 am UTC
Thank you, our problem was solved:)
Column masking for SYS as well
RaviTyagi, March 09, 2022 - 2:28 pm UTC
Hello Tom,
I have a table, and a column in it with the Credit Card No. So I need to restrict this column for all users including sys when the user performs only SELECT statement. How can be this done? (column masking with chars like #### OR any other alternative method)
Can you provide the complete process required to do this either column masking or any other method?
Thanks
Ravi Tyagi
March 09, 2022 - 3:15 pm UTC
SYS is special - it has power to do everything in the database. Even if you could apply a masking process, someone connected as SYS could disable/bypass this easily.
Only use SYS when you specifically need to; avoid connecting as this user for general use.