10 Replies Latest reply on May 28, 2019 1:59 PM by 3626843

    Oracle Redaction

    3626843

      Planning to apply the Oracle Redaction in our Oracle database 12c.

      My questions are

      1. How can I exclude 3 people from the reaction policy

      2.For address field how can we put REDACT instead of Address1

        • 1. Re: Oracle Redaction
          Emad Al-Mousa

          Hi,

           

          grant system privilege EXEMPT REDACTION POLICY to the 3 users

           

          also, useful links regarding DBMS_REDACT

           

           

          https://geodatamaster.com/2017/01/06/oracle-12c-database-data-redaction-dbms_redact/

           

           

           

          https://oracle-base.com/articles/12c/data-redaction-12cr1

          • 2. Re: Oracle Redaction
            Gaz in Oz

            Question 1. As Emad Al-Mousa states, "GRANT EXEMPT REDACTION POLICY to x; Will allow x to see the data.

            Note that anyone with the DBA role, will be automatically exempt.

             

            Question 2. is vague.

            . Is "Address1" part of the "address" field value?

            . Is "Address field" actually a column in a table?

            . What do you actually want replaced with "REDACT", the whole value in "address" column or just the part where it is "Address1"?

             

            Here is a working example, replacing the default varchar2 redaction character of space to the word REDACT.

            (You can query what the current settings are for all datatypes by querying REDACTION_VALUES_FOR_TYPE_FULL)

            redact_example.sql:

            CREATE TABLE gaz.redact_example (
              id      NUMBER,
              address VARCHAR2(4000));
            begin
              DBMS_REDACT.add_policy(
                  object_schema => 'gaz',
                  object_name   => 'redact_example',
                  column_name   => 'address',
                  policy_name   => 'redact_address',
                  function_type => DBMS_REDACT.full,
                  expression    => '1=1');
            end;
            /
            INSERT INTO gaz.redact_example (id, address)
               SELECT 1, '121 Reading Rd. Finchamstead, Bwekshire RG12 1RA' from dual union all
               SELECT 2, 'PO BOX 121, Grenwich'                             from dual union all
               SELECT 3, '101 Wilder Street London W1'                      from dual;
            COMMIT;
            select * from gaz.redact_example;
            GRANT SELECT ON gaz.redact_example TO x;
            conn x/x@ora12c
            select * from gaz.redact_example;
            select id, replace(address, ' ', 'REDACT') address from gaz.redact_example;
            

            Running the working example:

            GAZ@ora12c:SQL+> @redact_example.sql

             

            Table dropped.

             

            Table created.

             

            PL/SQL procedure successfully completed.

             

            3 rows created.

             

                    ID ADDRESS

            ---------- ----------------------------------------

                     1 121 Reading Rd. Finchamstead, Bwekshire

                       RG12 1RA

                     2 PO BOX 121, Grenwich

                     3 101 Wilder Street London W1

             

            3 rows selected.

             

            Grant succeeded.

             

            Connected.

             

                    ID ADDRESS

            ---------- ----------------------------------------

                     1

                     2

                     3

             

            3 rows selected.

             

                    ID ADDRESS

            ---------- ----------------------------------------

                     1 REDACT

                     2 REDACT

                     3 REDACT

             

            3 rows selected.

             

            X@ora12c:SQL+>

            • 3. Re: Oracle Redaction
              3626843

              . Is "Address1" part of the "address" field value? Yes

              . Is "Address field" actually a column in a table? Yes

              . What do you actually want replaced with "REDACT", the whole value in "address" column or just the part where it is "Address1"?

               

              All the address column needs to be shown as REDACT when someone query the column

              • 4. Re: Oracle Redaction
                Gaz in Oz

                See above working example.

                • 5. Re: Oracle Redaction
                  3626843

                  But developers are not selecting as

                  select id, replace(address, ' ', 'REDACT') address from gaz.redact_example;

                   

                  they are selecting as select address from gaz.redact_example;  and need to see as 'REDACT' is they any way we can do like that?

                  • 6. Re: Oracle Redaction
                    Gaz in Oz

                    Yes. By using something like:

                    dbms_redact.add_policy (

                       ...

                       function_type        => dbms_redact.regexp,

                       regexp_pattern       => '^.*$',

                       regexp_relace_string => 'REDACT'

                       ...

                    );

                    https://docs.oracle.com/database/121/ARPLS/d_redact.htm#ARPLS73811

                    Note: using regex will likely slow queries down.

                    • 7. Re: Oracle Redaction
                      3626843

                      When I did the below, I am getting the error

                       

                      BEGIN

                          dbms_redact.add_policy (object_schema        => 'HELLO',

                                                  object_name          => 'HAI,

                                                  column_name          => 'ADDRESS1',

                                                  policy_name          => 'REDACTION_ADDR',

                                                  function_type        => dbms_redact.regexp,

                                                  regexp_pattern       => '^.*$',

                                                  regexp_relace_string => 'REDACT',

                                                  expression           => '1=1'

                      );

                      END;

                      /

                       

                      Got the below error

                      [1]: PLS-00306: wrong number or types of arguments in call to 'ADD_POLICY' - Following procedure is used, so how can I say which arguments I need to put as a policy

                       

                        PROCEDURE add_policy

                          (object_schema          IN VARCHAR2       := NULL

                          ,object_name            IN VARCHAR2

                          ,policy_name            IN VARCHAR2

                          ,policy_description     IN VARCHAR2       := NULL

                          ,column_name            IN VARCHAR2       := NULL

                          ,column_description     IN VARCHAR2       := NULL

                          ,function_type          IN BINARY_INTEGER := dbms_redact.FULL

                          ,function_parameters    IN VARCHAR2       := NULL

                          ,expression             IN VARCHAR2

                          ,enable                 IN BOOLEAN        := TRUE

                          ,regexp_pattern         IN VARCHAR2       := NULL

                          ,regexp_replace_string  IN VARCHAR2       := NULL

                          ,regexp_position        IN BINARY_INTEGER := 1

                          ,regexp_occurrence      IN BINARY_INTEGER := 0

                          ,regexp_match_parameter IN VARCHAR2       := NULL

                          );

                      • 8. Re: Oracle Redaction
                        3626843

                        Ok I figured out the process. Now I have the problem with the grant

                        • 9. Re: Oracle Redaction
                          Gaz in Oz

                          <random number> wrote:

                           

                          Ok I figured out the process. Now I have the problem with the grant

                           

                          We need to isolate this to only the developers

                          Show what you did to resolve your issue so others may benefit from this thread.

                           

                          What do you mean "isolate it to only developers"?

                          Anyone with dba role or anyone who has been granted "exempt redact policy" will be able to see the data in its un-redacted form.

                          • 10. Re: Oracle Redaction
                            3626843

                            What is the correct grant statement? I mean the syntax

                             

                            grant system privilege EXEMPT REDACTION POLICY to <USER> - returning syntax error

                             

                            GRANT EXEMPT REDACTION POLICY to USER; - didn't work