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

Kilobytes, Kibibytes and DBMS_XPLAN undocumented functions

How many bytes in a Kilobyte ? The answer to this question is pretty obvious…and, apparently, wrong.
Yep, apparently we’ve had it wrong all these years for there are, officially, 1000 bytes in a Kilobyte, not 1024.
Never mind that 1000 is not a factor of 2 and that, unless some earth-shattering breakthrough has happened whilst I wasn’t paying attention, binary is still the fundemental basis of computing.
According to the IEEE, there are 1000 bytes in a kilobyte and we should all get used to talking about a collection of 1024 bytes as a Kibibyte

Can you imagine dropping that into a conversation ? People might look at you in a strange way the first time “Kibibyte” passes your lips. If you then move on and start talking about Yobibytes, they may well conclude that you’re just being silly.

Let’s face it, if you’re going to be like that about things then C++ is actually and object orientated language and the proof is not in the pudding – the proof of the pudding is in the eating.

All of which petulant pedantry brings me on to the point of this particular post – some rather helpful formatting functions that are hidden in, of all places, the DBMS_XPLAN pacakge… 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

Going dotty – Generating a Filename containing a parameter value in SQL*Plus

As I have alluded to previously, I was not born in the UK.
Nope, my parents decided to up-sticks and move from London all the way to the
other side of the world, namely Auckland.
Then they had me. Then they came back.
To this day, they refuse to comment on whether these two events were related.

I went back to New Zealand a few years ago.
As I wandered around places that I hadn’t seen since I was five, it was strange how memories that I had forgotten came flooding back.
That last sentence doesn’t make much sense. It’s probably more accurate to say that memories I hadn’t thought about for years came flooding back.

I recently remembered something else I once knew, and then forgot – namely how to generate a SQL*Plus file name which includes a parameter value. Continue reading

Sayonara to Sequences and Trouble for Triggers – Fun and Games in Oracle 12c

Ah, Nostalgia.
Not only can I remember the Good Old Days, I also remember them being far more fun than they probably were at the time.
Oh yes, and I was much younger….and had hair.
Yes, the Good Old Days, when Oracle introduced PL/SQL database packages, partitioning, and when the sequence became extinct.
Hang on, I don’t remember that last one…
Continue reading

Getting Python to play with Oracle using cxOracle on Mint and Ubuntu

“We need to go through Tow-ces-ter”, suggested Deb.
“It’s pronounced Toast-er”, I corrected gently.
“Well, that’s just silly”, came the indignant response, “I mean, why can’t they just spell it as it sounds ?”
At this point I resisted the temptation of pointing out that, in her Welsh homeland, placenames are, if anything, even more difficult to pronounce if you’ve only ever seen them written down.
Llanelli is a linguistic trap for the unwary let alone the intriguingly named Betws-Y-Coed.
Instead, I reflected on the fact that, even when you have directions, things can sometimes be a little less than straight forward.

Which brings me to the wonderful world of Python. Having spent some time playing around with this language, I wanted to see how easy it is to plug it into Oracle.
To do this, I needed the cxOracle Python library.
Unfortunately, installation of this library proved somewhat less than straightforward – on Linux Mint at least.
What follows are the gory details of how I got it working in the hope that it will help anyone else struggling with this particular conundurum. Continue reading