Database Security Enhancements in Oracle Database 10g

VIRTUAL PRIVATE DATABASE (VPD) ENHANCEMENTS

2. COLUMN MASKING

column masking behaviour is implemented by using
the "sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS" parameter.
this allows you to display all rows but mask the values
of the specified columns for the restricted rows:

EXAMPLE
-----------
CREATE TABLE employee
(
empno NUMBER PRIMARY KEY,
ename VARCHAR2(30),
ejob VARCHAR2(30),
esalary NUMBER,
ecomments VARCHAR2(30),
edeptno NUMBER
);

INSERT INTO employee VALUES(1,'kuamr','DBD',10000,'',10);
INSERT INTO employee VALUES(2,'ram','PHP',20000,'',20);
INSERT INTO employee VALUES(3,'raja','DBD',30000,'',10);
INSERT INTO employee VALUES(4,'kanna','PHP',40000,'',20);
INSERT INTO employee VALUES(5,'karna','DBD',50000,'',30);
INSERT INTO employee VALUES(6,'arun','DBD',60000,'',3);
INSERT INTO employee VALUES(7,'mani','DBD',70000,'',10);


-- create the policy function to restrict access to sal and comm columns
-- if the employee is not part of the department 20.

CREATE OR REPLACE FUNCTION pf_job (OOWNER IN VARCHAR2, OJNAME IN VARCHAR2)
RETURN VARCHAR2 AS
CON VARCHAR2 (200);
BEGIN
CON := 'deptno = 20';
RETURN (CON);
END pf_job;

-- now create the policy to restrict to salary and
-- comments for employee belongs to the department 20

DECLARE
VRESTRICTDEPT VARCHAR2(200);
BEGIN
VRESTRICTDEPT := 'deptno = 20';
DBMS_RLS.ADD_POLICY (OBJECT_SCHEMA => 'test',
OBJECT_NAME => 'e_employee',
POLICY_NAME => 'sp_job',
FUNCTION_SCHEMA => 'test',
POLICY_FUNCTION => 'pf_job',
SEC_RELEVANT_COLS => 'salary,comm',
SEC_RELEVANT_COLS_OPT => DBMS_RLS.ALL_ROWS);

EXCEPTION WHEN OTHERS THEN
NULL;
END;


-- when we select the fields which not come unders the restriction, then

SELECT EMPNO, ENAME, JOB FROM employee;


EMPNO ENAME JOB
-------------------------
1 KUAMR DBD
2 RAM PHP
3 RAJA DBD
4 KANNA PHP
5 KARNA DBD
6 ARUN DBD
7 MANI DBD

-- while selecting the field which is issued in the policy for
-- restriction then we get,

SELECT empno, ename, job, salary, comm FROM employee;

EMPNO ENAME JOB SALARY COMM
---------------------------------------------------------
1 KUAMR DB
2 RAM PHP 20000
3 RAJA DB
4 KANNA PHP 40000
5 KARNA DB
6 ARUN DB
7 MANI DB

-- now we get the results based on the department 20 which
-- is used in the policy and we cannot see other person details.


-- Remove the policy function from the table.

BEGIN
DBMS_RLS.DROP_POLICY (OBJECT_SCHEMA => 'test',
OBJECT_NAME => 'employee',
POLICY_NAME => 'sp_job');
END;

__________________
Thanks,
v.kumarasan@gmail.com