Implementing a Database Authentication Scheme in APEX

The following tangential opening was written especially for Scott Wesley in the hope that he’ll be minded to point out any errors in what follows. The same applies to Jeff Kemp ( although I don’t know if he’s into the AFL).
Unlike me, both of these guys are APEX experts.

Football. It’s a term that means different things to different people.
To a European, it’s most likely to be a reference to good old Association Football ( or Soccer).
To an American, it’s more likely to be the Grid-iron game.
A New Zealander will probably immediately think of Rugby Union.
An Australian ? Well, it’s probably a fair bet that they’ll think of Aussie Rules Football.

On the face of it, the rules appear rather arcane to an outsider. 18-a-side teams kicking, catching and punching something that resembles a Rugby ball around a pitch that resembles a cricket oval. Then there is the scoring system.
“Nice Behind”, to an AFL player is more likely to be taken as a compliment of their skill at the game than an appreciation of their anatomy.

Then again, it’s easy to scoff at any sport with which you are unfamiliar.
For example, Rugby could be characterised as 30 people chasing after an egg. Occasionally, they all stop and half of them go into some strange kind of group hug. I wonder if the backs ever get paranoid because they think the forwards are talking about them ?

As for soccer, even afficionados will acknowledge that there’s something a bit odd about a game where 22 millionares spend lots of time chasing after one ball…when they’re not rolling around in apparent agony after appearing to trip over an earth worm. I mean, the ball isn’t that expensive, surely they can afford one each ?

The point of all of this ? Well, what is considered to be obscure, eccentric, or just plain odd often depends on the perspective of the observer.

Take APEX authentication schemes for example.
Whilst not the default, Database Authentication is a scheme that is readily available. However, there doesn’t seem to be much written on this subject.

In contrast, there is a fair bit out there about APEX Custom Authentication. A lot of it would appear to re-enforce the idea that implementing security by hand is fraught with difficulty.
Just one example can be seen here.

If we were to approach this topic from the perspective of looking to migrate an elderly Oracle Forms application – where each user has their own database account – to APEX, we might be attracted to the idea of a Database Authentication Scheme and want to find out more.

What follows is my adventure through setting up such an Authentication Scheme.
Specifically, I’m going to cover :

• Creating an APEX Database Authentication Scheme
• Default behaviour
• Adding a Verification Function to restrict access to a sub-set of Database Users
• The vexed question of password resets

Why use Database Authentication

The Oracle documentation states :

“Database Account Credentials is a good choice if having one database account for each named user of your application is feasible and account maintenance using database tools meets your needs.”

If we’re migrating an application from Oracle Forms, then chances are that this is what we’re doing now, so a Database Authentication Scheme should save us a fair bit of work.
The other major advantage is that utilising the Database’s built-in User and Security management means that we don’t have to try and re-invent the wheel.
So, the objective here is to implement Authentication in our new Application without having to :

• Create and maintain extra tables
• Write lots of extra code
• Figure out a secure way of storing passwords

The Application

Firing up my trusty XE 11g installation, I’ll be using a simple APEX application that consist of a standard login page and, initially at least, a Home Page with two read only fields in an HTML Region called WHOAMI.
These are :

• Application User – the APP_USER that I’m connected to APEX as
• Database User – the actual user connected to the database

For the P1_APPLICATION_USER, the Source Type is Item (application or page item name).
The source value is APP_USER.
For the P1_DATABASE_USER, the Source Type is set to SQL Query(return single value).
The source value is simply the query :

select user from dual

A Note on the Design

In this example, I’ve taken the approach that the code required to implement this functionality is included in the parsing schema ( HR in this case). As a consequence, the privileges required to execute this code are also granted to the parsing schema.
I’ve done this for the purposes of clarity.
Careful consideration needs to be given to this design decision if you’re planning to implement it in a “proper” production environment.

Creating a Database Authentication Scheme

After navigating to the Application in Application Builder, rather than do anything to the Application itself, we need to create a Shared Component…

The type of component we want is an Authentication Scheme.

NOTE – Authentication Scheme – controls login to the Application.
Authorisation Scheme – governs which bits of the Application the user can see…once they’re connected.

Anyway, in the Security Region, select Authentication Scheme :

…and then hit the Create button…

We want to create a scheme “Based on a pre-configured scheme in the gallery” …

In the next screen :

Name : HR_DB
Scheme Type : Database Accounts

And finally, we click the Create Authentication Scheme button and…

We can see from this that HR_DB is now the Authentication Scheme currently being used by any Application in the Workspace.

Anyway, now to test it.

