Virtual Private DataBase

In 10g, VPD has beed extedned to inculde column - level VPD. In which security policies are applied only when a particular column or columns are accessed etc..

Initial Config:

VPD- allows you to customize the ways different users see the same table.

ie., VPD - Requires the use of a package named DPMS_RLS. From the SYSDBA - Privilege account grant execute privilege on the DBMS_RLS package to all users

Virtual Private Database (VPD) Enhancements

* Column-Level VPD Policy
* Column Masking
* Policy Types
* Application Context Support for Parallel Queries

Column-Level VPD Policy

SQL> CONNECT SYS AS SYSDBA
Connected.

SQL> GRANT EXECUTE ON DBMS_RLS TO BABU;

Grant succeeded.

SQL> CONNECT BABU/BABU@KEYSTONE
Connected.

SQL> DROP TABLE EMP;

Table dropped.

SQL> CREATE TABLE EMP ( ENO INT, ENAME VARCHAR2(22), SAL INT);

Table created.

SQL> INSERT INTO EMP VALUES (101,'BABU',5000);

1 row created.

SQL> INSERT INTO EMP VALUES (102,'TAJ',6000);

1 row created.

SQL> INSERT INTO EMP VALUES (103,'SRI',7000);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM EMP;

ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ 6000
103 SRI 7000

SQL> CREATE OR REPLACE FUNCTION JOB_COL_VPD (OOWNER IN VARCHAR2,OJNAME IN VARCHAR2)
2 RETURN VARCHAR2 AS
3 CON VARCHAR2(100);
4 BEGIN
5 CON := 'SAL=5000' ;
6 RETURN (CON);
7 END JOB_COL_VPD;
8 /

Function created.

SQL> BEGIN
2 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST',
5 FUNCTION_SCHEMA => 'BABU',
6 POLICY_FUNCTION => 'JOB_COL_VPD',
7 SYS_RELEVANT_COLS => 'SAL');
8 END;
9 /
DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'BABU',
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'ADD_POLICY'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST',
5 FUNCTION_SCHEMA => 'BABU',
6 POLICY_FUNCTION => 'JOB_COL_VPD',
7 SEC_RELEVANT_COLS => 'SAL');
8* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM EMP;

ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000

SQL> BEGIN
2 DBMS_RLS.DROP_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST');
5 END;
6 /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM EMP;

ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ 6000
103 SRI 7000

Column Masking

SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_RLS.ADD_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST',
5 FUNCTION_SCHEMA => 'BABU',
6 POLICY_FUNCTION => 'JOB_COL_VPD',
7 SEC_RELEVANT_COLS => 'SAL',
8 SEC_RELEVANT_COLS_OPT => DBMS_RLS.ALL_ROWS);
9* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM EMP;

ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ
103 SRI

SQL> ED
Wrote file afiedt.buf

1 BEGIN
2 DBMS_RLS.DROP_POLICY ( OBJECT_SCHEMA => 'BABU',
3 OBJECT_NAME => 'EMP',
4 POLICY_NAME => 'FIRST_TEST');
5* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM EMP;

ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ 6000
103 SRI 7000


FGA- Now inculude DML statements in addition to quweies.

SQL> SHOW USER
USER is "BABU"
SQL> CONNECT SYS AS SYSDBA
Connected.

SQL>

SQL> SHOW USER
USER is "SYS"


SQL> TRUNCATE TABLE FGA_LOG$;

Table truncated.

SQL>
SQL> BEGIN
2 DBMS_FGA.ADD_POLICY
3 (
4 OBJECT_SCHEMA => 'BABU',
5 OBJECT_NAME => 'EMP',
6 POLICY_NAME => 'FIRST_FGA',
7 AUDIT_CONDITION => NULL,
8 AUDIT_COLUMNS => 'ENO,SAL,ENAME',
9 STATEMENT_TYPES => 'INSERT,UDPATE,DELETE,SELECT');
10 END;
11 /
DBMS_FGA.ADD_POLICY
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'ADD_POLICY'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored


SQL> SHOW ERROR
No errors.
SQL> SELECT * FROM USER_ERRORS
2 /

no rows selected


SQL> begin
2 DBMS_FGA.add_policy ( object_schema => 'BABU', object_name => 'EMP', policy_name => 'FIRST', audit_condition => NULL , audit_column => 'ENO,ENAME,SAL', statement_types => 'SELECT,INSERT,UPDATE,DELETE');
3 END;
4 /

PL/SQL procedure successfully completed.

SQL> connect babu/babu@keystone
Connected.
SQL>
SQL> show user
USER is "BABU"

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$yZPDgfriR8OKWMUf5FlP+Q==$0 TABLE
EMP TABLE
BONUS TABLE
DEPT TABLE
SALGRADE TABLE
PLAN_TABLE TABLE
INVOICE_LINE TABLE

7 rows selected.

SQL> select * from EMP;

ENO ENAME SAL
---------- ---------------------- ----------
101 BABU 5000
102 TAJ 6000
103 SRI 7000

SQL> INSERT INTO EMP VALUES ( 104,'MD', 10000);

1 row created.

SQL> COMMIT
2 /

Commit complete.

SQL> UPDATE EMP SET SAL=500 WHERE ENO=104;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> DELETE FROM EMP WHERE ENO=101;

1 row deleted.

SQL> SELECT * FROM EMP;

ENO ENAME SAL
---------- ---------------------- ----------
102 TAJ 6000
103 SRI 7000
104 MD 500



SQL> CONNECT SYS AS SYSDBA
Connected.
SQL>
SQL> SELECT sql_text FROM dba_fga_audit_trail;

SQL_TEXT
--------------------------------------------------------------------------------
select * from EMP
INSERT INTO EMP VALUES ( 104,'MD', 10000)
UPDATE EMP SET SAL=500 WHERE ENO=104
DELETE FROM EMP WHERE ENO=101
SELECT * FROM EMP


SQL> ED
Wrote file afiedt.buf

1 BEGIN
2 DBMS_FGA.drop_policy ( object_schema => 'BABU', object_name => 'EMP', plicy_name => 'FIRST' );
3* END;
SQL> /
DBMS_FGA.drop_policy ( object_schema => 'BABU', object_name => 'EMP', plicy_name => 'FIRST' );
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'DROP_POLICY'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored


SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 DBMS_FGA.drop_policy ( object_schema => 'BABU', object_name => 'EMP', policy_name => 'FIRST' );
3* END;
SQL> /

PL/SQL procedure successfully completed.

Uniform Audit Trail

The DBA_COMMON_AUDIT_TRAIL view has been added to display the complete audit trail:

* DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
* DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
* DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.