Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to encrypt Oracle Data Redaction data, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Overview of 1.Oracle Redaction

Oracle Data Redaction is an advanced function of Oracle security encryption class, which can be used to encrypt sensitive data. Encryption configuration processing is implemented at the Oracle level. This is a security-related technology category that allows specified users to restrict the display of encrypted values in certain columns of certain tables. Before Redaction, you may need to customize the encryption function, create a specific view, or encrypt it with an encryption algorithm when stored in the database. Redaction can encrypt the data directly, which will not affect the real storage of the data. It is transparent to the application and does not need to be changed.

For permissions, Redaction cannot encrypt data for sys and system users. Because they all have the role of EXP_FULL_DATABASE, which in turn contains EXEMPT REDACTION POLICY system permissions. At the same time, users can not be directly granted dba permissions, dba automatically includes the EXP_FULL_DATABASE role. During the test, it is found that for users with dba rights, the data of the table can be encrypted, but there is no actual encryption effect.

The commonly used encryption types are described as follows:

1.Full redaction: all the data of a field will be encrypted, and all columns of type number will be returned as 0st character type columns will be returned as spaces, and date type will be returned as yyyy-mm-dd.

2.Partial redaction: redact part of the data in the column. For example, the middle digits of ID card number or mobile phone number can be returned to *, and the remaining digits remain the same. This scenario is suitable for fixed length.

3.Regular expressions: partial encryption of non-fixed length character type data

4.Random redaction: random encryption. The encryption result is not necessarily the same each time.

The information of this encryption test environment is as follows:

OS version

Oracle version

Whether RAC

RHEL6.5

11.2.0.4.170418

Yes

2. Oracle Redaction encryption test 2.1 environment test preparation

This time, we only simulate several commonly used encryption scenarios, create users, tables, and grant corresponding permissions. Unless otherwise specified, the users who call DBMS_REDACT are all zhangxg users.

SQL > create user zhangxg identified by zhangxg

User created.

SQL > grant connect,resource to zhangxg

Grant succeeded.

SQL > grant select on sys.redaction_policies TO zhangxg

Grant succeeded.

SQL > grant select on sys.redaction_columns TO zhangxg

Grant succeeded.

SQL > grant execute on dbms_redact TO zhangxg

Grant succeeded.

SQL > create user nosee identified by zhangxg

User created.

SQL > grant connect,resource to nosee

Grant succeeded.

SQL > CREATE TABLE ZHANGXG.TAB1 (

2 "EMPLOYEE_ID" NUMBER (6. 0)

3 "FIRST_NAME" VARCHAR2 (20)

4 "LAST_NAME" VARCHAR2 (25)

5 "SOCIAL_SECURITY" VARCHAR2 (11)

6 "SALARY" NUMBER (4. 0)

7)

Table created.

SQL > insert into tab1 values (100,7000,85,9056th)

1 row created.

SQL > insert into tab1 values (101). Kochhart.

1 row created.

SQL > commit

Commit complete.

SQL > grant select on zhangxg.tab1 to nosee

Grant succeeded.

SQL > select * from tab1

EMPLOYEE_ID FIRST_NAME LAST_NAME SOCIAL_SECU SALARY

--

100 Steven King 247-85-9056 7000

101101 Neena Kochhar 334-08-6578 5000

2.2 encrypt some fields of the table

Call DBMS_REDACT package to create policy policy

SQL > BEGIN

2 DBMS_REDACT.ADD_POLICY (

3 object_schema = > 'ZHANGXG'

4 object_name = > 'TAB1'

5 policy_name = > 'REDACT_1'

6 column_name = > 'SOCIAL_SECURITY'

7 function_type = > DBMS_REDACT.PARTIAL

8 EXPRESSION = > '1cm 1'

9 function_parameters = > 'VVVFVVFVVVV,VVV-VV-VVVV,*,1,5'

10)

11 END

12 /

PL/SQL procedure successfully completed.

SQL >

SQL > select * from tab1

EMPLOYEE_ID FIRST_NAME LAST_NAME SOCIAL_SECURITY SALARY

--

100 Steven King * *-9056 7000

101 Neena Kochhar * *-6578 5000

SQL > SQL > conn nosee/zhangxg

Connected.

SQL >

SQL > select * from zhangxg.tab1

EMPLOYEE_ID FIRST_NAME LAST_NAME SOCIAL_SECU SALARY

--

100 Steven King * *-9056 7000

101 Neena Kochhar * *-6578 5000

Based on the encryption of a certain column, a garbled display column is added, that is, a multi-column encrypted display.

SQL > BEGIN

2 DBMS_REDACT.ALTER_POLICY (

3 object_schema = > 'ZHANGXG'

4 object_name = > 'TAB1'

5 policy_name = > 'REDACT_1'

6 action = > DBMS_REDACT.ADD_COLUMN

7 column_name = > 'LAST_NAME'

8 function_type = > DBMS_REDACT.RANDOM)

9 END

10 /

PL/SQL procedure successfully completed.

SQL > select * from tab1

EMPLOYEE_ID FIRST_NAME LAST_NAME SOCIAL_SECU SALARY

--

100 Steven K5r. * *-9056 7000

101 Neena ymP'@Ea * *-6578 5000

SQL > conn nosee/zhangxg

Connected.

SQL > select * from zhangxg.tab1

EMPLOYEE_ID FIRST_NAME LAST_NAME SOCIAL_SECU SALARY

--

100 Steven YZ$z * *-9056 7000

101 Neena HSTk} 5l * *-- 6578 5000

Authority Control of 2.3Redaction

For permission control, we can directly select users to filter, but if there are too many users, we can use role to control permissions.

The results seen by nosee users are also encrypted

SQL > show user

USER is "ZHANGXG"

SQL >

SQL > select * from tab1

EMPLOYEE_ID FIRST_NAME LAST_NAME SOCIAL_SECU SALARY

--

100 Steven NPnj * *-9056 7000

101 Neena ~

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.