Controlling who can and who can't access your application is an extremely important consideration when designing your applications. The authentication method you choose for your application will define how the identity of users is determined and verified. A comprehensive security model will also address authorization—the process of specifying what a user can do once he is logged into an application.
As you'll learn in this chapter, APEX allows you to define many different authentication schemes in your application. However, only one of the schemes can be set as the current one. You can use one of the preconfigured authentication schemes or create your own from scratch, enabling you to build any logic you like into your authentication scheme.
With APEX, you can specify, on a page-by-page basis, whether that particular page requires authentication or is public. If the page is public, anyone can directly access that page by typing its URL into a browser.
Once users have successfully authenticated to your application, they will be able to access any pages in the application that require authentication without having to reauthenticate (assuming the user passes any authorization checks, which we will cover in the next chapter). In other words, authentication is a one-time process—once you have authenticated, you remain authenticated for the duration of your session. Given increasing focus on application security, it is a common practice to time a session out after a period of inactivity. In earlier versions of APEX, you may have used custom functions to implement a session timeout, but this is no longer necessary in APEX 4.0. Both the session timeout (defaulted to 3600 seconds) and the maximum session length (defaulted to 28,800 seconds) can be set via the Security Settings section of the Manage Instance page.
APEX offers a number of built-in authentication schemes, including the following:
This scheme allows users to successfully authenticate using any username, without having to provide a password. The username is not checked against any sort of repository, so this scheme is really useful only for testing purposes or where you don't need to enforce any form of account uniqueness. This scheme also allows you to simulate (in your development environment) what happens when you authenticate as a particular user in another environment (for example the production environment).
This scheme allows anyone to access the pages in your application, as long as the Oracle username and password specified in the database access descriptor (DAD) are correct. You will not be able to uniquely identify users in your application, since they will all be using the username specified in the DAD (for example, APEX_PUBLIC_USER
or HTMLDB_PUBLIC_USER
).
This scheme uses the built-in users and groups created by a workspace administrator within the workspace where the application is installed. This method is often referred to as cookie user accounts. It offers a quick way to manage and maintain a simple user repository without having to create your own user-management routines.
This scheme allows you to use Oracle accounts to authenticate your users against. Users need to specify a valid database username and password in order to successfully authenticate to your application. This scheme is ideal if you have already created a database user for each of your end users. Note that using database account authentication will not affect the parsing schema for your application. In other words, the authentication scheme uses only the username and password to authenticate with; it is not establishing a session to the database as that user, nor is it running any code as that particular user.
Using this scheme, you can authenticate users against any LDAP directory, which includes Oracle Internet Directory (OID), Microsoft Active Directory, and Sun iPlanet, among many others.
If you use Oracle Application Server, you can take advantage of single sign-on (SSO) against an OID LDAP server. Using SSO enables users to authenticate once against the SSO server, and then be able to access many different applications without needing to reauthenticate.
To use a preconfigured authentication scheme, within your application in Application Builder, choose to create a new authentication scheme and select "Based on a preconfigured scheme from the gallery." You will see the page shown in Figure 2-1.
You can view the existing authentication methods for your application by navigating to Shared Components
Rather than apply a preconfigured scheme, you can instead use a custom authentication scheme, which gives you complete control over how your authentication scheme works. Typically, you might write a routine that authenticates a username and password against details that have been stored in a table.
This chapter covers all the preconfigured authentication methods, except the LDAP directory and SSO authentication schemes, as well as creating custom authentication schemes.
With open door credentials, you are essentially declaring, "I want to allow anyone to authenticate to my application, just as long as they type in a username." To use open door credentials, choose to create a new authentication scheme and select "Based on a preconfigured scheme from the gallery." Then choose Show Built-In Login Page and Use Open Door Credentials (see Figure 2-1).
When users run your application and try to connect to a page requiring authentication, they will be presented with a login screen similar to the one shown in Figure 2-3, where they will need to enter a username.
Whatever the user enters as the username will be used as the APP_USER
substitution variable. Since no password is requested and no validation is performed, users are free to use any username they like; for example, they could choose ADMIN
or something else completely undesirable.
Using the open door credentials authentication method is a good way to quickly test your application using different users without needing to maintain a user repository. This lets you track down problems that are perhaps related to the username (for example, authorization issues) without having that user in your user repository.
Open door credentials authentication is rarely used outside development and testing environments. You will usually want to either make an application completely public (no authentication) or be able to differentiate among your individual users. Since the open door credentials method does not require a password, you won't be able prevent different users from using the same username.
It may seem strange in a chapter devoted to authentication to discuss no authentication. However, it is completely valid to have all of your pages accessible without requiring users to log in.
To set up an application with no authentication, choose to create a new authentication method based on one from the gallery, and then select No Authentication (using DAD), as shown in Figure 2-1. Once you've chosen this scheme, users will be able to access the application without needing to authenticate.
When you use the no-authentication method, the APP_USER
substitution string that is used to identify the currently logged-in user will be set to the database user specified in the DAD configuration file. Typically, the user in the DAD will be specified as HTMLDB_PUBLIC_USER
or APEX_PUBLIC_USER
, but it depends on your particular configuration. Since the DAD configuration file usually specifies both the username and password to connect to the database, the user will not need to authenticate.
This authentication method is useful if you don't need to protect your application in any way. If you want to allow anyone to view all the pages in your application, and you're not concerned about the data they might be able to view and modify, this method is ideal. You may find it suitable when your application is essentially read-only—the end users just view the data (and that data is not deemed sensitive).
You should also take into account that with the no-authentication method, any form of auditing you might be performing (for example, keeping track of records being deleted) will be of limited use. Every user will be identified as APEX_PUBLIC_USER
or HTMLDB_PUBLIC_USER
(as specified in your DAD), so you won't be able to correlate the audit entries to a particular user.
Application Express account credentials, commonly referred to as cookie users or built-in users, is an authentication scheme that relies on a user repository within the APEX environment. Any user that is defined within the workspace in which the application is installed can be used to authenticate to the application.
Using Application Express account credentials allows you to quickly and easily create and maintain users. The ease with which you can set up users makes this authentication scheme attractive for quick prototyping.
One drawback to using Application Express account credentials is that users will be able to successfully authenticate to any application in the workspace that uses Application Express account credentials. However, you can use group membership and a custom authentication method to limit which users can successfully authenticate to your application. The following sections describe how to create users and groups, including how to use a custom authentication method to take group membership into account.
To create a new user who can authenticate to your application, log in to the workspace as a workspace administrator and navigate to Administration
Bear in mind that any end users you create will be able to authenticate to any application in that workspace that uses the Application Express account credentials authentication scheme. If you want to use this authentication method but would like to restrict the applications that users can log in to, you can use different workspaces to effectively partition your applications.
Once users have been authenticated, some applications may display the logged-in username, depending on the theme used. For example, the Light Blue theme (theme 15) shows the current username in the top-right corner of the page, as shown in Figure 2-5. Other themes may display the username in a different position, and some themes may not display the username at all.
If your application requires users to authenticate, displaying the username somewhere on the screen can be helpful. Being able to see the username at a glance can help to narrow down any problems you might have that affect one user but not another. Also, it's a quick and simple visual cue for users, allowing them to see exactly which account they have logged in as.
As well as being able to create individual users with the Application Express account credentials authentication scheme, you can also create groups and then add individual users to particular groups. A group can contain more than one user, and a user can belong to multiple groups.
To create a group, use the Manage Application Express Users link in the Application Builder. Figure 2-6 shows an example of a new group called End Users being created. After you create the group, you can make users members of that group. To do this, edit the individual user and select each group you want the user to be a member of, as shown in Figure 2-7.
As noted earlier, with groups, you can use a custom authentication method to limit which users can authenticate to your application. As shown in Figure 2-8, the default authentication function used for the Application Express account credentials method is the -BUILTIN-
function. This method will automatically check the username and password being used against the cookie (or built-in) users for the workspace.
In order to take group membership into account, you'll need to replace the -BUILTIN-
function with your own function, which should not only verify that the username and password are correct but also check that the user is in a particular group. To replace the existing -BUILTIN-
authentication function with your own, you need to provide a function with the following signature:
(p_username in varchar2, p_password in varchar2) return Boolean
The function should take two parameters: one for the username and one for the password. It needs to return a Boolean value that indicates whether the authentication succeeded or failed. For example, Listing 2-1 shows a custom authentication function that allows any user to authenticate (we'll get to a more useful function in a moment).
Example 2-1. A Simple Authentication Function That Always Succeeds
create or replace function authenticate(p_username in varchar2, p_password in varchar2) return boolean is begin return true; end authenticate;
You can then use your custom authentication function by replacing -BUILTIN-
in the Authentication Function section of the Login Processing page with the following:
return authenticate;
This tells the APEX engine that it should call your authenticate
function, passing in the username and password as parameters. The return result of your function is used to determine whether the user should be allowed to log in.
It is extremely important that your function have the exact signature that is expected; otherwise, you may receive an error similar to this:
ORA-06550: line 2, column 8: PLS-00306: wrong number or types of arguments in call to 'AUTHENTICATE' ORA-06550: line 2, column 1: PL/SQL: Statement ignored
The first step to making the authentication function useful is to modify it to verify the username and password in the same way that the -BUILTIN-
method does. Fortunately, you don't need to know the internals of where the usernames and passwords are stored, since APEX provides many helper packages, functions, and procedures to make your job easier.
The apex_util
package is one of these helper packages. It contains a number of functions and procedures directly related to working with cookie users. One such function is is_login_password_valid
, which can be used to validate a username and password against the cookie users defined in the workspace in which the application resides. This function has the following signature:
function is_login_password_valid(p_username in varchar2, p_password in varchar2) returns boolean
The apex_util
package was introduced in APEX version 2.2. If you have an earlier version, use htmldb_util
or wwv_flow_user_api
instead; for example, htmldb_util.is_login_password_valid
.
As discussed in Chapter 1, it's generally a good idea to put code such as a custom authentication method in a package, rather than using it as a stand-alone function. Using a package allows you to specify different authentication routines, perhaps one for development and another for a live environment. Then you can specify which authentication should be used in the package itself, rather than needing to modify the application when it is installed into the live environment.
Listing 2-2 shows the new packaged function, which can be used as a direct replacement for the -BUILTIN-
method.
Example 2-2. Authentication Packaged Function
create or replace package pkg_auth as function authenticate(p_username in varchar2, p_password in varchar2) return boolean; end; create or replace package body pkg_auth as function authenticate(p_username in varchar2, p_password in varchar2) return boolean is begin return apex_util.is_login_password_valid(p_username, p_password); end authenticate; end;
You will also need to change the Authentication Function section of the Login Processing page so that it references the packaged function:
return pkg_auth.authenticate;
Using this authentication function, any of the cookie users will be able to log in to the application, just as they could with the -BUILTIN-
method. You now need to modify the function to take group membership into account.
The apex_util
package also contains a function called get_groups_user_belongs_to
, which returns a string containing each group the user belongs to, delimited by commas. If the user doesn't belong to any groups, the function actually returns NULL
, rather than an empty string (which you might expect). The function also returns NULL
if you query the groups for a user that does not exist.
Listing 2-3 shows the updated packaged function that takes group membership into account.
Example 2-3. Authentication Package Function That Takes Group Membership into Account
create or replace package pkg_auth as -- define a constant to represent the group name c_end_user constant varchar2(9) := 'end_users'; function authenticate(p_username in varchar2, p_password in varchar2) return boolean; end; create or replace package body pkg_auth as function authenticate(p_username in varchar2, p_password in varchar2) return boolean is v_groups varchar2(32767); v_arrgroups apex_application_global.vc_arr2; b_group_member boolean := false; b_login_correct boolean; begin -- check the username and password are correct b_login_correct := apex_util.is_login_password_valid(p_username, p_password); -- retrieve comma delimited string containing each group v_groups := apex_util.get_groups_user_belongs_to(p_username); -- convert the comma delimited string into an array v_arrgroups := apex_util.string_to_table( p_string => v_groups, p_separator => ','), -- loop round the array and compare each entry to the constant -- representing the group for i in 1 .. v_arrgroups.count loop if(v_arrgroups(i) = c_end_user) then b_group_member := true; end if; end loop;
return(b_login_correct and b_group_member); end authenticate; end;
The package checks whether the username and password are valid, and whether the user is a member of the End Users group. In reality, you'd probably check one of these first, and then check the other only if the first condition evaluated to true. I wrote the example in this way so that it's easier to see how the return result depends on both the b_login_correct
value and the b_group_member
value.
You might think the package is more complicated than it needs to be, due to the use of the call to apex_util.string_to_table
. You might be tempted to simply use INSTR
or SUBSTR
to determine whether the comma-delimited string contains the group name of interest. However, you'd need to be extremely careful about the group names you were searching for. For example, Listing 2-4 shows how a false match can be made, since the group being searched for (Admin) appears as a substring of another group that the user is a member of (Payroll Admin).
Example 2-4. Incorrectly Matching the Group Name Using an INSTR
Match
jes@10gR2> var mygroups varchar2(200); jes@10gR2> var check_group varchar2(200); jes@10gR2> exec :mygroups := 'Payroll Admin,End User'; jes@10gR2> exec :check_group := 'Admin'; jes@10gR2> col is_member format a10 jes@10gR2> select decode(instr(:mygroups, :check_group), 0, 'N', 'Y') is_member from dual IS_MEMBE ---------- Y
Although this example shows how the INSTR
function was used incorrectly, you don't have to completely avoid it; APEX itself uses INSTR
to parse the input string. To ensure that the :mygroups
stringis parsed correctly, your decode must look for not only the group name, but also the delimiters. The code in Listing 2-5 shows the corrected decode statement.
Example 2-5. Correctly Matching the Group Name Using an INSTR
Match
jes@10gR2> select decode(instr(','||:mygroups||',', ','||:check_group||','), 0, 'N', 'Y') is_member from dual
The example in Listing 2-4 is not actually that contrived. In a very similar way, users of a production system (not an APEX system) were suddenly able to access parts of the system they should not have been able to reach. It was an absolute nightmare to track down the cause, mainly because developers swore that no code had been changed. Yet the application was behaving "incorrectly."
Using the string_to_table
routine to convert the comma-delimited string into an array makes searching through the entries much less error-prone when looking for an exact match. Whenever you write routines that deal with authentication and authorization, it's important to consider how they could be abused. You need to check whether an end user could make the routine behave in a way you wouldn't expect and possibly circumvent the security of your application, even unintentionally. It's also a good idea to hand your routine to a colleague and ask her to "break this if you can." It's far better to have one of your colleagues find a security risk than have it discovered by an end user (or even worse, someone who shouldn't even be an end user).
The string_to_table
routine is incredibly useful. The apex_util
package also contains a table_to_string
routine, which converts an array into a delimited string. These routines can save you a lot of time and effort when you need to pass multiple values around in your application.
So, with the new packaged authentication function in place, you should find that you'll be able to successfully authenticate to the application only if you enter a valid username and password, and the username you enter is a member of the End Users group. For example, the jes
user will be able to log in, but the peterw
user will receive the "Invalid Login Credentials" message. If you add the peterw
user to the End Users group, then you'll be able to successfully authenticate as peterw
.
You can now completely control which cookie users are able to successfully authenticate to your application. You can also deploy multiple applications within the same workspace and easily partition them from each other so that cookie users can't access your application unless they are in a particular group. You can easily use this method to allow different users to authenticate depending on whether the application is running in the development, test, or live environment.
Using the Application Express account credentials method is a very quick and easy way to establish a user repository for your application. However, many people don't like having to connect to the workspace as a workspace administrator in order to create and maintain the users and groups.
Once again, the apex_util
package comes to the rescue—with routines that enable you to programmatically perform many of the tasks that you'd otherwise need to perform as a workspace administrator. For example, if you want to be able to create new users from within the application itself, you could use the apex_util.create_user
function:
procedure create_user
and get the following results:
argument name type in/out default? --------------------------- ----------- ------ -------- p_user_id number in default p_user_name varchar2 in p_first_name varchar2 in default p_last_name varchar2 in default p_description varchar2 in default p_email_address varchar2 in default p_web_password varchar2 in p_web_password_format varchar2 in default p_group_ids varchar2 in default p_developer_privs varchar2 in default p_default_schema varchar2 in default p_allow_access_to_schemas varchar2 in default
p_attribute_01 varchar2 in default p_attribute_02 varchar2 in default p_attribute_03 varchar2 in default p_attribute_04 varchar2 in default p_attribute_05 varchar2 in default p_attribute_06 varchar2 in default p_attribute_07 varchar2 in default p_attribute_08 varchar2 in default p_attribute_09 varchar2 in default p_attribute_10 varchar2 in default
This function might look a bit overwhelming, but the majority of the parameters have default values. At the minimum, you could pass in just the p_user_name
and the p_web_password
parameters. For example, you could create a page process that contains the PL/SQL anonymous block code shown in Listing 2-5, passing in some of the page items as parameters.
Example 2-5. Calling the apex_util.create_user
Function from a PL/SQL Page Process
apex_util.create_user(p_user_name => :P1_USERNAME, p_email_address => :P1_EMAIL, p_first_name => :P1_FIRST_NAME, p_last_name => :P1_SURNAME, p_web_password => :P1_PASSWORD);
Note that you can successfully call the apex_util.create_user
function (and many of the other routines in the apex_util package
) only if you are logged in to the application as a cookie user with workspace administrator privileges. If you attempt to execute the call while logged in to the application as a developer or end user, you will get an error similar to this:
ORA-20001: User requires ADMIN privilege to perform this operation.
Anytime you see this error on your page, it's a sure sign you're trying to call one of the apex_util
routines while logged in to the application as a user who does not have ADMIN
privileges.
The apex_util
package contains other useful routines for managing users, such as delete_user, create_user_group
, and edit_user
, as well as routines to get and set particular attributes, such as set_email_address
. Using these routines, you can fully manage the cookie user repository from within your application. You could also use a separate administration application, which would enable you to present a more uniform appearance and seamless integration with your applications than you can with the workspace administration tools.
Many of the packages, functions, procedures, and views available in APEX work correctly only when they are used from within the APEX environment. For example, if you try to use them while connected to a SQL*Plus session, you may find that views return no rows and the functions and procedures don't work, or they appear to work but don't give the correct results.
The create_user
function has attribute parameters, such as p_attribute_01, p_attribute_02
, and so on, which you can use to store up to ten additional bits of information related to a particular user. For example, to store the telephone extension number for the user, modify the create_user
call as shown in Listing 2-6.
Example 2-6. Using Custom Attributes in the apex_util.create_user
Function
apex_util.create_user(p_user_name => :P1_USERNAME, p_email_address => :P1_EMAIL p_first_name => :P1_FIRST_NAME, p_last_name => :P1_SURNAME, p_web_password => :P1_PASSWORD, p_attribute_01 => :P1_EXT_NUMBER);
This example uses page items from page 1 in the application as the parameters to the procedure. For example, P1_FIRST_NAME
is a page item where the user can enter a first name.
You can then use the get_attribute
function to retrieve the value for a particular attribute. For example, putting the following piece of code in a PL/SQL anonymous block region would display the value of the first attribute for the logged-in user:
htp.p(apex_util.get_attribute(p_username => :APP_USER, p_attribute_number => 1));
Note that you refer to the attribute by number, not by name, so you need to be sure you're consistent when you use the attributes. It's easy to forget to change all references if you move the attributes around—for example, if you decide to store the extension number in p_attribute_02
rather than p_attribute_01
.
You can also store attributes with a user who has already been created by using the set_attribute
function, as shown in Listing 2-7. One quirk of using set_attribute
is that, rather than passing in the username as a string, you need to pass in the numeric ID of the username. (Not including an overridden set_attribute
procedure that allows you to pass in the username as a string seems like an oversight, since many of the other administration routines in the apex_util
package let you pass in either a string or numeric identifier.) Fortunately, you can easily retrieve this numeric ID for a particular username by using the get_user_id
function, as shown in Listing 2-7.
Database account authentication was added in APEX version 2.2 (and is also available in the 2.1 release bundled with Oracle XE). This method allows you authenticate your users against real Oracle database accounts.
To set up an application with database account credential authentication, choose to create a new authentication method based on one from the gallery, and then select Show Login Page and Use Database Account Credentials, as shown in Figure 2-9.
When you create the authentication scheme, you need to specify which login page you wish you use. You can usually select page 101 (assuming that's specified as your existing login page), or you can use a built-in login page. You can change this option later by editing the authentication scheme, so it doesn't really matter which you choose at this point. The disadvantage of using the built-in login page is that you can't customize it as easily. When you use your own login page, you can modify it to make it more integrated with your application.
Users will be able to successfully authenticate to your application only if they use a valid database username and password. This is an ideal solution if you are already using database accounts as a user repository for other applications.
To allow a new user to authenticate to your application, you'll need to create a new database user in the database. For example, you might create a demouser
user while connected to the database with SQL*Plus:
system@10gR2> create user demouser identified by demopassword; User created.
You can now use this username and password to authenticate to the application, as shown in Figure 2-10.
The database authentication method obeys the rules regarding whether the account is locked. For example, the demouser
account can be locked from a SQL*Plus session:
system@10gR2> alter user demouser account lock; User altered.
If you then tried to authenticate as demouser
user, you'd receive the message shown in Figure 2-11. The user will not be able to successfully authenticate to your application until the account is unlocked, as follows:
system@10gR2> alter user demouser account unlock; User altered.
At this point, you might start thinking of all the possibilities that have suddenly become open. For example, you might try to prevent an account from being shared by multiple people by restricting the number of times the user can be logged in simultaneously. This sort of functionality is relatively simple to achieve using Oracle accounts and profiles, as in this example:
system@10gR2> grant create session to demouser; Grant succeeded. system@10gR2> create profile demo_profile limit sessions_per_user 1; Profile created. system@10gR2> alter user demouser profile demo_profile; User altered.
Now if you were to use two different SQL*Plus sessions to connect to the database as demouser
, the first connection would succeed, but the second SQL*Plus session would result in an error:
Enter user-name: demouser Enter password: ERROR: ORA-02391: eceeded simultaneous SESSIONS_PER_USER limit
The second SQL*Plus session will not be able to succeed until the first SQL*Plus session is ended.
However, you'll find that if you launch two separate browser sessions and try to log in to your application as the demouser
in each session, both sessions will be allowed to successfully authenticate! The profile restriction you witnessed with a SQL*Plus session does not apply in your application. This demonstrates a very important point to remember when you use database authentication:
Database account credentials are only used to authenticate against. You are not actually connecting to the database as that user. The connection to the database is made using the credentials specified in the DAD.
If you look closely at the example of setting up the restriction, you'll see that it uses grant create session to demouser
. This is necessary to log in to the account using SQL*Plus; otherwise, you would receive this error:
ORA-01045: user DEMOUSER lacks CREATE SESSION privilege; logon denied
However, you are able to authenticate to the application before that grant is performed. This clearly shows that database account authentication is not creating a session to the database as the username you logged in with.
By creating your own custom authentication scheme, you are in complete control over how and where your user repository is stored and how you authenticate users against that repository. Your custom authentication scheme can be as simple or as complex as you need. You can create a custom authentication scheme that extends or adapts one of the preconfigured schemes in the gallery, or you can build an entirely new authentication scheme.
One common custom authentication scheme can be referred to as "table-driven authentication." With this type of scheme, you store your user repository in a table or across a number of tables. When a user tries to authenticate to your application, your authentication scheme checks to see if the supplied username and password match an entry stored in the table. The scheme can also include any other logic you'd like, such as checking to see if the account is active or inactive (similar to the locked/unlocked status for database account credentials).
To demonstrate creating a custom authentication scheme, this section describes how to build a simple application using an Excel spreadsheet as the source. This application will be the basis for most of the examples in this and following chapters. Once the application is built, you'll set up table-driven authentication. I'll also cover some typical business requirements you might come across, including locking accounts and automating user registration.
The previous version of this text went into greater detail on how to create applications using desktop tools as the source. While the ability to use desktop tools is a powerful feature, we will discuss it only as a method for creating a basic application, which we will then modify.
Figure 2-12 shows the spreadsheet we'll use to create the Buglist application. It is a basic spreadsheet with headings in row 1 and data in the subsequent rows. Apex will consume this file and create a report and a corresponding form for data entry and modification.
To create an application from this spreadsheet, navigate to the Application Builder and click the Create button. Choose the option to create a Database Application and click next. The screen shown in Figure 2-13 allows you to specify that the new application will be based on a spreadsheet.
The spreadsheet wizard is very easy to use and creates a usable Apex application very quickly. In the next step of the process, you can either upload or cut and paste your spreadsheet into Apex as shown in Figure 2-14. If your spreadsheet is built such that you can use all of the contents, we recommend that you upload the file. If you only want to use a portion of an existing spreadsheet, cutting and pasting is the way to go.
To ensure that you get the data and only the data from the spreadsheet, the wizard asks for the field separator and for any delimiter that may be used to enclose strings of text. In most cases that use csv files, the field delimiter is the comma and the optional enclosing delimiter is the double quote mark. For the buglist file, the data is delimited by commas and there's no optional enclosure, as shown in Figure 2-15.
Excel does not export CSV or tab-delimited data in UTF-8 by default. The data may become corrupted if you export from Excel using either of those formats and subsequently load that data into a UTF-8 database. If your database uses UTF-8, make sure to export from Excel in UTF-8.
Once the file has been uploaded, you can define the properties of the table that will contain the data from the spreadsheet. In this case, we called the table "BUGLIST" as shown in Figure 2-16. Since the column names from the spreadsheet are nice and neat, they don't need to be changed. As you can see, you have complete control over the properties of each column in the source data.
Following the table definition, the Create Application wizard allows you to define the labels for each column on the data entry form as shown in Figure 2-17. The default values for column labels are the column names themselves. Since the column names are descriptive enough for a sample application, they can be left alone with the possible exception of "Bugid," which could be changed to "Bug Id." The singular and plural names are used on forms that we'll develop later. You can use "Bug" and "Bugs" for now, but either can be changed later.
During the final steps of the creation process, you can assign the name, type, and visual attributes of the application. The available creation types are:
Read Only
Read and Write; use this setting for the Buglist application
Report Implementation Type
Apex 4.0 provides two choices for Report Implementation:
Interactive
Classic; use this report type
The "Classic" report contains standard text items and search buttons that can be easily manipulated using the standard features. The "Interactive" report type includes a Search field and a Go button, but they are not as accessible as the same values in a classic report. We will get into interactive reporting in later chapters.
You also set the theme, or look and feel, of the application at this point. For the Buglist application, I selected the light blue theme (theme 15).
If you run the application you just created, you'll be presented with a page asking for your username and password. This is because the application was created using APEX authentication, which means you'll need to log in using the credentials of an APEX user created by a workspace administrator. Figure 2-18 shows the page that's displayed once you have logged into the application.
For the custom authentication example, we will modify the Buglist application so that all of the user account information is stored in a table. At the very least, we want to be able to store the following pieces of information about a user:
Username
Password
First name
Surname
E-mail address
Listing 2-8 shows the script for creating this user repository.
Example 2-8. Script for Creating the User Repository
apexdemo@10gR2> create table user_repository( username varchar2(8), password varchar2(8), forename varchar2(30), surname varchar2(30), email varchar2(50), primary key (username) ); Table created. apexdemo@10gR2> insert into user_repository values ('john', '1234', 'john', 'scott', '[email protected]'), 1 row created. apexdemo@10gR2> insert into user_repository values ('peterw', '9876', 'peter', 'ward', '[email protected]'), 1 row created. apexdemo@10gR2> commit;
Listing 2-8 shows two records inserted into the table, representing two different user accounts. This table definition is relatively simple. We will extend this definition later to make it more realistic and useful. For example, Listing 2-8 uses plain-text passwords, which is obviously extremely bad practice. Later in the chapter, you'll see how we can avoid using plain text and make the application much more secure.
Notice that the username is specified as the primary key. This works because it's unlikely you'd store two usernames with the same value. However, some people like to allow users to be able to change their usernames, and this approach would cause problems if the username were being used as a foreign key. For example, you might want to let a user who just got married change her username from anne_smith
to anne_jones
, but also want to maintain all of the user's history. In that case, you might prefer to use a sequence as a surrogate primary key, rather than the username.
Some people (such as the author) believe that entities such as usernames should be immutable. If the user wants to use a different username, you should create a new account with that new username, rather than letting the user change her username. The user can then stop using the old account and begin using the new one. Allowing identifiers such as usernames to be modified can create all sorts of headaches, particularly if you are trying to correlate audit log entries with a particular user. However, this is a personal preference. You may believe you have a valid business reason for allowing users to change their existing usernames.
You can now create a new authentication scheme. When you create an authentication scheme from scratch, you can use the creation wizard to step through each setting, or you can create the scheme and then edit it later to make your configuration changes. Figure 2-19 shows a new authentication scheme being created.
You can define many attributes for a custom authentication scheme, so it's worth taking the time to understand precisely what each attribute means:
You can specify a function that will be executed before a page in your application is requested or submitted. The function will return a Boolean value to indicate whether the page should be displayed or the user should be redirected to a login page, or some other page of your choice. If you don't specify anything here, the built-in logic will be used.
The page sentry function will call this function to determine if a valid session exists for the current page request or submission. If you specified your own page sentry function, you don't need to specify anything here, since your page sentry function should also perform the session verification. If you are using the built-in page sentry functionality, you can either specify your own session verification function here or leave it blank—in which case, the built-in functionality will be used.
You can specify the target that will be used if the page sentry function returns false, which indicates that the session is considered invalid. You can redirect the user to the built-in login page, a page in your application, a URL, or an Oracle SSO application server.
You can specify code that will be executed immediately before the user's account credentials are verified.
This is where you can specify the function that will perform the credentials verification. The function you specify must accept two parameters: p_username
and p_password
, which are both of type VARCHAR2
, and it must return a Boolean result, which is used to indicate whether the user has been successfully authenticated.
This is similar to the preauthentication process, except it allows you to specify code that will be executed after the credentials have been verified.
This allows you to specify the cookie attributes for your application, such as the cookie name, path, and domain.
This allows you to specify the value that will be used for the application attribute LOGOUT_URL
, which is commonly displayed on the page for the user to click to log out of an application. You can include many substitution variables here, such as &APP_ID.
(note the terminating period).
For now, create the scheme by clicking Create Scheme. You can go back and change the individual items after you've created the function to perform the actual authentication.
Your authentication function must have this signature:
(p_username in varchar2, p_password in varchar2) return Boolean
The function will need to compare the username and password with the rows stored in the user_repository
table, returning true if the username and password match a row in the table, or returning false if no matching row can be found. Listing 2-9 shows the pkg_auth.authenticate
function originally used in the Application Express Account Credentials section modified to verify users against the user_repository
table.
Example 2-9. The pkg_auth.authenticate
Function Modified to Verify Users Against a Table
create or replace package pkg_auth as function authenticate(p_username in varchar2, p_password in varchar2) return boolean; end; create or replace package body pkg_auth a function authenticate(p_username in varchar2, p_password in varchar2) return boolean is -- default the result to 0 v_result integer := 0; begin -- store 1 in v_result if a matching row -- can be found select 1 into v_result from user_repository where username = lower(p_username) and password = p_password; -- return true if a matching record was foun return(v_result = 1); exception -- if no record was found then return false when no_data_found then return false; end authenticate; end;
This authenticate
function is straightforward. It simply tries to find a row in the user_repository
table where the username and password match the parameters that were passed to the function. Notice that the p_username
variable is being forced to lowercase. This is done for two reasons:
The usernames inserted into the user_repository
table are in lowercase
The p_username
variable will be passed in as uppercase from the application. Using lower()
on the variable ensures that the cases in this comparison match. We avoid using a function on the username column as it may make an index on this column unusable.
Another way to handle this situation would be to populate the user_repository
table with uppercased values. Since the application passes in uppercase values, the comparison would not require case conversion.
You can verify that the packaged function works correctly by calling the function from some anonymous PL/SQL code in SQL*Plus, as shown in Listing 2-10.
Example 2-10. Confirming the authenticate
Function Works
apexdemo@10gR2> set serveroutput on; apexdemo@10gR2> declare bres boolean := false; begin-- use the correct username and password
bres := pkg_auth.authenticate('john', '1234'), if (bres = true) then dbms_output.put_line('Authentication was successful'), else dbms_output.put_line('Authentication failed'), end if; end;Authentication was successful
PL/SQL procedure successfully completed. apexdemo@10gR2> declar bres boolean := false; begin-- use an incorrect password
bres := pkg_auth.authenticate('john', '12345'), if (bres = true) then dbms_output.put_line('Authentication was successful'), else dbms_output.put_line('Authentication failed'), end if; end;Authentication failed
PL/SQL procedure successfully completed.
You can now modify the authentication scheme to use the packaged function, as shown in Figure 2-20.
You could, if you prefer, remove the case-sensitivity match altogether to make it even more difficult for someone to guess usernames and passwords (in other words, not only does he need to guess the username, he also needs to know the exact case of the username). This means that JScott
would be treated differently from jscott
, for example. In general, though, you should not make usernames case-sensitive unless you have specific reason to do so. However, passwords should most definitely be case-sensitive.
Actually, you can use another method to completely remove the issue of the username being automatically uppercased. If you are using a login page that was generated for you automatically by the application creation wizard (as in the case of the Buglist application), there will be an after-submit page process on the login page called Login. If you examine this page process, you'll find that it runs an anonymous PL/SQL block like this:
wwv_flow_custom_auth_std.login( P_UNAME => :P101_USERNAME, P_PASSWORD => :P101_PASSWORD, P_SESSION_ID => v('APP_SESSION'), P_FLOW_PAGE => :APP_ID||':1' );
This piece of code will set the :APP_USER
item and redirect to the value specified by the P_FLOW_PAGE
parameter after a successful login.
To prevent the username from being uppercased before it is passed to your authentication function, you can use the P_PRESERVE_CASE
parameter to the wwv_flow_custom_auth_std.login
function, as in this example:
wwv_flow_custom_auth_std.login(
P_UNAME => :P101_USERNAME,
P_PASSWORD => :P101_PASSWORD,
P_SESSION_ID => v('APP_SESSION'),
P_FLOW_PAGE => :APP_ID||':1',
P_PRESERVE_CASE => TRUE
);
Although we're not going to go into depthabout optimizing your queries, since that's a topic that could easily span several books, it's worth looking at the impact of modifying the query into one that performs a case-insensitive search against the username if you have a relatively large number of entries in the table.
Suppose you insert an extra 9,999 rows into the user_repository
table from a SQL*Plus session:
apexdemo@10gR2> insert into user_repository (username, password) (select 'user' || rownum, 'pass' || rownum from all_objects where rownum < 10000); 9999 rows created. apexdemo@10gR2> commit;
The user repository now has more than 10,000 entries (including the 2 original ones). A user repository of this size is certainly not unusual for an Internet application or an intranet application of a large corporation.
Make sure the statistics on the table are up-to-date:
apexdemo@10gR2> exec dbms_stats.gather_table_stats('APEXDEMO', 'USER_REPOSITORY'), PL/SQL procedure successfully completed.
Now if you view the explain plan for the original query, where the username is not uppercased:
apexdemo@10gR2> select 1 as x from user_repository where username = 'john' and password = '1234'
you'll see something similar to this
X ---------- 1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=18) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'USER_REPOSITORY' (TABLE) (Cost=2 Card=1 Bytes=18) 2 1 INDEX (UNIQUE SCAN) OF 'SYS_C008389' (INDEX (UNIQUE)) (Cost=1 Card=1) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 386 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Notice this line:
INDEX (UNIQUE SCAN) OF 'SYS_C008389'
This shows that the query used the system-generated index that was created due to the primary key on the username
column. Also, you can see that the query resulted in three consistent gets. Compare this with the explain plan you'd get for the query where you uppercase the username column:
apexdemo@10gR2> select 1 as x from user_repository where upper(username) = 'JOHN' and password = '1234' X ---------- 1
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=18) 1 0 TABLE ACCESS (FULL) OF 'USER_REPOSITORY' (TABLE) (Cost=9 Card=1 Bytes=18) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 39 consistent gets 0 physical reads 0 redo size 386 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
This time, the query results in a full scan of the user_repository
table, because the upper
function meant that the system-generated index could not be used. And as a result of the full scan, the consistent gets increase to 39.
To avoid having to full-scan the table, you can create a function-based index, which will be used by the query that contains the function call on the column:
apexdemo@10gR2> create index user_repository_upper_idx on user_repository(upper(username)); Index created.
Note that the function uses the same function call that is used in the query. In other words, the function has been applied to the data for that column, and the result of the function call is then contained in the index, rather than the original data.
If you now rerun the query containing the upper
function call and look at the explain plan, you'd see something similar to this:
apexdemo@10gR2> select 1 as x from user_repository 2 where upper(username) = 'JOHN' and password = '1234'; X ---------- 1
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=18) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'USER_REPOSITORY' (TABLE) (Cost=2 Card=1 Bytes=18) 2 1 INDEX (RANGE SCAN) OF 'USER_REPOSITORY_UPPER_IDX' (INDE ) (Cost=1 Card=40) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 386 bytes sent via SQL*Net to client 512 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
You can see that the function-based index user_repository_upper_idx
is now being used, and the number of consistent gets has dropped to 4.
If you are not storing your usernames in uppercase in your table and you are making a call to the upper
function in your query, investigate using a function-based index. You may feel that it's not worthwhile if you don't expect to have many users stored in your table repository. However, many huge systems have evolved from small ones, and quite often you don't get the chance to check things until performance problems start to occur.
It's a very bad idea to store a password in a table as plain text, since anyone who can access that table will be able to view the list of valid usernames and their corresponding passwords.
As an alternative, you might decide to store the username in an encrypted format. While this is obviously more secure, it still poses a security risk. Encrypting the password implies that there is a decryption method. If your encryption method uses an encryption key, you'll need to ensure that the key does not fall into the wrong hands; otherwise, the security can be easily compromised by running the encrypted value through the decryption routine using the key.
A far better solution is to use a cryptographic hashing function such as MD5. A hash function will, for a given input, produce an output called the hash value. You can use this function to store the hash value, rather than the actual plain text password, in the table. The authentication function will then use the same hash function against the supplied password and produce a hash value that can be compared against the hash value stored in the user repository. Hash functions are one-way functions—you can't obtain the original input value from the hash value.
Depending on the actual hash function, the chance of two inputs producing the same hash value can vary substantially. A hash value that can be produced by two different input values is known as a collision. If a function produces many collisions, malicious users would have an easier time gaining access, because they would only need to guess a password that produced the same hash value, rather than the precise password. The MD5 function produces very few collisions. In other words, it is mathematically unlikely that two input values will produce the same hash value.
The dbms_obfuscation_toolkit
package contains many procedures and functions related to encryption and hashing, including some for calculating MD5 hashes. Listing 2-11 shows how you can use the MD5 function call to convert a plain-text string into an MD5 hash value.
Example 2-11. Using the dbms_obfuscation_toolkit
Package
apexdemo@10gR2> var plaintext varchar2(30) apexdemo@10gR2> var hashvalue varchar2(32) apexdemo@10gR2> exec :plaintext := 'password'; PL/SQL procedure successfully completed. apexdemo@10gR2> exec :hashvalue := UTL_I18N.STRING_TO_RAW( dbms_obfuscation_toolkit.md5(input_string => :plaintext)); PL/SQL procedure successfully completed. apexdemo@10gR2> print hashvalue HASHVALU ------------------ 5F4DCC3B5AA765D61D8327DEB882CF99
The dbms_obfuscation_toolkit
package contains two overloaded MD5 functions. One accepts a RAW
parameter and returns the result as a RAW
. The other accepts a VARCHAR2
parameter and returns the result as a VARCHAR2
. However, since the RAW
data type is a subtype of the VARCHAR2
data type, this can lead to all sorts of problems when you try to call the MD5 function. Fortunately, the two functions have differently named parameters:
FUNCTION MD5(INPUT IN RAW) RETURNS RAW(16) FUNCTION MD5(INPUT_STRING IN VARCHAR2) RETURNS VARCHAR2(16)
Listing 2-11 specifies the named parameter input_string
so that the correct overloaded function will be used. Also, it uses the UTL_I18N
package so that the string_to_raw
function can be employed to cast the string returned from the MD5 function into the RAW
format. This is a much friendlier hexadecimal format to read and store than the string, which would likely contain unprintable characters.
You can now modify the user_repository
table to add a column for storing the hashed password:
apexdemo@10gR2> alter table user_repository add (password_hash raw(16)); Table altered.
Ideally, you'd update the table so that the password_hash
column contains the MD5 hash of the password stored in the password
column. However, if you try that, you'll run into the overloaded function issue mentioned earlier:
apexdemo@10gR2> update user_repository set password_hash = UTL_I18N.STRING_TO_RAW( dbms_obfuscation_toolkit.md5(password)); update user_repository set password_hash = UTL_I18N.STRING_TO_RAW( dbms_obfuscation_toolkit.md5(password)) * ERROR at line 1: ORA-06553: PLS-307: too many declarations of 'MD5' match this call
You can't use named parameters in SQL either, so it would be no good trying to specify input_string
in the query:
apexdemo@10gR2> update user_repository set password_hash = UTL_I18N.STRING_TO_RAW( dbms_obfuscation_toolkit.md5(input_string => password)) apexdemo@DBTEST> / update user_repository set password_hash = UTL_I18N.STRING_TO_RAW( dbms_obfuscation_toolkit.md5(input_string => password)) * ERROR at line 1:
ORA-00907: missing right parenthesis
Starting in 11 g, you can use the utl_raw.cast_to_raw
function instead of utl_i18n.string_to_raw
and the following update
statement will work:
update user_repository set password_hash = UTL_raw.cast_TO_RAW(dbms_obfuscation_toolkit.md5(input_string => password ))
To work around this problem in 10g versions of the database, you can create a function that will act as a wrapper around the MD5 call:
apexdemo@10gR2> create or replace function md5hash (p_input in varchar2) return varchar2 is begin return upper(dbms_obfuscation_toolkit.md5 (input => utl_i18n.string_to_raw(p_input))); end md5hash; Function created.
In 11g, the function would look like this:
apexdemo@10gR2> create or replace function md5hash (p_input in varchar2) return varchar2 is begin return utl_raw.cast_to_raw(dbms_obfuscation_toolkit.MD5(input_string=>p_input)); end md5hash;
Although the dbms_obfuscation_toolkit
package is still available in 11g (11.2.0.1), it is set to be deprecated. Its replacement is the dbms_crypto
package, which you should start using instead of the dbms_obfuscation_toolkit
. You can modify the md5hash
function to use the new encryption package as follows:
function md5hash (p_input in varchar2) return varchar2 is begin return dbms_crypto.hash(utl_raw.cast_to_raw(p_input),2); end md5hash;
The dbms_crypto.hash
function expects an input value of type raw
and an encryption algorithm designator. The hash encryption types are as follows:
1 = MD4
2 = MD5
3 = SH1
To ensure that you get the same results from both the dbms_obfuscation_toolkit
and the dbms_crypto
packages, use the MD5 designator (2) in the call to dbms_crypto
.
apexdemo@10gR2> select md5hash('password') from dual; MD5HASH('PASSWORD' --------------------------------------------------------- 5F4DCC3B5AA765D61D8327DEB882CF99
You can now use this wrapper function to update the user_repository
table:
apexdemo@10gR2> update user_repository 2 set password_hash = md5hash(password); 2 rows updated. apexdemo@10gR2> commit; Commit complete. apexdemo@10gR2> select * from user_repository;
apexdemo@10gR2> select username, password, password_hash 2 from user_repository; USERNAME PASSWORD PASSWORD_HAS -------- -------- -------------------------------- john 1234 81DC9BDB52D04DC20036DBD8313ED055 peterw 9876 912E79CD13C64069D91DA65D62FBB78C
You can make another improvement to this routine. The MD5 checksum is based only on the password, so two users who have the same password would have the same value in the password_hash
column, as shown in Listing 2-12.
Example 2-12. Two Users with the Same Password Have the Same Password Hash
apexdemo@10gR2> insert into user_repository (username, password, forename, surname, email) values ('jimb', '1234', 'James', 'Brookfield', '[email protected]') 1 row created. apexdemo@10gR2> update user_repository set password_hash = md5hash(password); 3 rows updated. apexdemo@10gR2> select username, password, password_hash from user_repository; USERNAME PASSWORD PASSWORD_HAS -------- -------- -------------------------------- john 1234 81DC9BDB52D04DC20036DBD8313ED055 peterw 9876 912E79CD13C64069D91DA65D62FBB78C jimb 1234 81DC9BDB52D04DC20036DBD8313ED055
This sort of information could potentially be used as an attack vector if a malicious user discovered that he and another user had the same password hash value. Since the malicious user already knows his own password, he would be able to deduce the other user's password from the fact that their hashes are the same. You can avoid this issue by passing in a string containing the concatenated username and password to the MD5 wrapper function, as shown in Listing 2-13.
Example 2-13. Combining the Username and Password in the Hash Function
apexdemo@10gR2> update user_repository set password_hash = md5hash(upper(username) || password); 3 rows updated. apexdemo@10gR2> commit;
Commit complete. apexdemo@10gR2> select username, password, password_hash from user_repository; USERNAME PASSWORD PASSWORD_HAS -------- -------- -------------------------------- john 1234 9B57B72DA06D24A934DEC92457B44974 peterw 9876 F635746DF6E7E69D1B6698B79D65CD7F jimb 1234 DF2270203A47F5A0A51D484D77C2FFC5
With this approach, even if two users have the same password, their hash values will be completely different. Note that you also uppercase the username before passing it to the MD5 hash function, since the username will uppercased before being passed to the custom authentication function, as described earlier.
The authentication function can now be modified so that it performs the same hash function on the username and password the user is trying to authenticate with, and then compares that hash value with the hash value that is stored in the user_repository
table, as shown in Listing 2-14.
Example 2-14. Modified pkg_auth
Package to Work with Password Hashes Using dbms_crypto
create or replace package pkg_auth as
function authenticate(p_username in varchar2, p_password in varchar2)
return boolean;
end;
create or replace package body pkg_auth a
-- wrapper function to compute the MD5 hash
function md5hash (p_input in varchar2) return varchar2 is
begin
return dbms_crypto.hash(utl_raw.cast_to_raw(p_input),2);
end md5hash;
function authenticate(p_username in varchar2, p_password in varchar2)
return boolean is
v_result integer := 0;
v_hash varchar2(32);
begin
v_hash := md5hash(p_username || p_password);
select 1
into v_result
from user_repository
where upper(username) = upper(p_username)
and password_hash = v_hash;
return(v_result = 1);
exception
when no_data_found then
return false;
end authenticate; end;
Now you just need to remember to drop the plain-text password column from the user_repository
table so that it can't be viewed:
apexdemo@10gR2> alter table user_repository drop(password); Table altered.
With a few relatively simple changes to the user_repository
table and your authentication scheme, you have made your whole application much more secure. Also, by keeping the authentication code within a package, you are forming the basis of a security module that can be easily reused in different applications.
The ability to lock and unlock user accounts is very useful. For example, when employees leave a company, you probably don't want them to be able to access your applications and systems. However, you may have problems deleting their accounts if their usernames (or other surrogate keys) are being used as foreign keys from other tables, such as an audit trail. An easier solution is to introduce an attribute into the account record that indicates whether it is locked. If an account is locked, the user can't authenticate using that account.
You can add an extra column to the user_repository
table—a flag indicating the locked status. A value of Y
indicates locked, and a value of N
indicates that the account is not locked (and therefore the user is allowed to authenticate with it). Listing 2-15 shows the modifications.
Example 2-15. Adding the locked_flag
and Constraint
to the user_repository
Table
-- add the new columnn to the table apexdemo@10gR2> alter table user_repository 2 add(locked_flag char(1)); Table altered. -- set one record to be a locked status apexdemo@10gR2> update user_repository set locked_flag = 'Y' 2 where username = 'john';
1 row updated. -- set all the other records to unlocked apexdemo@10gR2> update user_repository set locked_flag = 'N' 2 where locked_flag is null; 2 rows updated. apexdemo@10gR2> commit; -- add a not null constraint to the colum apexdemo@10gR2> alter table user_repository 2 modify locked_flag not null; Table altered. -- add a check constraint to ensure that the locked_flag is either 'Y' or 'N' apexdemo@10gR2> alter table user_repository add constraint 2 locked_flag_yn check (locked_flag in ('Y','N')); Table altered. apexdemo@10gR2> select username, locked_flag from user_repository; USERNAME LOCKED_FLAG -------- --------------- david Y tim N brian N
You can now modify the authentication function to take the value in the locked_flag
column into account when deciding whether to allow the user to authenticate. The simplest case would be to just include an additional where
clause restriction in the query to search for a record where the username and password hash match and the account is not locked, like this:
select 1 into v_result from user_repository where upper(username) = upper( p_username) and upper(password_hash) = v_hash ..............................................
You will now find that if you attempt to authenticate to the application with a username that has the locked_flag
set to 'Y
' (or more correctly it isn't set to 'N
'), you won't be able to log in.
When you attempt to log in with a locked account, you'll be presented with the "Invalid Login Credentials" message, as shown in Figure 2-21. Depending on your particular requirements, this may not be the most logical message to display.
This message appears because the page template for the login page includes the substitution item #NOTIFICATION_MESSAGE#
. You might think it would be straightforward to change the message to something like "The Account Is Locked," but that's not the case. To understand why changing this message is not straightforward, you need to know what happens when a user tries to log in to the application.
When a login page is created automatically by a wizard, it contains a page process with code similar to the following:
wwv_flow_custom_auth_std.login( P_UNAME => :P101_USERNAME, P_PASSWORD => :P101_PASSWORD, P_SESSION_ID => v('APP_SESSION'), P_FLOW_PAGE => :APP_ID||':1' );
The wwv_flow_custom_auth_std.login
procedure is also known as the Login API. The Login API is responsible for performing the authentication and session registration for a user. It determines your current authentication scheme and interfaces with all of the authentication scheme items described earlier in this chapter (such as the authentication function). You can think of the Login API as a wrapper around the authentication scheme.
The Login API produces the "Invalid Account Credentials" message if the authentication function returns false. There is no way to pass in an alternative message. The only way to change the message is by removing the call to wwv_flow_custom_auth_std.login
and using your own logic to determine what should happen if the authentication fails. To help with this, you can use some of the built-in session-handling features. For example, you can replace the code in the login page process with the code in Listing 2-16.
Example 2-16. Invoking a Custom Post-Login Procedure
declare
bresult boolean := FALSE;
begin
-- use the existing authenticate function
bresult := pkg_auth.authenticate(upper(:P101_USERNAME),
:P101_PASSWORD);
-- call the post_login procedure if th
-- authentication was successful
if (bresult = true) then
wwv_flow_custom_auth_std.post_login(
P_UNAME => :P101_USERNAME,
P_PASSWORD => :P101_PASSWORD,
P_SESSION_ID => v('APP_SESSION'),
P_FLOW_PAGE => :APP_ID||':1'
);
end if;
end;
Here, you need to call the packaged authenticate
function yourself. Since you are no longer using the Login API, the authentication scheme settings you've configured won't automatically be used. Therefore, you need to replicate the implementation details of the authentication scheme.
You call the post_login
procedure if the authenticate
function returns true (the authentication was successful). The post_login
procedure's signature is very similar to that of the login
procedure; however, the post_login
procedure is responsible for the session registration part of authentication. So unless you want to manually handle the session information yourself, it's better to use the built-in methods.
You should now find that you can still log in if you use a valid username and password (and the account is unlocked). However, if you use an invalid username or password or the account is locked, you are presented with a blank page. This is because you haven't included any logic to specify what should happen if the authenticate
function returns false.
You can use two different ways to return users to the login page if their login was unsuccessful:
Add a conditional branch to the page that fires when the Login button is clicked and branches back to login page. If the login is successful, this branch will not be executed. If the login is unsuccessful, this branch will fire and the user will be taken back to the login page.
Perform a redirect (using the owa_util.redirect_url
procedure) back to the login page as part of the login page process logic.
Listing 2-17 shows the modified login page process, which now performs a redirect if the authentication was unsuccessful.
Example 2-17. Performing a Redirect Back to the Login Page
declare
-- use the existing authenticate function
bresult boolean := FALSE;
begin
bresult := pkg_auth.authenticate(upper(:P101_USERNAME),
:P101_PASSWORD);
-- call the post_login procedure if th
-- authentication was successful
if (bresult = true) then
wwv_flow_custom_auth_std.post_login(
P_UNAME => :P101_USERNAME,
P_PASSWORD => :P101_PASSWORD,
P_SESSION_ID => v('APP_SESSION'),
P_FLOW_PAGE => :APP_ID||':1'
);
else
-- perform a redirect back to the login page
owa_util.redirect_url('f?p=&APP_ID.:101:&SESSION.'),
end if;
end;
Here, the login page ID is hard-coded (a bad practice!), to clearly illustrate which page is the target of the redirection. However, you could quite easily use a substitution item to avoid hard-coding the page ID.
You could actually use the ID of any page that requires authentication, since the default behavior of trying to navigate to a protected page when you are not currently authenticated is to redirect you to the login page. However, for clarity, you should use the login page as the target for the redirection.
However, you don't want to use page redirection for the redisplayed login page, because it could be treated as a completely new session (although sometimes the APEX engine is smart enough to detect that situation and issue the same session again). The correct way is to remove the redirection from the login page process and to use a page branch instead, as you'll see in the next section.
Now that you've replaced the Login API with your own logic, you've gained full control over how the login process is handled. With this control, you can give users a meaningful error message if the account they try to authenticate with is locked.
First, you create a new application item to use to store the message. You can then reference this application item in an HTML region on the page to display the message. Figure 2-22 shows the new LOGIN_MESSAGE
application item. Notice that we set the session state protection to Restricted; we will cover this setting in more detail in Chapter 5.
The LOGIN_MESSAGE
application item is then referenced in a new HTML region, as shown in Figure 2-23. This HTML region is positioned above the region containing the login page items, such as the username text field, so that the message will appear in a similar position to the notification message produced by the Login API. The region source simply uses the value of the application item, which is specified as &LOGIN_MESSAGE.
(Note the trailing period.) Also, specify that no template should be used for the region.
Using the condition shown in Figure 2-24, this region will display only if the LOGIN_MESSAGE
application item is not null; in other words, if a message has been set.
Listing 2-18 shows the modified pkg_auth
package body that will set the LOGIN_MESSAGE
application item to a meaningful message. (Important changes are in bold.)
Example 2-18. Setting the Application Item from the pkg_auth.authenticate
Function
create or replace package body pkg_auth as function md5hash (p_input in varchar2) return varchar2 is begin return upper(dbms_obfuscation_toolkit.md5 (input => utl_i18n.string_to_raw(p_input))); end md5hash; function authenticate(p_username in varchar2 p_password in varchar2) return boolean is
v_locked_flag char(1); v_hash varchar2(32); begin v_hash := md5hash(p_username || p_password);select locked_flag
into v_locked_flag
from user_repository
where upper(username) = upper( p_username)
and upper(password_hash) = v_hash;
if v_locked_flag = 'N' then
return true;
else
apex_util.set_session_state('LOGIN_MESSAGE',
'Your account is currently locked'),
return false;
end if;
exception when no_data_found thenapex_util.set_session_state('LOGIN_MESSAGE',
'Invalid username or password'),
return false; end authenticate; end;
As you can see, the query is modified so that if a record is found for that particular username and password, the value of the locked_flag
is stored in the v_locked_flag
variable. If the value of the locked flag is set to 'N'
(meaning that the account is unlocked), the function returns true, allowing the user to successfully authenticate. If the value of the locked flag is not set to 'N'
, you use the apex_util.set_session_state
procedure to set the value of the LOGIN_MESSAGE
application item to 'Your account is currently locked'
.
If no matching account is found, the query will generate a no_data_found
exception (due to the select...into...
clause), and the no_data_found
exception handler uses the apex_util.set_session_state
procedure to set the application item to a meaningful message. The authenticate
function then returns false (meaning the authentication was not successful) as before. Figure 2-25 shows the new logic for the login page process.
You should also create a page branch that will branch back to the login page whenever the Login button is clicked, and an after-footer page process that clears the value for the LOGIN_MESSAGE
application item. You need to clear the value so that the message is effectively reset each time the page is displayed; otherwise, the message would be displayed unnecessarily.
After you've made these changes, you should find that users receive a meaningful error message if they use the incorrect credentials or if the account is locked, as shown in Figures 2-26 and 2-27.
Just to complete the example, you can replicate the way the original message looked when the login page process used the Login API. You can search through the HTML source for the page where the Login API is used to find where the "Invalid Login Credentials" message is displayed. You will find something like this:
<div class="t15Notification">Invalid Login Credentials</div>
Depending on which theme you chose for your application, you may find that a different class is used. Change the region source for the HTML region used to display the message to the following:
<div class="t15Notification">&LOGIN_MESSAGE.</div>
The message will now be formatted in the same way as if you used the Login API, as shown in Figure 2-28.
In our example that modified the login page process, we used the standard wwv_flow_custom_auth_std.post_login
procedure to complete the session registration once the user authenticated. By using the existing built-in session registration and session management functionality, we let the APEX engine take care of handling all of the details surrounding whether the user has a valid session, whether a new session needs to be established, and so on, which drastically reduced the amount of code we needed to provide in the authentication process logic.
In the vast majority of cases, you should take advantage of the built-in session registration and session management functionality by using the post_login
procedure in your custom authentication processing. The Oracle developers behind APEX have put a lot of time and effort into making sure the built-in session-handling functionality works, and works well. It is tried-and-tested logic that is being used by many applications around the world.
If you do feel you want to perform your own session-handling functionality, you should be prepared to do the following:
Replicate the majority of the existing functionality and include your own custom handling.
Test your custom session handling ruthlessly to destruction and back again. Session-handling code is one area of your application that you really do not want to have bugs, since trying to recover from the aftermath of buggy session-handling code in a live environment is not for the faint-hearted.
Applications that require their own custom session management routines are the exception rather than the norm. If you believe that your application requires custom session management, reconsider your business case to see if your requirements can be achieved in a different (and simpler) way.
Many applications allow new users to register without manual intervention from an administrator. Typically, the registration process follows this series of steps:
User enters details into a form, including e-mail address.
An e-mail message is sent to the e-mail address provided. This message gives either a verification code that the user needs to enter on a web page, or contains a link the user should click to verify the account.
Upon successful verification, the user will be able to authenticate to the application.
You can implement automatic user registration in the following ways:
Create the user record directly in the user_repository
table and use a flag to determine whether the account has been verified.
Create the user record in a holding table, and then insert it into the user_repository
table once the user has responded to the verification e-mail.
Which method to use depends on your requirements. If you think a lot of people might indicate they want to register but then not respond to the verification e-mail, you may want to use a holding table. This avoids ending up with a lot of unverified records in the user_repository
table, which could incur a performance penalty due to the need to search through more records when users try to authenticate. Or you may prefer to create the records directly in the user_repository
table, so that you don't need to go through the process of copying the record from the holding table into the user_repository
table and then deleting the original record from the holding table.
Creating the records directly in the user_repository
table has some other benefits. You can use a flag to indicate whether the account is verified. If you also record the timestamp of when the record was created, you can use a scheduled job to remove records that have not been verified within a certain period (for example, within 48 hours), so that you don't end up with unverified accounts in your user repository. You could also take advantage of features such as table partitioning so that records that are verified are stored in one partition, while unverified accounts are stored in a different partition. This way, the query used in your authentication function will benefit from needing to look at only the records in the partition containing the verified records (unless you wanted to provide a meaningful "Your account is not verified" type of message).
To demonstrate, let's walk through the process of allowing user registration for the Buglist application. Although this functionality might not really be practical for this application, the same techniques apply to other applications.
The first step is to modify the user_repository
table to include a verified_flag
column. Listing 2-19 shows the table being modified, as well as the current records being updated so that they are all in a verified status.
Example 2-19. Adding a Verified Flag to the user_repository
Table
-- add the new column apexdemo@10gR2> alter table user_repository 2 add(verified char(1)); Table altered. -- set one account to an unverified state apexdemo@10gR2> update user_repository set verified = 'N' where username = 'brian'; 1 row updated. apexdemo@10gR2> update user_repository set verified = 'Y' where username <> 'brian'; 2 rows updated. apexdemo@10gR2> commit; Commit complete. -- add a not null constraint to the new column apexdemo@10gR2> alter table user_repository 2 modify verified not null; Table altered. -- add a check constraint to the column
apexdemo@10gR2> alter table user_repository 2 add constraint verified_yn check (verified in ('Y','N')); Table altered.
Now you need to modify the pkg_auth.authenticate
routine slightly to take the verified flag into account, as shown in Listing 2-20.
Example 2-20. Modified pkg_auth
to Handle Account Verification Status
create or replace package body "pkg_auth" as function md5hash (p_input in varchar2) return varchar2 is begin return upper(dbms_obfuscation_toolkit.md5 (input => utl_i18n.string_to_raw(p_input))); end md5hash; function authenticate(p_username in varchar2, p_password in varchar2) return boolean is v_locked_flag char(1); v_verified char(1); v_hash varchar2(32); begin v_hash := md5hash(p_username || p_password); select locked_flag, verified into v_locked_flag, v_verified from user_repository where upper(username) =upper( p_username) and upper(password_hash) = v_hash; -- if the account is not verified then set the login messag -- and fail authentication if v_verified = 'N' then apex_util.set_session_state('login_message', 'your account has not been verified yet'), return false; else if v_locked_flag = 'N' then return true;
else apex_util.set_session_state('login_message', 'your account is locked'), return false; end if; end if; exception when no_data_found then apex_util.set_session_state('login_message', 'invalid username or password'), return false; end authenticate; end;
Now if you try to log in with an account that has not been verified (the verified column contains 'N'
), you will receive the message shown in Figure 2-29.
Next you need to create a simple registration form where users can enter their details. Figure 2-30 shows the form to capture the basic details to store in the user_repository
table.
Create an additional table to store the verification link that will be sent to the user, as shown in Listing 2-21. In theory, you could store the verification link in the user_repository
table; however, since the verification link should be used only during the registration process, it seems unnecessary to clutter up the user_repository
table with this data.
Example 2-21. Creating the verification_link
Table
apexdemo@10gR2> create table verification_link( 2 username varchar2(8) not null, 3 registered timestamp, 4 verification_code raw(16), 5 primary key (username)); Table created.
You're storing the timestamp of when the user registered the account in the registered
column. This column can be used within a cleanup routine that removes accounts over a certain age that haven't been registered yet.
Add a register_user
procedure to the pkg_auth
package, as shown in Listing 2-22. This procedure inserts the user details into the user_repository
table, and generates a verification link for that user and sends it via e-mail.
Example 2-22. User Registration Procedure
procedure register_user(p_username in varchar2,
p_password in varchar2,
p_forename in varchar2,
p_surname in varchar2,
p_email in varchar2) is
v_hash varchar2(32);
v_code raw(32);
begin
-- generate the password hash for the user
v_hash := md5hash(upper(p_username) || p_password);
-- generate the verification link that will be used
v_code := md5hash(p_username || dbms_random.string('A', 8));
insert into verification_link
(username,
registered,
verification_code)
values
(p_username,
sysdate,
v_code);
-- store the new account in the user_repository table
-- the account is stored unlocked and unverified
insert into user_repository
(username,
forename,
surname,
email,
password_hash,
locked_flag,
verified)
values
(p_username,
p_forename,
p_surname,
p_email,
v_hash,
'N',
'N'),
-- send the verification email
send_verification_email(p_username, p_email, v_code);
end register_user;
This procedure uses the following code in generating the verification link:
v_code := md5hash(p_username || dbms_random.string('A', 8));
The code uses the same md5hash
helper function we used to generate an MD5 hash of the user's password. However, in this case, you are using it to generate a verification link that is based on the username and a random string of characters (generated by using the dbms_random.string
function). It's perhaps easiest to visualize what the dbms_random.string
function returns with an example:
apexdemo@10gR2> select dbms_random.string('A', 8) as X from dual; X -------------------- ZStZMclU apexdemo@10gR2> / X -------------------- KBwWsrmj
apexdemo@10gR2> / X -------------------- FQzCCTPI
You concatenate the random string onto the end of the username before passing it to the md5hash
function, just so the generated verification link is harder to deduce. This can be very important in an automated registration procedure where you want to prevent account generation from being abused by an automated tool written by a malicious user.
Here we are going to modify the user registration page and add a PL/SQL page process, which calls the pkg_auth.register_user
procedure, using the page items as the parameters when the user clicks the Register button. Note that you can modify the c_base_url
variable to reflect the fully qualified URL the user should use.
Although you could send a simple, single-line e-mail message from within the body of the register_user
procedure, we'll put the actual e-mailing of the verification link into a separate procedure so we can send a nicely formatted text e-mail message. You could even extend the example to send an HTML e-mail instead of a plain-text message, by using the P_BODY_HTML
parameter to the APEX_MAIL
procedure.
Listing 2-23 shows the procedure for sending the verification e-mail message. Note that you'll need to use the correct values for the address and port number of your own e-mail server in order to have the e-mail sent.
Example 2-23. Procedure to Send Verification E-mail
procedure send_verification_email(p_username in varchar2, p_email in varchar2, p_code in raw) is l_body clob; l_link clob; c_smtp_server varchar2(10) := 'localhost'; c_smtp_port integer := 25; c_base_url varchar2(200) := 'http://apexdemo/pls/apex/apexdemo.pkg_auth.verify_user?p_user='; c_from varchar2(30) := '[email protected]'; begin l_body := '==============================================' || utl_tcp.crlf; l_body := l_body || '= This Is an Automated Message, Do Not Reply =' || utl_tcp.crlf; l_body := l_body || '==============================================' || utl_tcp.crlf; l_body := l_body || utl_tcp.crlf; l_body := l_body || utl_tcp.crlf; l_body := l_body || 'Hello ' || p_username || ',' || utl_tcp.crlf;
l_body := l_body || utl_tcp.crlf; l_body := l_body || 'Thanks for taking the time to register.' || utl_tcp.crlf; l_body := l_body || utl_tcp.crlf; l_body := l_body || 'in order to complete your registration you will need to verify your email address.' || utl_tcp.crlf; l_body := l_body || utl_tcp.crlf; l_body := l_body || 'to verify your email address, simply click the link below, or copy it and paste it into the address field of your web browser.' || utl_tcp.crlf; l_body := l_body || utl_tcp.crlf; l_link := c_base_url || p_username || '&p_code=' || p_code; l_body := l_body || l_link || utl_tcp.crlf; l_body := l_body || utl_tcp.crlf; l_body := l_body || 'You only need to click this link once, and your account will be updated.' || utl_tcp.crlf; l_body := l_body || utl_tcp.crlf; l_body := l_body || 'You need to verify your email address within 5 days of receiving this mail.' || utl_tcp.crlf; apex_mail.send(p_to => p_email, p_from => c_from, p_body => l_body, p_subj => 'Your verification email'), apex_mail.push_queue(c_smtp_server, c_smtp_port); end send_verification_email;
Notice that we call the apex_mail.push_queue
procedure immediately after calling the apex_mail.send
procedure. The send procedure just puts the mail into the APEX mail queue. Usually, a scheduled job will run every 10 or so minutes and push out all e-mail messages in the queue. Calling push_queue
yourself sends the messages immediately, rather than waiting for the scheduled job. You can actually omit the hostname and port parameters from the apex_mail.push_queue
procedure, since it picks up the server settings for those values (so you don't need to know them yourself). You may wish to work with your e-mail server administrator to determine the best option for your system.
Here's an example of an e-mail message sent to a registering user.
============================================== = This Is an Automated Message, Do Not Reply = ============================================== Hello markw, Thanks for taking the time to register. In order to complete your registration you will need to verify your email address.
To verify your email address, simply click the link below, or copy it and paste it into the address field of your web browser (note this should be a single line, but is broken for clarity here). http://apexdemo/pls/apex/apexdemo.pkg_auth.verify_user ?p_user=markw&p_code=F6C61F52B08B5F9E5A684EFDD63D5709 You only need to click this link once, and your account will be updated. You need to verify your email address within 5 days of receiving this mail.
Tying all these pieces together, you now have a process that does the following:
Allows a user to submit their details into a page
Creates a new user in the user_repository
table, with a status of unverified (verified is set to 'N'
)
Stores a record in the verification_link
table that contains the username, when the account was registered, and the verification code
The last, step is to write the procedure that handles the verification link in the e-mail that the user will click. Listing 2-24 shows the definition of the pkg_auth.verify_user
procedure.
Example 2-24. Procedure for Verifying Users
procedure verify_user(p_user in varchar2, p_code in varchar2) is begin update user_repository ur set ur.verified = 'y' where upper(ur.username) = upper(p_user) and exists (select 1 from verification_link vl where vl.username = ur.username and vl.verification_code = p_code); if sql%rowcount > 0 then htp.p('Thank you, your account has now been verified.'), else htp.p('Sorry the link you have used is invalid.'), end if; end verify_user;
Before this procedure can be called via a URL, you need to grant execute rights on it to the user specified in the DAD:
apexdemo@DBTEST> grant execute on pkg_auth to apex_public_user; Grant succeeded.
The user should now be able to click the link, and the verify_user
procedure will try to match the username and the code used with the entry in the verification_link
table. If a match is found, the corresponding user account in the user_repository
table will have the verified flag set to 'Y'
.
Note the use of SQL%ROWCOUNT
, so you can send a simple message back to the user's browser to let the user know whether the verification succeeded.
This simple example should give you some ideas about how you can set up an automated registration system. You could quite easily use this type of automated sign-up with some of the other authentication schemes. For example, with cookie user accounts, you could store the verified flag and the verification code using the custom attributes such as p_attribute_01, p_attribute_02
, and so on. You could also make many improvements to this automated registration procedure, such as checking to see if the username is already registered.
In APEX 4.0, session timeout management is a built-in feature. You can set both a session-timeout value, which invalidates a session after some period of inactivity, and a maximum session-length value, which terminates an active session after a specified time period. To set these parameters, you must log in as an APEX administrator (using the http://.../apex/apex_admin
URL).
Both parameters are set in seconds via the Security screen under the Manage Instance menu as shown in Figure 2-31.
The values you set for the session timeouts should be based on either your corporate security guidelines (if they exist) or the usage patterns of your user base. If normal user operations include significant "think time" between transactions, you should set the Maximum Idle Time accordingly. You don't want your users to have to re-login during a normal transaction.
It is important to note that you can't set an "unlimited" value for either parameter. The pop-up lists of values restrict entries to one of the following values:
1 Minute |
5 Minutes |
10 Minutes |
30 Minutes |
1 hour |
2 Hours |
5 Hours |
10 Hours |
Authentication is important to any application because it's the mechanism by which you ensure that users really are who they say they are. Authentication is a necessary precursor to authorization. You must first know who a user is before you can set appropriate limits on what that user can do.
You can take advantage of several built-in authentication schemes that APEX provides, or build your own mechanism for authenticating users who log in to your applications. You can manage users individually, or you can place them into groups. Placing users into groups makes it more efficient to manage large numbers of users, because you define policies for a group at a time rather than one at a time.
Chapter 3 builds on what you've just learned to demonstrate how to set limits on what users can do. Combining robust authentication with well-thought-out authorization ensures that application users can do only what they are supposed to be able to do—no more and no less.