Error creating database users in Apex (or my life as bald Britney)

Hello, my name is David, and I have suffered a relapse.

I haven't blogged in many, many months: I'm sorry. The reason for the silence is simple; after all this time enjoying the fresh new oxygen of Apex, I've got to confess that I've fallen off the wagon a lot lately and gone back to developing in Oracle Forms. What can I say? For close to 10 years Forms has been my addiction; it's hard to go cold turkey. I keep sneaking back down those dark, open-sewered alleys to return to my dirty addiction. So I guess I'm a bit like Britney Spears. Except I've got no hair and I can't sing.

Um ... er... oh...

I have done some Apex development of recent and I ran into a problem. I was developing a small app that is designed to be used by a small number of users. I decided to make them database users, rather than make them Application Express users. To that end I created a user page and backed it up with a pl/sql page process containing the following code:

Begin
Execute immediate 'create user '||:P10_USERNAME||' identified by '||:P10_PASSWORD;
End;

So far, so simple. However, when I clicked my button I had an error spat in my face: ORA-01031: Insufficient privileges. Hmm, I'm like Tarzan - I've grown up in this jungle of error messages, the roar of an ORA number no longer fills me with fear. Obviously all I need to do is grant the CREATE USER privilege to my user.

But who? All Apex commands are executed as the APEX_PUBLIC_USER user (or whichever user you have specified in your dads.conf file). Thus my first thought was to grant the privilege to this user. Easily done.

I went back to my page, pressed my button - and again was greeted with the ugliness of an error message.

Further investigation (and some very gracious assistance from OTN users) led me to the "parsing schema". The parsing schema is the schema that backs up your application - of course you know what it is, but you can doublecheck by clicking on Shared Components >>> Edit Application Definition.

Ay, but there's the rub: logged into SQL Plus as this user I was able to create users. So what was going on?

To cut a long story short (and that's not something I often do) I found that if the parsing schema has the create user privilege as part of a role that it has been granted, the parsing schema will be able to create database users in SQL Plus - but not through an Apex app. To create database users through Apex you have to grant create user directly to the parsing schema.

And that, dear friend, is what I learned last week. Gotta go now; they're trying to make me go to Oracle Forms rehab. No no no...