[OracleDatabase12cNewFeature] AdvancedSecurity

1. What is OracleDataRedaction? I translate it into data rewriting. This is a new Oracle Advanced Security component that appears in 12c. It restricts the return result style of SQL statements. For specific users, You can restrict certain fields to display automatically rewritten values. This is a very useful security function.

1. What is Oracle Data Redaction? I did not confirm what the official Chinese translation is. I translated it into Data rewriting. This is a new Oracle Advanced Security component that appears in 12c. It restricts the return result style of SQL statements. For specific users, You can restrict certain fields to display automatically rewritten values. This is a very useful security function.

1. What is Oracle Data Redaction?
I am not sure what the official Chinese translation is. I translate it into data rewriting. This is a new Oracle Advanced Security component that appears in 12c. It restricts the return result style of SQL statements. For specific users, You can restrict certain fields to display automatically rewritten values. This is a very useful security feature. To implement the same features before 12c, you may need to create a specific view or use an encryption algorithm to encrypt the data stored in the database. The data rewriting function of 12c will rewrite the data one minute before the last data is returned to the client, which does not affect the real data storage. Let's look at a simple example.

The data records originally stored are as follows:

SQL> SELECT * FROM EMPLOYEES;?EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY----------- -------------------- ------------------------- ----------- ----------        100 Steven               King                      247-85-9056       7000        101 Neena                Kochhar                   334-08-6578       5000

After setting the Data Redaction, execute the same statement again. You can see that the private column-the social security number is overwritten and only the last four digits are displayed, this achieves the goal of security.

SQL> SELECT * FROM kamus.employees;?EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY----------- -------------------- ------------------------- ----------- ----------        100 Steven               King                      ***-**-9056       7000        101 Neena                Kochhar                   ***-**-6578       5000

2. How to Set Oracle Data Redaction
Set the appropriate permissions for the user. Because Oracle Redaction is ineffective for DBA users, You need to revoke the DBA role.

REVOKE dba FROM kamus;GRANT CONNECT, resource, unlimited tablespace TO kamus;GRANT SELECT ON sys.redaction_policies TO kamus;GRANT SELECT ON sys.redaction_columns TO kamus;GRANT EXECUTE ON dbms_redact TO kamus;

Create a test environment, including test tables and test data.

CREATE TABLE "EMPLOYEES" ("EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20), "LAST_NAME" VARCHAR2(25), "SOCIAL_SECURITY" VARCHAR2(11), "SALARY" NUMBER(4,0))/?INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SOCIAL_SECURITY,SALARY) VALUES (100,'Steven','King','247-85-9056',7000);INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SOCIAL_SECURITY,SALARY) VALUES (101,'Neena','Kochhar','334-08-6578',5000);commit;?SQL> SELECT * FROM EMPLOYEES;?EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY----------- -------------------- ------------------------- ----------- ----------        100 Steven               King                      247-85-9056       7000        101 Neena                Kochhar                   334-08-6578       5000

Oracle Redaction sets a field in a single table, which is controlled by the object_name and column_name of the ADD_POLICY stored procedure. There are multiple types of Redaction. The first type is FULL, that is, completely rewrite, A field of the character type is rewritten as a space, a field of the digit type is rewritten to 0, and a field of the date type is rewritten to. The type is controlled by the function_type parameter.

BEGINDBMS_REDACT.ADD_POLICY (   object_schema          => 'KAMUS',   object_name            => 'EMPLOYEES',   policy_name            => 'REDACT_EMP',   column_name            => 'SOCIAL_SECURITY',   function_type          => DBMS_REDACT.FULL,   expression             => '1=1',   enable                 => TRUE   );END;/?SQL> SELECT * FROM employees;?EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY----------- -------------------- ------------------------- ----------- ----------        100 Steven               King                                        7000        101 Neena                Kochhar                                     5000

The second type of Redaction is RANDOM. For a character-type field, it is rewritten to a RANDOM character, and for a numeric field, it is rewritten to a RANDOM number with the same length, replace a field of the date type with a random date (never the same as a real date ).

BEGINDBMS_REDACT.ALTER_POLICY (   object_schema          => 'KAMUS',   object_name            => 'EMPLOYEES',   policy_name            => 'REDACT_EMP',   column_name            => 'SOCIAL_SECURITY',   action                 => DBMS_REDACT.MODIFY_COLUMN,   function_type          => DBMS_REDACT.RANDOM);END;/?SQL> SELECT * FROM kamus.employees;?EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY----------- -------------------- ------------------------- ----------- ----------        100 Steven               King                      wa};w~ZC i&       7000        101 Neena                Kochhar                   Q9N]##T/YAV       5000

