To create a new user who can authenticate to your application, log in to the workspace as a workspace administrator and navigate to Administration

Figure 2.4. Creating a new end user

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.

Figure 2.5. Username displayed in an application after a user has authenticated

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.

Figure 2.6. Creating a new group

Figure 2.7. Adding a user to a group

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.

Figure 2.8. Using the -BUILTIN- authentication function

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).

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

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.

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.

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).

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.

jes@10gR2> select decode(instr(','||:mygroups||',', ','||:check_group||','), 0, 'N', 'Y')
is_member from dual

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.

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.

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.

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.

apex_util.set_attribute(p_userid =>

                          apex_util.get_user_id(:APP_USER),
                        p_attribute_number => 1,

                        p_attribute_value => :P1_EXT_NUMBER);

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.

Figure 2.12. Bug Report spreadsheet

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.

Figure 2.13. Create Application wizard

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.

Figure 2.14. Preparing to upload a csv file or spreadsheet

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.

Figure 2.15. Uploading a csv file or spreadsheet

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.

Figure 2.16. Table Properties

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.

Figure 2.17. Setting user interface defaults

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:

Apex 4.0 provides two choices for Report Implementation:

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.

Figure 2.18. Home page of Buglist application based on the spreadsheet.

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:

Listing 2-8 shows the script for creating this 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.

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.

Figure 2.19. Creating a custom authentication scheme from scratch

You can define many attributes for a custom authentication scheme, so it's worth taking the time to understand precisely what each attribute means:

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.

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:

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.

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.

Figure 2.20. Using the packaged authentication function

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.

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.

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.

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.

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.

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.

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:

Listing 2-17 shows the modified login page process, which now performs a redirect if the authentication was unsuccessful.

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.

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.

Figure 2.22. Creating the LOGIN_MESSAGE application item

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.

Figure 2.23. HTML region referencing the LOGIN_MESSAGE application item

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.

Figure 2.24. Conditional logic to show the region only 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.)

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 then

      apex_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.

Figure 2.25. New login process logic

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.

Figure 2.26. Message the user receives after using incorrect credentials

Figure 2.27. Message the user receives if the account is locked

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.

Figure 2.28. Using the original formatting of the notification message

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:

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.

-- 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.

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.

Figure 2.29. Authentication message with an unverified account

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.

Figure 2.30. A simple user registration page

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.

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.

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.

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:

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.

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.