Yet another day and Oracle Application Express has revealed yet another facet of its extensive capabilities to me. It's almost as if it's performing a slow, sexy striptease for me: with each passing day it pops another button, loosens another strap, until one day - hopefully - it'll be dancing naked before me to a Pussycat Dolls song.
(Welcome to the dark and disturbing crannies of my mind.)
The database that my Apex application is going to be sitting on is shared by the other (Oracle Forms) application that my company supplies to these clients. Both applications use Database Authentication (in fact, the plan is to manage the Apex users from the Oracle Forms application). However, being a database user is not enough to grant a person access to the Apex application; there are other things (rows in tables etc.) I have to check.
And so I was forced today to study the authentication schemes closer. I noticed that using the Database Authentication Scheme Apex uses the code -DBACCOUNT- to validate a user's password. Ah, so all I needed to do was replace those words with a call to a function of my own. Apex specifies that my function will have to take two parameters (username, password) and return a boolean.
I thought this would be easy. I wrote a function; inside it I started off by testing if the parameterised password was correct for the user. To do this I used the native Apex function APEX_UTIL.IS_LOGIN_PASSWORD_VALID(username,password). If the password was incorrect I returned FALSE and did not continue with further tests; however, if it was correct I then proceeded to carry out the additional tests required by my clients.
However - and this is the weird thing that I cannot explain - I found that apex_util.is_login_password_valid() ALWAYS returned false - even when I passed in correct credentials!
I spent the next 10 minutes swearing like a sailor and torturing Larry Ellison slowly in my mind, but then I started investigating a Plan B.
Apex allows you to create your own custom authentication: should I try that? I looked into it for a while, but decided against it because I felt it was possibly an overkill for what I was trying to achieve.
Eventually what I decided to do was this. I rewrote my function, turning it into a procedure which carried out my clients' additional tests and which, if a user failed, called wwv_flow_custom_auth_std.logout to kick them out and redirect them back to the Login page. I then returned to my Database Authentication and stuck a call to my procedure in the Post-Authentication section.
And it worked fantastically. Users who do not meet my clients' rules can no longer log into the application, even if they are set up as database users.
By this time it was 5.30 in the evening, and so I shut my computer down, let Larry Ellison go free and headed home a happy man. (I have since then discovered this Custom Authentication how-to written by Duncan Mein however, since my system works well and is much easier to implement, I'll leave it be.)
4 comments:
David,
This is the difference between authentication and authorization. Why not use an authorization function to keep users out after being authenticated?
Regards,
Dan
Hi Dan,
The customer's specification is that users who don't meet all their criteria are deemed to have failed authentication and should not be allowed into the app. That's why I'm using authentication rather than authorisation.
David.
Many institutions limit access to their online information. Making this information available will be an asset to all.
Using Upper might have helped.
Seemingly
apex_authentication.login(
p_username => :P9999_USERNAME,
p_password => :P9999_PASSWORD )
does not care whether the username is written using capitals or not.
But apex_util.is_login_password_valid does care.
Therefore I did the following and it worked as expected:
apex_util.is_login(p_username => UPPER(:P9999_USERNAME),p_password => :P9999_PASSWORD))
Post a Comment