I am using Oracle Application Express 4.2.5. I am creating an application with custom login page..
And I have created an Application Items from Shared Components (Ex. Variable Name is 'GLOBAL_VAR') with Application Scope and Protection level is Unrestricted.
My requirement is
1. How can i assign value to this application item variable ('GLOBAL_VAR')?
2. After Login to the application I need to assign value to this item from database using function or suggest any other way to assign.
Set the value using an Application Computation with Computation Point On New Instance (new session).
3. And I need to use this variable to all my PL/SQL queries(where clause) which i have been used in this application.
This is known as Fine Grained Access Control (FGAC), and is normally implemented using views or Oracle's Virtual Private Database (VPD) feature.
If you're licensed to use an Enterprise Edition database and want to apply these restrictions across the entire application, or across multiple applications, then use Virtual Private Database (VPD). In APEX 4.2 you can set up/tear down the security context using the Initialization PL/SQL Code/Cleanup PL/SQL Code attributes. In earlier versions the set up attribute is termed Virtual Private Database (VPD), and there is no tear down option. (An out of date article on using VPD with APEX is also available.)
Should you not be using EE, then you can roll your own VPD using parameterized views, again using the VPD security attribute to set the required application context with the application item value. A simpler approach using a standard view where the user name is determined using
v('GLOBAL_VAR')is another possibility, but note that Oracle do not recommend using the
vfunction in queries on large result sets for performance reasons.
One of the benefits of using VPD over views is that with views you may have to modify the SQL used everywhere in your app. Using VPD, the policies can be applied to the existing tables without any modification of existing queries.
Hi Pars and fac586,
Actually I am new to Oracle Application Express. I need to develop an application with following criteria.
In this application I need to use authentication using database table(Login Page).
After Login I need to create a form for tranaction(PUR_ORDER form).
In this PUR_ORDER form I need to capture username(who have currently loggon) into table(CRE_BY column) who have been created the transaction.
And I need to pass a global value parameter into developed forms and reports.