Custom Authentication in APEX

Trust is a great force multiplier. – Tom Ridge When you build a web application you have two choices for the pages: they can either be public or protected. Mostly you will build applications using a combination of the two. Some pages will be publicly available, like the landing page of your application, but there will also be pages which

Trust is a great force multiplier. – Tom Ridge

When you build a web application you have two choices for the pages: they can either be public or protected. Mostly you will build applications using a combination of the two. Some pages will be publicly available, like the landing page of your application, but there will also be pages which are protected. Users need to login to the application to use these pages.

Authentication methods

APEX provides us with a couple of authentication schemes by default. Some of which are:

  • Application Express – Every user must exist as an APEX user
  • Database Account – Every user must have a database account
  • Open Door Credentials

The custom authentication is a good option so you can keep full control over how you want this to work. When you have created your application, or at least the start pages, you go to the shared components page where you go select the Authentication Schemes from the Security section.

Custom authentication scheme

When you want to create your own authentication scheme you must create a (packaged) function that must obey a few rules. The function must accept two parameters: the first is the username, and the second is the password. Both these parameters are varchar2 type. Remember, the username and password parameter are sent as clear text. If you want your application to be more secure, you may want to obfuscate the values before sending them to the authentication function. The simplest form of the authentication function is like this:

This function is pretty much the same as the open door version, but it’s the start of a real authentication. You create a new authentication scheme by selecting the create button on the screen with the list of defined schemes for this application.

When you create a new scheme you can choose to create a scheme based on an existing scheme, but in this case we want to create a new scheme based on one of the pre-configured schemes. In this screen you can create the code for the Authentication Function. You can write the code for the function in this screen, but you can also create the function as a stored (package) function so you can use your IDE to create the code.

To create a real authentication scheme you need to do more than just return true for whatever parameters are sent in. You can write code to check the usernames and their passwords, but that would mean you would have to alter the code every time you want to add or remove a user. You are better off using a table which stores the users for the application. The table can look like this: Table USERS

ID NUMBER(15,0) Primary key
USERNAME VARCHAR2(50) Unique constraint
PASSWORD VARCHAR2(50)  

You can this table to save more information on the user, such as the email address but for this example that is not necessary. The function could be updated to something like this:

In this example the username and password are validated against the data in the table. The username and password are stored in plain text in the database, which is not a good idea. You may want to obfuscate the password before storing it. This is where creating a package for the authentication functions comes in handy. You want to use the same function for obfuscating the password when you store the data as when you check the credentials. You can do this by creating a private function in the package that does the obfuscating and use the outcome of this function for both storing the data and checking the entered credentials. A package like this could look like this:

Now all you have to do is tell your application that it needs to reference the custom authentication schema. When you created the new authentication schema your application was automatically told to use the new schema, but if you created an authentication scheme in one application and you created a new application where you copied the authentication scheme from an existing application you have to do this yourself. It can also happen that during development you want to use a different authentication scheme than you might in production.

To change the current authentication scheme you go to the Shared components – Authentication Schemes. Select the scheme you want to use and press the ‘Make Current Scheme’ button.

If the scheme you selected is already the current scheme, then this button will not be available.