Laurent Schneider

First you need to install OID. Check the Installation Guide, the Doc and download the Software. If you do not need the dbconsole, stop it (emctl stop dbconsole) and remove the oracle_home/hostname_sid directory

Once you have a running OID, test it with ldapsearch. For this workshop, I use two servers and two usernames. Having the client and the server sharing the same wallet is not a good idea. If you have an Oracle Database running as user oracle, prefer using a different user for OID installation. By installing OID, I specified the following staticports.ini (missing from CD, bug 5936042) :

Oracle HTTP Server port  = 44000
Oracle HTTP Server Listen port  = 44001
Oracle HTTP Server SSL port  = 44002
Oracle HTTP Server Listen (SSL) port  = 44003
Oracle HTTP Server Diagnostic port  = 44004
Java Object Cache port  = 44005
DCM Discovery port  = 44006
Oracle Notification Server Request port  = 44007
Oracle Notification Server Local port  = 44008
Oracle Notification Server Remote port  = 44009
Application Server Control port  = 44010
Application Server Control RMI port  = 44011
Oracle Management Agent port  = 44012
Log Loader port  = 44013
ASG port  = 44014
Oracle Internet Directory port  = 44015
Oracle Internet Directory (SSL) port  = 44016
Oracle Certificate Authority SSL Server 
    Authentication port  = 44017
Oracle Certificate Authority SSL Mutual
    Authentication port  = 44018

Ok, let’s do the search from another server

lsc@dbserver01 $ ldapsearch -h oidserver01 -p 44015 -z 1
orclreplicaid=oidserver01_oid1014,cn=replication configuration
ldap_search: Sizelimit exceeded

Now let’s try with SSL. First, with no authentication (-U 1).

lsc@dbserver01 $ ldapsearch -h oidserver01 -p 44016 -z 1 -U 1
orclreplicaid=oidserver01_oid1014,cn=replication configuration
ldap_search: Sizelimit exceeded

Fine. Let’s create the wallets. You need a wallet for your client (lsc@dbserver01), a wallet for your server (ldapusr@oidserver01). Create a certification request for CN=lsc,cn=users,dc=yourdomain,dc=com, export your user certificate from your client wallet (lsc@dbserver01) and import it as a trusted certificate in your oid wallet (ldapusr@oidserver01). It is the same procedure as described in the user identified externally with SSL certificate post

Then, launch oidadmin (you can launch it from a pc client), and configure the OID for ssl.
User for OID is CN=ORCLADMIN
In Oracle Internet Directory Servers –> cn=orcladmin@oidserver01 –> Server Management –> Directory Server. Right click on Configuration Set1 and chose Create Like. In the Configuration Set2, chose the SSL Settings, specify SSL Client and Server Authentication, SSL only, file://etc/ORACLE/WALLETS/lsc, SSL Port 44019. Apply. Quit

Start a second instance with oidctl where oid1014 is your SID

ldapusr@oidserver01 $ oidctl connect=oid1014 server=oidldapd
    instance=2 configset=2 start

Check the status :

ldapusr@oidserver01 $ oidctl connect=oid1014 server=oidldapd
    status
oidctl:Checking Oracle Internet Directory Processes ...

    Process oidmon is alive as PID = 16191 

    Checking OIDLDAPD instance 1  ...
    Process oidldapd (dispatcher) is alive as PID = 16197
        port=44015 sslport=44016
            oidldapd (server)     is alive as PID = 16206

    Checking OIDLDAPD instance 2  ...
    Process oidldapd (dispatcher) is alive as PID = 16422
        sslport=44019
            oidldapd (server)     is alive as PID = 16426

    Checking ODISRV instance 1  ...
    Process odisrv   is alive as PID = 16203

so far not bad!

Let’s pray !

$ ldapbind -h novgaasdv01.eu.novartis.net -p 44019  -U 3
    -P mywalletpasswd -W file://etc/ORACLE/WALLETS/lsc  
    -D cn=orcladmin -w myorcladminpasswd
bind successful

What a satisfaction I have being failing on this for days. Mostly getting meaningless message like UnKnown Error Encountered. You cannot start anything with that error. It could be a wallet path error, a wallet password error, a non-authorized certificate, a certification authority problem, and many other errors. Really poor error messaging there. One may argue that meaningless error message on unsuccessful login does increase the security, but well, it is a nightmare to debug

Now I need to stop the non-secure part of it :

ldapusr@oidserver01 $ oidctl connect=oid1014 server=oidldapd
    instance=1 stop

