Hiding sensitive data with oracle10g column value masking technique

Oracle's virtual Private database features, also known as fine-grained access control, provide row-level security checks on data management language DML statements such as SELECT. The Pl/sql policy function is associated with a data table that can examine the context background of the current user and add the condition (assertion) of the WHERE statement in the query, a user or application that can write:

SELECT * FROM Employees;
But in practice Oracle will execute the statement:
SELECT * FROM Employees
            WHERE department_id = 60;

Therefore, only rows within the lookup range (the first 60 rows in the department datasheet) are returned by the query statement. With the new options in Oracle 10g, Oracle can return all rows, not just authorized rows. However, some columns that are not included in the authorization line (called security-related columns) will display NULL instead of the actual data, and the other column values will display normally.

To use a column value mask, you must do two things in a virtual private database policy.

You must first create a column-level policy to design some columns that are listed as security-related. Second, you must include the All_rows option in the query to return all rows. The combination of these two parameters enables you to implement a column-value mask.

CREATE OR REPLACE
FUNCTION rls_dept 
(Obj_owner in VARCHAR2, obj_name in VARCHAR2) return
VARCHAR2
as
predicate                 
VARCHAR2 ();
BEGIN
predicate: = ' department_id = ';
return (predicate);
End Rls_dept;
            /

List A

List A shows a policy function called rls_dept. It returns the assertion "Department_id=60", which is used to set the Department field within 60 rows of the Employees table. (Actually, this function does not return a static table, it can determine who the current user is, and return the correct departmental value to the user accordingly.) ）

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;
            /

List B

List B shows how to apply a function in List A to create a column-value mask. The procedure in the Dbms_rls package Add_policy create a new policy called Restrict_dept_policy. The parameter sec_relevant_cols indicates that the field salary and commission_pct are security-related columns. A query that contains the two fields above will be applied to the policy function, and queries that are not included will not apply the policy. Finally, the parameter sec_relevant_cols_opts is set to constant all_rows.

The column value mask is applied to the SELECT statement, and no matter which client accesses the database, the column value mask can be enforced, such as SQL *plus,. NET application or other tools.