Overview and simple example of Oracle Virtual Private Database (VPD)

 

Overview and simple example of Oracle Virtual Private Database (VPD)

1. Overview of Oracle Virtual Private Database (VPD)

VPD provides better row-level access control than roles and views. For example, in internet access, a virtual private database ensures that online bank users can only view their own accounts. Internet application service companies can maintain data of multiple companies in one database, while ensuring that each company can only view its own data.

Within an enterprise, VPD reduces the overhead of application deployment. Security only needs to be constructed once on the data server, instead of deployed on each data access application. This is more secure, because the database enforces the application of security policies, regardless of the way a user accesses data. You can no longer use a query tool (such as SQL * PLUS) or report software (such as Informaker) to skip the security policies compiled in the application. VPD is a key technology for enterprises to build Internet applications.

2. Working Principle of VPD

VPD takes effect by applying one or more security policies to tables or views. Directly or indirectly accessing a table that applies a security policy generates an access condition, that is, a "Predicate" (WHERE clause) is generated ), the database adds the predicate to the user's SQL statement to dynamically modify the user's data access.

You can add an SQL statement predicate to each SQL statement by writing a stored procedure to control row-level access to this statement. For example, if John (belonging to dept 10) inputs the select * from emp statement, you can use VPD to add the where dept = 10 clause, in this way, you can rewrite the query statement to restrict data access to a specific row.

VPD ensures that no matter how users access data (through applications, report tools, or SQL * PLUS), they will encounter an equally powerful access control policy. Therefore, VPD can help banks ensure that their users can only view their own accounts. telecom companies can ensure that the records of each user are securely isolated. HR applications can support complex rules for accessing employee records.

3. The following is a simple example to introduce the basic implementation process of VPD for your reference only:

Objectives:

A. Use VPD to implement A security policy: restrict users to view their own records only in the EMP table;

B. Create a policy function;

C. Add a new security policy;

D. Test VPD.

Configuration instructions:

Oracle version: Oracle 10g 10.2.0.4.0

4. The process is as follows:

A. Create A policy function to restrict users to view their records only in the EMP table:

SQL> conn/as sysdba

Connected.

SQL> create or replace function func1 (

2 p_schema in varchar2,

3 p_object in varchar2)

4 return varchar2

5

6 begin

7 return 'ename = sys_context (''userenv'', ''session _ user '')';

8 end;

9/

 

The function has been created.

B. Add this new security policy using the DBMS_RLS package. Specify the EMP table that the policy applies to SCOTT mode. The Policy Name Is MY_POLICY. The policy function used is FUNC1. The policy applies to DELETE, UPDATE, the SELECT statement takes effect:

Note: After a policy is specified for a table under a user, if the table does not contain user name information, no messages can be queried.

SQL> begin

2 dbms_rls.add_policy (

3 object_schema => 'Scott ',

4 object_name => 'emp'

5,

6. policy_name => 'my _ Policy'

7,

8 policy_function => 'function1'

9,

10 statement_types => 'select, UPDATE, delete ');

11 END;

12/

 

The PL/SQL process is successfully completed.

C. Test VPD. First, test the query policy: connect to the database with SCOTT. After the EMP table is applied, the query cannot return the record of the entire table. You can only view your own information:

SQL> conn scott/tiger

Connected.

SQL> select * from emp;

 

EMPNO ENAME JOB MGR HIREDATE SAL COMM

-------------------------------------------------------------------------

DEPTNO

----------

7788 scott analyst 7566 3000

20

D. Check update policy: update the salaries of all employees whose DEPTNO is 20. Only one SCOTT record is updated:

SQL> conn scott/tiger

Connected.

SQL> update emp set sal = sal + 10 where deptno = 20;

 

1 row updated.

 

SQL> select * from emp;

 

EMPNO ENAME JOB MGR HIREDATE SAL COMM

-------------------------------------------------------------------------

DEPTNO

----------

7788 scott analyst 7566 3010

20

E. Check the deletion policy: Delete the SMITH user records in the EMP table and find records that do not meet the conditions. Delete all records in the EMP table and only delete the records of the SCOTT User:

SQL> delete emp where ename = 'Smith'

2/

 

0 rows have been deleted.

 

SQL> delete emp;

 

One row has been deleted.

 

As can be seen from the above, the security policy has been applied on the EMP table, And the WHERE clause is automatically returned to control access when the query is executed.

5. Summary

A. the application context is A series of "attributes and values" pairs (such as session_user and scott) that record user sessions stored in the memory ). Oracle provides the default application context for each database session, that is, the USERENV namespace, which records session attributes such as IP_ADDRESS, TERMINAL, SESSION_USER, and SESSION_USERID. In addition to the default application context USERENV, you can also customize the application context. In this example, the SESSION_USER attribute provided by USERENV is used when the policy function FUNC1 is created.

B. Use the DBMS_RLS package to add, update, and delete a security policy.