Oracle 12c Data Redaction

DATA REDACTION ( 12c New Features)
----------------------------------

Oracle Data Redaction adds the ability to change the output from sql query field values (in full or in part), depending on conditions.  DBMS_REDACT package managed the Data Redaction.
 
create the redaction user:
--------------------------
create user redact_user identified by oracle;
User created.

Grant appropriate privileges to manage redaction policies:
----------------------------------------------------------
grant connect, resource, unlimited tablespace to redact_user;
grant select  on  Sys.redaction_policies to redact_user;
grant select  on  Sys.redaction_columns to redact_user;
grant execute on dbms_redact to redact_user;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.

Create a test table redtest
-----------------------------
SQL> conn / as sysdba
Connected.
SQL> grant select on hr.employees to redact_user
SQL> /
Grant succeeded.
SQL> grant create table to redact_user;
Grant succeeded.
SQL> conn redact_user/oracle
Connected.
SQL> create table redtest as select * from hr.employees;
Table created.
SQL> desc redtest;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                                        NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

Add a policy
---------------
SQL> Begin
 dbms_redact.ADD_POLICY (
  OBJECT_SCHEMA  =>'REDACT_USER',
  OBJECT_NAME => 'REDTEST',
  POLICY_NAME  =>'Redact_test',
  COLUMN_NAME  => 'EMPLOYEE_ID',
  FUNCTION_TYPE  =>DBMS_REDACT.FULL,
  EXPRESSION  =>'1=1'); 
end;
/  2    3    4    5    6    7    8    9   10 
PL/SQL procedure successfully completed.
SQL> select employee_id from redtest;
EMPLOYEE_ID
-----------
          0
          0
          0
          0
          0
          0
          0
          0
          0
          0
          0
          0
          0
   
ALTER POLICY (Modifying Column)
-------------------------------
Begin
 dbms_redact.ALTER_POLICY (
  OBJECT_SCHEMA  =>'REDACT_USER',
  OBJECT_NAME => 'REDTEST',
  POLICY_NAME  =>'Redact_test',
  COLUMN_NAME  => 'EMPLOYEE_ID',
  FUNCTION_TYPE  =>DBMS_REDACT.PARTIAL,
  FUNCTION_PARAMETERS => '4,1,2',
  EXPRESSION  =>'1=1',
  action =>DBMS_REDACT.MODIFY_COLUMN); 
end;
/
  2    3    4    5    6    7    8    9   10   11   12 
PL/SQL procedure successfully completed.

NoTE:
 FUNCTION_TYPE  =>DBMS_REDACT.PARTIAL --for partial masking
  FUNCTION_PARAMETERS => '4,1,2'  -- first two digit will be  replaced with 4
  EXPRESSION  =>'1=1' -- can use SYS_CONTEXT function

Output:
SQL> select employee_id from redtest;
EMPLOYEE_ID
-----------
        440
        441
        442
        443
        444
        445
        446
        447
        448
        449
        440

Alter policy [Adding Column] for email:
-------------------------------------------------------------------
SQL> Begin
 dbms_redact.ALTER_POLICY (
  OBJECT_SCHEMA  =>'REDACT_USER',
  OBJECT_NAME => 'REDTEST',
  POLICY_NAME  =>'Redact_test',
  COLUMN_NAME  => 'EMAIL',
  FUNCTION_TYPE  =>DBMS_REDACT.RANDOM,
  action =>DBMS_REDACT.ADD_COLUMN); 
end;
/  2    3    4    5    6    7    8    9   10 
PL/SQL procedure successfully completed.
SQL> select email from redtest;
EMAIL
-------------------------
1jk`K
\>DWc28X
[^4l0\C
E_9P0RC
A^JW*Z6q@M_+k
IE@1:t|Q
u_ayXIx#
VeO^hGcP
SEjBNia
P%OG>

SYS_CONTEXT function - to get database user/role, IP address etc.
Modifying an Expression [To exempt HR user from the redaction policy]
--------------------------
SQL> Begin
 dbms_redact.ALTER_POLICY (
  OBJECT_SCHEMA  =>'REDACT_USER',
  OBJECT_NAME => 'REDTEST',
  POLICY_NAME  =>'Redact_test',
  COLUMN_NAME  => 'EMPLOYEE_ID',
  EXPRESSION  =>'SYS_CONTEXT(''USERENV'',''SESSION_USER'')!=''HR''',
  action =>DBMS_REDACT.MODIFY_EXPRESSION); 
end;
/  2    3    4    5    6    7    8    9   10 
PL/SQL procedure successfully completed.

DISABLE the policy
-------------------
Begin
 dbms_redact.DISABLE_POLICY (
  OBJECT_SCHEMA  =>'REDACT_USER',
  OBJECT_NAME => 'REDTEST',
  POLICY_NAME  =>'Redact_test'); 
end;
/
PL/SQL procedure successfully completed.

DROP the policy
----------------
Begin
 dbms_redact.DROP_POLICY (
  OBJECT_SCHEMA  =>'REDACT_USER',
  OBJECT_NAME => 'REDTEST',
  POLICY_NAME  =>'Redact_test'); 
end;
/
PL/SQL procedure successfully completed.

New System Privileges for bypassing redaction policies
-------------------------------------------------------
  • Exempt Redaction Policy
  • Exempt DML Redaction Policy
  • Exempt DDL Redaction Policy

Comments

Post a Comment

Popular posts from this blog

Oracle DataGuard – How to resize Redo Log and Standby Redo Log

Oracle DataGuard – How to resize Redo Log and Standby Redo Log set linesize 200 col dest_name for a30 select DEST_NAME,STATUS,PROTECTION_MODE,ARCHIVED_SEQ#,APPLIED_SEQ# from v$archive_dest_status; On Standby server - make file management parameter to manual if not: show parameter standby; alter system set standby_file_management=manual; ================== +     On Primary             + ==================  SQL> select group#,thread#,status from v$log; col member for a60 set linesize 200 select member,group#,status from v$logfile; SQL> select group#,thread#,status from v$log;     GROUP#    THREAD# STATUS ---------- ---------- ----------------          1          1 ACTIVE          2       ...
Read more

Exadata - Smart Scan Testing

Image
Exadata - SMART SCAN testing -------------------------------------------- Samrt scan works with cell offloading which relate to offloaded the sql processing from the database layer to the cell (storage) layer. Smart scan is the access mechanism to offload data from storage layer. For example passing only required data blocks after applying predicate filters at the cell layer instead of sending every possible blocks from cell server to database server.                           Figure 1: Traditional Database Storage I/O and SQL Processing Model   Process Steps: ------------------- 1.    The client issues a SELECT statement . 2.    The database kernel maps this request to the database files and extents which contain the table data. 3.    The database kernel issues the I/Os to read all the table bloc...
Read more

Exadata Flash Cache - Management

Total size of Flash CellCLI>  list flashcache attributes effectiveCacheSize          5.82122802734375T Flashcache Mode:  [root@Cellc01-adm ~]# su - celladmin cellcli CellCLI>  list cell attributes flashCacheMode,status          WriteThrough    online > list celldisk where disktype=flashdisk > list physicaldisk FLASH_1_1 detail List PhysicalDisk for FlashCache: CellCLI> list physicaldisk attributes name, disktype, makemodel, physicalrpm, physicalport, status      8:0           HardDisk      "HGST    H7240AS60SUN4.0T"                              normal      8:1  ...
Read more