Oracle handbook Series 6: VPD

VPD: Oracle Virtual Private Database, that is, [Oracle Virtual Private Database]. By applying some policies, you can only access the data that is allowed to be accessed. The principle is relatively simple. Oracle automatically adds a Where clause for the statement submitted by the corresponding user according to the policy to control the data that the user can access and operate on.

 

First, we prepare several tables for demonstration and insert a small amount of test data:

Create table vpdsample_clothing (
Clothing_id NUMBER,
Type VARCHAR2 (30 ),
Brand VARCHAR2 (30 ),
Descripton VARCHAR2 (100)
);
--
Insert into vpdsample_clothing VALUES (10002, 'jacket ', 'abc', 'autumn style ');
Insert into vpdsample_clothing VALUES (10003,'t-shirt ', 'xyz', 'summer style ');
Commit;

Create table vpdsample_books (
Book_id NUMBER,
Name VARCHAR2 (30 ),
Author VARCHAR2 (20)
);
--
Insert into vpdsample_books VALUES (10005, 'country Driving ', 'Peter Hessler ');
Insert into vpdsample_books VALUES (10006, 'life without limits ', 'Nick Vujicic ');
Commit;
(The above two tables simulate a simple inventory. There are two types of items in the Library: clothing and books. Here we need to satisfy the uniqueness of the IDs in the two tables (which can be achieved through sequence ).)
 

 

Create table vpdsample_users (
User_name VARCHAR2 (20 ),
User_privilege NUMBER
);
--
Insert into vpdsample_users VALUES ('jack', 1 );
Insert into vpdsample_users VALUES ('Rose ', 2 );
COMMIT;
(This table stores user permission information. The permission is the permission ID field in the vpdsample_privileges table .)

Create table vpdsample_privileges (
Privilege_id NUMBER,
Object_id NUMBER
);
--
Insert into vpdsample_privileges VALUES (1,10002 );
Insert into vpdsample_privileges VALUES (1,10005 );
Insert into vpdsample_privileges VALUES (2,10003 );
Insert into vpdsample_privileges VALUES (2,10006 );
COMMIT;
(This table stores the permission information corresponding to each permission ID, that is, what objects (clothing or books) have permissions .)

 

Step 2: create a context (actually the context namespace ]). You can simply understand context as a container defined in the memory. In this container, we can define several key-value pairs, these key-value pairs can be shared within a certain range (for example, in the same session or in the same Oracle instance)

First, use the system user to log on and grant the Creator the following permissions:

Grant create any context to user1;

Then pass:

Create or replace context vpd using pkg_vpdsample accessed globally;

Here we have created a context named 'vp', followed by a PLSQL package name. For security reasons, Oracle requires you to specify a package name when creating the context, it indicates that subsequent modifications to some context can only be made through the stored procedure in this package, but cannot be directly modified through dbms_session.set_context. When the context is created, the package does not exist and does not cause compilation errors.

The last 'accesssed globally 'is an optional option. If this option is not added, the range of the context is a session. If this option is specified, the context can be shared across the database instance.

 

To delete a context, you must grant the following permissions:

Grant drop any context to user1;

Drop context VPD;

 

Step 3: Create a package (pkg_vpdsample above). The functions of each function and stored procedure in the package will be given one by one:

Create or replace package pkg_vpdsample
IS
PROCEDURE enable_vpd;
PROCEDURE disable_vpd;
PROCEDURE set_context (p_user_name IN VARCHAR2 );
FUNCTION gen_vpd_predicate (p_column_name IN VARCHAR2) RETURN VARCHAR2;
FUNCTION apply_vpd_clothing (p1 in varchar2, p2 in varchar2) RETURN VARCHAR2;
FUNCTION apply_vpd_books (p1 in varchar2, p2 in varchar2) RETURN VARCHAR2;
END;
 
Create or replace package body pkg_vpdsample IS
PROCEDURE enable_vpd IS
BEGIN
DBMS_SESSION.set_context (namespace => 'vps ',
Attribute => 'enable ',
Value => '1 ');
END;

/* =================================== */
PROCEDURE disable_vpd IS
BEGIN
DBMS_SESSION.set_context (namespace => 'vps ',
Attribute => 'enable ',
Value => '0 ');
END;

/* =================================== */
PROCEDURE set_context (p_user_name IN VARCHAR2) IS
Rochelle privilege VARCHAR2 (10 );
BEGIN
SELECT user_privilege
INTO l_privilege
FROM vpdsample_users
WHERE user_name = p_user_name;
DBMS_SESSION.set_identifier (client_id => l_privilege );
END;

