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 :
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 :
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 :
We now have some means of determining which database users are our application users :
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 :
and then I’m going to create the function in the HR schema :
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 :
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 :
Create a Change Password Procedure to be called from the application
Create a Change Password Page where the user can change their password ( and which will call the procedure)
Create a branch in the Application to re-direct a user to the Change Password Page if their password is due to expire
Allowable characters in the password
As we’re going to have to change the password by executing an ALTER USER command from within a PL/SQL procedure, we’re going to have to use dynamic SQL. Critically, we’re not going to be able to use bind variables for this command because it’s a DDL statement.
In order to ensure that the resulting procedure is not vulnerable to SQL Injection, we’re going to have to make sure that passwords do not contain the single quote (‘) character.
To do this, we’re going to create a profile for our application users which includes a password verify function and assign it to them.
So, the Password Verify Function, which needs to be created in the SYS schema, looks like this :
A quick test of this function shows that it works as expected :
Run this as we get…
Note that, although the string containing “chr(39)” is allowed, because there is no way to concatenate a quote into the entry string, this is treated as a collection of characters rather than a call to the CHR function.
Incidentally 39 is the ASCII code for a single quote.
Also note that this particular password verify function has been kept simple deliberately for the purposes of clarity.
Something rather more complex is likely to be in place in a real-life production scenario.
The profile then, looks like this :
Finally, we’re going to assign the profile to PLUGGER :
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 :
As this procedure also needs to reference DBA_USERS, we’ll need to grant SELECT on that too.
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 :
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 input parameter values must not contain a single quote character
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.
Once again, we can use a test harness to check the parameter tests at least :
Running this gives us :
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 :
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
If I try to enter a password that contains a single quote, I get :
We’ll have none of those naughty quotes thank you very much.
Finally, 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 :
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 :
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 [link to post] 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 ?
Filed under: APEX, Oracle, PL/SQL, SQL Tagged: APEX Database Authentication Scheme, change password procedure, dba_role_privs, dba_users, password verify function, profile, verify function