To this point, I haven’t setup any users for this application.

So, Can I log in as a user that does exist in the database ?
Well, I have a user called MIKE :

select 1
from dba_users
where username = 'MIKE'
/

         1
----------
         1

SQL> 

So, if I now run my application and try to connect using my database credentials…

… I can connect using my database credentials.

It’s worth noting that, despite this, the actual database connection from APEX is as the ANONYMOUS user.
If you’re using the APEX Listener instead of the Embedded PL/SQL Gateway (the default in XE), then it’ll probably be APEX_PUBLIC_USER.

So, in order to login to my application, you now have to be a database user.
All the messy password encryption stuff is handled by Oracle and I can now get on with polishing my finely crafted APEX Application….or so you might think.

Just consider this :

…also let’s you connect :

We’re not fussy, we’ll let anyone in !

Now, my imaginary Forms application – remember, that’s the one I want to migrate to APEX – may be sitting on a Database Instance with a number of other Applications. So, how do I restrict access to my application to a subset of the users in the database ?
Time for a bit of a re-think then…

The verify function

What we need is a means of identifying a database user as an Application user.
At this point it may well be worth revisiting the role of database roles in APEX applications.
Hang on, you’re thinking, last time you said they were pretty much useless in APEX.
Well, bear with me.

Roles as Privileges, sort of

What we’re going to do here is to simply create an empty role and assign it to a database user :

create role hr_user
/

grant hr_user to mike
/

We now have some means of determining which database users are our application users :

select 1
from dba_role_privs
where granted_role = 'HR_USER'
and grantee = 'MIKE'
/

The function

Now all we need is a function that checks to see if the user attempting to login has this role granted to them.
It’s worth bearing in mind here that, for a function based on the above statement, select privileges on DBA_ROLE_PRIVS is required.

To start with I’m going to grant the privilege to HR :

grant select on sys.dba_role_privs to hr
/

and then I’m going to create the function in the HR schema :

create or replace function is_hr_user_fn
    return boolean
is
--
-- Is this user a database user with privileges to access the APEX Application ?
-- NOTE - the owner of this function requires SELECT privilege on DBA_ROLE_PRIVS
--
    l_dummy pls_integer;
begin
    select 1 
    into l_dummy 
    from sys.dba_role_privs
    where granted_role = 'HR_USER'
    and grantee = apex_040200.v('APP_USER');
    
    return true;
exception
    when no_data_found then
        raise_application_error('-20000', 'You are not an application user');
end;
/ 

You’ll note that the references to both DBA_ROLE_PRIVS and the V function are done directly on the objects themselves rather than through their public synonyms.
In many cases, but especially where security is concerned, it’s usually a good idea to make sure that you’re referencing the object that you intend rather than relying on a synonym.

If you want to see an example of how public synonyms can be changed to point to objects other than those originally intended, then have a look here.

Now we need to tell our Authentication scheme to use this function as the Verify Function.
In the Application Builder, go back to the Shared Components screen then select Authentication Schemes.

Now click on the pencil icon next to HR_DB – Current :

If you want to be a bit more discerning…

In the Session Not Valid section, there is a field called Verify Function Name.
In here, simply enter the name of our function – i.e. is_hr_user_fn :

…add a Verify Function

And save the changes.

So, we should now be able to connect as MIKE, but not any other database user.

Connecting as MIKE works as before. However, for SYSTEM, the results are slighty different :

Your name’s not down, you’re not coming in !

As we can see, the Application Error raised by the function is displayed. If you hit the OK button, you’ll then be returned to the Login Page.

The Principle of Least Privilege

In case your not familiar with the term, it basically boils down to the principle that access to an application should be restricted to the minimum level required for a user, application or program to function.
Have a look here for a proper explanation.

It’s probably worth noting that, implementing this approach to Authentication means that, in order to create a new application user, all that’s required is the following :

create user plugger identified by pwd
/

grant hr_user to plugger
/

In case you’re wondering, Plugger is the nickname of a certain Tony Lockett who, apparently, was a pretty good Aussie Rules player in his time.

Anyway, as you can see, our new user requires no system privileges, not even CREATE SESSION. They simply need to be granted the role so that they can be identified as an application user.

Whilst were on the subject of least privilege, you might consider that it is by no means necessary for the parsing schema of an APEX application to have CREATE SESSION priviliges, or indeed, to even be the owner of the application’s database objects.
This applies irrespective of the Authentication Scheme being used.

We now have a robust and efficient Authentication Scheme. There is however, one rather thorny issue that we still need to consider.

Changing Passwords

