How is CLIENT_INFO being replaced in R12?

Here is a list of security enhancement features:

Column-Level VPD -
Virtual Private Database (VPD) is now finer grained. You are now able to enforce VPD rewrite when a query references a particular column.

VPD Static and Dynamic Policies
- Virtual Private Database (VPD) now lets you distinguish between static policies, which are suitable for hosting environments that always need to enforce an unchanging policy, and dynamic policies, which are suitable for time-dependent enforcement, such as time of day, where rows returned must vary at a particular time.

Fine-Grained Auditing (FGA) on DML -
Fine-Grained Auditing (FGA) extends the support to include UPDATE, INSERT, and DELETE statements.

Virtual Private Database

Virtual Private Database (VPD) was first introduced in Oracle8i. It set a new standard in database security, being built into the database server, instead of each application accessing the data. Security is no longer bypassed when a user accesses the database with an ad hoc query tool or a new application. Virtual Private Database is a key enabling technology for organizations building hosted, web-based applications that expose mission-critical resources to customers.

Virtual Private Database Overview

Virtual Private Database enables the database to perform query modification based on a security policy you have defined in a package. A security policy is a restriction associated with a table or view.

 

When a user directly or indirectly accesses a table or view associated with a VPD security policy, the server dynamically modifies the user's SQL statement. The modification is based on a where condition (a predicate) returned by a function which implements the security policy. The database modifies the statement dynamically and is transparent to the user.

 

Data access via Virtual Private Database will perform the following five steps:

1. User sends SQL to the database server.
   
2. The associated table triggers a pre-defined security policy.
   
3. The security policy returns a predicate.
   
4. The SQL statement is modified according to the security policy.
   
5. Secured data returns to user.
   

 

 

 

In pre Release 12, you would have had following methodology for PO_HEADERS_ALL

a. A table is created in PO Schema, named PO_HEADERS_ALL

b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL

c. Create a view PO_HEADERS in APPS schema, as "select * from po_headers_all where org_id=client_info"

 

But now in R12, following will happen

a. A table is created in PO Schema, named PO_HEADERS_ALL

b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL

c. Another synonym named PO_HEADERS is created in APPS, referring to PO_HEADERS_ALL

d. A Row Level security is applied to PO_HEADERS, using package function MO_GLOBAL.ORG_SECURITY.

This can be double-checked by running SQL select * from all_policies where object_name='PO_HEADERS'

e. The effect of this policy is that,whenever you access PO_HEADERS, Oracle RLS will dynamically append WHERE

CLAUSE similar to below

SELECT *

FROM PO_HEADERS

WHERE EXISTS

(SELECT 1

FROM mo_glob_org_access_tmp oa

WHERE oa.organization_id = org_id)

 

This mean, if I create a new custom table, I will have to apply RLS [ Row Level Security ] against Custom table too?

Yes indeed, if it contains data partitioned by ORG_ID. All you need to do in such case is to assign package function

MO_GLOBAL.ORG_SECURITY to that table/synonym/view.

 

 

In theory, RLS can be applied against Table, View and Synonym. However in practice, you will apply RLS against Objects

in APPS Schema. This means, you will most probably apply RLS on Synonyms. Basically, the Multi Org Views are now

replaced by RLS Secured Synonyms. Hence no code change is required where the pre-R12 Multi-Org secured view was

being accessed. The responsibility of securing data as per ORG_ID now lies with RLS [also known as VPD - Virtual

Private Database].