Skip navigation.

The Anti-Kyte

Syndicate content The Anti-Kyte
Oracle - for when it was like that when you got there
Updated: 8 min 46 sec ago

Installing Oracle XE on CentOS

Sun, 2015-03-01 11:33

Another Cricket World Cup is underway. England are fulfilling their traditional role of making all of the other teams look like world beaters.
To take my mind off this excruciating spectacle, I’ll concentrate this week on installing Oracle XE 11g on CentOS 7.

Before I get into the nuts and bolts of the installation…

Flavours of Linux

Whilst there are many Linux Distros out there, they all share the same common Linux Kernel. Within this there are a few Distros upon which most others are based.
Debian provides the basis for Ubuntu and Mint among others.
It uses the .deb package format.

Red Hat Linux in contrast uses the RPM file format for it’s packages. Red Hat is the basis for Distros such as Fedora, CentOS…and Oracle Linux.

For this reason, the Oracle Express Edition Linux version is packaged using rpm.
Whilst it is possible to deploy it to a Debian based Distro – instructions for which are available here, deploying on CentOS is rather more straightforward.
More straightforward, but not entirely so, as we will discover shortly…

Getting Oracle Express Edition 11G

Open your web browser and head over the the Oracle Express Edition download page.

You’ll need to register for an account if you don’t already have one but it is free.

The file you need to download is listed under :

Oracle Express Edition 11g Release 2 for Linux x64.

NOTE XE 11G only comes in the 64-bit variety for Linux. If you’re running a 32-bit version of your Distro, then you’re out of luck as far as 11G is concerned.

If you’re not sure whether you’re on 32-bit or 64-bit, the following command will help you :

uname -i

If this returns x86_64 then your OS is 64-bit.

Installing XE

You should now have downloaded the zipped rpm file which will look something like this :

cd $HOME/Downloads
ls -l
-rwxrwx---. 1 mike mike 315891481 Dec 16 20:21 oracle-xe-11.2.0-1.0.x86_64.rpm.zip

The next step is to uncompress…

 unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip

When you run this, the output will look like this :

   creating: Disk1/
   creating: Disk1/upgrade/
  inflating: Disk1/upgrade/gen_inst.sql  
   creating: Disk1/response/
  inflating: Disk1/response/xe.rsp   
  inflating: Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm 

You now need to switch to the newly created Disk1 directory and become root

cd Disk1
su

…and then install the package…

rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm

If all goes well you should see…

Preparing...                          ################################# [100%]
Updating / installing...
   1:oracle-xe-11.2.0-1.0             ################################# [100%]
Executing post-install steps...
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.
Configuring XE

The configuration will be prompt you for

  1. the APEX http port (8080 by default)
  2. the database (TNS) listener port (1521 by default)
  3. A single password to be assigned to the database SYS and SYSTEM users
  4. whether you want the database to start automatically when the system starts (Yes by default)

Unless you have other software, or Oracle Instances, running elsewhere, the defaults should be fine.

Here we go then, still as root, run :

/etc/init.d/oracle-xe configure

The output, complete with the prompts will be something like :

Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express 
Edition.  The following questions will determine whether the database should 
be starting upon system boot, the ports it will use, and the passwords that 
will be used for database accounts.  Press <Enter> to accept the defaults. 
Ctrl-C will abort.

Specify the HTTP port that will be used for Oracle Application Express [8080]:8081

Specify a port that will be used for the database listener [1521]:1525

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of 
different passwords for each database account.  This can be done after 
initial configuration:
Confirm the password:

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:y

Starting Oracle Net Listener...Done
Configuring database...Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.

Congratulations, you now have a running database. The first thing to do with it, however, is to shut it down.
In fact, we need to do a re-start so that the menu items that have been added as part of the installation are visible.
So, re-boot.

NOTE – from this point on you can stop being root (although you may need to sudo occasionally).

Once the system comes back, you will see the new Menu icons in the Applications menu under others :

oracle_menu

Just to confirm that your database is up and running, you can select the Run SQL Command Line option from this menu
and run the following :


conn system/pwd
select sysdate from dual
/

This should return the current date.

Sorting out the Environment Variables

In the normal run of things, this is the one fiddly bit. There is a bug in one of the scripts Oracle uses to set the environment variables which may cause issues.

To start with, let’s have a look at the main environment script…

cat /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh

This script is as follows :

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export ORACLE_BASE=/u01/app/oracle
export PATH=$ORACLE_HOME/bin:$PATH

There is a bug in the nls_lang.sh that is called from here. If you’re NLS_LANG value contains a space, then it will not be configured correctly. A full list of the affected NLS_LANG values is available on the Oracle XE Installation Guide for Debian based systems I mentioned earlier.

The easiest way to fix this is to just edit the script :

sudo gedit /u01/app/oracle/product/11.2.0/xe/bin/nls_lang.sh

Right at the bottom of the script where it says :

# construct the NLS_LANG
#
NLS_LANG=${nlslang}.${charset}

echo $NLS_LANG

…amend it so that the $NLS_LANG value is quoted :

# construct the NLS_LANG
#
NLS_LANG="${nlslang}.${charset}"

echo $NLS_LANG

To test the change and make sure everything is now working properly…

cd /u01/app/oracle/product/11.2.0/xe/bin

. ./oracle_env.sh
echo $ORACLE_HOME
echo $ORACLE_SID
echo $NLS_LANG
echo $PATH

You should now see the following environment variable settings :

echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/xe
echo $ORACLE_SID
XE
echo $NLS_LANG
ENGLISH_UNITED KINGDOM.AL32UTF8
$PATH
/u01/app/oracle/product/11.2.0/xe/bin:/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin:/bin:/sbin:/home/mike/.local/bin:/home/mike/bin

NOTE – the $NLS_LANG should have a setting appropriate for your system (in my case ENGLISH_UNITED KINGDOM.AL32UTF8).

The Oracle bin directory is now at the start of $PATH.

Next, we need to ensure that these environment variables are set for all sessions. This can be done by running …

sudo cp /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh /etc/profile.d/.

To check this, you can start a new terminal session and echo the environment variables to make sure they have been set.

Getting the Menu Items to Work

To do this, you simply need to make sure that the oracle user, as well as your own user, is a member of the dba group :

sudo usermod -a -G dba oracle
sudo usermod -a -G dba mike

To check :

sudo grep dba /etc/group
dba:x:1001:oracle,mike
$

The menu items for starting up and shutting down the database etc. should now work.

Enabling the Getting Started Desktop Icon

The final touch. The installation creates a Getting Started icon on the desktop which is designed to open the Database Home Page of the APEX application that comes with XE.

In order to make it work as desired, you simply need to right-click the icon and select Properties.
In the Permissions Tab check the box to “Allow executing file as program”.
Close the window.

You will notice that the icon has transformed into the familiar Oracle beehive and is now called
Get Started With Oracle Database 11g Express Edition.

Clicking on it now will reward you with …

db_home

All-in-all then, this installation is reasonably painless when compared with doing the same thing on a Debian system.
I wish the same could be said of following the England Cricket Team.


Filed under: Linux, Oracle Tagged: CentOS, nls_lang.sh, Oracle 11g Express Edition, oracle_env.sh

SQL*Plus Terminator Torture

Thu, 2015-02-26 05:42

“Leave that jar of Nutella alone, it’s got my name on it !”
The context in which Deb issued this injunction to me probably requires some explanation.
It was Friday evening.
Wales had just…well…come second in the latest installment of their eternal battle with the English through the medium of Rugby.
There was no alcohol left in the house.
And only one source of chocolate.
From the safety of the Cupboard under the stairs, to which I had retreated at kick-off – the Welsh do take their Rugby quite seriously – I wondered about my better half’s change of name.
Shorn of it’s chocolate hazelnut spread connotations, you might think that Nutella was quite an nice name for a girl.
It certainly seems appropriate if the “Girl” in question is slightly unhinged by a combination of wine and wounded national pride.

I was going to write something here about how Rugby players all look like the Terminator and use this as a way of introducting the topic at hand. However, I realise that this would simply be too contrived…even for me.
Instead, I’ll jump straight in…

The Nature of SQL*Plus

SQL*Plus is the command line interface for the Oracle RDBMS. As such, it supports three categories of statement :

  • SQL*Plus commands – which require no terminator
  • SQL commands – terminated by a “/” on a new line
  • PL/SQL blocks – containing statements that are terminated by “;”

…but that’s not the whole story as we are about to discover….

select 'Hasta La Vista' from dual
/

set serveroutput on size unlimited

begin
    dbms_output.put_line(q'[Ill be back...from PL/SQL]');
end;
/

select 'Just you wait until the World Cup!' 
from dual;

Here we can see an example of all three statement types – SQL, SQL*Plus and PL/SQL.
However the final SQL statement is a bit different. The terminator is not the newline “/” as with the first statement, but a “;”.

