Skip to content

[WIP] Oracle Wallet

Jump to bottom
Jörn Friedrich Dreyer edited this page Feb 15, 2016 · 12 revisions

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.

Ultimately this code creates the connection:

$this->dbh = $persistent
      ? @oci_pconnect($username, $password, $db, $charset, $sessionMode)
      : @oci_connect($username, $password, $db, $charset, $sessionMode);

With our current code we have the following variables:

$charset = "AL32UTF8"
$db = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.99.100)(PORT=1521))(CONNECT_DATA=(SID=orcl)))"
$password = "owncloud"
$persistent = false
$sessionMode = 0
$username = "autotest"

Looking at the AbstractOracleDriver again we see that the other codepath is taken if the database host is empty. Let's see how the connection works with a tnsnames.ora.

Basically, the tnsnames.ora containes named easy connection strings. The driver searches in various places. Ultimately, /etc/tnsnames.ora should always work. The linebreaks may look weird. A line starting with anything but whitespace starts a new connection string:

# cat /opt/tnsnames.ora 
ORCL =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.100)(PORT = 1521))
          (CONNECT_DATA =
                      (SID = ORCL)
                          )
            )

If you ignore the whitespace you get the same easy connection string that doctrine generated on the fly. To use it we just set the dbhost to '' in our config.php, eg:

  'dbname' => 'orcl',
  'dbhost' => '',
  'dbuser' => 'autotest',
  'dbpassword' => 'owncloud',

This leads to the following variables for the connection:

$charset = "AL32UTF8"
$db = "orcl"
$password = "owncloud"
$persistent = false
$sessionMode = 0
$username = "autotest"

The OCI8 driver will look op the easy connection string named 'orcl' and use it to establish the connection. We can demonstrate that by changing it to something else, eg. VIA_TNS. The config.php now contains:

  'dbname' => 'VIA_TNS',
  'dbhost' => '',
  'dbuser' => 'autotest',
  'dbpassword' => 'owncloud',

and our /etc/tnsnames.ora:

# cat /etc/tnsnames.ora 
VIA_TNS =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.99.100)(PORT = 1521))
          (CONNECT_DATA =
                      (SID = ORCL)
                          )
            )

Creating the wallet

After logging into our oracle server / docker we can follow https://docs.oracle.com/middleware/1213/wls/JDBCA/oraclewallet.htm#JDBCA600

# go to tho bin dir of yor oracle install, eg:
$ pwd
/opt/oracle/app/product/11.2.0/dbhome_1/bin
# create a wallet, will ask for a new password (MUST be alphanumeric and at least 8 char long, eg password1)
$ ./mkstore -wrl /wallet -create
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter password:

Enter password again:

# add credentials for a connect string. We will use VIA_TNS from above
$ ./mkstore -wrl /wallet -createCredential VIA_TNS autotest owncloud
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

Create credential oracle.security.client.connect_string1

What deas this wallet look like? TODO ls

Now we need to copy these files to our onwcloud server.

Getting them out of the docker container works like this:

# on the host machine
$ .\docker.exe ps
CONTAINER ID        IMAGE                 COMMAND                  CREATED             STATUS              PORTS                              NAMES
94423a3819ad        oracle11g-installed   "/assets/entrypoint.s"   10 days ago         Up 3 hours          8080/tcp, 0.0.0.0:1521->1521/tcp   oracle11g
# I just copied the folder to my home dir
$ docker cp 94423a3819ad:/wallet C:\Users\jfd\

To get them into the oc server I used the shared VirtualBox foldet. You might need to take different steps.

Once you have the wallet on the server you need to add a few environment variables, otherwise the wallet connection may fail with ORA-01017: invalid username/password; logon denied. In my case I had to make sure, not only ORACLE_HOME and TNS_ADMIN were set but also LD_LIBRARY_PATH:

grep env /etc/php5/fpm/pool.d/www.conf | grep opt
env[TNS_ADMIN] = /opt/wallet
env[ORACLE_HOME] = /opt/instantclient_11_2
env[LD_LIBRARY_PATH] = /opt/instantclient_11_2

Wait, what is that TNS_ADMIN thing there? It basicall tells OCI where to find configuraton files, such as tnsnames.ora and the sqlnet.ora, which we need to change to use oracle wallet. Furthermore, I will use that folder as the new wallet folder. It now looks like this:

$ ls -l /opt/wallet/
total 24
-rwxrwxr-- 1 www-data www-data 5077 Feb 12 15:14 cwallet.sso*
-rwxrwxr-- 1 www-data www-data 5000 Feb 12 15:14 ewallet.p12*
-rwxrwxr-- 1 www-data www-data  162 Feb 12 15:19 sqlnet.ora*
-rwxrwxr-- 1 www-data www-data  261 Feb 12 15:22 tnsnames.ora*

Currently, I couldn't care less about the permissions. But we should narrow them down later.

So our /opt/wallet now contains the wallet files (cwallet.sso and ewallet.p12) as well as the sqlnet.ora and the tnsnames.ora. You should actually remove the /etc/tnsnames.ora. /etc is the last location the instant client checks and we don't want sideeffects that stem from an old tnsnames.ora lying around.

While the tnsnames.ora only changed its location we also have the new sqlnet.ora:

$ cat /opt/wallet/sqlnet.ora 
SQLNET.WALLET_OVERRIDE=TRUE
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY = /opt/wallet)))

The first line tells oci to replace host based authentication with oracle wallet based authenticaton. The second line tells it where to find the wallet directory.

Host based authentication? Yes. Going back to actual connection establishing to use oraclo wallet in php you need to use "/" as the username and an empty password (and host). The database name will be used as the connect string. Config.php looks like this:

  'dbname' => 'VIA_TNS',
  'dbhost' => '',
  'dbuser' => '/',
  'dbpassword' => '',

Leading to the following variables when connecting to doctrine:

$charset = "AL32UTF8"
$db = "VIA_TNS"
$password = ""
$persistent = false
$sessionMode = -2147483648
$username = "/"

If you are here but don't get a connection debug this, set a breakpoint in the connection class and compare the $sessionMode. The above value reflects OCI_CRED_EXT, which is what the first patch mentioned above is about.

The root problem

While using an oracle wallet to connect to an oracle database works during normal operations (after jumping through the above hoops), trying to do a migration fails with the following error:

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE "oc_appconfig" ADD ("appid" VARCHAR2(32) DEFAULT '' NOT NULL, "configkey" VARCHAR2(64) DEFAULT '' NOT NULL, "configvalue" CLOB DEFAULT NULL NULL)': ORA-01430: column being added already exists in table
Clone this wiki locally