This post started as a shameless copy of Jeff Smiths Resetting Your Oracle User Password with SQL Developer - but it ended in a trilogy about OSX, Oracle and SQLDeveloper.
In my first two posts I made myself comfortable with Oracle instant client on my MacBook. Now I want to use it for something more serious, like changing passwords.

Even with all the modifications from previous post SQLDeveloper still does not show the Reset Password... option. The reason: it just don't know about the library correctly.

First the PATH as shown in previous post is not set in an application started via launcher - It does not contain the ORACLE_HOME at all. But even as I forced that with a temporary hack it did not help; so I just can say: with OSX it's more complicated to let SQLDeveloper use the proper libraries.

As the PATH is not that important on OSX, the real issue is DYLD_LIBRARY_PATH. Regardless if it's set in /etc/profiles or ~/.profiles - they never reach an application started via launcher, probably for similar reasons like PATH shown above.

But there is hope: ORACLE_HOME as set in /etc/launchd.conf is shown in the environment of SQLDeveloper. At the end, that's all we need, now just to check where to put this little flame of hope to let it grow to a bonfire.


The script to check is
/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/ide/bin/launcher.sh. There is a function called CheckLibraryPath() which is supposed to check for a proper LD_LIBRARY_PATH (or it's equivalences) and if not set create a proper setting. Oracle just forgot to check about OSX! I write forgot on purpose, as HP-UX is checked with
if [ `uname -s` = 'HP-UX' ] and SHLIB_PATH is set instead of LD_LIBRARY_PATH.

So my solution is simple: I just enhance this function with this little patch:

524a525,541
>  elif [ `uname -s` = 'Darwin' ]
>  then
>   echo "OSX!"
>   if [ "X$DYLD_LIBRARY_PATH" = "X" ]
>  then
>   DYLD_LIBRARY_PATH=$ORACLE_HOME
>   #  only instant client exists on OSX
>  else
>   echo $DYLD_LIBRARY_PATH | egrep -e "(^|\:)$ORACLE_HOME($|\:)" > /dev/null
>   if [ $? != 0 ]
>   then
>    DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:$ORACLE_HOME
>   fi
> 
>  fi
>  export DYLD_LIBRARY_PATH
>  #echo "$DYLD_LIBRARY_PATH"

With this little patch now I can change the password of my users with SQLDeveloper on OSX!

In my previous post I showed how to create an universal binary for Oracle instant client so I don't need to care about 32/64 bit anymore.

Now I try to show how to set some environment variables so I only need to enter sqlplus and can start my work without preparing anything first.

I know I could do this in my local .profile, but I was looking for a more centralized way to provide them, maybe on a shared desktop where basic applications are installed just once.

This is all about OSX Mountain Lion - I'm working on 10.8.2 right now.

The variables I want to set/enhance are

• PATH
  That is quite simple: I just added (as root) a new line in /etc/paths:
  /Users/berx/instantclient_11_2
  
  
• ORACLE_HOME
  That was a little bit more tricky as I had to create (as root) the file first. In /etc/launchd.conf
  there is one line now:
  setenv ORACLE_HOME /Users/berx/instantclient_11_2
  
  
• DYLD_LIBRARY_PATH
  If you are curious what this is used for, it's the equivalent to Linux LD_LIBRARY_PATH. This is needed for all applications which are linked dynamically. Even sqlplus is linked that way.
  Unfortunately it can not be set in /etc/launchd.conf. To be more precise, it CAN be set, but it will not show up in the terminal.
  Instead it will create this line at the top of each terminal window direct after it opens:
  dyld: DYLD_ environment variables being ignored because main executable (/usr/bin/login) is setuid or setgid
  That's due to a security setting in Mountain Lion.
  I decided to use the profile for this work. In my case ~/.profile, but /etc/profile also works:
  # ORACLE_HOME is set in /etc/launchd.conf !!
export DYLD_LIBRARY_PATH=$ORACLE_HOME:$DYLD_LIBRARY_PATH

  

With these 3 little steps now I can start sqlplus directly from my terminal without caring about the environment anymore.

There is just one little drawback: all commands with setuid/setgid bit, like ps or sudo now throw a warning like this:
dyld: DYLD_ environment variables being ignored because main executable (/bin/ps) is setuid or setgid
But for me this is acceptable.

The only workaround I could imagine is any kind of wrapper for sqlplus.

Recently Oracle announced a new Oracle Instant Client for OSX. As always there is a 32-bit and a 64-bit version available.
I was curious if they can be merged together to an universal binary. The short answer: yes, they can.

It all starts with downloading the instant client zips for 32 and 64 binary. I loaded basic and sqlplus and unzipped them into 2 directories instantclient_11_2_32 and instantclient_11_2_64. I also created a target directory instantclient_11_2.

Then the simple script

for i in `ls instantclient_11_2_32`
do 
lipo -create ~/instantclient_11_2_{32,64}/$i -output ~/instantclient_11_2/$i || cp ~/instantclient_11_2_64/$i ~/instantclient_11_2/$i
done

did the work with this output:

lipo: can't figure out the architecture type of: /Users/berx/instantclient_11_2_32/BASIC_README
lipo: can't figure out the architecture type of: /Users/berx/instantclient_11_2_32/SQLPLUS_README
lipo: can't figure out the architecture type of: /Users/berx/instantclient_11_2_32/glogin.sql
lipo: can't figure out the architecture type of: /Users/berx/instantclient_11_2_32/ojdbc5.jar
lipo: can't figure out the architecture type of: /Users/berx/instantclient_11_2_32/ojdbc6.jar
lipo: can't figure out the architecture type of: /Users/berx/instantclient_11_2_32/xstreams.jar

here what a simple file * shows:

BASIC_README:         ASCII text
SQLPLUS_README:       ASCII text
adrci:                Mach-O universal binary with 2 architectures
adrci (for architecture i386): Mach-O executable i386
adrci (for architecture x86_64): Mach-O 64-bit executable x86_64
genezi:               Mach-O universal binary with 2 architectures
genezi (for architecture i386): Mach-O executable i386
genezi (for architecture x86_64): Mach-O 64-bit executable x86_64
glogin.sql:           ASCII English text
libclntsh.dylib.11.1: Mach-O universal binary with 2 architectures
libclntsh.dylib.11.1 (for architecture i386): Mach-O dynamically linked shared library i386
libclntsh.dylib.11.1 (for architecture x86_64): Mach-O 64-bit dynamically linked shared library x86_64
libnnz11.dylib:       Mach-O universal binary with 2 architectures
libnnz11.dylib (for architecture i386): Mach-O dynamically linked shared library i386
libnnz11.dylib (for architecture x86_64): Mach-O 64-bit dynamically linked shared library x86_64
libocci.dylib.11.1:   Mach-O universal binary with 2 architectures
libocci.dylib.11.1 (for architecture i386): Mach-O dynamically linked shared library i386
libocci.dylib.11.1 (for architecture x86_64): Mach-O 64-bit dynamically linked shared library x86_64
libociei.dylib:       Mach-O universal binary with 2 architectures
libociei.dylib (for architecture i386): Mach-O bundle i386
libociei.dylib (for architecture x86_64): Mach-O 64-bit bundle x86_64
libocijdbc11.dylib:   Mach-O universal binary with 2 architectures
libocijdbc11.dylib (for architecture i386): Mach-O bundle i386
libocijdbc11.dylib (for architecture x86_64): Mach-O 64-bit bundle x86_64
libsqlplus.dylib:     Mach-O universal binary with 2 architectures
libsqlplus.dylib (for architecture i386): Mach-O dynamically linked shared library i386
libsqlplus.dylib (for architecture x86_64): Mach-O 64-bit dynamically linked shared library x86_64
libsqlplusic.dylib:   Mach-O universal binary with 2 architectures
libsqlplusic.dylib (for architecture i386): Mach-O bundle i386
libsqlplusic.dylib (for architecture x86_64): Mach-O 64-bit bundle x86_64
ojdbc5.jar:           Zip archive data, at least v1.0 to extract
ojdbc6.jar:           Zip archive data, at least v1.0 to extract
sqlplus:              Mach-O universal binary with 2 architectures
sqlplus (for architecture i386): Mach-O executable i386
sqlplus (for architecture x86_64): Mach-O 64-bit executable x86_64
uidrvci:              Mach-O universal binary with 2 architectures
uidrvci (for architecture i386): Mach-O executable i386
uidrvci (for architecture x86_64): Mach-O 64-bit executable x86_64
xstreams.jar:         Zip archive data, at least v1.0 to extract

Ok, now all the files are merged (or just copied) together.
Now let's check if 32 and 64 bit works.

I need this environment set:

export DYLD_LIBRARY_PATH=/Users/berx/instantclient_11_2
export PATH=$PATH:/Users/berx/instantclient_11_2

So let's thy the 32 bit version:

arch -arch i386 ./sqlplus 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 15 11:22:46 2013

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

Enter user-name: 

It looks good, just let's check if it's really running at 32 bit. Activity Monitor helps:

---

And the same thing for 64 bit:

arch -arch x86_64 sqlplus 

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 15 11:38:23 2013

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

Enter user-name: 

---

So this can be seen as a short example how we can use only one ORACLE_HOME for 32 and 64 bit binaries on OSX.
But as always there is the usual disclaimer: It's not supported by Oracle; never do it on a production system (who is running anything Oracle-Related on OSX Server at all?); it's not tested with all the different applications which use oracle client.

Update 1 (2012-02-17 21:50):

If you want the binary installation more like a real ORACLE_HOME check Ronald Roods blog!

Oracle Databases has a powerful set of grants and permissions. One of the easy philosophies behind it is just to hide anything a user is not allowed to see. Technically this leads to an error message
ORA-00942: table or view does not exist.
More precisely it should give a text like table or view does not exist or you are not allowed to access it.
For an ordinary user/schema separation there might be no big difference: If user A can not read table B.TAB it's of no value for user A whether the object does not exist or is just not accessible. But from a security point of view it might be of some interest if there exists a particular object within the database. Even it's not exploitable yet, maybe it's worth to try to reach it, or there is a combined vector of attack together with other objects.
To gain information about a system which should be kept hidden a well known method for physical cryptosystems is the side channel attack. At this attack extra information about the system is gained by measuring external channels like timings, power consumption and so on.
I am showing a similar method to get many informations about objects within a database a user should not know in general.

The described attack combines 2 well known concepts of every Oracle database: row cache and session statistics.

The row rache (or data dictionary cache) ... is a collection of database tables and views containing reference information about the database, its structures, and its users. - It's important to know this cache is filled by SQL-statements against the data dictionary tables.

session statistics just increase a counter for every session every time an action is executed.

Now let's check if we can get some hidden information out of my test system.
I did a alter system flush shared_pool; to make it slightly easier for the testcase, but even with a pre-filled row cache I am sure these informations can get collected with only little more effort.

My measurement query is

select ms.sid, ms.statistic#, sn.name, ms.value 
from v$mystat ms, V$STATNAME sn 
where sn.name in ('recursive calls', 'execute count') and ms.statistic# = sn.statistic# order by 2;

with a result like

       SID STATISTIC# NAME                      VALUE
---------- ---------- -------------------- ----------
       221          9 recursive calls            3108
       221        588 execute count               947

Now let's see where we can go. Something easy for the beginning. A select from a table which does not exist:

SQL> select * from gfdsaqwert;
select * from gfdsaqwert
              *
ERROR at line 1:
ORA-00942: table or view does not exist

with the measurement

       SID STATISTIC# NAME                      VALUE
---------- ---------- -------------------- ----------
       221          9 recursive calls            3111
       221        588 execute count               950

this means, there where 3 recursive calls and 3 execute counts.

Now for something more interesting - let's check if a user exists:

SQL> select * from zzz.uztrfghj;
select * from zzz.uztrfghj
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

20 recursive calls
 3 execute count

SQL> select * from a.uztrfghj;
select * from a.uztrfghj
                *
ERROR at line 1:
ORA-00942: table or view does not exist

21 recursive calls
 3 execute count

In my test-DB no user ZZZ exists, but a user A exists. (but no table A.UZTRFGHJ) There is one more recursive call if the user exists. Not much, but a difference.

SQL> select * from a.m ;
select * from a.m
                *
ERROR at line 1:
ORA-00942: table or view does not exist

26 recursive calls
 7 execute count

Now there are even more recursive calls and execute count. The Table A.M exists in my system.

I'd say this shows the concept. The official answer from the database was ORA-00942: table or view does not exist all the time. But v$mystat gave us some more information we did not get officially.