APEXtras

SQL*Plus would not run on one of our Windows Oracle/APEX servers, although it was running fine on all the other apparently identical servers. ORACLE_HOME and ORACLE_SID were correctly set.

We were getting one or other of these errors:
ORA-12560: TNS:protocol adapter error
ORA-12557: TNS:protocol adapter not loadable

The problem was the Windows “path” environment variable. The \bin directory associated with each of the two Oracle homes was correctly in the path, but in the wrong order. The path associated with the Oracle/Apache HTTP server preceeded the path associated with the Oracle database.

Ie we had: path=C:\oracle\product\10.2.0\http_1\bin;C:\oracle\product\10.2.0\db_1\bin
when we should have had: path=C:\oracle\product\10.2.0\db_1\bin;C:\oracle\product\10.2.0\http_1\bin
(ignoring all the other non-Oracle elements of the path).

Presumably SQL*Plus looks for the configuration files LISTENER.ORA, SQLNET.ORA and TNSNAMES.ORA in the first Oracle home it finds.

With the path correctly set, SQL*Plus would start correctly without environment variables ORACLE_HOME or ORACLE_SID explicitly set - probably because these were also set in the registry keys

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1\ORACLE_SID
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1\ORACLE_HOME

Software Versions:
Oracle 10.2
SQL*Plus 10.1.0.3.0
Windows Server 2003

To integrate the low-cost YubiKey device we use a custom APEX authentication scheme in which we manage user names and passwords in a table under our control.

To enable this you need to enter “return name_of_your_custom_authentication_function” in the setup screen for the APEX authentication scheme in the “Login Processing/Authentication Function” box. The APEX popup help for this field gives an excellent guide to the requirements for that function. In our demonstration application we called the function APEXTRAS_YUBICO.auth.

The YubiKey outputs a unique string every time it is activated. This is the one-time password (OTP). Its first 12 characters constitiute the YubiKey ID, the unique identifier for each YubiKey. In other words, the first 12 characters of a YubiKey’s output are always the same, and are always different from those of any other YubiKey.

It is quite simple to incorporate the YubiKey into an APEX Authentication Scheme.

1. Add a column to the user table to hold the YubiKey ID of the YubiKey issued to each user.

2. Then add a third field to the login screen to take the YubiKey output as well as the login name and password.

3. Perform this pseudocode to log in:

Verify the YubiKey OTP
IF the YubiKey OTP passes verification THEN
    Retrieve the user name corresponding to the YubiKey ID
    IF the retrieved user name matches the one entered THEN
        Make the call to APEX_CUSTOM_AUTH.LOGIN to check the hash
        of the entered password against the stored hash of the user's password
    END IF
END IF

If this succeeds, then the logged-in user is

a) in possession of a valid YubiKey that is assigned to their account

and

b) knows the password assigned to that account

To verify the YubiKey OTP you need to submit it (via an HTTP GET) to a YubiKey authentication service. You can either run this somewhere on your own network, or you can use Yubico’s public authentication service.

If you use the Yubico service you will also need two pieces of information about your Yubico account: your API key and your Yubico-issued company user ID: both are available from the Yubico management site. Within our demonstration client the API Key, User ID and the URI of the authentication service need to be entered into the APEXTRAS_YUBICO package as package constants.

The call to the authentication service looks something like this:

http://api.yubico.com/wsapi/verify?id=NNNN&otp=XXX…XXX&h=ZZZZZZZ

id is your company user ID
otp is the one time password you want to verify
h is a hashed MAC (using hashed SHA-1 keyed on your API key) of the query string

The authentication service will respond with a status such as OK, BAD_OTP, REPLAYED_OTP, BAD_SIGNATURE etc

Demonstration of YubiKey two-factor authentication integrated with APEX login.

Authentication Package
APEX After Submit process

Sometimes we develop applications that really require more security than is provided by the standard combination of a username and password. We like two-factor/strong authentication which typically consists of something you know (a password) and something you have (a token). In the past we’ve used RSA’s SecureID but we’ve recently been evaluating the Yubikey from Yubico.

The Yubikey is a slim device that plugs into the USB port on any computer. To the computer it appears to be a USB keyboard which means it works across all operating systems and doesn’t require any drivers to be installed. 

When you touch the button on the top of a Yubikey it generates a 32 character one-time password (OTP) which appears at the current cursor position: remember the host computer sees the Yubikey as a keyboard. You can validate this OTP by passing it in a call to a restful web service provided by Yubico or you can pass it to your own authentication servers. Yubico provides Java and PHP versions of the authentication server software under an Open Source license.

We like the concept of the Yubikey because, compared to other two-factor solutions, it is very cost effective.  Each Yubikey costs about $25 (if you were to buy hundreds that would drop to under $10) and both public authentication service and the authentication server software are free.

We often develop systems that consist of two APEX applications on top of the same database — a public application and an administration application that provides access to the back-end of the system and to table maintenance tasks.  The administration application is typically used by just a handful of users and would really benefit from two-factor authentication.  The Yubikey would allow us to incorporate strong authentication into the administration application at a common-sense price.

Roger has successfully integrated the Yubikey into APEX.

Demonstration of YubiKey two-factor authentication integrated with APEX login.

Our first test upgrade from APEX 3.1 to 3.2 was 100% error-free, but our applications showed up all sorts of strange errors. Then I remembered that we were using Patrick Wolf’s wrapped versions of the v, nv, and dv functions - see here for more details. We needed to replace “FLOWS_030100.V” with “APEX_030200.V” throughout - then everything worked again.