Whilst we now have a mechanism for authenticating users through their database accounts, unless we give them the facility to change their passwords before they expire, we’ll be storing up a significant amount of admin for the poor, hard-pressed DBA.

The venerable Forms Application we’re migrating was written in the days prior to SSO becoming prevalent and authentication is still managed entirely within the database. Remember, the whole point of chosing Database Authentication is so that we minimise the amount of effort required to migrate this application onto APEX in terms of re-coding the Application’s Security Model.

This is where things get a bit tricky.
Whilst our users are authenticating as themselves, they are actually connecting to the database as ANONYMOUS or APEX_PUBLIC_USER.

Therefore, we need a procedure in a schema with ALTER USER privileges to change passwords from within the APEX application.

So, how do we provide this functionality in our application.

Danger ! Assumption Imminent !

As I’m all too aware ( often through bitter experience), Assumption is the Mother of all cock-ups.
Therefore, the assumption I’m about to make here requires careful explanation.

Here goes then…

I’m assuming that I can safely call a stored procedure from within APEX, passing a user password in clear text.

Clear text ! I hear you cry, Have you gone mad ?
Well, possibly. On the other hand a trawl through of the APEX documentation reveals that there are a few package members in the APEX packages themselves where this takes place.

These are :

• APEX_UTIL.IS_LOGIN_PASSWORD_VALID
• APEX_UTIL.EDIT_USER
• APEX_CUSTOM_AUTH.LOGIN
• APEX_AUTHENTICATION.LOGIN

Further research reveals that, certainly in the latest versions of APEX, there do not appear to be any exploits available to compromise these procedures. The most recent one I found was for APEX 3.1, an example of which can be seen on the Red Database Security site.

As well as giving the user the ability to change their password at any time, we also want to check immeadiately after the user connects and find out whether their password is near to expiry. If so, then we need to re-direct them to a password change page.
What was Jeff saying about scary code ?

Anyway, the steps to build this functionality are, in order :

1. Create a Change Password Procedure to be called from the application
2. Create a Change Password Page where the user can change their password ( and which will call the procedure)
3. Create a branch in the Application to re-direct a user to the Change Password Page if their password is due to expire

UPDATE :

Now in my original post, I had a whole section in here about not allowing quotes in passwords so that our dynamically built password change command would not be susceptible to injection.
Fortunately, Jeff Kemp took the time to point out the error of my ways (see the comments at the end of this post).
I’ve also now posted a follow up which explores this particular aspect of password changes in more detail.

Allowable characters in the password

As per the update above, I’ve now removed this section completely because it’s not necessary.
The password input parameters to the Change Password Procedure that we come up with should simply be enclosed in double-quotes when being concatenated into the ALTER USER statement.

The Change Password Procedure

Once again, this procedure is being created in the HR schema. It will be used to ultimately issue the ALTER USER command to change the passwords. Therefore, we need to grant the ALTER USER privilege to HR :

grant alter user to hr
/

As this procedure also needs to reference DBA_USERS, we’ll need to grant SELECT on that too.

grant select on sys.dba_users to hr
/

When writing this procedure, paranoia is the watchword. Objects need to be referenced directly, rather than via synonyms and any user input needs to be sanitised before we plug it into the dynamic SQL statement we need to run.
The result might look something like this :

create or replace procedure change_apex_user_pwd_pr
(
    i_old_pwd in varchar2,
    i_new_pwd in varchar2
)
is
--
-- Procedure to change the password for a user of the NEW_HR APEX application
-- The old password is required, as well as the new one because, if we're
-- using a verify function in the profile the user is assigned to, the
-- old password must be specified in the ALTER USER statement.
--
    l_user sys.dba_users.username%type;
    
    lc_apex_user constant sys.dba_users.username%type := 'ANONYMOUS';
    
    l_dummy pls_integer;
    cursor c_validate_user( cp_user sys.dba_users.username%type)
    is
        select 1
        from sys.dba_users usr
        inner join sys.dba_role_privs rol
            on rol.grantee = usr.username
        where usr.username = cp_user;
