A question about External Authentication With PHP OCI8 using a wallet store

dariyoosh

Hello,

SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> 

OS: Linux Fedora Core 17 (x86_64)
PHP Version: 5.4.14
Apache version (httpd): Apache/2.2.23 (Fedora) 

I have a question about OCI8 connection to oracle database via wallet which as I understand is a good practice because we don't hard code users password. Currently I'm reading pages 117 and 118 of the online book The Underground PHP and Oracle Manual in order to learn how to setup a wallet. I proceeded step by step as explained in these two pages (except that at the end I connect as a normal user not a privileged user) yet, apparently PHP doesn't detect the user.

Here is what I write in my script

$connection = 
                oci_connect 
                    (
                        "training", 
                        "", 
                        "php_ora_usr", 
                        "AL32UTF8"
                    );

training is the name of the user I use in my PHP scripts in order to connect to oracle database and the password is mypassword (is not real, just for the purpose of this thread ) The above code gives me the following error:

Warning: oci_connect(): ORA-01005: null password given; logon denied in /var/www/html/myscript.php on line 91

Fatal error: Connection step: ORA-01005: null password given; logon denied in /var/www/html/myscript.php on line 13

But if I provide also the password that is,

$connection = 
                oci_connect 
                    (
                        "training", 
                        "mypassword", 
                        "php_ora_usr", 
                        "AL32UTF8"
                    );

Obviously this works, but at the same time, it means that my wallet is not operational.

So I'm going to write down here, step by step, how I proceeded according to the instructions in the pages 117 and 118 of the above mentioned book and I would appreciate if you could kindly tell where I made mistake(s)

The following was run as my linux oracle user (therefore group dba)

Step 01:_
I created and provided the password for the wallet directory

mkstore -wrl /home/oracle/wallet_dir -create

Step 02:_
I created an entry for my oracle user which will be used in my PHP scripts to connect to oracle
user: training
password: mypassword

mkstore -wrl "/home/oracle/wallet_dir" -createCredential php_ora_usr training mypassword

I also checked that the entry was in fact created for my user in the wallet

$ mkstore -wrl "/home/oracle/wallet_dir" -listCredential
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:                
   
List credential (index: connect_string username)
1: php_ora_usr training
$ 

Step 03:_
I added the following to my $TNS_ADMIN/tnsnames.ora

.  .  .
php_ora_usr = 
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db02)
    )
  )
.  .  .

Step 04_
I added the following to my $TNS_ADMIN/sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /u01/app/oracle

WALLET_LOCATION =
    (SOURCE =
        (METHOD = FILE)
        (METHOD_DATA =
            (DIRECTORY = /home/oracle/wallet_dir)
        )
    )
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

Step 05_
The TNS_ADMIN environment variable is already defined properly in */etc/sysconfig/httpd* so I didn't need to do anything for this step


Step 06_
I gave to Apache (being as root) access to the wallet directory(actually it seems that the linux oracle (dba) user has also the required privileges in order to grant this access to apache)

# setfacl -m u:apache:rx /home/oracle/wallet_dir
# setfacl -m u:apache:r /home/oracle/wallet_dir/{cwallet.sso,ewallet.p12}

Step 07_
Restart Apache

# service httpd restart

Step 08_
Finally doing a connection test by using the connection string php_ora_usr whose entry was created above. And the test was not successful as indicated above

$connection = 
                oci_connect 
                    (
                        "training", 
                        "", 
                        "php_ora_usr", 
                        "AL32UTF8"
                    );

The error message:

Warning: oci_connect(): ORA-01005: null password given; logon denied in /var/www/html/myscript.php on line 91

Fatal error: Connection step: ORA-01005: null password given; logon denied in /var/www/html/myscript.php on line 13

I would appreciate if you could kindly make some clarification.

Thanks in advance,
Dariyoosh

Christopher Jones-Oracle

That gives a good clue to start with. With your platform/Apache/DB you might need different ACLs. Experiment with them and directory permissions. Check what effective uid your Apache runs as. Good luck.

Christopher Jones-Oracle

Your oci_connect() call needs to use OCI_CRED_EXT mode.

Note (as the UG Manual points out): "The OCI_CRED_EXT mode can only be used with a username of "/" and an empty password."

dariyoosh

Hello there,


Thank you for your help and your attention to my problem. I changed the code according to what you said

$connection = oci_connect 
                           (
                                 "/"             , 
                                 ""              , 
                                 "php_ora_usr"   , 
                                 "AL32UTF8"      ,
                                 OCI_CRED_EXT
                           );

But now the following new error message appears

Warning: oci_connect(): ORA-12578: TNS:wallet open failed in /var/www/html/myscript.php on line 92

Fatal error: Connection step: ORA-12578: TNS:wallet open failed in /var/www/html/myscript.php on line 13

Thanks in advance,

Regards,
Dariyoosh

Christopher Jones-Oracle

That gives a good clue to start with. With your platform/Apache/DB you might need different ACLs. Experiment with them and directory permissions. Check what effective uid your Apache runs as. Good luck.

dariyoosh

Hi,

With your platform/Apache/DB you might need different ACLs. Experiment with them and directory permissions

So I must understand that what is written in the manual is not necessarily platform independent (I mean by that : not the same instructions for different Linux distributions: Redhat, Fedora, Ubuntu, etc), is that right?
Anyway, I copied the wallet directory named wallet_dir which had been created initially in */home/oracle* to the */tmp* directory where apache (actually everybody) can access, just for a test to see whether it is a problem at file permission level.

As a result, I modified the *$TNS_ADMIN/sqlnet.ora* accordingly

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


ADR_BASE = /u01/app/oracle

