Security - Beginning Oracle Application Express 4.2 - page 299

time, additional features requiring administration could be added to this navigational
structure.

User Maintenance Data Entry

As part of the Help Desk design, you should be able to maintain the users from the ap-
plication. To do this, you need to implement some new database objects by locating,
uploading, and running the script ch9_security_objects.sql . Refer to Chapter
4 if you need step-by-step instructions. You should see 13 rows, all of which complete
successfully.
Let's walk through briefly what this script does for you:

• Lines 1-16 : Create a function called hash_password that encodes any

string passed to it.

• Lines 18-24 : Create the USERS table that will hold the user records.

• Lines 26-27 : Create the USER_SEQ sequence that will be used as the

primary key for the USERS table.

• Lines 29-37 : Create a Before Insert trigger on the USERS table that auto-

matically assigns the next sequence as the primary key, converts the user-
name to uppercase, and calls the hash_password function to encrypt the
user's password.

• Lines 39-50 : Create a Before Update trigger that converts the username to

uppercase and hashes the user's password if it has changed.

• Lines 52- 87: Create the authenticate_user function that validates

whether the passed username and password are

• valid compared to what exists in the USERS table.

• Lines 90- 103: Create six entries in the USERS table, all with the password

apress .

Now that you have your new database objects, you can continue to implement the
security model:

1. Edit Page 600 of the application.