I have been playing around with Apex 4.0 on http://tryapexnow.com. When playing around with some ideas I have for application I decided to use the logging mechanism I created a while ago, to see what’s going on in the custom code.
Since this is a hosted environment it is not too easy to check the records in the log. There were a couple of queries needed to check the log. I usually run these from my IDE, but the is not possible now. They can be easily run using the SQL Workshop, but I have APEX at my disposal. So why not create a simple application to display the information in the log table. But if I create an application and start using it, it becomes publicly available and everybody can see what’s being logged (including the values of parameters). I need some sort of authentication here.

Luckily APEX provides the possibility to add your own authentication schemes. Since I am running this on a hosted environment I cannot use ‘Database’ or ‘Database Account’. So I need to create my own, custom authentication. This is described in detail in the book Pro Oracle Application Express especially in chapter 3 . So I decided to follow the steps here.

The steps include examples of creating a user table and a custom authorization function in a package. It also includes some pointers on how to handle passwords. I did everything exactly as described in the book, changed the authorization scheme to the newly created one and tried to log in to my application. But it failed. Even with the correct credentials. What is going on here? Must be something I did wrong. Let’s try to create the schema again (dispose of the ‘old’ one) and now follow all the steps in the wizard.
In the book they said to create the scheme from here (with minimal information) and then change the properties later. But I want to go through the entire wizard so I decided to click the ‘Next>’ button.
The 6th page of the wizard is where the real action takes place. I want to use my own custom function to authenticate the session.
The function should return a boolean and take username and password as input.
The function is declared in the package as follows:

FUNCTION authenticate(p_username IN VARCHAR2
,p_password IN VARCHAR2) RETURN BOOLEAN;Now it’s time to test the new scheme. Change the current authorization scheme to the one just created.
Then try to run the application again.
Login with some faulty credentials and notice that I am not authenticated.
When trying to login with correct credentials I am allowed access to the application.

Now what is the difference between this authorization scheme and the one I created earlier. I think it has something to do with the session not valid part of the authentication. When I created it by filling in just the name and the description of the scheme it used the –BUILTIN- ‘Not Valid URL’. In my new scheme it says here to use the login page instead. It seems that did the trick. Checking the Application Express scheme shows me roughly the same settings. I think I nailed this one. It took me a lot of time and I want to thank my friends Jorrit and Alex for helping me where they could (although they had not needed this yet) especially where to look or provide me with more documentation.