As Published In September/October 2006

DEVELOPER: Browser-Based

Express Lockdown

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 .
2. Click Create .
3. Select Create from Spreadsheet , and click Next .
4. Select Upload file... , and click Next .
5. Click Browse ; select the om_employees.csv script, available for download; and click Next .
6. Enter OM_EMPLOYEES into the Table Name field, and click Next .
7. Enter Employee and Employees into the Singular Name and Plural Names fields, respectively.
8. Click Next .
9. Select DEPARTMENT_ID in the Summary by Column field, and click Next .
10. Click Next again on each of the subsequent pages, accepting the defaults, to complete the wizard. (On the User Interface page, select a theme other than the default—Theme 1, if you like.)
11. Click Create on the last page of the wizard to confirm your decision to create an application.

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 .
2. Select New Page , and click Next .
3. Select Access Control , and click Next .
4. Accept the default administration page number, 8, and click Next .
5. Select Use an existing tab set and create a new tab within the existing tab set .
6. Select TS1 (Employees, Analyze) in the Tab Set field.
7. Enter Administration in the New Tab Label field, and click Next .
8. Click Finish .

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 .
2. Enter JCHEN in the Username field, and select Administrator in the Privilege column.
3. Click Apply Changes , and then click Add User .
4. Enter AFRIPP in the Username field, and select Edit in the Privilege column.
5. Click Apply Changes , and then click Add User .
6. Enter LPOPP in the Username field, and select View in the Privilege column.
7. Click Apply Changes .

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).
2. Enter the Application Express development environment by clicking Edit Application in the developer toolbar at the bottom of the screen.
3. Click the SQL Workshop tab.
4. Click SQL Commands .
5. Copy and paste the function code from Listing 1 into the SQL Command field.
6. Click Run .

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.
2. Click OM_EMPLOYEES .
3. Click Shared Components .
4. Click Authentication Schemes .
5. Click Application Express - Current .
6. In the Authentication Function field in the Login Processing section, replace the -BUILTIN- value by entering return om_custom_auth .
7. Click Apply Changes .

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.
2. Click Edit Attributes .
3. Click Security .
4. Select access control - view in the Authorization Scheme field in the Authorization section.
5. Click Apply Changes .

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.
2. Enter 1 in the Page field, and click Go .
3. Click the Report link in the Regions section.
4. In the list of report columns, click the Edit icon in the ID row.
5. Select access control - edit in the Authorization Scheme field in the Authorization section.
6. Click Apply Changes .

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.