4 Replies Latest reply: Jun 10, 2010 10:46 PM by 778683 RSS

    Problems with Column Masking

    778683
      We tried to use Oracle's VPD to mask a column but encountered some problems. We hope the experts here would be able to offer some advises. Million thanks in advance.

      Using PL/SQL, we created a function in sys schema to generate the WHERE clause to mask the column from user, Scott.

      CREATE OR REPLACE FUNCTION RESTR_VIEW (P_OWNER IN VARCHAR2, P_NAME IN VARCHAR2) RETURN VARCHAR2

      BEGIN
      IF
      SYS_CONTEXT ('USERENV', 'SESSION_USER') = 'SCOTT'
      THEN
      RETURN '1=0',
      ELSE
      RETURN NULL;
      END IF;
      END
      /

      Then we create a policy, Restrict_View to attach the function to a table, Forecast in schema, FPCS.

      BEGIN
      DBMS_RLS.ADD_POLICY (
      OBJECT_SCHEMA=>'FPCS',
      OBJECT_NAME=>'FORECAST',
      POLICY_NAME=>'RESTRICT_VIEW',
      FUNCTION_SCHEMA=>'SYS',
      POLICY_FUNCTION=>'RESTR_VIEW',
      SEC_RELEVANT_COLS=>'2010/01',
      SEC_RELEVANT_COLS_OPT=>DBMS_RLS.ALL_ROWS);
      END;
      /

      PL/SQL returned errors when we tried to run the above:

      Error at line 1:
      ORA-28104: input value for sec_relevant_cols is not valid
      ORA-06512: at "SYS.DBMS.RLS", line 20
      ORA-06512: at line 2

      Using the same policy, if we change the affected column from "2010/01" to another column "SKU", the PL/SQL statement will run perfectly and the results were also what we wanted. We therefore suspect the problem lies with the slash "/" in "2010/01". As the naming convention is a requirement from our user, we could not rename the column. Is our suspicion spot-on? If it is, is there any solution to this? We would appreciate your advises. Btw, we are using Oracle 10g.

      Thanks for the replies in advance.
        • 1. Re: Problems with Column Masking
          fsitja
          I can't test it right here, but have you tried using double quotes there?
          ...
          SEC_RELEVANT_COLS=>'"2010/01"',
          ...
          • 2. Re: Problems with Column Masking
            Frank Kulash
            Hi,

            Welcome to the forum!

            Non-standard column names (all non-standard identifiers, actually) must be enclosed in double-quotes. Starting with a digit, as well as containing a slash, make "2010/01" a non-standard identifier. Don't you have to use double-quotes around the column name in lots of other places?

            Try this:
            BEGIN
                 DBMS_RLS.ADD_POLICY 
                 (
                      OBJECT_SCHEMA     => 'FPCS',
                      OBJECT_NAME     => 'FORECAST',
                      POLICY_NAME     => 'RESTRICT_VIEW',
                      FUNCTION_SCHEMA     => 'SYS',
                      POLICY_FUNCTION     => 'RESTR_VIEW',
                      SEC_RELEVANT_COLS      =>'"2010/01"',
                      SEC_RELEVANT_COLS_OPT     => DBMS_RLS.ALL_ROWS
                 );
            END;
            /
            By the way, creating your own objects (such as the restr_view function) in the SYS schema can cause problems. Why can't you create it in the fpcs schema? Failing that, you might create a schema just for your own SYS-like objects. I have a schema with lots of special privileges that I use for things that I might be tempted to put in SYS or SYSTEM.
            One advantage is that it makes life simpler when I create a new database. I can simply import that whole schema, the same way I import other schemas.
            • 3. Re: Problems with Column Masking
              778683
              Thanks!

              This is one of them which we haven't tried. And it works.

              Really a big thank you.
              • 4. Re: Problems with Column Masking
                778683
                Hi, thank you for your advise. I just started to learn Oracle. Therefore, I am still very new to Oracle terminologies and conventions. This forum will surely make my learning curve less steep. :D