News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Virtual Private Database – part 2

  • Posted by Gavin Soorma
  • On June 19, 2009
  • 0 Comments

 

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.

 

 

 

 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *