Oracle 12c新特性--数据编写（Redaction）

逃离我推掉我的手 2022-09-25 03:23 76阅读 0赞

**Oracle 12c新特性--数据编写（Redaction）**

**   在Oracle数据库中**，**当低权限的用户查询列中的敏感数据时，O****racle redaction可以对返回给用户的数据稍作掩藏，以保证机密数据的安全。对于列中的数据有以下几种redaction方式：**

**1.Full redaction.对列中的数据全部redact，number类型的列将全部返回为0，character类型的列将全部返回为空格，日期类型返回为2001-01-01。**

**2.Partial redaction.对列中的一部分数据进行redact，比如，可以对社会保险号的前几位设置返回为\*，剩下的几位保持不变。只有列中的数据为固定宽度时才能使用这种方式，如果列中存储的是email地址，每个email地址的宽度不尽相同，此时要使用Regular expressions。**

**3.Regular expressions. You can use regular expressions to look for patterns of data to redact. For example, you can use regular expressions to redact email addresses, which can have varying character lengths. It is designed for use with character data only.**

**4.Random redaction. The redacted data presented to the querying user appears as randomly generated values each time it is displayed, depending on the data type of the column.**

**5.No redaction.This option enables you to test the internal operation of your redaction policies, with no effect on the results of queries against tables with policies defined on them. You can use this option to test the redaction policy definitions before applying them to a production environment.**

**不能对sys和system用户进行数据的redact。因为他们都有EXP\_FULL\_DATABASE这个角色,而这个角色又包含了EXEMPT REDACTION POLICY系统权限。同理，也不能直接赋予用户dba权限，dba自动包含EXP\_FULL\_DATABASE角色。**

![Center][]

![Center 1][]

![Center 2][]

**案例分析：**

**SQL> create table employee(id number,name varchar2(10),salary number,jobdate date,mobile varchar2(20));**

Table created.

**SQL> insert into employee values(1,'tom',6000,to\_date('01-07-2012','dd-mm-yyyy'),'135-2009-1146');**

1 row created.

** SQL> insert into employee values(2,'mary',9000,to\_date('01-07-2013','dd-mm-yyyy'),'135-2009-1111');**

1 row created.

**SQL> commit;**

Commit complete.

**SQL> select \*from employee;**

ID NAME SALARY JOBDATE MOBILE

\---------- ---------- ---------- --------- --------------------

**1 tom 6000 01-JUL-12 135-2009-1146**

**2 mary 9000 01-JUL-13 135-2009-1111**

**1、完全编写**

**full redaction的验证（number）**

**建立编写策略**

SQL> begin dbms\_redact.add\_policy(

2 object\_schema=>'scott',

3 object\_name=>'employee',

4 policy\_name=>'p1',

5 column\_name=>'salary',

6 function\_type=>dbms\_redact.full,

7 enable=>true,

8 expression=>'1=1');

9 end;

10 /

PL/SQL procedure successfully completed.

**SQL> select \*from employee;**

ID NAME **SALARY** JOBDATE MOBILE

\---------- ---------- ---------- --------- --------------------

1 tom **0**01-JUL-12 135-2009-1146

2 mary **0** 01-JUL-13 135-2009-1111

**full redaction**的验证（**char**）

SQL> begin dbms\_redact.alter\_policy(

2 object\_schema=>‘scottf',

3 object\_name=>'employee',

4 policy\_name=>'p1',

5 column\_name=>'name',

6 action=>dbms\_redact.add\_column,

7 function\_type=>dbms\_redact.full,

8 expression=>'1=1');

9 end;

10 /

PL/SQL procedure successfully completed.

**SQL> select \*from employee;**

ID **NAME** SALARY JOBDATE MOBILE

\---------- ---------- ---------- --------- --------------------

1             0   01-JUL-12 135-2009-1146

2             0   01-JUL-13 135-2009-1111

**full redaction的验证（date）**

SQL> begin dbms\_redact.alter\_policy(

2 object\_schema=>'scott',

3 object\_name=>'employee',

4 policy\_name=>'p1',

5 column\_name=>'jobdate',

6 action=>dbms\_redact.add\_column,

7 function\_type=>dbms\_redact.full,

8 expression=>'1=1');

9 end;

10 /

PL/SQL procedure successfully completed.

**SQL> select \*from employee;**

ID NAME SALARY  **JOBDATE** MOBILE

\---------- ---------- ---------- --------- --------------------

1           0  **01-JAN-01**135-2009-1146

2           0 **01-JAN-01**135-2009-1111

** 2、部分编写**

**partial redaction**的验证（**char**）

SQL> begin dbms\_redact.alter\_policy(

2 object\_schema=>'scott',

3 object\_name=>'employee',

4 policy\_name=>'p1',

5 column\_name=>'mobile',

6 action=>dbms\_redact.add\_column,

7 function\_type=>dbms\_redact.partial,

8 expression=>'1=1',

9 function\_parameters=>'VVVFVVVVFVVVV,VVV-VVVV-VVVV,\*,1,8');