begin
    --
    -- Make sure that the parameter values have been specified
    --
    if i_new_pwd is null or i_old_pwd is null then
        raise_application_error(-20000, 'Both the Old Password and the New Password must be specified');
    end if;
    --
    -- Additionally, check that the password does not exceed the maximum length
    -- allowed ( 50 in 11g)
    --
    if length( i_old_pwd) > 50 
        or length( i_new_pwd) > 50
    then
        raise_application_error(-20001, 'Passwords must not exceed 50 characters in length.');
    end if;
    --
    -- Now validate that the user is indeed 
    --  (a) calling the function from APEX
    --  (b) exists in the database
    --  (c) is a user of this application

    l_user := apex_040200.v('APP_USER');
    if l_user is null 
        or user != lc_apex_user
    then
        raise_application_error(-20002, 'This function can only be called from APEX');
    end if;
    open c_validate_user( l_user);
    fetch c_validate_user into l_dummy;
    if c_validate_user%notfound then
        close c_validate_user;
        raise_application_error(-20003, 'This user is not a NEW_HR Application user');
    end if;
    close c_validate_user;
    --
    -- Now change the password. REPLACE clause is required in case the
    -- user's default profile has a password verify function specified
    -- Enclose the old and new passwords in double quotes...
    --
    execute immediate 'alter user '||l_user||' identified by '||chr(34)
        ||i_new_pwd||chr(34)||' replace '||chr(34)||i_old_pwd||chr(34);
end;
/

In the procedure itself, we’re taking a number of precautions :

• Values for both parameters must be supplied
• The input parameter values must not exceed 50 characters – the maximum length of an 11g password
• The user currently connected to the database is the Apex user ( in my case ANONYMOUS)
• A call to the V function for the application user returns a value
• The application user we’re changing is indeed a valid user of the NEW_HR Apex application – and a database user
• references to any database objects are done directly and not via synonyms

Hopefully, that’s enough paranoia to prevent the procedure being misused.
We can use a test harness to check the parameter tests at least :

set serveroutput on size unlimited
declare
--
-- test for the change_apex_user_pwd_pr procedure.
-- Note all of these tests should fail as we're running from SQL*Plus and
-- are not connected as ANONYMOUS.
-- 
    type rec_params is record
    (
        old_pwd varchar2(100),
        new_pwd varchar2(100)
    );
    type typ_params is table of rec_params index by pls_integer;
    tbl_params typ_params;
begin
    -- populate the test parameter array
    
    -- Test 1 - missing old password value
    tbl_params(1).old_pwd := null;
    tbl_params(1).new_pwd := 'Boring';
    
    -- Test 2 - missing new password value
    tbl_params(2).old_pwd := 'Boring';
    tbl_params(2).new_pwd := null;
 
    -- Test 3 - old password > 50 characters
    tbl_params(3).old_pwd := 'just leaning on the keyboard until i have printed over 50 characters zzzzz';
    tbl_params(3).new_pwd := 'short_and_to_the_point';
    
    -- Test 4 - new password > 50 characters
    tbl_params(4).old_pwd := 'short_and_to_the_point';
    tbl_params(4).new_pwd := 'just leaning on the keyboard until i have printed over 50 characters zzzzz';
    
    -- Test 5 - parameters are valid but we're not connected through APEX...
    
    tbl_params(5).old_pwd := 'Valid_pwd';
    tbl_params(5).new_pwd := 'anotherboringpassword';
    
    --
    -- Execute the tests
    --
    for i in 1..tbl_params.count loop
        begin
            change_apex_user_pwd_pr
            (
                i_old_pwd => tbl_params(i).old_pwd,
                i_new_pwd => tbl_params(i).new_pwd
            );
            dbms_output.put_line('Test '||i||' - Somthing has gone wrong - no error !');
        exception
            when others then
                dbms_output.put_line('Test '||i||' Error : '||sqlerrm);
        end;
    end loop;
end;
/

Running this gives us :

Test 1 Error : ORA-20000: Both the Old Password and the New Password must be specified
Test 2 Error : ORA-20000: Both the Old Password and the New Password must be specified
Test 3 Error : ORA-20001: Passwords must not exceed 50 characters in length.
Test 4 Error : ORA-20001: Passwords must not exceed 50 characters in length.
Test 5 Error : ORA-20002: This function can only be called from APEX

PL/SQL procedure successfully completed.

To test the rest of the function, we will of course, need to be connected via APEX.

The Change Password Page

Now we come to the page we will be using to call the procedure we’ve just created.
The page will have :

• a password field for the application user to enter their current password
• a password field for the application user to enter their new password
• and another one for them to re-type it
• some validation that the new password and confirm password matches
• a button to call the change password procedure
• a field to present a message to the user after the password change call

Sounds simple (dangerous) enough…

In Application Builder hit the Create Page button…

select Blank Page ….

In the Page Attributes…

Page Alias : change_db_pwd

In the Page Name …

Name : Change My Password
HTML Region1 : change password

In Tab Options…

Tab Options : Use an existing tab set and create a new tab within the existing tab set
New Tab Label : Change Password