It is this small quirk that can cause some interesting things to happen and leave you scratching your head for a good while until you figure out exactly what’s happened.

Spot the Terminator

Consider the following, run as an unattended batch job :

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;
declare
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
begin
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
end;
/
    
create table tenuous_links( arnie_quote varchar2(100));
/

Simple enough – drop the table ( ignoring the ORA-00942 error if it doesn’t exist in the first place), then create it again…


TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 13:12:20


PL/SQL procedure successfully completed.


Table created.

create table tenuous_links( arnie_quote varchar2(100))
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> 

At first glance, it would seem that our PL/SQL block to drop the table didn’t work for some reason.
However, if we dig a bit deeper…

select to_char(created, 'DD-MON-YYYY HH24:MI:SS')
from user_objects
where object_name = 'TENUOUS_LINKS'
and object_type = 'TABLE'
/

TO_CHAR(CREATED,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 13:12:20

So, the table was created at the time we ran our script. We know it still exists (and is not in the recycle bin) because it’s still in USER_OBJECTS.

If the table was dropped then re-created when we ran our script then why did we get the error ?

Let’s have a look at that CREATE TABLE statement again :

create table tenuous_links( arnie_quote varchar2(100));
/

Notice that “;” at the end of the first line ?
SQL*Plus takes this to mean “execute the last statement in the buffer”.
Then on the next line we have the same directive – expressed using a different syntax – i.e. “/”.

If we correct the script by removing the extraneous “;” all now works as expected :


select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;
declare
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
begin
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
end;
/
    
create table tenuous_links( arnie_quote varchar2(100))
/

TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 13:30:48


PL/SQL procedure successfully completed.


Table created.

Whilst debugging a small script like this is relatively straight forward, you’re likely to be confronted with a much longer script in the real world and spend considerably more time tracking down the problem.

Culling the Terminators

At this point we have a number of questions :

  1. How and why does SQL*Plus allow two terminator characters ?
  2. Can we turn one of them off ?
  3. Can we standardise on one of them and if so, which ?

It’s probably worth taking a closer look at the two terminator characters as they behave slightly differently in
certain circumstances. For example…

create or replace function arnie
    return varchar2
as
begin
    return(q'[I'll be back!]');
end;
/

Function created.

However, we get a bit less success if we try :

create or replace function arnie
    return varchar2
as
    begin
        return(q'[I'll be back!]');
end;;
SQL> 
SQL> 
SQL> 

SQL*Plus doesn’t recognise the second “;” as a terminator. Putting it on a separate line fares no better.
From this then, we can infer that the “/” is mandatory when generating DDL for PL/SQL stored program units.

The next question is how we end up with two terminators, and whether we can switch one of them off.
Well, let’s take a look at the SQL*Plus settings, or one setting in particular :

SQL> show sqlterminator
sqlterminator ";" (hex 3b)
SQL> 

We can use this parameter to set the terminator to another single character…


SQL> set sqlterminator !

SQL> select 'Another Terminator reference'
  2  from dual
  3  !

'ANOTHERTERMINATORREFERENCE'
----------------------------
Another Terminator reference

SQL> 

SQL> select 'Another Terminator reference' from dual !

'ANOTHERTERMINATORREFERENCE'
----------------------------
Another Terminator reference

SQL> 

SQL> select 'Another Terminator reference!' from dual !

'ANOTHERTERMINATORREFERENCE!'
-----------------------------
Another Terminator reference!

SQL> 

…but not multiple characters…

SQL> set sqlterminator runbuffer
string "runbuffer" is too long. maximum size is 1 character.
SQL> 

Interestingly, we can also disable it altogether and then re-enable it…

SQL> set sqlterminator off
SQL> show sqlterminator
sqlterminator OFF
SQL> select sysdate
  2  from dual;
  3  
SQL> 
SQL> select sysdate
  2  from dual
  3  /

SYSDATE
------------------
20-FEB-15

SQL> set sqlterminator ;
SQL> select sysdate
  2  from dual;

SYSDATE
------------------
20-FEB-15

SQL> 

Whilst this can be quite useful, especially if you’re running scripts that contain only DDL for stored program units, it does feel a bit odd having to put the terminator on a new line.
Additionally, you may consider that standardising this would require some serious regression testing of any SQL scripts to make sure that they’re not using the “;” terminator, not to mention any scripts that get generated dynamically.

Missing Terminators

Just as vexing as having too many terminators is not having enough.

consider :

create or replace package the_terminator as
    function favourite_terminator
        return varchar2;
end;

create or replace package body the terminator as

    function favourite_terminator
        return varchar2
    is
    begin
        return q'[There's only one Arnie !]';
    end favourite_terminator;
end the_terminator;
/

Warning: Package created with compilation errors.

SQL> sho err
Errors for PACKAGE THE_TERMINATOR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1	 PLS-00103: Encountered the symbol "CREATE"

SQL> select object_type          
  2  from user_objects
  3  where object_name = 'THE_TERMINATOR'
  4  /

OBJECT_TYPE
-------------------
PACKAGE

SQL> 

Now consider this in a longer script ( which I’ve instrumented with prompt statements) :

prompt Recording Start Time

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;

prompt Dropping TENUOUS_LINKS

declare
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
begin
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
end;
/

prompt Creating table TENUOUS_LINKS
    
create table tenuous_links( arnie_quote varchar2(100))
/


prompt Creating Function ARNIE

create or replace function arnie
    return varchar2
as
begin
    return(q'[I'll be back !]');
end;

prompt Creating Package THE_TERMINATOR

create or replace package the_terminator as
    function favourite_terminator
        return varchar2;
end;
/

create or replace package body the terminator as

    function favourite_terminator
        return varchar2
    is
    begin
        return q'[There's only one Arnie !]';
    end favourite_terminator;
end the_terminator;
/

This time, we’ve missed the terminator at the end of the function.
However, this causes the Package to error as well :

Recording Start Time

TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 14:11:39

Dropping TENUOUS_LINKS

PL/SQL procedure successfully completed.

Creating table TENUOUS_LINKS

Table created.

Creating Function ARNIE

Warning: Function created with compilation errors.


Warning: Package Body created with compilation errors.

SQL> sho err
Errors for PACKAGE BODY THE_TERMINATOR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0	 PL/SQL: Compilation unit analysis terminated
1/14	 PLS-00304: cannot compile body of 'THE_TERMINATOR' without its
	 specification

1/14	 PLS-00905: object MIKE.THE_TERMINATOR is invalid
SQL> select object_name, object_type
  2  from user_objects
  3  where object_name in ('ARNIE', 'THE_TERMINATOR')
  4  /

OBJECT_NAME		       OBJECT_TYPE
------------------------------ -------------------
ARNIE			       FUNCTION
THE_TERMINATOR		       PACKAGE
THE_TERMINATOR		       PACKAGE BODY

SQL> 

There are a couple of things to note here. First is that it may well be worth enhancing the instrumentation in the script by including SHOW ERRORS after each stored program unit creation statement. The second is that there is no “smoking gun” error for either too many terminators, or not enough.
With SHOW ERRORS in place, it becomes a bit easier to spot what’s going wrong :

prompt Recording Start Time

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual;

prompt Dropping TENUOUS_LINKS

declare
    e_not_exists exception; 
    pragma exception_init(e_not_exists, -942);
begin
    execute immediate 'drop table tenuous_links purge';
exception when e_not_exists then null;
end;
/

prompt Creating table TENUOUS_LINKS
    
create table tenuous_links( arnie_quote varchar2(100))
/


prompt Creating Function ARNIE

create or replace function arnie
    return varchar2
as
begin
    return(q'[I'll be back !]');
end;

show error

prompt Creating Package THE_TERMINATOR

create or replace package the_terminator as
    function favourite_terminator
        return varchar2;
end;
/

show error


create or replace package body the_terminator as

    function favourite_terminator
        return varchar2
    is
    begin
        return q'[There's only one Arnie !]';
    end favourite_terminator;
end the_terminator;
/

show error

When you run this script, the problem is a bit easier to spot :

Recording Start Time

TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
21-FEB-2015 14:17:03

Dropping TENUOUS_LINKS

PL/SQL procedure successfully completed.

Creating table TENUOUS_LINKS

Table created.

Creating Function ARNIE

Warning: Function created with compilation errors.

Errors for FUNCTION ARNIE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/1	 PLS-00103: Encountered the symbol "SHOW"

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY THE_TERMINATOR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0	 PL/SQL: Compilation unit analysis terminated
1/14	 PLS-00304: cannot compile body of 'THE_TERMINATOR' without its
	 specification

1/14	 PLS-00905: object MIKE.THE_TERMINATOR is invalid
SQL> 

There is another SQL*Plus setting that you might consider to at least reduce some of the pain caused by this tangle of terminators.
Remember, the terminator is a directive for SQL*Plus to run what’s in it’s buffer. By default this is the last SQL or PL/SQL statement. SQL*Plus commands are not held in the buffer.
The CLEAR BUFFER command will, well, clear the contents of the buffer…

