Forum Stats

  • 3,741,762 Users
  • 2,248,473 Discussions
  • 7,861,978 Comments

Discussions

Oracle Redaction

mathews07
mathews07 Member Posts: 10
edited May 28, 2019 9:59AM in Database Security - General

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

andrewmy

Best Answer

Answers

  • Emad Al-Mousa
    Emad Al-Mousa Member Posts: 716 Bronze Trophy
    edited May 25, 2019 5:01PM Accepted Answer
  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited May 26, 2019 5:16AM

    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/[email protected] * from gaz.redact_example;select id, replace(address, ' ', 'REDACT') address from gaz.redact_example;

    Running the working example:

    [email protected]:SQL+> @redact_example.sqlTable 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 W13 rows selected.Grant succeeded.Connected.        ID ADDRESS---------- ----------------------------------------         1         2         33 rows selected.        ID ADDRESS---------- ----------------------------------------         1 REDACT         2 REDACT         3 REDACT3 rows [email protected]:SQL+>
    andrewmy
  • mathews07
    mathews07 Member Posts: 10
    edited May 26, 2019 7:19AM

    . 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

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited May 26, 2019 7:45AM

    See above working example.

  • mathews07
    mathews07 Member Posts: 10
    edited May 26, 2019 8:03AM

    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?

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited May 26, 2019 9:58AM

    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.

  • mathews07
    mathews07 Member Posts: 10
    edited May 28, 2019 8:40AM

    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

        );

  • mathews07
    mathews07 Member Posts: 10
    edited May 28, 2019 9:59AM

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

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited May 28, 2019 9:54AM
    <random number> wrote:Ok I figured out the process. Now I have the problem with the grantWe 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.

  • mathews07
    mathews07 Member Posts: 10
    edited May 28, 2019 9:59AM

    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

Sign In or Register to comment.