[WIP] Oracle Wallet

Connecting to an Oracle Database can be done by using an oracle wallet to prevent having the credentials in the config.php.

It currently requires two patches:

1. https://github.com/owncloud/core/pull/16266 - allow configuring oracle session mode, needed when using oracle wallet
2. doctrines getListTableColumnsSQL needs to be patched:

diff --git a/doctrine/dbal/lib/Doctrine/DBAL/Platforms/OraclePlatform.php b/doctrine/dbal/lib/Doctrine/DBAL/Platforms/OraclePlatform.php
index a58c747..3c9546d 100644
--- a/doctrine/dbal/lib/Doctrine/DBAL/Platforms/OraclePlatform.php
+++ b/doctrine/dbal/lib/Doctrine/DBAL/Platforms/OraclePlatform.php
@@ -634,12 +634,14 @@ LEFT JOIN user_cons_columns r_cols
         $colCommentsTableName = "user_col_comments";
         $ownerCondition = '';
 
+/* incompatible with oracle wallet because that changes the user name to be different than the database name
         if (null !== $database) {
             $database = $this->normalizeIdentifier($database);
             $tabColumnsTableName = "all_tab_columns";
             $colCommentsTableName = "all_col_comments";
             $ownerCondition = "AND c.owner = '" . $database->getName() . "'";
         }
+*/
 
         return "SELECT c.*, d.comments FROM $tabColumnsTableName c ".
                "INNER JOIN " . $colCommentsTableName . " d ON d.TABLE_NAME = c.TABLE_NAME AND d.COLUMN_NAME = c.COLUMN_NAME ".

To debug this you will need an full oracle instance. An XE edition is not enough because you cannot create a wallet with it. Try a docker container like https://github.com/jaspeen/oracle-11g. You still need an oracle account to download the installation files, eg linux.x64_11gR2_database_1of2.zip and linux.x64_11gR2_database_2of2.zip.

After installation you can test the connection with SQL Developer. It also allows you to manage users and inspect the DB a little nicer then the horrible, horrible SQLplus. If you really need to use sqlplus, wrapping it in rlwrap may ease your pain ... a little.

If your connection to oracle works (the above docker container uses SYS:oracle) you can prepare the DB to install owncloud. To do that you need to execute the following sql which will create a user autotest and give him the necessary rights to create the db:

create user autotest identified by owncloud;

alter user autotest default tablespace users 
  temporary tablespace temp
  quota unlimited on users;

grant create session, create table, create procedure
  , create sequence, create trigger, create view
  , create synonym, alter session
to autotest;

Before connecting to an oracle db you need to install the OCI8 PECL extension.

A dependency is the oracle instantclient. Get instantclient-basiclite-linux.x64-11.2.0.4.0.zip and instantclient-sdk-linux.x64-11.2.0.4.0.zip from the oracle download page and unzip them into eg. /opt/instantclient_11_2. You may need to create a symlink for the PECL install config script to find the necessary libs: cd /opt/instantclient_11_2; sudo ln -s libclntsh.so.11.1 libclntsh.so

Now you can use pecl install oci8-2.0.10 to install OCI8 for PHP5.2 - PHP 5.6. For PHP 7 just use pecl install oci8. This works nice on debian or ubuntu. If not go read The Underground PHP and Oracle Manual. Yes, that is the correct name for the official documentation...

Compilation should complete and you should now have an oci8.so.

Check that the oci8 module has been enabled:

$ cat /etc/php5/mods-available/oci8.ini
extension=oci8.so
$ ls -l /etc/php5/cli/conf.d/20-oci8.ini
lrwxrwxrwx 1 root root 29 Feb  8 13:23 /etc/php5/cli/conf.d/20-oci8.ini -> ../../mods-available/oci8.ini
$ ls -l /etc/php5/fpm/conf.d/20-oci8.ini
lrwxrwxrwx 1 root root 29 Feb  8 13:23 /etc/php5/fpm/conf.d/20-oci8.ini -> ../../mods-available/oci8.ini
# or
$ ls -l /etc/php5/apache2/conf.d/20-oci8.ini
lrwxrwxrwx 1 root root 29 Feb  8 13:23 /etc/php5/apache2/conf.d/20-oci8.ini -> ../../mods-available/oci8.ini
$ php -i | grep oci8
23:/etc/php5/cli/conf.d/20-oci8.ini,
633:oci8
635:OCI8 Support => enabled
636:OCI8 DTrace Support => disabled
637:OCI8 Version => 2.0.10
643:oci8.connection_class => no value => no value
644:oci8.default_prefetch => 100 => 100
645:oci8.events => Off => Off
646:oci8.max_persistent => -1 => -1
647:oci8.old_oci_close_semantics => Off => Off
648:oci8.persistent_timeout => -1 => -1
649:oci8.ping_interval => 60 => 60
650:oci8.privileged_connect => Off => Off
651:oci8.statement_cache_size => 20 => 20

Now comes the tricky part: the connection parameters.

Installing from the command line works similar to other databases:

$ sudo -u www-data ./occ maintenance:install --database="oci" --database-name="orcl" --database-host="192.168.99.100" --database-user="autotest" --database-pass=owncloud --database-table-prefix=oc_ --admin-user="admin" --admin-pass=admin --data-dir="/data"
ownCloud is not installed - only a limited number of commands are available
ownCloud was successfully installed

What this does - deep down in the guts of doctrine - is, it creates an EasyConnectstring like this:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.100)(PORT=1521))(CONNECT_DATA=(SID=orcl)))

Easy connect string ... well, on to some more oracle specific fun. The tnsnames.ora because we will need it for oracle wallet. Remember that is what we are trying to debug in the first place.