SQL> select 'I really must watch the movies again'
  2  from dual
  3  /

'IREALLYMUSTWATCHTHEMOVIESAGAIN'
------------------------------------
I really must watch the movies again

SQL> list
  1  select 'I really must watch the movies again'
  2* from dual
SQL> clear buffer
buffer cleared
SQL> list
SP2-0223: No lines in SQL buffer.
SQL> 

So, run a statement and we can then see it’s in the buffer by issuing the list command ( usually abbreviated to “l”).
After the CLEAR BUFFER, there’s nothing to see.

Conclusion

SQL*Plus facilitates the use of two terminator characters which behave in the same way…most of the time.
Whilst it’s possible to disable, or even change the “;”, it may not be practical to go forward using just the “/” as a standard.

Deb has just “asked” me to add a disclaimer to this post.
A certain amount of artistic licence was used in the first couple of paragraphs.
After all, there’s so much junk in the cupboard under the stairs that I’d never fit in.
The Shed, Deb assures me, is an entirely different matter.


Filed under: Oracle, SQL Tagged: clear buffer, prompt, set sqlterminator, show errors, SQL*Plus, statement terminators

Installing and Configuring an Oracle Developer Day VirtualBox Image

Sun, 2015-01-25 15:28

It’s winter. I can tell. First, it’s still dark. Secondly it’s bitterly cold.
Standing on the platform at Milton Keynes Central, it would appear that we now have further evidence that being late is a train driver’s prerogative.
Hiking up the season ticket price is obviously the prerogative of train operators.
The last time I spent this much money to feel this cold, I found myself standing on the top of a mountain in the South Island of New Zealand.

Unlike the “Great” British Railway, installation and configuration of an Oracle Developer Day Appliance is somewhat simpler, not to mention more reliable.

What I’m going to cover here is the installation of a Developer Day Appliance in Virtual Box as well as some settings you might want to tweak to gain access to the Appliance Database from the Host OS.

Environment, Pre-Requisites and terminology

As always, when we’re talking about virtualization, there are going to be mentions of :

  • The Host – the machine and Operating System on which the virtualization software (in this case VirtualBox) is running
  • The Guest – the system that’s running inside the Virtual Machine

These instructions should work, irrespective of the Host OS we’re using.
In my case, I’m using Mint 17 as my Host with VirtualBox 4.3.20 installed.

If you need installation instructions for VirtualBox, you can find them here for Debian based systems or here for Windows.

Alternatively, you can check the fairly comprehensive documentation on the VirtualBox website.

As for the Developer Day Image itself, I’m going to be using the Database Application Development VM.
The system requirements for this are :

  • 2GB RAM for the Guest OS
  • 22GB of disk space (15 GB for the Image plus 5.6 GB for the download file)

One other point to note – the latest incarnation of the Database Application Development VM has port-forwarding for port 1521 enabled by default.
This is the port for the TNS listener on the database in the Image. If you already have an Oracle instance running on your host, you’ll probably want to ensure that either, it’s not using 1521 for TNS connections, or that you disable the port forwarding on the Developer Day Image once you’ve installed it.
We’ll come back to this port-forwarding malarkey in a bit.

Getting the software

Open your browser and head over to Oracle’s Pre-Build Developer VMs page.

This is part of Oracle’s Technet site and you will need a Technet account to download the software, if you don’t already have one. However, registration is free and relatively painless.

Have a look through the available images and select the one that contains the stuff your interested in.
In my case, I want to have a play around with the 12c Database, hence the choice of the Database Application Development VM.

NOTE : this page itself may be a bit out-of-date in terms of what each VM contains, so click the Downloads and Instructions link next to the VM you may be interested in. This will give you a more accurate inventory of what software the VM contains.

Anyway, once you’ve selected your VM, simply hit the Download icon, enter your Technet credentials and – depending on your connection speed – find something else to do for a bit.
Eventually, you will have a file in your Downloads directory called :

Oracle_DB_Developer_VM_new.ova

In case you were wondering, .ova files are Open VirtualBox Format. Creating a VM with this type of file is slightly different to setting one up for a .iso …

Setting up the VM

Start the Oracle VM VirtualBox Manager on your machine and from the menu, select File/Import Appliance

appliance1

Appliance to Import is the file you’ve just downloaded :

appliance2

Appliance Settings are all already completed so just click Import :

…and agree to the License

appliance4

You’ll then see a progress bar….

appliance5

And finally, you’ll see the new VM listed in VirtualBox Manager.

Now, let’s power it up and see what happens….

Once you see the desktop, click the big armoured penguin icon…

vm_start

…and wait…

your patience will be rewarded with the introduction page to the hands-on sessions included in the image :

welcome_page

We can check that the database is up using the copy of SQLDeveloper available in the VM ( a connection for SYSTEM is already there).

Alternatively, we could just open a Terminal and login via SQL*Plus as SYSTEM ( all of the passwords are initially set to oracle).

Actually, that’s probably a point worth reiterating :

All of the accounts that you are likely to want to access on this VM have their password set to “oracle”

Either way connecting as SYSTEM will establish that the database is up. A couple of further queries will confirm the version of the Database and of APEX…

select banner
from v$version
where banner LIKE 'Oracle Database%'
union
select comp_name||' Version '||version as banner
from dba_registry
where comp_id = 'APEX'
/

BANNER
--------------------------------------------------------------------------------
Oracle Application Express Version 4.2.5.00.08
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

… as well as the identity and current status of the database itself :

select db.name, ins.host_name,
    ins.status, ins.logins,
    db.log_mode 
FROM v$database db, v$instance ins
/

NAME	  HOST_NAME							   STATUS	LOGINS	   LOG_MODE
--------- ---------------------------------------------------------------- ------------ ---------- ------------
CDB1	  localhost.localdomain 					   OPEN 	ALLOWED    NOARCHIVELOG
Some Housekeeping Time and Date

The first thing you’ll probably notice is that the time and date is defaulted to US Pacific Standard time (I think).
If you want to change this :

Go to the Applicationsmenu and select Utilities/Settings/Time and Date,
Click the Unlock button and then set the time and date that’s appropriate for your location.
Finally, click the Lock button.

Region and Language (as well as Keyboard)

If, like me, you have trouble remembering where the US keyboard layout puts the “@” symbol (not to mention the “|”) then you’ll probably want to re-configure the Keyboard settings to your locality, along with the Region settings :

Go to Applications/System Tools/Settings/Regions & Language.

To change the region settings, Hit the “+” button under Input Sources and select the appropriate region.
To change the Keyboard itself, first highlight the current Input Source – i.e. English (US) – then click add to add the source you require.
Once this is done, Highlight the English (US) entry again and hit the “-” button to remove it.

For Region Changes to take effect, the system will need to be restarted. The keyboard change, however, should take effect immediately.

Setting the APEX user not to Expire

Given that these images were initially created for Developer Day events, it is perhaps not surprising that the expiration of the APEX_PUBLIC_USER password after 6 months was not a primary consideration.
If you want to avoid this little inconvenience, then the simplest way is to change the DEFAULT profile, to which all database accounts in the image are assigned :

alter profile default limit password_life_time unlimited;

NOTE: In a production environment you’d probably want to take a bit more care here. However, the Developer Day Images are purely for research.

Accessing the Database running on the VM from the Host Accessing the Database via TNS

As mentioned previously, port-forwarding of 1521 (The default SQL*Net port) is already pre-configured.
Therefore, assuming that you have an Oracle Client installed, you can connect to the database on the Guest (whilst it’s running) from the Host as follows :

sqlplus system/oracle@0.0.0.0:1521/cdb1

Once you have a SQL prompt, you can check to see that you now have access to Oracle in all it’s 12c splendour…

select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

For convenience, you can always add the following entries to $ORACLE_HOME/network/admin/tnsnames.ora on your host :

CDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb1)
    )
  )
  
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
 
Accessing APEX

We can repeat the simple port-forwarding trick to make the APEX home page available on the Host as well.

In Virtual Box Manager, with the Developer Day VM highlighted, click Settings
Select Network
Click the Advanced arrow

network_advanced

Click the Port Forwarding button
Now Click the Add icon on the right-hand side of the window and enter the following values for each of the fields :

  • Name : http
  • Protocol : TCP
  • Host Port : 8080
  • Guest Port : 8080

Note : once again, you’ll probably want to change these values if you have a local instance of Oracle running on the Host.

Anyway, your rules should now look like this :

http

You can now hit the APEX main page from a browser in the host by entering :


http://localhost:8080/apex

apex_login

Note – the credentials for the ADMIN user for APEX are :

Workspace : INTERNAL
Username : ADMIN
Password : oracle

And there you have it, a quick and simple (not to mention, cheap) way to get Oracle 12c installed on your local machine. Now, if only they’d do the equivalent for getting from Milton Keynes to London.


