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.
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.
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.
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 .
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.
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.
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.
Add a new user. After creating the role(s), add the user(s):
1. Click the Users tab.
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.
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 .
Subscribe to the menu framework's authorization scheme. In the Application Builder, click the application you want to add to the menu system:
2. Click Authorization Schemes .
3. Click Create .
4. Select As a copy of an existing authorization scheme , 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 Menu_App_User Authorization , and click Copy .
7. Click the Application (<num>) link to return to the application's home page.
8. From the application's home page, click Edit Attributes .
9. Click Edit Security Attributes .
10. Select Copy of Menu_App_User from the list in the Authorization region, and then click Apply Changes .
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:
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.
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).
1. Click the Add Row button to create a new entry.
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.
Tony Jedlinski (email@example.com) is vice president of administration and warehouse operations for Addison, Illinois-based giftware distributor Roman. Jedlinski is Oracle Magazine's Oracle HTML DB Developer of the Year for 2005.