External authentication with Oracle & accented characters in username

External authentication with Oracle & accented characters in username

by Aidan Whitehall -
Number of replies: 1

We've been happily using Oracle as an external database authentication plug-in with our Moodle 2.2 installation (running on Ubuntu 2.6.32-39-server), using a local MySQL (5.1.63) Moodle database.

The Oracle database is set to AL32UTF8, and the data within is correctly stored using UTF-8. I've noticed (but am not sure if it matters) that within the Moodle MySQL database, character_set_server is set to latin1 and collation_database is set to latin1_swedish_ci. All tables within the MySQL "moodle" database, and any text columns within those tables, all have a collation value of utf8_unicode_ci. The external authentication plugin is set to use oci8.

  • With usernames that don't contain accented characters, authentication via Oracle works fine.
  • When users attempt to authenticate with a username that contains anything outside abcdef...z, authentication fails.

In the external database authentication plugin settings, External db encoding is set to utf-8. The password is a SHA-1 hash, which is presumably why accented characters in passwords haven't caused an issue.

Having discovered the issue, I added the following lines to /etc/profile.d/oracle.sh (an executable file):

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/oracle/instantclient_11_2
export NLS_LANG=ENGLISH_UNITED\ KINGDOM.AL32UTF8
export ORACLE_HOME=/opt/oracle
export PATH=$PATH:/opt/oracle/instantclient_11_2

and rebooted. Doing a SET at the command line confirms that the NLS_LANG parameter is being set correctly, but this hasn't resolved the issue.

I'm not sufficiently knowledgeable about Moodle to know how to execute ad-hoc queries against Oracle, so can't see exactly what results are being returned by the database.

Can anyone please give me any pointers on where we might be going wrong? Thank you!

Average of ratings: -
In reply to Aidan Whitehall

Re: External authentication with Oracle & accented characters in username

by Aidan Whitehall -

We’ve got it working now! It required a combination of a few things. Bear in mind I'm neither a PHP developer nor someone who is particularly au fait with Linux, so treat the advice below with caution. And some of this is Oracle-specific, which might not be of use to you…

 

To use non-ASCII characters with a Moodle username, enable the following setting in Moodle:

Administration > Security > Site policies > check Allow extended characters in usernames

 

Enable UTF-8 in PHP

Edit /etc/php5/apache2/php.ini so that the default character set is UTF-8; alter:

;default_charset = "iso-8859-1"

to

default_charset = "utf-8"

 

For Oracle, once the Instant Client is installed, add the following environment variables:

Create the file /etc/profile.d/oracle.sh. Include the contents:

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/oracle/instantclient_11_2

NLS_LANG=ENGLISH_UNITED\ KINGDOM.AL32UTF8

ORACLE_HOME=/opt/oracle/instantclient_11_2

PATH=$PATH:/opt/oracle/instantclient_11_2

 

Make the file executable with:

sudo chmod a+x oracle.sh

 

Export the environment variables so they are available to Apache/PHP. Append to the file /etc/apache2/envvars:

export NLS_LANG=ENGLISH_UNITED\ KINGDOM.AL32UTF8

export ORACLE_HOME=/opt/oracle/instantclient_11_2

Note: simply using export NLS_LANG appears to be insufficient, as the environment variables that exist on the command line aren't available to PHP by just exporting them. They must be both exported and initialised (to the correct value).

 

Hope this helps someone out there.

Average of ratings: -