ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 12c - Data Redaction

Oracle 12c - Data Redaction

原创 Linux操作系统 作者:chncaesar 时间:2013-08-15 11:12:54 0 删除 编辑
Env:
Virtualbox + Oracle Linux 64bit 6.4 + Oracle database 12.1
 
Introduction:
 
A new security feature is intorudced in 12c, one of top-10 favourite new features of Tom Kyte. It's also known as data masking. Data redaction hides sensitive data from low-privileged users. For example, your credit card number, date of birth should be masked in a CRM application.
 
Data redaction takes places on the fly, it does not change the data in the database.Data redaction does not apply to users with "EXEMPT REDACTION POLICY". SYSDBA and DBA are not affected by data redaction.
 
Adding a new redaction policy:
 
begin
  dbms_redact.add_policy(object_schema => 'HR',
                         object_name => 'EMPLOYEES',
                         column_name => 'SALARY',
                         policy_name => 'SALARY_REDACTION',
                         function_type => dbms_redact.FULL,
                         expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''HR'' OR SYS_CONTEXT(''USERENV'',''SESSION_USER'') IS NULL'
                         );
end;
 
User needs execute privilege on dbms_redact. Even if the user is the owner of the object. Say, user hr wanted to add a redaction policy to table employees, hiding column salary. But he's not allowed to do so until he gets select privilege on dbms_redact. See the error as follows:
 
ORA-06550: line 6, column 43:
PLS-00201: identifier 'DBMS_REDACT' must be declared
 
SQL> connect  sys/123456@pdborcl as sysdba;
Connected.
SQL> show user;
USER is "SYS"
SQL> grant execute on dbms_redact to hr;
Grant succeeded.
Execute the add_policy again, you're all set.
 
Observing policies in the database:
select * from redaction_policies;
 
Examine the data redaction:
Login as nobody who has select privilege on hr.employees.
 
SQL> select first_name, last_name, salary from hr.employees where rownum <= 3;
FIRST_NAME      LAST_NAME      SALARY
-------------------- ------------------------- ----------
Steven       King    0
Neena       Kochhar    0
Lex       De Haan    0
Drop the redaction policy:
EXEC DBMS_REDACT.DROP_POLICY('HR','EMPLOYEES','SALARY_REDACTION');
 
Changing the display format: 
begin
  dbms_redact.alter_policy(object_schema => 'HR',
                         object_name => 'EMPLOYEES',
                         policy_name => 'SALARY_REDACTION',
                         action => dbms_redact.MODIFY_COLUMN,
                         column_name => 'SALARY',                        
                         function_type => dbms_redact.partial,
                         function_parameters => '9,1,8'
  );
end;
SQL>  select first_name, last_name, salary from hr.employees where rownum <= 3;
FIRST_NAME      LAST_NAME      SALARY
-------------------- ------------------------- ----------
Steven       King       99999
Neena       Kochhar       99999
Lex       De Haan       99999

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/638844/viewspace-768583/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-30

  • 博文量
    102
  • 访问量
    899726