As Published In May/June 2006

DEVELOPER: Administration

Build a Menu Framework

By Tony Jedlinski

Centralize applications with a table-driven menu framework.

Developers often use Oracle Application Express (formerly Oracle HTML DB) to rapidly develop simple, non-business-critical applications—to replace spreadsheet-based applications, for example, or to automate paper-based or other manual systems. Given Oracle Application Express' short development cycle, ease of use, and enormous popularity, it's not surprising that the number of Oracle Application Express applications can grow quickly, without consistent standards or central control and administration.

A table-driven menu framework built with Oracle Application Express can improve accessibility and security and provide a more integrated appearance for your applications. With this menu framework in place, Oracle Application Express provides a secure front end for all your applications, including business-critical production applications.

The abbreviated steps in the print version of this article use an export of the menu application framework that you can install and customize. A future article will provide step-by-step instructions for creating the menu framework, from start to finish.

Menu Framework Architecture

The menu framework application's architecture is built on a foundation of five tables (see Figure 1). These tables define the roles, users, and applications. 

Figure 1: Menu framework application architecture

Roles. One or more roles can be applied to each user and to each application. The m_roles table defines all the roles for the system. The m_app_roles table defines the roles associated with each application, and the m_user_roles table defines the roles associated with each user. (These roles are created in the Menu_Framework application and are not Oracle Database user roles.)

Users. Users of the menu system are defined in the m_users table. An administrator column in the m_users table serves as a flag to identify whether or not the user is an administrator. Only users who have been designated as administrators can access the Roles and Users tabs (and consequently add roles and users to the system or make changes).

Applications. The applications table (m_applications) contains information about all the applications and URLs that constitute the menus.

The menu framework application uses the concept of roles to determine authorization privileges on applications and dynamically create customized menus for each user. Access to the menu framework application is controlled by means of a username/password combination used for authentication at login. Once a user is authenticated, that user's assigned roles are compared with the roles associated with each application; ultimately the menu presented to any user is based on a match between the roles assigned to that user and the roles applied to specific applications.

The menu framework application includes a login page, master-detail forms, reports, and several other components. You see these elements and their configuration details after you import and install the menu framework application export file (menu_framework_app.sql).

The application also includes shared components, such as the authentication and authorization schemes that control access to all registered applications.

Menu_User authentication scheme. The Menu_User authentication scheme is set as the current authentication scheme for the Menu_Framework and all applications you want to make accessible through this menu system. This authentication scheme ensures that after logging in to the menu system, users can access appropriate applications without logging in again. The Menu_User authentication scheme invokes the authentication function created as part of the setup script (see Listing 1). This function performs one-way encryption of passwords for additional security.

Code Listing 1: The authentication function 

create or replace FUNCTION authenticate_m_user(
p_username IN VARCHAR2, p_password IN VARCHAR2)
RETURN BOOLEAN IS
CURSOR m_cur( username_p VARCHAR,password_p VARCHAR) IS
SELECT COUNT(*) user_count FROM m_users
  WHERE UPPER(username) = username_p
  AND PASSWORD = password_p AND in_use = 'Y';
result_v   BOOLEAN := FALSE;
BEGIN
  FOR m_rec IN m_cur(p_username, dbms_obfuscation_toolkit.md5( input_string => p_password))
  LOOP
     IF m_rec.user_count = 1 THEN result_v    := TRUE; END IF;
  END LOOP; 
RETURN result_v;
END authenticate_m_user;

Menu_App_User authorization scheme. The Menu_App_User authorization scheme ensures that users can access only the appropriate applications, regardless of how they navigate to the application. Any application you want to make available through the menu system should have its Security attribute set to this authorization scheme. The Menu_App_User authorization scheme invokes the authorization functions (see Listing 2) to evaluate the numeric application ID (application_id) and determine if a user can access a specific application.

Code Listing 2: Authorization functions 

