This article describes some considerations about how APEX authentication works and describes a possible way of building your own authentication politics.
When you create a new APEX application, along with the pages that you add in the application creation process, APEX automatically creates a login page with the number 101. Also in the application creation process the user can chose one of the three options: Application Express, No authentication and Database account. It is not really that important the choice, as it can be modified afterwards at any point, and for example the Application Express scheme can be basically modified to act just like any of the other two.
If you don't like the fact that the login page was automatically changed with the '101' number you can change this in 2 different ways:
1. Copy the 101.Login page and assign the desired number to the newly created page. In this case you will also have to edit the options of the current authentication scheme. This is done in the Shared Components->Authentication Schemes->Edit screen. The new login page number must be entered in the Session Not Valid Page field and also if you want the logout page to be the same with the login one in the Logout URL field. After this just delete the initial 101 Login page so that you don't have two login pages to confuse you.
2. Create a new page with the desired number and chose Login page when asked the type of page that you desire. This option automatically sets the values of the new login page in the Shared Components->Authentication Schemes->Edit page. And again you should delete the 101 Login page.
After creating the application, if the Application Express authentication scheme is active, if you run the application then you can login with the username and password of your APEX workspace account if you have an administrator account. Therefore anyone that needs to use the application will have to have an admin account on the apex workspace, which is obviously not a solution. This is because the current active authentication scheme is the built-in Application Express scheme. The solution is to modify the scheme so that the Authentication Function will no longer be the -BUILTIN- option, but a function that is created by the user in the database. Therefore the text in the Authentication Function field should refer to a function that returns a Boolean. Let's consider this function the apexAuth function in the pkgAuth package. Therefore the content of the Authentication Function field should be:
where the apexAuth function can be something like this:
function apexAuth (username in varchar2, passw in varchar2) return boolean is
select 1 into nAux
u.USR_ID = up.USR_ID
u.LOGIN_NAME = upper(username)
up.PSW_DESC = pkgAuth.passEncr(upper(username),passw)
when no_data_found then return false;
when others then return false;
where LOGIN_NAME is a field in the USR table that represents the username used to login into the application, PSW_DESC is the encrypted value of the user password, and pkgAuth.passEncr is a function that encrypts a given password.
After setting the new value of the Authentication Function field for the current authentication scheme in the way that was explained above the application will be available to you with the username and password that are stored in the database tables.
Of course from this point on, you can modify the login page in multiple ways. One thing that can and should be done in most applications is adding a Change password button that redirects the authenticated user to a screen where he can modify the password. The user should actually be taken to this page to modify his password after the first login with a newly generated/reset password or after a period of time after which it is considered that the password has expired. This is a suggestion, but each developer can actually modify the login page the way he considers or needs.