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

Continue reading

APEX and Privileges Granted through Roles

The mystery has finally been solved. England’s surrendering of the Ashes last winter was nothing to do with Australia being a much better cricket team. Thanks to Kevin Pietersen’s recently published Autobiography, we now know that the problem was that there were rather too many silly points in the England dressing room.
Moving swiftly on from that weak pun, the subject at hand can also be rather mystifying at first glance.

In a “traditional” Oracle Forms application, you would have one database user per application users.
Connections via the Application to the database would be done as the individual users.
It’s quite likely that database roles would be used to grant the appropriate privileges.

For applications using other web technologies, the application may interact with the database via a single account, often that of the Application Owner. Whether or not this is a good idea is probably a discussion for another time.

For now though, the question we’re asking is, how an APEX application connect to the database ?
On the face of it, it would seem that it’s pretty similar to the second of the two approaches above. APEX connects as the Parsing Schema (usually the application owner).
As Kevin will tell you, appearances can be deceiving…
Continue reading

Re-structuring data for Hierarchical Queries – or (Tree) Walking With Big Macs

Steve McNulty. Even the name sounds a bit hard. This is not the hero in Jason Statham’s latest celluloid exploit. Neither is it the central character in a hard-bitten cop drama.
Steve McNulty is, in fact the current Luton Town captain and a member of an endangered species – the stopper Centre-Half.
When you first set eyes on him, he looks, well, a bit chunky. You might imagine his nickname to be “Big Mac” because of his penchant for a certain fast-food chain.
This is something of a mis-conception.
Firstly, he’s not overweight. In contrast to the other players on the pitch, his body has not so much been honed to athletic perfection as hewn from solid rock.
It is a build that has not been seen for years in the elite (effete ?) Premier League.
He’s not the fastest player, as you’d expect, but he’s strong in the tackle. When he heads the ball, adjectives such as cushioning and glancing do not apply. It’s a Kirby Kiss (he’s a Scouser). The ball is definitely not his friend.
So, Big Mac he is not. He couldn’t be associated with anything that’s served with namby-pamby french-fries. A McNulty burger is a huge slab of meat wedged between two halves of a cottage loaf. It would only ever be served with chunky chips.
It’s McNulty and friends that provide the inspiration for the examples that follow.
I recently came across a situation where I needed to take some relational data and convert it into a hierarchy for the purposes of dropping it into an APEX tree. This proved slightly more challenging than I originally thought. Continue reading

Turning off Password expiration on Oracle XE and Apex

It is a strange time in the UK.
I’m not referring to England being 2-0 up after 2 tests in the Ashes (something that happens about as often as a Briton winnng the Men’s Singles at Wimbledon), nor the fact that a Briton has won the Tour de France for the second year running.
Stranger even than that is the bright yellow ball in the sky which has replaced the traditional warm rain of the British Summer.

This phenomenon has had a strange effect on the cat. Her animal instincts obviously alerted by the unfamiliar change in the climate, she currently spends almost all of her time out in the garden.
Unfortunately, she seems to have decided to treat said garden as something of a litter tray. This will necessitate something of a mine-sweeping exercise before I next mow the lawn.

It’s frustrating when you’re anxious to start something but then hit an unpleasant roadblock.
For example, you may have decided to have a play with that Oracle XE/Apex installation on your laptop that you haven’t used for a little while. However, when you come to connect, you realise that you can’t remember the password.

Health Warning – Before I go any further, I should point out that doing this is not something I’d recommend on anything other than a system that’s being used simply as a playground for you to experiment and which contains no sensitive data.

Continue reading

An APEX Database Monitoring App for XE – Guilty GUI pleasures

Guilty pleasures. For some, it’s a “diet” burger with “diet” fries, washed down with a “diet” shake. Others have a penchant for Kurt Geiger shoes. “I’m Welsh and I’m worth it”, they may well say. It may even be that Def Leppard track nestled in your playlist between Coldplay and Oasis.

In programming terms, APEX seems to fall into this category for me. On the one hand, it’s a declarative development environment. This means that, unless you’re very careful, the application you write for it is not going to be too portable to other front-end technologies. But, oh, it’s so nice to be able to bang out a bit of SQL and/or PL/SQL, click my mouse in the right place, and have a nice GUI application drop onto my browser.

If you’ve decided to try the latest and greatest APEX version on your XE installation, you’ll notice that the default Database Welcome Page disappears after the upgrade.
Rather than hunting around for it, I’ve decided to knock up something a bit better…well, different.
So, if you’d like to know how to get some interesting configuration information out of the database…or just want the entertainment value of watching me blunder about in APEX then read on… Continue reading

APEX – Getting back to where you came from

Ah, sunny Milton Keynes. There’s no place like it. Nestling in the heart of England, halfway between London and Birmingham, my home town has plenty to recommend it.
Yes, many of my countrymen like to poke fun at the Concrete Cows that are Milton Keynes’ most famous landmark. However, the one irrefutable benefit of living here, especially if you tend toward the geeky, is that Milton Keynes is also the home of the National Museum of Computing, hosted in Bletchley Park.
One thing about Milton Keynes is the interminable roundabouts throughout the city. Just keep turning left at every roundabout and you’ll be guaranteed to end up back where you started.

The same however, cannot be said of APEX – at least, not without a little bit of work.

In the example that follows, we have a page in an APEX application that can be invoked from a number other pages. The target page has a back button to return to the page you just came from. The question is, how do you make the target page re-direct back to the correct calling page ?
It must be said that the solution that follows can best be described as crude but effective. I’d be interested to hear if you come up with a more elegant solution.
Anyway, here goes…. Continue reading