create or replace FUNCTION authorize_m_user(
app_id_in IN PLS_INTEGER, username_in IN VARCHAR2)
RETURN BOOLEAN IS
CURSOR app_cur(app_id_p PLS_INTEGER,user_p VARCHAR2) IS
SELECT DISTINCT ma.m_application_num
FROM m_users mu, m_user_roles mur, m_roles mr,
m_app_roles mar, m_applications ma
  WHERE mu.m_user_num = mur.m_user_num
    AND mur.m_role_num = mr.m_role_num
    AND mr.m_role_num = mar.m_role_num
    AND mar.m_application_num = ma.m_application_num
    AND UPPER(mu.username) = user_p
    AND ma.application_id = app_id_p
    AND ma.in_use = 'Y' AND mr.in_use = 'Y' AND mu.in_use = 'Y';
result_v   BOOLEAN := FALSE;
BEGIN
FOR app_rec IN app_cur(app_id_in, username_in)
LOOP result_v    := TRUE; END LOOP;
RETURN result_v;
END authorize_m_user;

create or replace FUNCTION authorize_m_user2(
app_num_in IN PLS_INTEGER,username_in IN VARCHAR2)
RETURN varchar IS
CURSOR app_cur(app_num_p PLS_INTEGER, user_p VARCHAR2) IS
SELECT DISTINCT ma.m_application_num
FROM m_users mu,m_user_roles mur,
m_roles mr,m_app_roles mar,m_applications ma
  WHERE mu.m_user_num = mur.m_user_num
    AND mur.m_role_num = mr.m_role_num
    AND mr.m_role_num = mar.m_role_num
    AND mar.m_application_num = ma.m_application_num
    AND UPPER(mu.username) = user_p
    AND ma.m_application_num = app_num_p
    AND ma.in_use = 'Y' AND mr.in_use = 'Y' AND mu.in_use = 'Y';
result_v   varchar2(1) := 'N';
BEGIN
FOR app_rec IN app_cur(app_num_in, username_in)
LOOP result_v    := 'Y'; END LOOP;
RETURN result_v;
END authorize_m_user2;

Once created and configured, the menu application is the starting point from which all other Oracle Application Express applications (and valid URLs) can be accessed. The menu is designed to display only the applications for which the current user is authorized, based on the roles assigned by the system to both the application and the user.

For example, users granted the role of warehouse clerk will see a customized menu displaying only applications that have been assigned the warehouse clerk role. Figure 2 shows an example of the menu framework in action, displaying a typical customized menu. 

Figure 2: A dynamically generated menu

Administrators are users who have been identified as such in the system; they can create users and roles and define new applications to be made available to users on the menu. The menu that appears when a user with administrator privileges logs in to the system reflects these additional capabilities—note the tabs for Roles and Users and the edit links next to the applications listed on the menu in Figure 3, none of which are available in the nonadministrative user menu shown in Figure 2. 

Figure 3: An administrator's menu

You can designate any user as an administrator, by setting the attribute on the user definition page.

Step 1: Set Up the Tables

To get started, create (or have your Oracle Application Express administrator create) a new workspace, if necessary. Log in to your workspace and set up tables for the menu framework:

1. Click SQL Workshop .
2. Click SQL Scripts .
3. Click Upload .
4. Click Browse; select the menu_framework_objects.sql script, and click Upload .
5. Click the menu_framework_objects.sql icon and name the script Menu Objects.
6. Click Run , and then click Run on the Confirmation screen.
7. Verify the results of the script execution, by clicking the menu_framework_objects.sql icon. All steps should show successful execution.

Step 2: Import the Application

With the tables, indexes, sequences, triggers, and authentication functions in place, import the menu framework application.

1. Click the Application Builder tab.
2. Click Import .
3. Click Browse ; select the menu_framework_app.sql script, and click Next .
4. Click Install .
5. Select your schema from the menu; select Run, Build Application , and Auto Assign New Application ID ; and then click Install Application .

Step 3: Change the Default Password

The setup script creates a default user—admin—with a default password of admin. After importing the application and running the setup script, you can log in as admin to the application and modify the password before doing anything else:

1. Click the Application Builder tab.
2. Click the Menu_Framework application icon.
3. Click Run Application .
4. Enter admin/admin as the username and password.
5. Click the Users tab.
6. Click the Edit icon adjacent to the admin user listed on the page (it should be the only user in the list).
7. Scroll down the form, and enter a new password for the admin user in the Username and password region.
8. Click Apply Changes .

Step 4: Create Roles and Users

Now create the roles and add users to the system. While still logged in as the admin user