/* =================================== */
FUNCTION gen_vpd_predicate (p_column_name IN VARCHAR2) RETURN VARCHAR2 IS
Rochelle vpd_flag VARCHAR2 (1 );
Rochelle privilege VARCHAR2 (10 );
BEGIN
Rochelle vpd_flag: = NVL (SYS_CONTEXT ('vp', 'enable'), '0 ');
IF l_vpd_flag = 0 THEN
Return null;
ELSE
Rochelle privilege: = SYS_CONTEXT ('userenv', 'client _ IDENTIFIER ');
IF l_privilege IS NULL THEN
RETURN '1 = 2 ';
ELSE
RETURN p_column_name | 'IN (SELECT object_id FROM vpdsample_privileges WHERE privilege_id =' | l_privilege | ')';
End if;
End if;
END;

/* =================================== */
FUNCTION apply_vpd_clothing (p1 in varchar2, p2 in varchar2) RETURN VARCHAR2 IS
BEGIN
RETURN gen_vpd_predicate ('clothing _ id ');
END;

/* =================================== */
FUNCTION apply_vpd_books (p1 in varchar2, p2 in varchar2) RETURN VARCHAR2 IS
BEGIN
RETURN gen_vpd_predicate ('book _ id ');
END;
END;

Enable_vpd, disable_vpd: These two stored procedures are used to set a custom attribute 'enable' under context vpd. Table 1 enables vpd, and Table 0 disables vpd, because accessed globally is specified when this context is created, these attributes can be accessed across sessions. The two stored procedures call dbms_session, so the corresponding permissions must be granted: grant execute on dbms_session to user1;

 

Set_context: the context is mentioned above, and dbms_session.set_context is used in enable_vpd and disable_vpd to set Custom Attributes. In addition to the custom context, oracle also provides a pre-defined context 'userenv', which contains attributes defined for intervention, such as 'client _ identifier '. We do not use set_context to set this attribute, instead, use dbms_session.set_identifier ().

In this example, the stored procedure finds the permission ID of the user based on the input user name, and saves this ID as client_identifier to the context 'userenv' for later use.

In actual applications, this stored procedure should be called by external applications. Applications can call this stored procedure to write the context after login verification is complete.

 

Gen_vpd_predicate: generates a correct where clause based on the input field name. Here, we first determine whether vpd is enabled. If the vpd. enable attribute is not set, we also think that vpd is enabled. Then determine whether the userenv. client_identifier attribute has been set. If this attribute is not set, a where clause always false is returned to prevent users from viewing data. In the last generated statement, find the permission table vpdsample_privileges Based on the obtained client_identifier (that is, the user's permission ID) to obtain all the object IDs that the user has permissions on.

 

Apply_vpd_clothing, apply_vpd_books: visible by name. These two functions are used in two different tables, because the clothing table and the fields used for permission verification in the book table have different names. In addition, you can see that the two functions that will be used for vpd have two seemingly useless parameters p1 and p2, which are the requirements of the vpd interface. The first one is used to receive the schema name, the second is used to receive table/view/synonym names. We define that the function must meet the interface requirements. Of course, you can have additional parameters besides p1 and p2.

 

After all preparations are complete, call the dbms_rls package provided by Oracle to apply the vpd policy. before calling the policy, grant the user the following permissions:

Grant execute on dbms_rls to user1;

Then:

Begin
Dbms_rls.add_policy (object_name => 'vpdsample _ CLOTHING ', policy_name => 'Pol _ CLOTHING', policy_function => 'pkg _ VPDSAMPLE. APPLY_VPD_CLOTHING ');
Dbms_rls.add_policy (object_name => 'vpdsample _ Book', policy_name => 'Pol _ reads', policy_function => 'pkg _ VPDSAMPLE. apply_vpd_books ');
End;

Policy_name can be customized.

Then we enable vpd:

Begin
Pkg_vpdsample.enable_vpd;
End;

(It should be noted that the DBMS_RLS package itself has the ENABLE_POLICY () method used to enable or disable a vpd policy, but it can only enable/disable one vpd policy on a table at a time, to enable/disable the vpd policy for all tables at one time, you can follow the preceding procedure .)

 

And set context:

Begin
Pkg_vpdsample.set_context ('jack ');
End;

Then we query the vpdsample_clothing table and only return the clothing information with the ID of 10002. the query of vpdsample_books is similar, and only the book information with the ID of 10005 is returned.

 

Users can be changed:

Begin
Pkg_vpdsample.set_context ('Rose ');
End;

 

To delete a vpd policy, use:

Begin
Dbms_rls.drop_policy (object_name => 'vpsample _ CLOTHING ', policy_name => 'Pol _ CLOTHING ');
Dbms_rls.drop_policy (object_name => 'vpsample _ Book', policy_name => 'Pol _ Book ');
End;

 

Author Snowtoday MSN: MyYe110w@hotmail.com