Oracle 12c RDBMS Redaction Policies allow the RDBMS Developer or DBA the ability to obscure data. This prevention of viewing data can be defined for all users, specific users, environmental variables, or roles. This procedure shows the use of 12c Redaction against a common table and restricts access to a specific user TEST1.
- Logon as the oracle DBA user.
mylinux# sqlplus system/password
SQL*Plus: Release 12.1.0.2.0 Production on Sun May 1 13:58:21 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
- Create TEST user, TEST table, and insert dummy data for execute this procedure against with the following SQL statements.
create user test identified by test default tablespace users
quota unlimited on users;
grant resource to test;
grant create session to test;
create table test.emp(f_name varchar2(30),
l_name varchar2(30),
start_date date,
salary number(8,2));
insert into test.emp values(‘Bob’, ‘Smith’, sysdate-30, 45000);
insert into test.emp values(‘Nancy’,’Jones’, sysdate-628,87000);
insert into test.emp values(‘Thomas’,’Harris’,sysdate-1245,79000);
commit;
- Now create a user VIEW1 with the following sql statements.
create user test1 identified by test default tablespace users
quota unlimited on users;
grant resource to test1;
grant create session to test1;
- Now as the system user create a policy using the RDBMS_REDACTION package to redact the column SALARY in the table TEST.EMP. The key to restricting the viewing of SALARY column is the EXPRESSION clause. If the EXPRESSION clause is TRUE, the redaction policy will be applied. In this example, when the logon user equals TEST1, the redaction policy will be applied.
BEGIN
DBMS_REDACT.ADD_POLICY( object_schema => ‘test’,
object_name => ’emp’,
column_name => ‘salary’,
policy_name => ‘redact_salary’,
function_type => DBMS_REDACT.FULL,
expression => ‘SYS_CONTEXT(”USERENV”,”SESSION_USER”) = ”TEST1”’);
END;
/
- Now as the TEST user, select the values from table TEST.EMP and you will notice that the SALARY columns are all zero.
SQL> connect test/test
Connected.
SQL> set linesize 140
SQL> select * from test.emp;
F_NAME L_NAME START_DAT SALARY
—————————— —————————— ——— ———-
Bob Smith 04-APR-16 45000
Nancy Jones 15-AUG-14 87000
Thomas Harris 06-DEC-12 79000
SQL>
- Now logon as the user TEST1 and not that the select returns 0 values for the SALARY column. This will only be true for a user id referenced by the EXPRESSION clause unless those users have the EXAMPT REDACTION POLICY privilege.
SQL> connect test1/test
Connected.
SQL> set linesize 140
SQL> select * from test.emp;
F_NAME L_NAME START_DAT SALARY
—————————— —————————— ——— ———-
Bob Smith 04-APR-16 0
Nancy Jones 15-AUG-14 0
Thomas Harris 06-DEC-12 0
SQL>
- Now the column is protected from all users defined in the EXPRESSION clause unless they have exempt redaction policy privileges.
Larry Catt, OCP