1. Click the Roles tab at the top of the page.
2. Click Create .
3. Enter a description for the role.
4. To organize roles hierarchically, select the parent role from the menu. (The notion of parent roles in the menu is strictly for organizational purposes—roles are not inherited.)
5. Leave the In Use field set to the default, Yes , and the Look Only field set to the default, No . (The Look Only attribute is available for application developers to reference when appropriate. To use this feature, you must also condition the Create , Delete , and Apply Changes buttons in your own applications with a function to ensure that the current user belongs to at least one application role where LOOK_ONLY = 'N'.)
6. Click Create again to save the role. The Report on M_Roles page reappears, and you'll see the role listed.
7. Repeat as needed to create all the roles you want to establish in your menu system.

Add a new user. After creating the role(s), add the user(s):

1. Click the Users tab.
2. Click Create .
3. Enter First Name, Last Name, Email Address, and other details. The only required entries are Last Name, Username, and Password; the required In Use field defaults to Yes .)
4. Click Create to save. Repeat steps 3 and 4 until all users have been created.
5. Click Cancel to return to the report of users.

Add a role to the user. After creating a user, go back to edit the user and assign one or more roles.

1. Click the Users tab.
2. Click the Edit link adjacent to a user name.
3. Click the Add Row button in the Roles specified for this user region of the page.
4. Select a role from the list (M_Role_Num).
5. Click Add Row again to save the selection for this user.
6. Repeat as needed to add the appropriate roles for this user, and click Apply Changes when finished.

The user's attributes can be edited at any time by an administrator. Users who are not administrators can change only their own passwords, by clicking the Change Password tab.

Step 5: Add Applications to the Menu

Before you can add applications to the menu, you must open the application in Oracle Application Express and subscribe to the menu framework application's authentication and authorization schemes.

Subscribe to the menu framework's authentication scheme. In the Application Builder, click the application you want to add to the menu system:

1. Click Shared Components .
2. Click Authentication Schemes .
3. Click Create .
4. Click the As a copy of an existing authentication scheme button, and click Next .
5. Select NNN Menu Framework from the Copy from Application list, and click Next .
6. Select Copy and Subscribe from the list adjacent to Copy of Menu_User Authentication , and click Copy Scheme .
7. Click the Change Current link.
8. Select Copy of Menu_User Authentication from the authentication schemes in the list, and click Next .
9. Click Make Current on the confirmation screen.

Subscribe to the menu framework's authorization scheme. In the Application Builder, click the application you want to add to the menu system: 

The application is now ready to be added to the menu as a link. However, you should also provide a means within the application to return to the menu application. For example, add a navigation bar entry in the application that refers to the menu framework application, using the URL for the Menu_Framework application: 

f?p=menu:1:&APP_SESSION.

Add the application to the Menu tree. Access the menu application and add the application to the main menu:

1. Log in to the Menu application as an administrator.
2. Click Create .
3. Complete the details about the application (see Figure 4), where NNN is the Oracle Application Express application ID and P is the starting page (usually 1). For non-Oracle Application Express applications, enter the application's complete URL, such as oracle.com/technology. Setting In Use to No disables the application and removes it from all menus, except the administrator's.
4. Click Create again to save this application definition.

Figure 4: A configured user defined in m_users and m_user roles tables

After the application is created, it appears in the administrator's main menu. From the menu, while still logged in as admin, click the Edit link adjacent to the menu item and display the application properties page in edit mode. You'll now note an additional region displayed on the page for defining roles for this application (see Figure 5). 

Figure 5: Entry form built around the m applications table

1. Click the Add Row button to create a new entry.
2. Select a role from the list.
3. Click Add Row again to add more roles to this application, as needed.
4. Click Apply Changes when finished to save changes.

The roles, users, and applications tables all include an in-use column that can be set from the respective entry form. Rather than deleting a user, role, or application, administrators of the menu framework can set the In Use attribute to No , thus disabling the objects in the system.

Oracle Application Express lets you easily develop a suite of production applications that are tied together using this dynamic menu framework application. The menu serves as a central point from which users can access appropriate applications, and you can administer and control that access. I hope this description demonstrates its usefulness. A future article will address how to build the entire menu system from scratch. The result is a professional-looking application suite with a consistent look and feel that is secure and easily maintained.