10 end;

11 /

PL/SQL procedure successfully completed.

**SQL> select \*from employee;**

ID NAME SALARY JOBDATE**MOBILE**

\---------- ---------- ---------- --------- --------------------

1 0 01-JAN-01  **\*\*\*-\*\*\*\*-\*146**

2 0 01-JAN-01  **\*\*\*-\*\*\*\*-\*111**

**partial redaction**的验证（**number**）

**SQL> alter table employee add num number(38);**

Table altered.

**SQL> update employee set num=12345 where id=1;**

1 row updated.

**SQL> update employee set num=67890 where id=2;**

1 row updated.

**SQL> commit;**

Commit complete.

**SQL> select \*from employee;**

ID NAME SALARY JOBDATE MOBILE NUM

\---------- ---------- ---------- --------- -------------------- ----------

1 0 01-JAN-01 \*\*\*-\*\*\*\*-\*146 12345

2 0 01-JAN-01 \*\*\*-\*\*\*\*-\*111 67890

SQL> begin dbms\_redact.alter\_policy(

2 object\_schema=>'scott',

3 object\_name=>'employee',

4 policy\_name=>'p1',

5 column\_name=>'num',

6 action=>dbms\_redact.add\_column,

7 function\_type=>dbms\_redact.partial,

8 expression=>'1=1',

9 function\_parameters=>'9,1,3');

10 end;

11 /

PL/SQL procedure successfully completed.

**SQL> select \*from employee;**

ID NAME SALARY JOBDATE MOBILE**NUM**

\---------- ---------- ---------- --------- -------------------- ----------

1 0 01-JAN-01 \*\*\*-\*\*\*\*-\*146**99945**

2 0 01-JAN-01 \*\*\*-\*\*\*\*-\*111**99990**

**partial redaction**的验证（**date**）

SQL> begin dbms\_redact.alter\_policy(

2 object\_schema=>'scott',

3 object\_name=>'employee',

4 policy\_name=>'p1',

5 column\_name=>'jobdate',

6 action=>dbms\_redact.**drop\_column**,

7 expression=>'1=1')

8 ;

9 end;

10 /

PL/SQL procedure successfully completed.

**SQL> select \*from employee;**

ID NAME SALARY JOBDATE MOBILE NUM

\---------- ---------- ---------- --------- -------------------- ----------

1 0 01-JUL-12 \*\*\*-\*\*\*\*-\*146 99945

2 0 01-JUL-13 \*\*\*-\*\*\*\*-\*111 99990

SQL> begin dbms\_redact.alter\_policy(

2 object\_schema=>'scott',

3 object\_name=>'employee',

4 policy\_name=>'p1',

5 column\_name=>'jobdate',

6 action=>dbms\_redact.add\_column,

7 function\_type=>dbms\_redact.partial,

8 expression=>'1=1',

9 function\_parameters=>'Md15YHMS');----Md15YHMS:month day year hour minute second

10 end;

11 /

PL/SQL procedure successfully completed.

** SQL> select \*from employee;**

ID NAME SALARY  **JOBDATE** MOBILE NUM

\---------- ---------- ---------- --------- -------------------- ----------

1 0 **15-JUL-12**\*\*\*-\*\*\*\*-\*146 99945

2 0 **15-JUL-13** \*\*\*-\*\*\*\*-\*111 99990

**partial redaction**的验证

SQL> begin dbms\_redact.alter\_policy(

2 object\_schema=>'scott',

3 object\_name=>'employee',

4 policy\_name=>'p1',

5 column\_name=>'mobile',

6 action=>dbms\_redact.**drop\_column**);

7 end;

8 /

PL/SQL procedure successfully completed.

SQL> begin dbms\_redact.alter\_policy(

2 object\_schema=>'scott',

3 object\_name=>'employee',

4 policy\_name=>'p1',

5 column\_name=>'num',

6 action=>dbms\_redact.**drop\_column)**;

7 end;

8 /

PL/SQL procedure successfully completed.

**SQL> select \*from employee;**

ID NAME SALARY JOBDATE MOBILE NUM

\---------- ---------- ---------- --------- -------------------- ----------

1 0 15-JUL-12 135-2009-1146 12345

2 0 15-JUL-13 135-2009-1111 67890

SQL> begin dbms\_redact.alter\_policy(

2 object\_schema=>'scott',

3 object\_name=>'employee',

4 policy\_name=>'p1',

5 column\_name=>'mobile',

6 action=>dbms\_redact.add\_column,

7 function\_type=>dbms\_redact.random,

8 expression=>'1=1');

9 end;

10 /

PL/SQL procedure successfully completed.

SQL> begin dbms\_redact.alter\_policy(

2 object\_schema=>'scott',

3 object\_name=>'employee',

4 policy\_name=>'p1',

5 column\_name=>'num',

6 action=>dbms\_redact.add\_column,

7 function\_type=>dbms\_redact.random,

8 expression=>'1=1');

9 end;

10 /

PL/SQL procedure successfully completed.

**SQL> select \*from employee;**