…and hit Finish.

Now Edit the Page.

Create a new field with an Item Type of Password :

In the Display Position and Name screen,

Item Name : PX_OLD_PWD (where X is the number of the page you’re editing).

In the Item Attributes Screen :

Label : Current Password
Field Width : 50

In the Settings Screen –

Value Required : Yes
Submit when Enter pressed : No

In the Source Screen :

Source Used : Always, replacing any existing session state

Hopefully, the APEX5 Graphical Page Designer will result in fewer screenshots being required in future !

And hit Create Item.

Now create two further fields with the same properties except :

PX_NEW_PWD has a label of New Password
PX_CONFIRM_PWD has a label of Confirm New Password

Next, we create a Display Only field called PX_MESSAGE.
We’ll use this to provide feedback to the user.
We define this with no label so that it doesn’t show up on the screen, until it’s populated.

Now we’ve got all of the fields on the page the next step is to create the Change Password button :

Accept the defaults for Button Region and Button Position.

In the Button Attributes Page :

Button Name : change_pwd_btn
Label : Change Password

Then just hit Create Button.

Finally, we need to add a Dynamic Action to validate that the values in PX_NEW_PWD and PX_CONFIRM_PWD are not null and identical, and then to call the Procedure.

NOTE – I daresay any APEX experts reading this may have a better way of doing this !

So, Create a Dynamic Action.

In the Identification Page :

Name : change_pwd_da

In the When Page :
Action : Click
Selection Type : Button
Button : CHANGE_PWD_BTN

In the True Action Page :

Action : Execute PL/SQL Code

The PL/SQL Code is as follows :

begin
    if nvl(:P6_NEW_PWD, 'x') != nvl(:P6_CONFIRM_PWD, 'y')
    then
       :P6_MESSAGE := 'Confirm Password does not match New Password.';
    else
        hr.change_apex_user_pwd_pr
        (
             i_old_pwd => :P6_OLD_PWD,
             i_new_pwd => :P6_NEW_PWD
        );
        :P6_MESSAGE := 'Your password has been changed';
    end if;
exception when others then
      :P6_MESSAGE := SQLERRM;
end;

Page Items to Submit : P6_OLD_PWD,P6_NEW_PWD,P6_CONFIRM_PWD,P6_MESSAGE
Page Items to Return : P6_MESSAGE

Click Create Dynamic Action.

Now to test.
I’m connected as PLUGGER and I want to change my password.
So, I click on the Change Password Tab and I see :

If the new and confirm password fields don’t match, I get an error from the Dynamic Action itself, before it calls the procedure :

Someone’s having a fat-finger moment

When I manage to get it right and am rewarded with :

Invoking the Change Password Programatically

All that remains now is for us to arrange for the user to be re-directed to the change password page when they connect and their password is near expiry.

The password expiry_date is available in the DBA_USERS view so we need to grant SELECT on this to HR :

grant select on sys.dba_users to hr
/

As I’m re-directing them to a page that belongs specifically to the current application, I’m going to put the re-direction in the application itself.
So, I’m going to add a Branch to the Home Page.
Once again we need to pause here for the APEX gurus to explain the proper way to do this !

Edit the Home Page and Create a Branch…

In Branch Attributes

Name : pwd_change_br
Branch Point : On Load : Before Header

In Target

Page : the number of the Change Password Page ( 6 in my case)

In Branch Conditions

Condition Type : Exists( SQL query returns at least one row)
In Expression 1, enter the query :

select 1
from sys.dba_users
where username = apex_040200.v('APP_USER')
and expiry_date < trunc(sysdate) + 7

This will return 1 if the password is due to expire within the next 7 days.

and hit Create Branch.

In order to test the branch, I’ve had a bit of a fiddle with the FIXED_DATE parameter so that PLUGGER’s password is now due to expire in less than 7 days.

Now, when I login as plugger…

…I go straight to the Change Password Page…

Summary

What started off as a fairly short post about Database Authentication Schemes in APEX has grown quite a bit more than I intended.
I believe that the solution to password management, which I have outlined here, is secure.
Obviously, if anyone can spot any flaws in this, I (and anyone reading this), would find it immensly helpful if you could provide reasons/code as to why and how this approach could be exploited.
Whilst the Change Password functionality is something of an overhead in going down the Database Authentication route, the use of database roles, not to mention the RDBMS itself, does mean that this is an approach worth considering when porting older applications to APEX….or maybe it isn’t.
I wonder if there’s a passing Australian who’d like to share their opinion on this ?