Filed under: Oracle, VirtualBox Tagged: acessing apex, connecting to a guest database from the host, default passwords, Developer Day image, keyboard configuration, port forwarding, region settings, VirtualBox, VM

CREATE USER and ALTER USER – changing passwords and a New Year’s Resolution

Sun, 2015-01-11 08:39

Monday morning. The first day back at work in the New Year.
Still groggy, having been awoken from my slumber at the insistence of my Darth Vader Lego Alarm Clock, I stagger downstairs in search of coffee.
The clock was a Christmas present from Deb. Whilst clinking around the kitchen, I wonder whether it was intended as a subtle reminder of how she likes her coffee with only a little milk. Yes, she prefers it on the Dark Side.
Then I remember. I usually avoid New Year’s resolutions. I find them to be not unlike a cheap plastic toy at Christmas – there in the morning and then broken by the first afternoon.
This year however, is an exception.

In a recent post about APEX Authentication Schemes, I went to great lengths to ensure that a dynamic SQL statement to re-set a users password was safe from the possibility of injection.
Fortunately, Jeff Kemp took the time to point out a couple of issues with my approach.
As a result, this year, my resolution is to :
READ THE MANUAL.

What follows is the result of keeping this resolution ( so far, at least)…

Setting a Password with CREATE USER and ALTER USER

To start with, we’re going to forget about any PL/SQL context for this exercise and simply focus on the ALTER USER command when used to change a user’s password.

First of all, we need a user…

create user marvin identified by 42!
/

create user marvin identified by 42!
                                   *
ERROR at line 1:
ORA-00922: missing or invalid option


That’s a bit surprising. There’s no password verify function on the DEFAULT profile in the database :

select resource_name, limit
from dba_profiles
where resource_type = 'PASSWORD'
and resource_name = 'PASSWORD_VERIFY_FUNCTION'
and profile = 'DEFAULT'
/

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
PASSWORD_VERIFY_FUNCTION         NULL

…and even if there were – ORA-00922 ?

At this point, much head-scratching can be saved if we follow Jeff’s recommended link to the relevant documentation.

From this we can see that the password has the same rules applied to it as a database object. So…

  • it must start with a letter
  • …and can’t contain spaces…
  • …or characters other than alphanumeric, $, # or _

…unless…

create user marvin identified by "42!"
/

User created.

Yes, double-quotes around the password will allow other characters to be used, just as with database objects.
So, does the user need to provide the double-quotes when they connect ?

grant create session to marvin
/

…now….

connect marvin/42!@mydb

…works fine. No need to include the double-quotes when connecting.
If the password contains spaces however, then the double-quotes are required :

alter user marvin identified by "Feeling very depressed"
/

conn marvin/Feeling very depressed@XE
/
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]

conn marvin/"Feeling very depressed"@XE
/
Connected.
A Sensible Change Password Procedure

We now come back to the question of how to provide the functionality for a user to change their password by means of a PL/SQL procedure. For the sake of simplicity, we’re going to assume that there is no password verify function in place and therefore the old password does not need to be provided :

create or replace procedure change_pwd_pr
(
    i_new_pwd in varchar2
)
    authid current_user
is
begin
    execute immediate 'alter user '||user||' identified by '||CHR(34)||i_new_pwd||CHR(34);
end;
/

Now, we are dropping user input directly into a dynamic SQL statement, something that rings alarm bells in terms of the potential for injection.
There would appear to be two techniques that are common to injection of such code.

Here, however, we’re only concatenating a password. This is not a database object per se. Once it is set, it will be referenced only as the password hash by Oracle’s internal authentication process and will therefore not be useful as a blind injection string.
Additionally, by enclosing the password in double-quotes, it is simply treated as a string within the dynamic SQL statement itself.

Let’s put this to the test.

First, we need to make the procedure available to MARVIN :

grant execute on change_pwd_pr to marvin;

Now let’s run some tests. Connect as MARVIN and …

set serveroutput on size unlimited
declare
    
    function test_pwd_fn( i_test_no in pls_integer, i_pwd in varchar2)
        return varchar2
    is
    begin
        mike.change_pwd_pr( i_new_pwd => i_pwd);
        return ('Test '||i_test_no||' - Password Changed.');
    exception
        when others then
            return('Test '||i_test_no||'- Password Change Failed : '||substr(sqlerrm,1,100));
    end test_pwd_fn;