ID NAME SALARY JOBDATE**MOBILENUM**

\---------- ---------- ---------- --------- -------------------- ----------

1 0 15-JUL-12  **)3;&xt\]Y1;C.! 9903**

2 0 15-JUL-13  **"Rf(LML)\*Zn0T 18940**

**SQL> select \*from employee;**

ID NAME SALARY JOBDATE**MOBILE NUM**

\---------- ---------- ---------- --------- -------------------- ----------

1 0 15-JUL-12  **NHP\*iNGYVPX2q 8443**

2 0 15-JUL-13  **pA,s<**

**用户可以对employee表进行dml操作，但是不能基于employee表进行ctas操作。如下：**

SQL> insert into employee values(3,'mouse',10000,to\_date('01-08-2013','dd-mm-yyyy'),'135-2009-1126',12345);

1 row created.

**SQL> commit; **

Commit complete.

**SQL> select \* from employee;**

ID NAME SALARY JOBDATE MOBILE NUM

\---------- ---------- ---------- --------- -------------------- ----------

1 0 15-JUL-12

2 0 15-JUL-13 @adG3r.LHilO; 20119

3 0 15-AUG-13 T\]@7MM(2eH?U 9883

**SQL> create table test as select \* from employee;**

create table test as select \* from employee

ERROR at line 1:

ORA-28081: Insufficient privileges - the command references a redacted object.

**3、修改policy使得其他用户可以访问到真实数据**

**当前用户是test**

**SQL> show user**

USER is "TEST"

SQL> select \*from ysf.employee;

ID NAME SALARY JOBDATE MOBILE NUM

\---------- ---------- ---------- --------- -------------------- ----------

1 0 15-JUL-12 =!'<\[j9.E)/Dc 12012

2 0 15-JUL-13 b6JNe.\`?j>RVm 44494

3 0 15-AUG-13 /.v~m-Gt76~'u 4632

**SQL> begin dbms\_redact.alter\_policy(**

**2 object\_schema=>'ysf',**

**3 object\_name=>'employee',**

**4 policy\_name=>'p1',**

**5 action=>dbms\_redact.modify\_expression,**

**6 expression=>'SYS\_CONTEXT(''USERENV'',''SESSION\_USER'') != ''TEST''');**

**7 end;**

**8 /**

PL/SQL procedure successfully completed.

**SQL> select \*from ysf.employee;**

ID NAME SALARY JOBDATE MOBILE NUM

\---------- ---------- ---------- --------- -------------------- ----------

1 tom 6000 01-JUL-12 135-2009-1146 12345

2 mary 9000 01-JUL-13 135-2009-1111 67890

3 mouse 10000 01-AUG-13 135-2009-1126 12345

让test用户看到真实数据的话，在policy中不能只针对某一列，这样的话不会生效。

**4、查看redaction信息：**

**8:00:04 SYS@ orcl> select \* from redaction\_policies**

OBJECT\_OWN OBJECT\_NAME          POLICY\_NAME          EXPRESSION           ENABLE  POLICY\_DESCRIPTION  
\---------- -------------------- -------------------- -------------------- ------- --------------------------------------------------  
SCOTT      employee                  p1                   SYS\_CONTEXT('USERENV YES

','SESSION\_USER') !=  
                                                      'TOM'  
Elapsed: 00:00:00.00

18:04:28 SYS@ orcl>col COLUMN\_NAME for a20  
18:04:47 SYS@ orcl>col FUNCTION\_PARAMETERS for a40  
**18:05:02 SYS@ orcl>select object\_owner,object\_name,column\_name,function\_type,function\_parameters  
  2\*  from redaction\_columns**

OBJECT\_OWN OBJECT\_NAME          COLUMN\_NAME          FUNCTION\_TYPE               FUNCTION\_PARAMETERS  
\---------- -------------------- -------------------- --------------------------- ----------------------------------------  
SCOTT      employee                   JOBDATE              PARTIAL REDACTION           Md15YHMS  
SCOTT      employee                 NUM                  PARTIAL REDACTION           9,1,3  
SCOTT      employee                   MOBILE               PARTIAL REDACTION           VVVFVVVVFVVVV,VVV-VVVV-VVVV,\*,1,8  
SCOTT     employee                  NAME                 FULL REDACTION  
SCOTT      employee                   SALARY               FULL REDACTION

17:18:14 SCOTT@ orcl>select \* from t1;

ID NAME           SALARY JOBDATE   MOBILE                      NUM  
\---------- ---------- ---------- --------- -------------------- ----------  
         1 tom              6000 01-JUL-12 135-2009-1146             12345  
         2 mary             9000 01-JUL-13 135-2009-1111             67890

Elapsed: 00:00:00.02

**以上文章内容参考部分网友的内容，在这里一并感谢！**

[Center]: /images/20220718/9046436cfea549cfb844c9a17e92adb7.png
[Center 1]: /images/20220718/f71f89839a1d446aa6de71364558104e.png
[Center 2]: /images/20220718/b0dbab9c13dd4f51aa008304cd221455.png