Express LockdownBy Raj Mattamal
Lock down your Oracle Application Express applications.
When talking about Oracle Application Express, I find that the concept of authentication is easy to explain. Access control within applications, however, seems to be a bit more difficult to convey. With that in mind, I'd like to walk through the process of creating an application and then locking it down so that only privileged users can perform specific operations. In doing so, I'll take advantage of a feature newly added to Oracle Application Express in version 2.2: the Access Control Page wizard.
Step 1: Set Up the Tables
To get started, create (or have your administrator create) a new workspace. Once logged in to your Oracle Application Express workspace, create an application based on the employee data in the provided spreadsheet:
1. Click Application Builder .
Step 2: Take a Quick Look at the Application
At this point, you can see the application in action by clicking Run Application on the success page. Log in, using the same username and password you used to log in to your Oracle Application Express workspace. The application is a simple one that allows you to view and update the employee data you uploaded in step 1. Clicking the Analysis tab provides you with access to visual and tabular breakdowns of the number of employees in each department.
Step 3: Add an Access Control Page
Traditionally, you would secure the application by first defining a set of authorization schemes that determine if the current user has specific privileges based on some rules. With those schemes defined, the next step would be to flag the various parts of the application, including user interface components and application logic, to render or process only after they pass those security checks.
Oracle Application Express 2.2, however, introduces a much more streamlined approach to creating and managing those authorization schemes, with the Access Control Page wizard. Using this wizard creates two tables in your application schema as well as a screen for managing the values they store. The wizard also creates three authorization schemes that check those tables to see two things: (1) the access mode of the application overall and (2) whether the current user has been identified as having Admin, Edit, or View privileges. Using this new wizard saves you from having to create this plumbing, so you can focus on securing the areas that need securing.
To start the Access Control Page wizard, do the following:
1. In the developer toolbar at the bottom of the screen, click Create .
If you click Run Page on the success page of the wizard, you'll see the access control page you just added to your application. The page is divided into two regions, one where you set the Access Mode of the application and another where you populate a list of privileged users.
Step 4: Identify Privileged Users
Select the second Access Mode option, Restricted Access , and click Set Application Mode . In the Access Control region, you'll specify which users have what privileges. To keep things simple, the users of your application will be the employees in the om_employees.csv spreadsheet. Within that list, give JCHEN administrative privileges, AFRIPP edit privileges, and LPOPP view privileges.
1. Click Add User .
As mentioned earlier, the completed application will treat employees in the spreadsheet as users. To implement this rule, you must add a tiny authentication function to the current authentication scheme. First, let's create the function:
1. Click the Logout link, to log out of your application (because you'll have to log in as a privileged employee when you've completed step 5).
Code Listing 1: om_custom_auth function
create or replace function om_custom_auth ( p_username in VARCHAR2, p_password in VARCHAR2) return BOOLEAN is begin for c1 in (select 1 from om_employees where upper(userid) = p_username and upper(last_name) = upper(p_password)) loop return true; end loop; return false; end; /
With the function created, now make the current authentication scheme use it:
1. Click the Application Builder tab.
Step 5: Apply Authorization Rules to Application Components
The final step is to apply the authorization schemes in the appropriate places. The first step in this process is to decide which users should be allowed to perform what actions. The easiest approach I've found is to start with the most-common application actions and privileges. So to start with, allow users to access the application only if they've been granted at least View privileges:
1. In the breadcrumb links in the upper left corner of the Application Express development environment screen, click the Application nnn link, where nnn represents the numeric ID associated with your current application.
The next level of common application actions involves editing. Only users with at least Edit privileges should be able to edit or delete data, so allow only Edit- and Administrator-privileged users to see the Edit icon when viewing the report on page 1 of your application:
1. Click the Edit icon (resembling a pencil over a pad) in the upper right corner of the screen to navigate to the definition of page 101.
In the same way you hid the Edit icon for unprivileged users above, apply the access control - edit scheme to the Create button on page 1. Then apply the same rule to the whole of page 2, by selecting access control - edit in the Authorization Scheme field of the page-level attributes page.
The last steps for securing your application are to allow only administrators to access the Administration page, page 8, and its associated tab. Restrict this access by selecting access control - administrator in the Authorization Scheme field of the page-level attributes page for page 8. Hide the tab from nonadministrators by applying the administrator's authorization scheme to the Administration tab of your application.
Your application is now secure, and users are able to perform only actions for which they have privileges. Test it out by logging in (credentials are now userid/last_name) as each of the three users you empowered, and you'll see that they can do only what you've allowed them to do.
Raj Mattamal is a vice president at Sumner Technologies. Before joining Sumner, he was with Oracle for more than 10 years, helping customers in a wide range of industries deliver Web-based solutions on Oracle Database. Beyond speaking and customer engagements, Mattamal has developed Web systems for various lines of business. His main focus recently has been Web application development using Oracle Application Express.