This example takes VPD a bit further and also restricts access at the column level as well as shown in the example below – the earlier example will work in 8i and 9i, but this is an enhancement in 10g Release 2.
SQL> conn sysadmin_vpd/sysadmin
Connected.
CREATE THE SECURITY FUNCTION
SQL> create or replace function hidecol
(object_schema in varchar2, object_name in varchar2)
return varchar2 is d_predicate varchar2(2000);
BEGIN
d_predicate := ‘(ename = sys_context (”USERENV”, ”SESSION_USER”))’;
RETURN d_predicate;
END hidecol;
/
Function created.
BEFORE THE POLICY HAS BEEN APPLIED TO THE TABLE ALL THE ROWS ARE VISIBLE
SQL> select count(*) from scott.emp;
COUNT(*)
———-
14
ADD THE POLICY TO THE EMP TABLE – NOTE THE SAL,HIREDATE AND COMN COLUMNS ARE FURTHER RESTRICTED
SQL> BEGIN
dbms_rls.add_policy(object_schema => ‘scott’,
object_name => ’emp’,
policy_name => ‘col_policy’,
function_schema => ‘sysadmin_vpd’,
policy_function => ‘hidecol’,
statement_types => ‘select’,
sec_relevant_cols => ‘sal,hiredate,comm’);
END;
/
PL/SQL procedure successfully completed.
USER SYSADMIN_VPD DOES NOT HAVE ANY RECORDS IN THE EMP TABLE, SO THE QUERY WILL RETURN NO ROWS AFTER POLICY IS APPLIED
SQL> select count(*) from scott.emp;
COUNT(*)
———-
0
CONNECT AS A VALID USER – MILLER
SQL> conn miller/miller
Connected.
SQL> /
COUNT(*)
———-
1
NOTE- SINCE THE COLUMNS THAT HAVE BEEN RESTRICTED ARE NOT USED THE QUERY THE USER CAN ACCESS ALL ROWS IN THE TABLE
SQL> select ename from scott.emp;
ENAME
———-
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
WHEN THE COLUMN SAL IS INCLUDED IN THE QUERY, ONLY HIS RECORDS ARE VISIBLE
SQL> select ename,sal from scott.emp;
ENAME SAL
———- ———-
MILLER 1300
USING THE DBMS_RLS.ALL_ROWS OPTION TO DISPLAY COLUMNS WITHOUT THE DATA
SQL> CONN sysadmin_vpd/sysadmin
Connected.
SQL> exec dbms_rls.drop_policy(‘SCOTT’,’EMP’,’COL_POLICY’);
PL/SQL procedure successfully completed.
SQL> BEGIN
dbms_rls.add_policy(object_schema => ‘scott’,
object_name => ’emp’,
policy_name => ‘col_policy’,
function_schema => ‘sysadmin_vpd’,
policy_function => ‘hidecol’,
statement_types => ‘select’,
sec_relevant_cols => ‘sal,hiredate,comm’,
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;
/
PL/SQL procedure successfully completed.
NOTE ALL THE ROWS IN THE TABLE ARE RETURNED, BUT ONLY SALARY FOR MILLER IS DISPLAYED
SQL> conn miller/miller
Connected.
SQL> select ename,job,sal from scott.emp;
ENAME JOB SAL
———- ——— ———-
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK 1300
14 rows selected.
Gavin Soorma
