Shulou(Shulou.com)06/01 Report--

This section provides a brief introduction to Oracle VPD. VPD is used for fine-grained rights management.

About Oracle Virtual Private Database

What?

What VPD does: VPD automatically adds a where clause to the SQL statement

Affected objects: table, view, synonym

Statements involved: select, insert, update, index and delete

Not supported: DDL

Examples of basic practices:

SELECT FROM OE.ORDERS

-> apply VPD

SELECT FROM OE.ORDERS

WHERE SALES_REP_ID = 159,

Benefits

Security: no matter how users access data, you can ensure that fine-grained access control policies are enforced.

Simplicity: you only need to operate once on table and view.

Flexibility:select, insert, update, and delete can all have their own strategies

Using Oracle Virtual Private Database with an Application Context

SELECT FROM orders_tab

->

SELECT FROM orders_tab

WHERE custno = SYS_CONTEXT ('order_entry',' cust_num')

Components of an Oracle Virtual Private Database Policy

Function

Used to generate where clauses (predicates)

Input parameters: schema, object name

Output parameter: where clause (valid)

Configuring an Oracle Virtual Private Database Policy

What

Function needs to be bound to object. You can bind it by configuring policy.

Policy itself is used to manage VPD function, while providing granular access control, such as specifying the type of SQL statement or specific columns affected by the policy.

Oracle provides DBMS_RLS for policy management: ADD, DROP, ENABLE/DISABLE...

How

Example:

SELECT fname, lname, ssn FROM emp

->

SELECT fname, lname, ssn FROM emp

WHERE ssn = 'my_ssn'

CREATE OR REPLACE FUNCTION hide_sal_comm (

V_schema IN VARCHAR2

V_objname IN VARCHAR2)

RETURN VARCHAR2 AS

Con VARCHAR2 (200)

BEGIN

Con: = 'deptno=30'

RETURN (con)

END hide_sal_comm

/

BEGIN

DBMS_RLS.ADD_POLICY (

Object_schema = > 'scott'

Object_name = > 'emp'

Policy_name = > 'hide_sal_policy'

Policy_function = > 'hide_sal_comm'

Sec_relevant_cols = > 'sal,comm')

END

/

-set sensitive column output

BEGIN

DBMS_RLS.ADD_POLICY (

Object_schema = > 'scott'

Object_name = > 'emp'

Policy_name = > 'hide_sal_policy'

Policy_function = > 'hide_sal_comm'

Sec_relevant_cols = > 'sal,comm'

Sec_relevant_cols_opt = > dbms_rls.ALL_ROWS)

END

/

Tutorials

Create a data table and set the predicate to: username=' test'

Drop table tours vpddholders 1: create table t_vpd_1 (id int,username varchar2 (20), name varchar2 (30)); insert into t_vpd_1 (id,username,name) values (1 Magi insert into t_vpd_1 id,username,name values (2 mini 'Zhang San', 'name1'); insert into t_vpd_1 (id,username,name) values (3 Magi' Test', 'Test name 1') Insert into t_vpd_1 (id,username,name) values (4 'test', 'test name 2'); commit

Create function

CREATE OR REPLACE FUNCTION func_testvpd_1 (schemaname IN VARCHAR2, tablename IN VARCHAR2) RETURN VARCHAR2 IS ret VARCHAR2; BEGIN ret: = 'username =' 'Test''; RETURN ret; END func_testvpd_1;/

Create a policy (select)

BEGIN DBMS_RLS.DROP_POLICY (object_schema = > 'test', object_name = >' tasking vpdfdl1, policy_name = > 'policy_t_vpd_1'); END / BEGIN DBMS_RLS.ADD_POLICY (object_schema = > 'test', object_name = >' tasking vpdfdl1), policy_name = > 'policy_t_vpd_1', function_schema = >' test', policy_function = > 'func_testvpd_1', statement_types = >' select'); END;/

Testing strategy

Insert TEST-orcl@DESKTOP-V430TU3 > create table t_vpd_2 as select * from t_vpd_1 where 1 / 2 / table created.TEST-orcl@DESKTOP-V430TU3 > insert into t_vpd_2 (id,username,name) select * from / vpd1 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 3 / 2 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 2 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 2 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 / 3 /

Create a policy (select, insert, update, delete)

BEGIN DBMS_RLS.DROP_POLICY (object_schema = > 'test', object_name = >' tasking vpdfdl1, policy_name = > 'policy_t_vpd_1'); END / BEGIN DBMS_RLS.ADD_POLICY (object_schema = > 'test', object_name = >' tasking vpdfdl1), policy_name = > 'policy_t_vpd_1', function_schema = >' test', policy_function = > 'func_testvpd_1', statement_types = >' select,insert,update,delete'); END;/

Testing strategy

Insert into t_vpd_1 (id,username,name) values (1); insert into t_vpd_1 (id,username,name) values (2) 'Zhang San', 'name1'); insert into t_vpd_1 (id,username,name) values (3' test', 'test name 1'); insert into t_vpd_1 (id,username,name) values (4) 'test', 'test name 2'); commit -- query TEST-orcl@DESKTOP-V430TU3 > select * from t_vpd_1 ID USERNAME NAME- 3 Test Test name 1 4 Test name 2 Mak- Insert TEST-orcl@DESKTOP-V430TU3 > drop table t_vpd_2 Table created.TEST-orcl@DESKTOP-V430TU3 > insert into t_vpd_2 (id,username,name) select * from tweets Table dropped.TEST-orcl@DESKTOP-V430TU3 2 rows created.TEST-orcl@DESKTOP-V430TU3 > update t_vpd_1 set name = 'test';2 rows updated.TEST-orcl@DESKTOP-V430TU3 > delete from tweets vpd1 place2 rows deleted.TEST-orcl@DESKTOP-V430TU3 >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.