WALLET_LOCATION =
    (SOURCE =
        (METHOD = FILE)
        (METHOD_DATA =
            (DIRECTORY = /tmp/wallet_dir)
        )
    )
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

Then I run again the following commands

# setfacl -m u:apache:rxw /tmp/wallet_dir
# setfacl -m u:apache:rxw /tmp/wallet_dir/{cwallet.sso,ewallet.p12}
# service httpd restart

But the same problem persisted, Once I even did a global chmod, that is

#chmod -R 777 /tmp/wallet_dir

But again the same problem.

However, at SQL*Plus level, it works ( it used to work even before doing the above mentioned operations* ), so I can run the following

$ sqlplus /@php_ora_usr

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 6 12:39:33 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SHOW USER
USER is "TRAINING"
SQL> 
SQL> 

I checked the online documentation to read more about the error message ORA-12578
>
ORA-12578: TNS:wallet open failed
Cause: An error was encountered while trying to open the specified wallet.
Action: For further details, turn on tracing and re-execute the operation
>

I modified the content of the *$TNS_ADMIN/sqlnet.ora* by adding the following line in order to enable tracing

trace_level_client=16

I restarted the oracle listener, but again the same problem and besides it didn't generate any sqlnet.trc file on the system. So I don't even have a log file indicating some error message allowing to understand in more details what is actually the problem.

Also you said:

Check what effective uid your Apache runs as.

I'm not sure to have understood this part of your advice. Here is the information that I have on my system

$ id apache
uid=48(apache) gid=48(apache) groups=48(apache),990(backuppc)
$ 

Thanks in advance,

Regards,
Dariyoosh

Edited by: dariyoosh on Jun 6, 2013 12:53 PM

dariyoosh

Ok, finally I solved the problem by reading the online documentation: Secure External Password Store which is actually quite similar to the instructions indicated in the online book The Underground PHP and Oracle Manual except that in the example given in the online documentation the suggested location for creating the wallet was $TNS_ADMIN, or maybe this was just an example.

So for those, who may have encountered the same problem, I'm going to write down here and I explain, what was my mistake that led me to the error.

In short: the problem was my lack of attention to the user privileges of the Linux directory including the wallet directory

Initially I had followed the instructions indicated in the book The Underground PHP and Oracle Manual step by step and therefore I run the following

mkstore -wrl /home/oracle/wallet_dir -create

And this was the big mistake, because I just followed the instructions in the book without paying attention to the fact that although the new wallet_dir was created successfully in */home/oracle* , however this was the set of permissions that I had on my system for */home/oracle*

# ls -l /home
.  .  .
drwxr-----+ 25 oracle    oinstall  4096 Jun  6 21:03 oracle

(I don't know what the + in drwxr-----+ means)

So only the oracle user could enter in that folder. As a result the following instructions had simply no effect

# setfacl -m u:apache:rx /home/oracle/wallet_dir
# setfacl -m u:apache:r  /home/oracle/wallet_dir/{cwallet.sso,ewallet.p12}

So even if you explicitly assign permission with setfacl command, this does not become effective for apache as the global directory doesn't grant r,x privileges for other group.

Consequently, I created the wallet in $TNS_ADMIN where I had the following privileges

$ ls -l $ORACLE_HOME/network
.  .  .
drwxr-xr-x. 4 oracle oinstall 4096 Jun  6 21:03 admin

So, I just run the following instructions

$ su - oracle
password:
$
$ cd $TNS_ADMIN
$
$ pwd
/u01/app/oracle/product/11.2.0/db_1/network/admin
$
$
$ mkstore -wrl ./wallet_dir -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:                
   
$
$ mkstore -wrl ./wallet_dir/ -createCredential php_ora_usr training mypassword
$
$ 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

$
$ mkstore -wrl ./wallet_dir/ -listCredential
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:                

List credential (index: connect_string username)
1: php_ora_usr training
$
$

As for granting privileges, the manual uses the setfacl command, but on my system, the apache, from oracle linux-user point of view belongs to the group others. and accesses my *$TNS_ADMIN* in lecture (r) and execute (x) mode. Consequently, instead of using the setfacl command I just simply run a chmod with the same privileges of its parent directory, that is *$TNS_ADMIN*

chmod -R 755 wallet_dir/

The sqlnet.ora file was also modified accordingly

$ cat $TNS_ADMIN/sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


ADR_BASE = /u01/app/oracle

WALLET_LOCATION =
    (SOURCE =
        (METHOD = FILE)
        (METHOD_DATA =
            (DIRECTORY = /u01/app/oracle/product/11.2.0/db_1/network/admin/wallet_dir)
        )
    )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0
$ 

As I indicated in my first comment, the following entry was also added to my *$TNS_ADMIN/tnsnames.ora*

.  .  .
php_ora_usr = 
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db02)
    )
  )

Where db02 is my *$ORACLE_SID* and php_ora_usr is the connection string specified above when we created our secure wallet.

Remark: On my computer, both oracle server and the client are on the same physical machine.

I restarted the oracle listener ( being as oracle linux user )

$ lsnrctl stop
$ lsnrctl start

So I checked to see whether in the command line mode by using SQL*Plus my secure wallet was accessible and operational by my connection string php_ora_usr . And the connection test was successful.

$ sqlplus /@php_ora_usr

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 6 21:52:48 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SHOW USER;
USER is "TRAINING"
SQL> 
SQL> 

And finally restart httpd as root

# service httpd restart

And everything worked pretty well and my php script could connect to oracle by using the secure wallet

$connection = 
                oci_connect 
                    (
                        "/"             , 
                        ""              , 
                        "php_ora_usr"   , 
                        "AL32UTF8"      ,
                        OCI_CRED_EXT
                    );

Thanks to cj for the help and I hope that this may also help others.

Regards,
Dariyoosh