How to hide sensitive data using oracle 10 Gb column value Mask Technology

The Virtual Private Database feature of Oracle is also known as fine-grained access control. It provides row-level security checks for DML statements in data management languages such as SELECT. PL/SQL policy functions are associated with a data table. This function can check the context of the current user and add conditional assertions for the WHERE statement in the query). A user or application can write the statements as follows:

SELECT * FROM employees;

However, oracle will execute the following statement:

SELECT * FROM employees WHERE department_id = 60;

Therefore, only the first 60 rows in the department data table within the search range are returned by the query statement. The new oracle 10 GB option allows oracle to return all rows, not just authorized rows. However, some columns that are not included in the authorization row are referred to as security-related columns. NULL is displayed instead of actual data, and other column values are displayed normally.

To use the column value mask, you must do two things in the Virtual Private Database policy.

First, you must create a column-level policy to design columns related to security. Second, you must include the ALL_ROWS option in the query to return all rows. The combination of these two parameters enables the column value mask.

List A shows A policy function called rls_dept. It returns the asserted "department_id = 60", which is used to set the department field within 60 rows in the EMPLOYEES table. In fact, this function does not return a static table. It can determine who the current user is and return the correct department value to the user accordingly .)

List B shows how to apply the function in list A to create A column value mask. Create a new policy called restrict_dept_policy during the ADD_POLICY process in the DBMS_RLS package. The sec_relevant_cols parameter indicates that the salary and commission_pct fields are security-related columns. A query that contains the preceding two fields will be applied to this policy function. A query that does not contain the preceding two fields will not apply this policy. Finally, set the sec_relevant_cols_opts parameter to the constant ALL_ROWS.

The column value mask is applied to SELECT statements. No matter which client accesses the database, the column value mask can be implemented, such as SQL * Plus,. NET applications, or other tools.

List:

CREATE OR REPLACE FUNCTION rls_dept (obj_owner IN VARCHAR2, obj_name IN VARCHAR2) RETURN VARCHAR2 AS predicate                  VARCHAR2 (200); BEGIN predicate := 'department_id = 60'; RETURN (predicate); END rls_dept; /

List B:

BEGIN DBMS_RLS.ADD_POLICY(object_schema=>'HR', object_name=>'EMPLOYEES', policy_name=>'restrict_dept_policy', function_schema=>'HR', policy_function=>'rls_dept', sec_relevant_cols=>'salary,commission_pct', sec_relevant_cols_opt=>dbms_rls.ALL_ROWS); END; /