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.

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

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

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>

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

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

I'm gonna give this a go.

cheers.

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 ?

Thanks .

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.

"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

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.

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>

Thank you, our problem was solved:)

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