Re: column masking in 10g

From: Alexander Fatkulin <afatkulin_at_gmail.com>
Date: Fri, 2 Mar 2007 00:01:43 +1000
Message-ID: <49d668000703010601m78893632qc66649a74e404011@mail.gmail.com>

Dave, here is the straight forward example. Hope you'll get the feelings of what is it like to use column masking.

SQL> create table col_masking
  2 (
  3 n number,
  4 data varchar2(100)
  5 );

Table created.

SQL> insert into col_masking
  2 select level, to_char(level)
  3 from dual
  4 connect by level <= 10;

10 rows created.

SQL> commit;

Commit complete.

Now we will show only rows with even N

SQL> create function sec_function(
  2 p_owner in varchar2,
  3 p_object in varchar2
  4 ) return varchar2 is
  5 begin
  6 return 'mod(n,2)=0';
  7 end;
  8 /

Function created.

SQL> begin
  2 dbms_rls.add_policy(

  3    object_name => 'col_masking',
  4    policy_name => 'col_masking',
  5    policy_function => 'sec_function',

  6 sec_relevant_cols => 'data',
  7 sec_relevant_cols_opt => dbms_rls.ALL_ROWS   8 );
  9 end;
 10 /

PL/SQL procedure successfully completed.

SQL> column data format a5
SQL> select * from col_masking;

         N DATA
---------- -----

         1
         2 2
         3
         4 4
         5
         6 6
         7
         8 8
         9
        10 10

10 rows selected.

Alternatively, you can use views with the inline predicates.

On 3/1/07, David Boyd <davidb158_at_hotmail.com> wrote:
> Hi All,
>
> We¡¯d like to block a column for some users, but open to other users. We
> don¡¯t care about row level security. If the user has the privilege to see
> the column data, she/he gets to see the data in the column for the entire
> table. Otherwise the column is null. I was wondering if any one uses VPD
> column masking in 10g to block columns. If you do, could you please share
> your code with me?
>
> Dave
>
> _________________________________________________________________
> Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month.
> Intro*Terms
> https://www2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h27f6&disc=y&vers=743&s=4056&p=5117
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Alexander Fatkulin,
Senior Oracle DBA
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 01 2007 - 08:01:43 CST