The third type of Redaction is PARTIAL, Which is rewritten to the format specified by the function_parameters parameter.

BEGINDBMS_REDACT.ALTER_POLICY (   object_schema          => 'KAMUS',   object_name            => 'EMPLOYEES',   policy_name            => 'REDACT_EMP',   column_name            => 'SOCIAL_SECURITY',   action                 => DBMS_REDACT.MODIFY_COLUMN,   function_type          => DBMS_REDACT.PARTIAL,   function_parameters    => 'VVVFVVFVVVV,VVV-VV-VVVV,*,1,5');END;/?SQL> SELECT * FROM kamus.employees;?EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY----------- -------------------- ------------------------- ----------- ----------        100 Steven               King                      ***-**-9056       7000        101 Neena                Kochhar                   ***-**-6578       5000

3. How to precisely set users to enable data Rewriting
Set the expression parameter, as shown in the following figure. All users that are not KAMUS are enabled in EMPLOYEES. the data on the SOCIAL_SECURITY field is rewritten. If the data is retrieved by a KAMUS user, the actual data is returned.

BEGINDBMS_REDACT.ALTER_POLICY (   object_schema          => 'KAMUS',   object_name            => 'EMPLOYEES',   policy_name            => 'REDACT_EMP',   column_name            => 'SOCIAL_SECURITY',   action                 => DBMS_REDACT.MODIFY_EXPRESSION,   expression             => 'SYS_CONTEXT ( ''USERENV'',''SESSION_USER'' ) !=''KAMUS''');END;/

The expression parameter is very flexible. With this parameter, Data Redaction can play a huge role. Shows the optional values of the expression parameter.

4. How to add a data rewriting Policy
You can use the DBMS_REDACT.ALTER_POLICY stored procedure to add data rewriting policies for multiple fields, such as the following example, to add full data rewriting on the SALARY field.

BEGINDBMS_REDACT.ALTER_POLICY (   object_schema          => 'KAMUS',   object_name            => 'EMPLOYEES',   policy_name            => 'REDACT_EMP',   column_name            => 'SALARY',   action                 => DBMS_REDACT.ADD_COLUMN,   function_type          => DBMS_REDACT.FULL,   expression             => 'SYS_CONTEXT ( ''USERENV'',''SESSION_USER'' ) !=''KAMUS''');END;/?SQL> SELECT * FROM kamus.employees;?EMPLOYEE_ID FIRST_NAME           LAST_NAME                 SOCIAL_SECU     SALARY----------- -------------------- ------------------------- ----------- ----------        100 Steven               King                      ***-**-9056          0        101 Neena                Kochhar                   ***-**-6578          0

5. What are the restrictions and stages of data rewriting?

-- Fields in the where condition are not affected by data rewriting. Even if SALARY = 0 is displayed at the end, the where condition is still executed normally and the correct results are filtered out. SQL> SELECT * FROM kamus. employees WHERE SALARY> 5000 ;? EMPLOYEE_ID FIRST_NAME LAST_NAME SOCIAL_SECU SALARY ----------- -------------------- ------------------------- ----------- ---------- 100 Steven King ***-**-9056 0? SQL> SELECT * FROM kamus. employees WHERE SALARY SELECT * FROM kamus. employees WHERE SALARY = 5000 ;? EMPLOYEE_ID FIRST_NAME LAST_NAME SOCIAL_SECU SALARY ----------- ----------------------------------------- -------------- 101 Neena Kochhar ***-**--6578 0? -- The full table CTAS operation cannot be performed on the table with the data rewrite policy enabled. SQL> CREATE TABLE t AS SELECT * FROM kamus. employees; create table t as select * FROM kamus. employees * ERROR at line 1: ORA-28081: Insufficient privileges-the command REFERENCES a redacted object .? -- If the data rewriting policy is not enabled for fields in the CTAS operation, the CTAS can be normally performed. SQL> CREATE TABLE t AS SELECT EMPLOYEE_ID, FIRST_NAME FROM kamus. employees ;? TABLE created .? SQL> DROP TABLE t ;? TABLE dropped .? -- If it contains a column that enables a Data override policy, a ORA-28081 error is reported. SQL> CREATE TABLE t AS SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM kamus. employees; create table t as select EMPLOYEE_ID, FIRST_NAME, salary from kamus. employees * ERROR at line 1: ORA-28081: Insufficient privileges-the command REFERENCES a redacted object.

Share/Save

Related posts:

1. Oracle 11g New Feature-Partition
2. Oracle 11g new feature-Virtual Column
3. Oracle Database Instance Startup Fails With Error ORA-27302 ORA-27301