Friday, October 12, 2012

Remember Me - APEX Autologin

I promised to publish about this subject to one of the attendees during my presentation at KScope 12 in San Antonio. I used this functionality in my demo application (FifApex) and it seems there is interest in how to do this.

Most of the public websites like Facebook, LinkedIn, Twitter, Amazon or OTN do have it: an option to stay logged in, even if you closed the browser, so you do not have to authenticate each time you visit the site again. I’m pretty used to it and would be surprised if a website didn’t offer this convenient feature. OK, it’s not entirely secure, but, as I said, very convenient.
I’m working on a “consumer” site/application, I’m building it with APEX and I want to offer this “remember me” option too. I knew an APEX based website that does offer this feature ( and I had a chat with the developer that implemented it’s login mechanism, Paul Broughton from Apex Evangelists. So, here my thanks to him for the original inspiration.
Oracle Application Express has neither a build in functionality nor is it providing a “standard” Authentication Schema that does provide this mechanism. But, with just a little effort you can implement this Autologin in your application. My example uses a Custom Authentication schema, meaning I have a user table and a package, providing all the necessary functionality. At the bottom of this post, you will find links, where you can download all files you need to install the demo application in your own environment.

Let’s start

First of all, let me define the mechanism: the user gets an option, usually a checkbox, in login screen to stay signed in. Next time the use visits the site, he doesn’t have to provide his credentials and is automagically signed in.
The common technique to achieve this, is using a cookie that holds a token to identify the returning user. So we have to set a cookie when the user signs into the application and check for the cookie and validate the token when the user comes back.
Setting and reading a cookie is easy, using the OWA_COOKIE package, but how to integrate this into a APEX authentication schema?
The following example is build with a standard “empty” application with Custom Authentication. Prior I created a table to hold the user data and a package, containing the authentication logic. Note: this is a simplified example, so using this in your production application is at your own risk! All code is included in the demo application download.

CREATE TABLE my_custom_users(
   username   VARCHAR2(25 BYTE)
, password   VARCHAR2(250 BYTE)
, token      VARCHAR2(25 BYTE)

INSERT INTO my_custom_users(username, password, token)
VALUES ('DEMO', my_cust_auth.encodeit('DEMO', 'demo'), NULL);


Step 1: setting the cookie during loginremember_me_01

First I’m going to modify the generated Login Page (101), adding the “Remember me” checkbox.
Then I modify the “Set Username Cookie” process that already should exists and stores the APEX username in a cookie (I do not make any changes to this functionality): The code I add, checks, if the “Remember me” has been ticked by the user, the user actually is a valid user from my table and then sets a cookie, called “REMEMBER_ME”. The value of this cookie is generated by a function, producing a random string and storing this as a token with the user data or fetches the token from the user data, if already existing. In my example, I choose to set the expiration date of this cookie one to about a year.
Running the application, logging in with the checkbox ticked, you can exam the cookies of your APEX application by using a tool like the Developer Console of Chrome. Next to some cookies, set by APEX itself, you should find the “REMEMBER_ME” entry holding the token string and expire date of today + 365 days:

Step 2: using the cookie on return

Now, when do we need to read the “REMEMBER_ME” cookie again? Every time the visitor returns to your site/application, has not signed in yet (obviously, as we want to do this automagically) and the cookie is set and holds a token that is known in the user table (assuming that the user is the same again!). I want to perform the check, regardless of the page visited is a public page or a page that requires authentication. The event that should be triggered, if the conditions are met (cookie set and valid, user is public), is the a automatic login, similar to the original login. To perform the check, I will use “PAGE 0”, but lets first create the autologin functionality to be called:
remember_me_04To realize the autologin, I create a new page (103 in example application). This page only contains a “On Load - Before Header” process and one page item. The process only fires, when the request name is “AUTOLOGIN” and calls the build in standard APEX login procedure provided for custom authentication. It uses the page item to “P103_TOPAGE” as target page after successful login, which I will set on the triggering process/branch on page 0, to return to the page the user actually requested in the URL. The username is derived from the cookie (the token belonging to one unique user) using the call OWA_COOKIE.get ('REMEMBER_ME'); in a stored procedure.
    P_UNAME       => my_cust_auth.get_user_from_cookie,
    P_PASSWORD    => null,
    P_FLOW_PAGE   => :APP_ID||':'||:P103_TOPAGE
As you see, the password parameter is NULL. So I have to add logic to the Authentication Function (my_cust_auth.validate_user) of my custom Authentication Schema, that handles two cases: either a valid combination of username/password is given, or the “REMEMBER_ME” token is set and a corresponding user is found. Again: this is very basic and may not be sufficient for your production application.

Step 3: automagically do it

I already mentioned that I will use Page 0 to call the auto login. Doing so, the user should be singed into the application, regardless which page he opens first. I simply create a “After Header” Region of type “Dynamic PL/SQL”. The redirect will be done using the APEX build in procedure sys.owa_util.redirect_url, passing the relative URL of page 103 with request parameter “AUTOLOGIN”. This region is conditional, checking for: user is public, cookie is set and belongs to “a” user.
I added an extra condition checking for the current page ID to be less or equal to 101, to prevent page 103 from recursively calling itself. Page 102 will be this demo application’s special logout page (see next section). All “normal” pages in this application are assumed to have ID’s in the rage of 1 to 100. You definitely should modify this condition to meet your actual applications page ID ranges.remember_me_08
Note to myself: I first tried to use a “Before-Header” Branch in Page 0 to implement this redirect. But: Branches in Page 0 do not ever get executed. It’s not the first time I hit this pitfall. After all: the APEX 4.1 Application Builder Treeview of Page 0 suggests Branches are possible.

Step 4: forget me

remember_me_07We need to offer the user a way to logout and remove the cookie. I create a new page, 102, containing one “On Load - Before Header” process and a branch.
The process removes (expires and replaces value with empty string) the cookie and performs the actual logout using WW_FLOW_CUSTOM_AUTH_STD.LOGOUT. The branch will take the user to the (public) Home page (1).
I then specify to use page 102 as the Logout URL of my Authentication Schema.
The request name “LOGOUT” is provided by APEX itself, so you might check for it in a condition, rather then defining the process as unconditioned, just to prevent accidental logout.
So, that’s it. Just a few simple modifications to your custom authentication. What do you think? Does anybody have a more elegant solution (I’m sure there are)? I would appreciate it, if you would post your ideas as comments to this article. If you haven’t got a solution yet, but want to use mine: go ahead! Down here the links of the demo application on and the full download of it for you as a template.


Demo and Download

Try the demo at!
(login with demo/demo)
Download the DemoApp!
(just import and install incl. supporting object scripts;
min. version: 4.1;
default login: demo/demo)


Possible Enhancements:

The whole mechanism is a rather simple and naïve approach. I just wanted to explain the basic principle of it. There are lots of enhancements and improvements one could think of, and actually, while writing this post I thought of some myself:
  • using pre/post function call of Authentication Schema instead of page processes
  • integrate autologin logic from page 103 to page 101
  • cookie name application variable or dynamically generated
  • investigating the possibility of an autologin authentication plugin