Technical analysis of PLSQL_R12 MOAC Multi-Organization Bottom technology (Oracle VPD)

2014-05-30 Baoxinjian in Capgemini

First, Introduction

There have been some doubts about Oracle R12 Multi-organization access, so we've queried some of the relevant data and introduced the Oracle VPD technology that implements R12 MOAC

Oracle VPD Full name Virtual Private Database, one of Oracle's security policies for controlling data access

Oracle Data Security Policy Access implementation method

• role-based Security
  • Create role Cust_role;
  • Grant Select on the customer to Cust_role;
  • Grant SELECT, update on orders to Cust_role;
  • Grant Cust_role to Scott;
• Grant-execute Security
  • Grant execute on procedure to Cust_role;
• Virtual Private Databases
  • The following is a detailed description

Ii. cases-realizing imitation R12 MOAC adding standard multi-organization access control security policy to customized tables

For example, the difference between Ap_invoices_all and ap_inovices access

When Mo_global.set_policy_context is not initialized, Ap_invoices_all is able to access all the data, and Ap_invoices is not returned by any data

When Mo_global.set_policy_context is initialized, Ap_invoices_all can still access all the data, and Ap_invoices can return records for the corresponding organization, while records from other organizations are not returned

The reason is that a security policy has been added to the view ap_invoices mo_golbal.org_security

Therefore, in the imitation of the standard MOAC function to establish a multi-organization access to the customized table, you can refer to the above example

Set up a base table (Bxj_vpd_invoices_b) to hold all data, whether or not it can be returned by Mo_global initialization session, similar to the functionality of the base table Ap_invoices_all

Establish a view (Bxj_vpd_invoices_v), which is built on top of the base table, and then adds security policy for multi-organization access, similar to the functionality of view Ap_invoices

Step1. Building a base table

CREATE TABLE Bxj_vpd_invoices_b (

INVOICE_ID number,

Invoice_num VARCHAR (100),

Invoice_description VARCHAR (240),

ORG_ID number

)

Step2. Build a View

CREATE VIEW Bxj_vpd_invoices_v as

SELECT invoice_id, Invoice_num, Invoice_description, org_id

From Bxj_vpd_invoices_b;

Step3. Prepare the test data, an org of 204, a sum of org 889

INSERT into Bxj_vpd_invoices_b VALUES (1, ' bxj_20140520_01 ', ' Bxj_vpd_show_with ORG204 ', 204)

INSERT into Bxj_vpd_invoices_b VALUES (2, ' bxj_20140520_02 ', ' Bxj_vpd_show_with ORG889 ', 889)

Step4. Query base tables and views are all two records can be returned

Step5. The most critical step is to add a security policy to the custom view Bxj_vpd_invoics_v Dbms_rls.add_policy

Step6. Whether the security policy was established successfully

Test1. Successful policy establishment, test, direct Select Table Bxj_vpd_invoics_v, no results returned by query

Test2. To initialize a session

Test3. Once again directly select Table Bxj_vpd_invoics_v, the query returns only one ORG204 record, and ORG889 's record is not displayed, enabling multi-organization access to the customized table

Iii. cases-Implement a simple custom VPD security policy control of table data access

Requirements: Invoice Total > 1000 records are not displayed on the datasheet bxj_invoice_total

Step1. Create a simple table and prepare 4 test data

Step2. View raw data with 4 records

Step3. The most critical step, the establishment of VPD Function, the system returns the following fields, as a VPD strategy, in fact, the personal feeling is based on different conditions and environment to distinguish between the combination of conditions to achieve data access control

Step4. Pay VPD policy via API Dbms_rls.add_policy

Step5. Check if the VPD policy is assigned a successful value

Test1. Directly querying tables that have been added to the customized VPD policy, and have pressed VPD policies to show only records with amounts less than 1000

Thanks and regards