begin
    dbms_output.put_line(test_pwd_fn( 1, '42!'));
    dbms_output.put_line(test_pwd_fn( 2, 'silly'||'; grant dba to marvin'));
    dbms_output.put_line(test_pwd_fn( 3, q'[I want you to know that I'm feeling very depressed]'));
    dbms_output.put_line(test_pwd_fn( 4, 'How awful!'));
end;
/    

Run this as Marvin and we get :

SQL> @tests.sql
Test 1 - Password Changed.
Test 2 - Password Changed.
Test 3- Password Change Failed : ORA-00972: identifier is too long
Test 4 - Password Changed.

PL/SQL procedure successfully completed.

SQL> 

If we now check, we can confirm that our attempt at injection in test2 was not successful :

select granted_role 
from user_role_privs
/

no rows selected

SQL> 
Conclusion

Thanks to Jeff, I will henceforth be moderating my paranoia when dealing with programatic password resets.
Oh yes, and I’ll try to remember to read the manual.


Filed under: Oracle, PL/SQL, SQL Tagged: alter user, alter user identified by, create user, create user identified by, dba_profiles, escape new password with double quotes, identified by, ORA-00922, ORA-00972, oracle password rules, password verify function

Installing VirtualBox on Mint with a CentOS Guest

Sun, 2014-12-21 12:48

Christmas is almost upon us. Black Friday has been followed by Small Business Saturday and Cyber Monday.
The rest of the month obviously started on Skint Tuesday.
Fortunately for all us geeks, Santa Claus is real. He’s currently posing as Richard Stallman.
I mean, look at the facts. He’s got the beard, he likes to give stuff away for free, and he most definitely has a “naughty” list.

Thanks to Santa Stallman and others like him, I can amuse myself in the Holidays without putting any more strain on my Credit Card.

My main machine is currently running Mint 17 with the Cinnamon desktop. Whilst I’m very happy with this arrangement, I would like to play with other Operating Systems, but without all the hassle of installing/uninstalling etc.
Now, I do have Virtualbox on a Windows partition, but I would rather indulge my OS promiscuity from the comfort of Linux… sorry Santa – GNU/Linux.

So what I’m going to cover here is :

  • Installing VirtualBox on a Debian-based distro
  • Installing CentOS as a Guest Operating System
  • Installing VirtualBox Guest Additions Drivers on CentOS

I’ve tried to stick to the command-line for the installation steps for VirtaulBox so they should be generic to any Debian based host.

Terminology

Throughout this post I’ll be referring to the Host OS and the Guest OS, as well as Guest Additions. These terms can be defined as :

  • Host OS – the Operating System of the physical machine that Virtualbox is running on ( Mint in my case)
  • Guest OS – the Operating System of the virtual machine that is running in VirtualBox (CentOS here)
  • Guest Additions – drivers that are installed on the Guest OS to enable file sharing, viewport resizing etc
Options for getting VirtualBox

Before I get into the installation steps it’s probably worth explaining why I chose the method I did for getting VirtualBox in the first place.
You can get VirtualBox from a repository, instructions for which are on the VirtualBox site itself. However, the version currently available ( 4.3.12 at the time of writing) does not play nicely with Red Hat based guests when it comes to Guest Additions. This issue is fixed in the latest version of Virtualbox (4.3.20) which can be downloaded directly from the site. Therefore, this is the approach I ended up taking.

Right, now that’s out of the way…

Installing VirtualBox Step 1 – Prepare the Host

Before we download VirtualBox, we need to ensure that the dkms package is installed and up to date. So, fire up good old terminal and type :

sudo apt-get install dkms

Running this, I got :

Reading package lists... Done
Building dependency tree       
Reading state information... Done
dkms is already the newest version.
0 to upgrade, 0 to newly install, 0 to remove and 37 not to upgrade.

One further step is to make sure that your system is up-to-date. For Debian based distros, this should do the job :

sudo apt-get update
Step 2 – Get the software

Now, head over to the VirtualBox Downloads Page and select the appropriate file.

NOTE – you will have the choice of downloading either the i386 or the AMD64 versions.
The difference is simply that i386 is 32-bit and AMD64 is 64-bit.

In my case, I’m running a 64-bit version of Mint (which is based on Ubuntu), so I selected :

Ubuntu 13.04( “Raring Ringtail”)/ 13.10(“Saucy Salamander”)/14.04(“Trusty Tahr”)/14.10(“Utopic Unicorn”) – the AMD64 version.

NOTE – if you’re not sure whether you’re running on 32 or 64-bit, simply type the following in a terminal session :

uname -i

If this comment returns x86_64 then you’re running a 64-bit version of your OS. If it returns i686, then you’re running a 32-bit version.

A short time later, you’ll find that Santa has descended the chimney that is your browser and in the Downloads folder that is your living room you have present. Run…

ls -lh $HOME/Downloads/virtualbox*

… and you’ll find the shiny new :

-rw-r--r-- 1 mike mike 63M Dec  5 16:22 /home/mike/Downloads/virtualbox-4.3_4.3.20-96996~Ubuntu~raring_amd64.deb
Step 3 – Installation

To virtually unwrap this virtual present….

cd $HOME/Downloads
sudo dpkg -i virtualbox-4.3_4.3.20-96996~Ubuntu~raring_amd64.deb

On running this the output should be similar to :

(Reading database ... 148385 files and directories currently installed.)
Preparing to unpack virtualbox-4.3_4.3.20-96996~Ubuntu~raring_amd64.deb ...
Stopping VirtualBox kernel modules ...done.
Unpacking virtualbox-4.3 (4.3.20-96996~Ubuntu~raring) over (4.3.12-93733~Ubuntu~raring) ...
Setting up virtualbox-4.3 (4.3.20-96996~Ubuntu~raring) ...
Installing new version of config file /etc/init.d/vboxdrv ...
addgroup: The group `vboxusers' already exists as a system group. Exiting.
Stopping VirtualBox kernel modules ...done.
Uninstalling old VirtualBox DKMS kernel modules ...done.
Trying to register the VirtualBox kernel modules using DKMS ...done.
Starting VirtualBox kernel modules ...done.
Processing triggers for ureadahead (0.100.0-16) ...
Processing triggers for hicolor-icon-theme (0.13-1) ...
Processing triggers for shared-mime-info (1.2-0ubuntu3) ...
Processing triggers for gnome-menus (3.10.1-0ubuntu2) ...
Processing triggers for desktop-file-utils (0.22-1ubuntu1) ...
Processing triggers for mime-support (3.54ubuntu1) ...

Note As this was not my first attempt at installing VirtualBox, there are some feedback lines here that you probably won’t get.

Anyway, once completed, you should have a new VirtualBox icon somewhere in your menu.
In my case (Cinnamon desktop on Mint 17, remember), it’s appeared in the Administration Menu :

vbox_menu

As part of the installation, a group called vboxusers has now been created.
You’ll want to add yourself to this group so that you can access the shared folders, which is something I’ll come onto in a bit. For now though…

sudo usermod -a -G vboxusers username

… where username is your user.

Now, finally, we’ve set it up and can start playing. Click on the menu icon. Alternatively, if you can’t find the icon, or if you just prefer the terminal, the following command should have the same effect :

VirtualBox

Either way, you should now see this :

vbox_welcome

One present unwrapped, assembled and ready to play with…and you don’t even need to worry about cleaning up the discarded wrapping paper.

Installing the CentOS Guest

I fancy having a play with a Red Hat-based distro for a change. CentOS fits the bill perfectly.
Additionally, I happen to have an iso lying around on a cover disk.
If you’re not so lucky, you can get the latest version of CentOS (currently 7) from the website here.

I’ve created a directory called isos and put the CentOS iso there :

ls -lh CentOS*
-rw------- 1 mike mike 687M Jul  9 22:53 CentOS-7.0-1406-x86_64-livecd.iso

Once again, I’ve downloaded the 64-bit version, as can be seen from the x86-64 in the filename.

Now for the installation.

Open VirtualBox and click New :

In the Name and operating system window enter :

Name : CentOS7
Type : Linux
Version Red Hat(64 bit)

vb_new

In the Memory Size Window :

Settings here depend on the resources available to the host machine and what you want to use the VM for.
In my case, my host machine has 8GB RAM.
Also, I want to install Oracle XE on this VM.
Given that, I’m going to allocate 2GB to this image :

vb_new3

In the Hard Drive Window :

I’ve got plenty of space available so I’ll just accept the default to Create a virtual hard drive of 8GB now.

Hard Drive File Type :

Accept the default ( VDI (VirtualBox Disk Image))

and hit Next…

Storage on physical hard drive :

I’ll leave this as the default – Dynamically allocated
Click Next…

File location and size :

I’ve left the size at the default…

vbnew_4

I now have a new VirtualBox image :
The vdi file created to act as the VM’s hard drive is in my home directory under VirtualBox VMs/CentOS7

summary

Now to point it at the iso file we want to use.

Hit Start and ….

fisrt_start

choose_iso

You should now see the chosen .iso file identified as the startup disk :

iso

Now hit start….
live_cd_desktop

Don’t worry too much about the small viewport for now. Guest Additions should resolve that issue once we get it installed.
You probably do need to be aware of the fact that you can transfer the mouse pointer between the Guest and Host by holding down the right CTRL key on your keyboard and left-clicking the mouse.
This may well take a bit of getting used to at first.

Anyway, once you’re guest knows where your mouse is, the first thing is to actually install CentOS into the VDI. At the moment, remember, we’re just running a Live Image.

So, click the Install to Hard Drive icon on the CentOS desktop and follow the prompts as normal.

At the end of the installation, make sure that you’ve ejected your virtual CD from the drive.
To do this :

  1. Get the Host to recapture the mouse (Right CTRL + left-click)
  2. Go to the VirtualBox Menu on the VDI and select Devices/CD/DVD Devices/Remove disk from virtual drive

eject_cd

Now re-start CentOS.

Once it comes back, we’re ready to round things off by…

Installing Guest Additions

It’s worth noting that when CentOS starts, Networking is disconnected by default. To enable, simply Click the Network icon on the toolbar at the top of the screen and switch it on :

enable

We need to make sure that the packages are up to date on CentOS in the same way as we did for the Host at the start of all this so…

sudo yum update

Depending on how recent the iso file you used is, this could take a while !

We also need to install further packages for Guest Additions to work…

sudo yum install gcc
sudo yum install kerenel-devel-2.10.0-123.9.3.el.x86_64

Note It’s also recommended that dkms is installed on “Fedora” (i.e. Red Hat) based Guests. However when I ran …

sudo yum install dkms

I got an error saying “No package dkms available”.
So, I’ve decided to press on regardless…

In the VirtualBox Devices Menu, select Insert Guest Additions CD Image

You should then see a CD icon on your desktop :

guest_additions

The CD should autorun on load.

You’ll see a Virtual Box Guest Additions Installation Terminal Window come up that looks something like this :

Verifying archive integrity... All good.
Uncompressing VirtualBox 4.3.20 Guest Additions for Linux............
VirtualBox Guest Additions installer
Removing installed version 4.3.12 of VirtualBox Guest Additions...
Copying additional installer modules ...
Installing additional modules ...
Removing existing VirtualBox non-DKMS kernel modules       [  OK  ]
Building the VirtualBox Guest Additions kernel modules
Building the main Guest Additions module                   [  OK  ]
Building the shared folder support module                  [  OK  ]
Building the OpenGL support module                         [  OK  ]
Doing non-kernel setup of the Guest Additions              [  OK  ]
Starting the VirtualBox Guest Additions                    [  OK  ]
Installing the Window System drivers
Installing X.Org Server 1.15 modules                       [  OK  ]
Setting up the Window System to use the Guest Additions    [  OK  ]
You may need to restart the hal service and the Window System (or just restart
the guest system) to enable the Guest Additions.

Installing graphics libraries and desktop services componen[  OK  ]

Eject the CD and re-start the Guest.

Now, you should see CentOS in it’s full-screen glory.

Tweaks after installing Guest Additions

First off, let’s make things run a bit more smoothly on the Guest :

On the Host OS in VirtualBox Manager, highlight the CentOS7 image and click on Settings.
Go to Display.

Here, we can increase the amount of Video Memory from the default 12MB to 64MB.
We can also check Enable 3D Acceleration :

vbox_video

Next, in the General Section, click on the Advanced Tab and set the following :

Shared Clipboard : Bidirectional
Drag’n’Drop : Bidirectional

vbox_clipboard

You should now be able to cut-and-paste from Guest to host and vice-versa.

Shared Folders

At some point you’re likely to want to either put files onto or get files from your Guest OS.

To do this :

On the Host

I’ve created a folder to share on my Host system :

mkdir $HOME/Desktop/vbox_shares/centos

Now, in VirtualBox Manager, back in the Settings for CentOS, open the Shared Folders section.

Click the Add icon

add_share

Select the folder and make it Auto-mount

add_share2

On the Guest

In earlier versions of VirtualBox, getting the shared folders to mount was, well, a bit of messing about.
Happily, things are now quite a bit easier.

As we’ve set the shared folder to Auto-mount, it’s mounted on the Guest on

/media/sf_sharename

…where sharename is the name of the share we assigned to it on the Host. So, the shared folder I created exists as :

/media/sf_centos

In order to gain full access to this folder, we simply need to add our user to the vboxsf group that was created when Guest Additions was installed :

sudo usermod -a -G vboxsf username

…where username is your user on the Guest OS.

Note – you’ll need to logout and login again for this change to take effect, but once you do, you should have access to the shared folder.

Right, that should keep me out of trouble (and debt) for a while, as well as offering a distraction from all the things I know I shouldn’t eat…but always do.
That reminds me, where did I leave my nutcracker ?


Filed under: Linux, VirtualBox Tagged: centos 7 guest, copy and paste from clipboard, guest additions, how to tell if your linux os is 32-bit or 64-bit, mint 17 host, shared folders, uname -i, VirtualBox

Implementing a Database Authentication Scheme in APEX

Sun, 2014-12-14 12:26

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…

db_auth1

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 :

db_auth2

…and then hit the Create button…

db_auth3

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

db_auth4

In the next screen :

Name : HR_DB
Scheme Type : Database Accounts

db_auth5

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

db_auth6

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…

run_app2

… I can connect using my database credentials.

db_auth_ok1

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 :

run_app_system

…also let’s you connect :

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

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...

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

…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 !

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 :

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

change_pwd1

In the Page Name …

Name : Change My Password
HTML Region1 : change password

change_pwd2

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

change_pwd3

…and hit Finish.

Now Edit the Page.

Create a new field with an Item Type of Password :

old_pwd1

In the Display Position and Name screen,

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

old_pwd2

In the Item Attributes Screen :

Label : Current Password
Field Width : 50

old_pwd3

In the Settings Screen –

Value Required : Yes
Submit when Enter pressed : No

old_pwd4

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 !

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

cpwd_btn1

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

da1

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

da2

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

da3

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 :

cpwd_page1

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

Someone’s having a fat-finger moment

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

pwd_changed

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

br_cpw1

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.

br_cpw2

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…

brtest1

…I go straight to the Change Password Page…

brtest2

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

APEX 503 – Service Unavailable – And you don’t know the APEX_PUBLIC_USER Password

Sun, 2014-12-07 12:46

It’s probably Monday morning. The caffeine from your first cup of coffee has not quite worked it’s way into your system.
The cold sweat running down the back of your neck provides an unpleasant contrast to the warm blast of panicked users as they call up to inform you that the Application is down.
APEX, which has been behaving impeccibly all this time, has suddenly decided to respond to all requests with :

503 – Service Unavailable.

The database is up. The APEX Listener is up. But something else is up. APEX just doesn’t want to play.
Better still, the person who set up the APEX in the first place has long-departed the company. You have no idea how the Apex Listener was configured.

Out of sympathy with your current predicament, what follows is :

  • How to confirm that this problem is related to the APEX_PUBLIC_USER (the most likely cause)
  • A quick and fairly dirty way of getting things back up and running again
  • How to stop this happening again

Note: These steps were tested Oracle Developer Day VM with a 12c database running on Oracle Linux 6.5. In this environment, APEX is configured to run with the APEX Listener.

Confirming the APEX User name

First of all, we want to make sure that APEX is connecting to the database as APEX_PUBLIC_USER. To do this, we need to check the default.xml file.
Assuming you’re on a Linux box :

cd /u01/oracle/apexListener/apex
cat default.xml

If you don’t see an entry for db.username then APEX_PUBLIC_USER is the one that’s being used.
If there is an entry for db.username then that is the name of the database user you need to check in the following steps.
For now, I’ll assume that it’s set to the default.

Incidentally, there will also be an entry for db.password. This will almost certainly be encrypted so is unlikely to be of use to you here.

Confirming the status of the APEX_PUBLIC_USER

The most likely reason for your current troubles is that the APEX_PUBLIC_USER’s database password has expired.
To verify this – and get the information we’ll need to fix it, connect to the database and run the query :

select account_status, profile
from dba_users
where username = 'APEX_PUBLIC_USER'
/

If the account_status is EXPIRED, then the issue you are facing is that the APEX_PUBLIC_USER is expired and therefore APEX can’t connect to the database.

The other item of interest here is the PROFILE assigned to the user.
We need to check this to make sure that there is no PASSWORD_VERIFY_FUNCTION assigned to the profile. If there is then you need to supply the existing password in order to change it, which is a bit of a problem if you don’t know what it is.
Whilst we’re at it, we need to check whether there is any restriction in place as to the length of time or number of password changes that must take place before a password can be reused.
In my case, APEX_PUBLIC_USER has been assigned the DEFAULT profile.

select resource_name, limit
from dba_profiles
where profile = 'DEFAULT'
and resource_name in 
(
  'PASSWORD_REUSE_TIME', 'PASSWORD_REUSE_MAX', 
  'PASSWORD_VERIFY_FUNCTION'
)
/

When I ran this, I was lucky and got :

RESOURCE_NAME                  LIMIT              
------------------------------ --------------------
PASSWORD_REUSE_TIME            UNLIMITED            
PASSWORD_REUSE_MAX             UNLIMITED            
PASSWORD_VERIFY_FUNCTION       NULL     

So, there are no restrictions on password reuse for this profile. Neither is there any verify function.

If your APEX_PUBLIC_USER is attached to a profile that has these restrictions, then you’ll want to change this before re-setting the password.
As we’re going to have to assign this user to another profile anyway, we may as well get it out of the way now.

The New Profile for the APEX_PUBLIC_USER

Oracle’s advice for the APEX_PUBLIC_USER is to set the PASSWORD_LIFE_TIME to UNLIMITED.

Whilst it’s only these four parameters we need to set in the profile for us to get out of our current predicament, it’s worth also including a limitation on the maxiumum number of failed login attempts, if only to provide some limited protection against brute-forcing.
In fact, I’ve just decided to use the settings from the DEFAULT profile for the attributes that I don’t need to change :

create profile apex_public limit
    failed_login_attempts 10
    password_life_time unlimited
    password_reuse_time unlimited
    password_reuse_max unlimited
    password_lock_time 1 
    composite_limit unlimited
    sessions_per_user unlimited
    cpu_per_session unlimited
    cpu_per_call unlimited
    logical_reads_per_session unlimited
    logical_reads_per_call unlimited
    idle_time unlimited
    connect_time unlimited
    private_sga unlimited
/

As we don’t specify a PASSWORD_VERIFY_FUNCTION, none is assigned to the new profile.

NOTE – it’s best to check the settings in your own default profile as they may well differ from those listed here.

Next, we assign this profile to APEX_PUBLIC_USER…

alter user apex_public_user profile apex_public
/

The next step is to reset the APEX_PUBLIC_USER password, which is the only way to unexpire the user.

No password, no problem

Remember, in this scenario, we don’t know the current password for APEX_PUBLIC_USER. We don’t want to reset the password to just anything because we’re not sure how to set the password in the DAD used by the Apex Listener.

First of all, we need to get the password hash for the current password. To do this :

select password
from sys.user$
where name = 'APEX_PUBLIC_USER'
/

You’ll get back a hex string – let’s say something like ‘DF37145AF23CCA4′.

Next step is to re-set the APEX_PUBLIC_USER password :

alter user apex_public_user identified by sometemporarypassword
/

We now immediately set it back to it’s original value using IDENTIFIED BY VALUES :

alter user apex_public_user identified by values 'DF37145AF23CCA4' 
/

At this point, APEX should be back up and running.

Once the dust settles…

Whilst your APEX installation may now be back up and running, you now have a database user for which the password never changes.
Although the APEX_PUBLIC_USER has only limited system and table privilges, it also has access to any database objects that are available to PUBLIC.
Whilst this is in-line with Oracle’s currently documented recommendations, you may consider that this is a situation that you want to address from a security perspective.
If there is a sensible way of changing the APEX_PUBLIC_USER password without breaking anything, then you may consider it preferable to simply setup some kind of reminder mechanism so that you know when the password is due to expire and can change it ahead of time.
You would then be able to set the password to expire as normal.
If you’re wondering why I’m being a bit vague here, it’s simply because I don’t currently know of a sensible way of doing this.
If you do, it would be really helpful if you could let me know :)


Filed under: APEX, Oracle, SQL Tagged: APEX 503 Unavailable, create profile, dba_profiles, dba_users.account_status, failed_login_attempts, identified by values, password_reuse_max, password_reuse_time, password_verify_function

Kilobytes, Kibibytes and DBMS_XPLAN undocumented functions

Mon, 2014-12-01 12:46

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…

Function Signatures

If we happened to be strolling through the Data Dictionary and issued the following query…

select text
from dba_source
where owner = 'SYS'
and type = 'PACKAGE'
and name = 'DBMS_XPLAN'
order by line
/

we might be surprised at what we find….

***snip***
  ----------------------------------------------------------------------------
  -- ---------------------------------------------------------------------- --
  --                                                                        --
  -- The folloing section of this package contains functions and procedures --
  -- which are for INTERNAL use ONLY. PLEASE DO NO DOCUMENT THEM.           --
  --                                                                        --
  -- ---------------------------------------------------------------------- --
  ----------------------------------------------------------------------------
  -- private procedure, used internally

*** snip ***

  FUNCTION format_size(num number)
  RETURN varchar2;

  FUNCTION format_number(num number)
  RETURN varchar2;

  FUNCTION format_size2(num number)
  RETURN varchar2;

  FUNCTION format_number2(num number)
  RETURN varchar2;

  --
  -- formats a number representing time in seconds using the format HH:MM:SS.
  -- This function is internal to this package
  --
  function format_time_s(num number)
  return varchar2;

***snip***
Formatting a time in seconds

Let’s start with DBMS_XPLAN.FORMAT_TIME_S because we pretty much know what it does from the header comments.
To save myself a bit of typing, I’m just going to use the following SQL to see how the function copes with various values :

with actual_time as
(
    select &1 as my_secs
    from dual
)
select my_secs,
    dbms_xplan.format_time_s(my_secs) as formatted_time
from actual_time
/

Plug in a variety of numbers ( representing a time in seconds) and …

SQL> @format_time.sql 60
old   3:     select &1 as my_secs
new   3:     select 60 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
               60.00 00:01:00

SQL> @format_time.sql 3600
old   3:     select &1 as my_secs
new   3:     select 3600 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
             3600.00 01:00:00

SQL> @format_time.sql 86400
old   3:     select &1 as my_secs
new   3:     select 86400 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
            86400.00 24:00:00

SQL> @format_time.sql 129784
old   3:     select &1 as my_secs
new   3:     select 129784 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
           129784.00 36:03:04

SQL> 

I wonder how it treats fractions of a second ….

SQL> @format_time.sql  5.4
old   3:     select &1 as my_secs
new   3:     select 5.4 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
                5.40 00:00:05

SQL> @format_time.sql  5.5
old   3:     select &1 as my_secs
new   3:     select 5.5 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
                5.50 00:00:06

SQL> 

So, the function appears to round to the nearest second. Not great if you’re trying to list the times of the Olympic Finalists of the 100 metres, but OK for longer durations where maybe rounding to the nearest second is appropriate.
One minor quirk to be aware of :

SQL> @format_time.sql 119.5
old   3:     select &1 as my_secs
new   3:     select 119.5 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
              119.50 00:01:60

SQL> 

SQL> @format_time.sql 3599.5
old   3:     select &1 as my_secs
new   3:     select 3599.5 as my_secs

             MY_SECS FORMATTED_TIME
-------------------- --------------------------------------------------
             3599.50 00:59:60

SQL> 


If 59.5 seconds is rounded up, the function returns a value containing 60 seconds, rather than displaying the value as a minute.

Formatting Numbers

Next on our list of functions to explore are FORMAT_NUMBER and FORMAT_NUMBER2. At first glance, it may appear that these functions are designed to represent sizes using the IEEE standard definitions…

with myval as
(
    select &1 as the_value
    from dual
)
select the_value, 
    dbms_xplan.format_number(the_value) as format_size, 
    dbms_xplan.format_number2(the_value) as format_size2
from myval
/

Run this with a variety of inputs and we get :

SQL> @format_number.sql 999
old   3:     select &1 as the_value
new   3:     select 999 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
       999 999                             999

SQL> @format_number.sql 1000
old   3:     select &1 as the_value
new   3:     select 1000 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
      1000 1000                              1K

SQL> @format_number.sql 1024
old   3:     select &1 as the_value
new   3:     select 1024 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
      1024 1024                              1K

SQL> @format_number.sql 1000000
old   3:     select &1 as the_value
new   3:     select 1000000 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
   1000000 1000K                             1M

SQL> 

SQL> @format_number.sql 1500
old   3:     select &1 as the_value
new   3:     select 1500 as the_value

 THE_VALUE FORMAT_NUMBER                  FORMAT_NUMBER2
---------- ------------------------------ ------------------------------
      1500 1500                              2K

SQL> 

The FORMAT_NUMBER2 function reports 1000 as 1K.
Furthermore, for numbers above 1000, it appears to round to the nearest 1000.
FORMAT_NUMBER on the other hand, doesn’t start rounding until you hit 1000000.

From this it seems reasonable to infer that these functions are designed to present large decimal numbers in an easily readable format rather than being an attempt to conform to the new-fangled definition of a Kilobyte ( or Megabyte…etc).

Using the following script, I’ve created the BIG_EMPLOYEES table and populated it with 100,000 or so rows…

create table big_employees as
    select * from hr.employees
/

begin
    for i in 1..1000 loop
        insert into big_employees
        select * from hr.employees;
    end loop;
    commit;
end;
/

If we now apply these functions to count the rows in the table, we get the following :

select count(*),
    dbms_xplan.format_number(count(*)) as format_number,
    dbms_xplan.format_number2(count(*)) as format_number2
from big_employees
/

  COUNT(*) FORMAT_NUMBER        FORMAT_NUMBER2
---------- -------------------- --------------------
    107107 107K                  107K

You can see from this, how these functions might be useful when you’re looking at the number of rows in a very large table ( perhaps several million).

Counting the Kilobytes properly

We now come to the other two functions we’ve identified – FORMAT_SIZE and FORMAT_SIZE2.

with myval as
(
    select &1 as the_value
    from dual
)
select the_value, 
    dbms_xplan.format_size(the_value) as format_size, 
    dbms_xplan.format_size2(the_value) as format_size2
from myval
/

Running this the results are :

SQL> @format_size.sql 999
old   3:     select &1 as the_value
new   3:     select 999 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
       999 999                   999

SQL> @format_size.sql 1000
old   3:     select &1 as the_value
new   3:     select 1000 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
      1000 1000                 1000

SQL> @format_size.sql 1024
old   3:     select &1 as the_value
new   3:     select 1024 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
      1024 1024                    1k

SQL> @format_size.sql 1000000
old   3:     select &1 as the_value
new   3:     select 1000000 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
   1000000 976K                  977k

SQL> @format_size.sql 1048576
old   3:     select &1 as the_value
new   3:     select 1048576 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
   1048576 1024K                   1m

SQL> @format_size.sql 2047.4
old   3:     select &1 as the_value
new   3:     select 2047.4 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
    2047.4 2047                    2k

SQL> @format_size.sql 2047.5
old   3:     select &1 as the_value
new   3:     select 2047.5 as the_value

 THE_VALUE FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
    2047.5 2047                    2k

SQL> 

Things to notice here include the fact that format_size appears to FLOOR the value (1000000 bytes = 976.56 K), wheras FORMAT_SIZE2 rounds it up.
Additionally, once you pass in a value of over 1024, FORMAT_SIZE2 returns values in Kilobytes.

So, if we want to know the size of the BIG_EMPLOYEES table we’ve just created :

select bytes, 
    dbms_xplan.format_size(bytes) as format_size,
    dbms_xplan.format_size2(bytes) as format_size2
from user_segments
where segment_name = 'BIG_EMPLOYEES'
/

     BYTES FORMAT_SIZE          FORMAT_SIZE2
---------- -------------------- --------------------
   9437184 9216K                   9m

If all you need is an approximate value, then FORMAT_SIZE2 could be considered a reasonable alternative to :

select bytes/1024/1024 as MB
from user_segments
where segment_name = 'BIG_EMPLOYEES'
/

As well as it’s primary purpose, DBMS_XPLAN does offer some fairly useful functions if you need a quick approximation of timings, or counts or even sizes.
Fortunately, it adheres to the traditional definition of a Kilobyte as 1024 bytes rather than “Litebytes”.


Filed under: Oracle, PL/SQL, SQL Tagged: dbms_xplan, format_number, format_number2, format_size, format_size2, format_time_s

APEX and Privileges Granted through Roles

Sat, 2014-11-15 15:33

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…

The Environment

For the purposes of this post, I’ll be using a simple APEX application that’s been created in it’s own workspace.
The application is called NEW_HR and uses the default APEX Authentication Scheme.
The parsing schema is defined as HR.
At this point the application consists of a login screen and a blank Home Page.
I’ve also created a Workspace Admin user called…well…let’s call it Kevin.
The database version is Oracle 11g Express Edition and the APEX version is 4.2.
This environment uses the embedded PL/SQL Gateway to manage database connections from APEX. This is the default setup on Oracle 11g XE.

Who am I ? No, really

Now, I know that there is no user called KEVIN in my database….

select count(*) 
from dba_users 
where username = 'KEVIN'
/
  COUNT(*)
----------
         0

SQL> 

…so I’d like to know who the database thinks I am when I login through my APEX app. I’d also like to check who the APEX itself thinks I am.

The first step then, is to add a couple of fields to the application Home Page…

First of all, I’ve add an HTML Region called whoami. Apart from the name I’ve just accepted the defaults.

Now to add a field to display the Application User – i.e. who APEX thinks I am.

This is a Display Only Item called P1_APEX_USER in the whoami region.
The source settings for this item are the defaults except for :

Source Used : Always, replacing any existing value in session state
Source value or expression : APP_USER

apex_user_source

Next up is to add a field to display the database user.

The field is defined in the same way as P1_APEX_USER, except for :

Source Type : SQL Query (return single value)

and the source itself which is the following query :

select user from dual

db_user_source

Now, if we connect as Kevin….

login

…we can start to resolve our identity crisis….

whoami

So, as expected, APEX knows that Kevin is the Application user. However, the database user is not HR, rather it’s something called ANONYMOUS.

NOTE – If you’re using the Embedded PL/SQL Gateway ( the default setup for Express Edition) then you’ll be connected as ANONYMOUS. If you have the APEX Listener setup then, unless you’ve changed the default, you’ll be connected as APEX_PUBLIC_USER.
For our current purposes we can treat these accounts as synonymous from a database standpoint.
I’ll continue to refer to ANONYMOUS from here on because (a) I’m running this on XE and (b) the name has slightly more comedic potential.

Let’s find out a bit more about this user whilst trying not to worry that our application has been visited by hacktivists.
Hmmm, maybe not so much comedic potential.

The ANONYMOUS User

Looking in the database, we can confirm that ANONYMOUS is indeed a database user :

select account_status, profile, authentication_type
from dba_users
where username = 'ANONYMOUS'
/

ACCOUNT_STATUS                   PROFILE                        AUTHENTI
-------------------------------- ------------------------------ --------
OPEN                             DEFAULT                        PASSWORD

Doesn’t seem to be anything out of the ordinary there.
Now let’s see what ANONYMOUS has granted to it. For good measure, we can see what objects it owns ( if any).
The query looks like this :

select 'SYSTEM PRIVILEGE' as priv_type,
    null as db_object,
    privilege
from dba_sys_privs
where grantee = 'ANONYMOUS'
union
select 'ROLE GRANTED' as priv_type,
    granted_role as db_object,
    null as privilege
from dba_role_privs
where grantee = 'ANONYMOUS'
union
select 'OBJECT PRIVILEGE' as priv_type,
    owner||'.'||table_name as db_object,
    privilege
from dba_tab_privs
where grantee = 'ANONYMOUS'
union
select 'OWNED OBJECT' as priv_type,
    object_name as db_object,
    null as privilege
from dba_objects
where owner = 'ANONYMOUS'
order by 1,2
/ 

When we run it we get variations on the theme of :

PRIV_TYPE            DB_OBJECT                                                    PRIVILEGE
-------------------- ------------------------------------------------------------ ------------------------------
OBJECT PRIVILEGE     APEX_040000.WWV_FLOW_EPG_INCLUDE_MODULES                     EXECUTE
OBJECT PRIVILEGE     APEX_040200.WWV_FLOW_EPG_INCLUDE_MODULES                     EXECUTE
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           ALTER
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           DELETE
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           FLASHBACK
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           INDEX
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           INSERT
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           ON COMMIT REFRESH
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           QUERY REWRITE
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           REFERENCES
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           SELECT
OBJECT PRIVILEGE     FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$                           UPDATE
SYSTEM PRIVILEGE                                                                  CREATE SESSION

Now, the Object Privileges listed here are probable the result of some of the sample APEX applications I’ve installed.
By default, the only thing granted to ANONYMOUS is the CREATE SESSION privilege.

More pertinent here though is that it has no permissions at all on any objects owned by HR. This begs the question as to how our APEX application will work. Remember, our parsing schema ‎( essentially the Application Owner) is HR. Therefore, it’s reasonable to assume that we’ll want to interact with the tables in that schema.

NOTE – at this point I should add that, of course, ANONYMOUS does have additional privileges – i.e. everything granted to PUBLIC in the database. Whilst th‎is is not strictly relevant to the matter at hand, it’s probably worth bearing in mind when you look at how you implement security around this user.

Anyway, let’s put it to the test…

The Regions Report

In our application we’re going to create a new page – a Report on the HR.REGIONS table so…

In the Application Builder, click on Create Page :

create_page

Select Report and click Next

Select Interactive Report and click Next

Accept the defaults for Page Region Attribute and click Next

In Tab Options choose Use an existing tab set and create a new tab within the existing set
New Tab Label is Regions :

tab_options

Click Next

For the SQL Query :

select region_id, region_name
from regions

Note – we’re not specifying the table owner in this query, even though ANONYMOUS does not have a synonym on the HR.REGIONS table ( let alone any privileges)

query

Click Next

…and click Create

create

When we now connect to the application as Kevin and click on the Regions tab….

regions

So, the report has worked without error, despite the lack of privileges and synonyms. So what’s happening ?

Session Privileges in APEX

To answer this, we’ll need to tweak our earlier privileges query. This time, we’ll use the USER_ version of the views.
We can then it to the Application Home Page in a new reports region to see what ANONYMOUS can actually do when connected via APEX.

First, the new query, using USER_ versions of the views and without the order by clause.

select 'SYSTEM PRIVILEGE' as priv_type,
    null as db_object,
    privilege
from user_sys_privs
union
select 'ROLE GRANTED' as priv_type,
    granted_role as db_object,
    null as privilege
from user_role_privs
union
select 'OBJECT PRIVILEGE' as priv_type,
    owner||'.'||table_name as db_object,
    privilege
from user_tab_privs
union
select 'OWNED OBJECT' as priv_type,
    object_name as db_object,
    null as privilege
from user_objects
where object_type != 'INDEX'
/

Spoiler Alert – the reason I’m not using the SESSION_PRIVS view here is because it will list privileges granted via roles. The distinction between these and directly granted privileges will shortly become apparent.

We now simply create a new interactive reports region called User Privileges on the Home Page, using the above query.
If we now filter on PRIV_TYPE = ‘OWNED OBJECT’, we can see that we’ve magically acquired ownership of all the HR objects…

owned_objects

If we filter on PRIV_TYPE = ‘SYSTEM PRIVILEGE’, we can see that we also seem to have inherited HR’s System Privileges…

sys_privs

So, we can infer from this that, although the database connection from APEX is as the ANONYMOUS user, the session will inherit all of the objects and privileges of the parsing schema.
A reasonable assumption, given the evidence, and a correct one…mostly.

Objects not owned by the parsing schema

I’ve created a simple function in my own schema :

create or replace function name_scandal_fn( i_basename varchar2)
    return varchar2
as
begin
    return i_basename||'gate';
end;
/

Next we’re going to create a role and then grant execute on this function to that role. Finally, we’re going to grant the role to hr :

create role hr_role
/

grant execute on name_scandal_fn to hr_role
/

grant hr_role to hr
/

First off, we’ll test this in SQL*Plus. Connect as HR and …

select mike.name_scandal_fn('Twitter') from dual
/

MIKE.NAME_SCANDAL_FN('TWITTER')
--------------------------------------------------------------------------------
Twittergate

SQL> 

So, we should have no problem invoking this function from our application then.

Let’s create a page with a Display Only field that is populated by a call to this function :

Blank Page :

fn_page1

Called Scandal

fn_page2

…With an HTML Region…

fn_page3

…on a new tab…

fn_page4

…and confirm…

fn_page5

Now, add the Item…

fn_item1

…called P3_SCANDAL_NAME…

fn_item2

Accept the defaults for the Item Attributes settings, and Settings…

… and change the Source settings to :

Source Used : Always, replacing any existing value in session state
Source Type : SQL Query (return single value)
Item Source Value – here we put in our call to the function :

select mike.name_scandal_fn('Twitter') from dual

fn_item3

Finally, hit the create button.

No problems so far. Now, let’s try running the page…

fn_err

Hmmm, not quite what we were expecting.

Looking at the error stack, a possible source of the problem emerges.
In the background, it looks as if APEX is calling a package called WWV_FLOW_FORMS, which in turn calls WWV_FLOW_DYNAMIC_EXEC.
Whilst the source for both of these packages is wrapped, there are some notes availble on the next package in the call stack, WWV_DBMS_SQL here.

Putting all together and looking at the package headers, it would seem reasonable to assume that, rather than running the SQL statement directly, APEX does this via a series of package calls which then run the statement as dynamic SQL.
The effect of calling a (presumably) Definer’s Rights package is that any privileges granted via roles are ignored.

In order to test this theory, we can revoke the role from HR and instead, grant execute on the function directly.
So, connected to SQL*Plus as the function owner ( in my case MIKE) :

revoke hr_role from hr
/

grant execute on name_scandal_fn to hr
/

Now a quick sanity check to make sure that HR can see the function.
Connect as HR and :

SQL> select mike.name_scandal_fn('Twitter') from dual
  2  /

MIKE.NAME_SCANDAL_FN('TWITTER')
--------------------------------------------------------------------------------
Twittergate

SQL> 

Now let’s see what APEX makes of this.
Re-run the page and we can see…

itworks

There you have it. APEX, like Kevin, is just a little bit different.


Filed under: APEX, Oracle, SQL Tagged: apex anonymous user, apex_public_user, granting privileges via roles, parsing schema