Added 29.3.2007
If I want to use a SSL to authentify my user, I must create a user, for example with the Security Console http://oidserver01:44000/oiddas/ui/oideushome, which matches my certificate.

In case you do not know it yet, having a bitmap on columns like GENDER(male/female) is a very bad practice in OLTP, because each insert does lock the whole table

create table t(name varchar2(10), gender varchar2(10));
create bitmap index bi on t(gender);

+--------------------------------+   +--------------------------------+ 
| Session 1                      |   | Session 2                      |
+--------------------------------+   +--------------------------------+ 
| SQL> insert into t             |   |                                |
|  2   values('JO','MALE');      |   |                                |
|                                |   |                                |
| 1 row created.                 |   |                                |
|                                |   |                                |
|                                |   | SQL> insert into t             |
|                                |   |  2   values('JANE','FEMALE');  |
|                                |   |                                |
| SQL> commit;                   |   |                                |
|                                |   |                                |
| Commit complete.               |   |                                |
|                                |   |                                |
|                                |   |                                |
|                                |   | 1 row created.                 |
|                                |   |                                |
|                                |   |                                |
+--------------------------------+   +--------------------------------+ 

A pending transaction is blocking a portion of the index. So session 2 has to wait for transaction 1 to complete.

read comments on this post

I have been asked yesterday how to read and write blobs in the database.

With java :
read from an input stream

InputStream myBlobInputStream = 
  connection . 
    createStatement() . 
      executeQuery("select myBlob from t") . 
        getBlob(1) . 
          getBinaryStream();

write to an output stream

OutputStream myBlobStream = 
  connection .
    createStatement() .
      executeQuery("select myBlob from t for update") . 
        getBlob(1) . 
          getBinaryOutputStream();

where connection is your java.sql.connection.

You can find a working demo here http://www.oracle.com/…/LOBSample.zip

You could also use PL/SQL and the DBMS_LOB API. There is a complete book (306 pages!) in the doc about working with large objects : Application Developer’s Guide - Large Objects.

Sometimes, you can use plain SQL.

SQL> create table t(x BLOB);
Table created

SQL> insert into t values(utl_raw.cast_from_number(1));
1 row inserted

SQL> select utl_raw.cast_to_number(x) from t;
UTL_RAW.CAST_TO_NUMBER(X)
-------------------------
                        1

A smart move may be to use SQL Loader. You can specify one file per row

LOAD DATA INFILE '/tmp/x.txt' INTO TABLE "T"
(name filler char(255), x lobfile(name) terminated by EOF)

and your import file /tmp/x.txt will look like

x.gif
y.gif

but you could also load a 10000 long characters column from your input file in a CLOB column, just by specifying VARCHARC(4,10000) as a datatype

Maybe you have a firewall in your company, only your PC can access the production server, only you have the root password in prod, and your company spend a lot of money in security, but you dare using xhost +.

This is a huge security hole, because it gives anyone access to your X resources, not only your display, but also your mouse and your keyboard, so anyone can read/modify/corrupt what you are typing/clicking. It is a bad habit to use xhost+. Even using xhost +localhost give access to your keyboard to anyone on localhost…

So what else could you use ?

The simplest is probably ssh tunnelling.

lsc@chltlxlsc:~> ssh -X oracle@localhost
Last login: Fri Max  2 10:24:09 2007 from localhost
oracle@chltlxlsc:~> xterm -e true && echo success
success

Another way to do this is to use X cookies.

lsc@chltlxlsc1:~&gt; xauth extract ~/my-x-cookie $DISPLAY<
lsc@chltlxlsc1:~&gt; setfacl -m u:oracle:r ~/my-x-cookie
lsc@chltlxlsc1:~&gt; su - oracle -c "DISPLAY=$DISPLAY bash"
Password:
oracle@chltlxlsc1:~&gt; xterm -e true && echo success
Xlib: connection to ":0.0" refused by server
Xlib: No protocol specified

xterm Xt error: Can't open display: :0.0
oracle@chltlxlsc1:~&gt; xauth merge ~lsc/my-x-cookie
xauth:  creating new authority file ~oracle/.Xauthority
oracle@chltlxlsc1:~&gt; xterm -e true && echo success
success

No need to type all. Here is my alias

alias oracle='
  xauth extract $HOME/my-x-cookie $DISPLAY;
  setfacl -m u:oracle:r $HOME/my-x-cookie;
  su - oracle -c "export DISPLAY=$DISPLAY;
    xauth merge $HOME/my-x-cookie; 
    bash"'