Monday, November 22, 2010

Novell acquired - Unix status uncertain

Oh, dear! Wonder where that is going to end. Not another SCO-like event, I hope.

And, in the press: MicroSoft bought "intelectual properties" from Novell. Which properties that would be is unclear.

Update: Novell released this: Novell will continue to own Novell’s UNIX copyrights following completion of the merger as a subsidiary of Attachmate.

Friday, November 19, 2010

ORA-12641 "Authentication service failed to initialize" on multi homed machines

Update: the TMPDIR environment variable gets picked up; the file will be created in that directory. A technical note will be released, as this is not documented anywhere.
I have been struggeling to get Kerberos Authentication running on multi homes machines at my clients site. I just could not figure out why one package (HP-speak for virtual machines) works just fine, where the next errs off with a dreaded "ORA-12641: Authentication service failed to initialize".
Mind you - both using the same keytab and Kerberos configuration files, and similar sqlnet.ora files.

Go trace, young man, go trace!

Today I had the awakening moment to enable server tracing on the database side: added the following code to sqlnet.ora:
trace_level_server = 32
trace_file_server = svr_trc
trace_directory_client = $TNS_ADMIN/trace

Sure enough, comparing the package with errors against the one that validated me just fine using Kerberos, gave me some idea:

[19-NOV-2010 12:46:38:931] nauk5ra_rcinit: Validating existing replay cache.
[19-NOV-2010 12:46:38:931] snauk5g_open_file: entry
[19-NOV-2010 12:46:38:931] snauk5g_open_file: Setting write lock.
[19-NOV-2010 12:46:38:931] snauk5g_open_file: Opening /var/tmp/kerb5srv.RC.
[19-NOV-2010 12:46:38:931] snauk5k_lock_file: entry
[19-NOV-2010 12:46:38:931] snauk5k_lock_file: Setting exclusive lock.
[19-NOV-2010 12:46:38:931] snauk5k_lock_file: exit
[19-NOV-2010 12:46:38:931] snauk5g_open_file: exit
[19-NOV-2010 12:46:38:931] nauk5rz_validate: entry
[19-NOV-2010 12:46:38:931] nauk5rz_validate: exit
[19-NOV-2010 12:46:38:931] snauk5t_close_file: entry
[19-NOV-2010 12:46:38:931] snauk5k_lock_file: entry
[19-NOV-2010 12:46:38:931] snauk5k_lock_file: Resetting lock.
[19-NOV-2010 12:46:38:931] snauk5k_lock_file: exit
[19-NOV-2010 12:46:38:931] snauk5t_close_file: exit

Now, compare that to the faulty environment:

[19-NOV-2010 12:38:56:805] nauk5ra_rcinit: Validating existing replay cache.
[19-NOV-2010 12:38:56:805] snauk5g_open_file: entry
[19-NOV-2010 12:38:56:805] snauk5g_open_file: Setting write lock.
[19-NOV-2010 12:38:56:805] snauk5g_open_file: Opening /var/tmp/kerb5srv.RC.
[19-NOV-2010 12:38:56:805] snauk5g_open_file: open failed with 13: File I/O error
[19-NOV-2010 12:38:56:805] snauk5g_open_file: Returning 203: File not found
[19-NOV-2010 12:38:56:805] snauk5g_open_file: exit
[19-NOV-2010 12:38:56:805] nauk5ra_rcinit: Could not open cache.
[19-NOV-2010 12:38:56:805] nauk5ru_create: entry
[19-NOV-2010 12:38:56:805] nauk5ru_create: Creating replay cache.
[19-NOV-2010 12:38:56:806] snauk5g_open_file: entry

This will eventually end in

[19-NOV-2010 12:38:56:807] na_csrd: exit
[19-NOV-2010 12:38:56:807] nacomer: error 12641 received from authentication service


Now, I would not write this, if I did not need to remind myself of the following. There is a workaround until this is permanently fixed by Oracle, but it is not without risk.
The problem is on file permissions. Once the file is created, it gets 600, or read-write for owner only permissions. Change that to 666 (read-write to everybody), and you're done.
In fact, the file does not need to have any contents:

rm /var/tmp/kerb5srv.RC
touch /var/tmp/kerb5srv.RC
chmod 666 /var/tmp/kerb5srv.RC

Asof now, all packages on this machine can use Kerberos authentication.
Please note: kerb5srv is the name of the Service Principal chosen by my client overhere - your implementation may use a different name. Use that (or, trace to see what file is used)

By the way, the entry on Privacy was updated, due to the signing of this.

The Catch

Apart from the fact you may have reservations to open up a file to the world like that, the file itself resides in a temporary directory, /var/tmp.
If any system administrator decides to clean that up, the dreaded ORA-12641 will be all over the place.
Just rerun code above, and you're done.

Permanent Solution

There seems no sqlnet variable to cure this; sqlnet.KERBEROS5_CC_NAME brings nothing. Strictly speaking, it is not the Credential Cache, but the service principal cache, so that figures.
What I would like to see is one of:
  • introduce an environment variable that allows to govern location of this file
  • use a suffix, just like in the credentials cache.
Update: the TMPDIR environment variable does point one...

Sunday, November 14, 2010

Congratulations, Sebastian

Sebastian Vettel, youngest world champion Formula 1 ever, congratulations!

And perhaps Ferrari should rethink contracting Mr. F. Alonso - the unsportsmanlike behavior towards Petrov says it all... If you cannot pass Petrov in like 20-odd rounds, you're clearly not World Champion material. There's only one to blame, Fernando - that is you. Not Petrov!

Thursday, November 04, 2010

Happy Days

Some post are still in backlog, I've been here...

Support pays off!

Update on a previous entry: we have a patch, and it actually works.
So when you ever get in the situation where fixed user database link do not work due to kerberos aare environments, ask for patch 8482256.
Available (and tested!) for HP-UX 11.23 and up; should become available for MS Windows (Server 2003, 32 bit and up), as well as VMS.

Monday, September 27, 2010

mount Oracle

Just curious:

mountd 20048/tcp NFS mount protocol
mountd 20048/udp NFS mount protocol
# Nicolas Williams <> 09 August 2010

Sunday, September 26, 2010

ORA-12638: Status 82 "Not a bug"

One thing that completely spoils the previous entry on Kerberos authentication in the database, is the state of denial Oracle is in. It seems impossible to admit there's a bug in SQL*Net in the sense there's no fallback for username/password authentication when Kerberos fails.

ORA-12638 [Credential retrieval failed]

What's the case? Well, after you have set up Kerberos authentication, your database links seize working with ORA-12638: Credential retrieval failed, as soon as both databases are Kerberos aware.

Now, of course this is a situation you do not want. You want administrators and users to log in using their working environment credentials, but you rely on named accounts, and passwords, for database links.
Alas. Here comes Bug 6341668: CLIENT CAN NOT CONNECT TO DATABASE BY PASSWORD IF ANY KERBEROS PROBLEM OCCURS. Current status: 82 "Closed, not a bug". Al least someone else tried Kerberos authentication...
This is in contradiction with another Kerberos-related bug, # 2647883: Kerberos current user database links do not work (See Doc ID 2647883.8), where the actual workaround is ... use fixed user database links (!).
Catch-22, eh?

I hate it when I get confronted with this rubbish on Friday afternoons. It ruins my weekend!
Oh - might anyone wonder: this is cross platform,

One other reference on Oracle Support: TNS-12638 when database authentication tried on Kerberos setup (Doc ID 972896.1)


ASO is standard

There's a note stating the Advanced Security Option is now standard with the Enterprise Database Install. Makes you wonder: is it included in the standard license fee, then?


Took the time to update the environment I tested the scenario in to, which does have some Kerberos related bugs resolved. The result of all this is not only a ruined Sunday, it's also not resolving anything.
C:\>sqlplus /@db10
SQL*Plus: Release - Production on Sun Sep 26 18:32:25 2010
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> select sysdate from dual@fvb;
select sysdate from dual@fvb
ERROR at line 1:
ORA-12638: Credential retrieval failed

Only after removing the services from sqlnet.ora on the server side, it works:

[ora10@db1 admin]$ cat sqlnet.ora
# sqlnet.authentication_services=(beq, kerberos5)

Which makes the same query on the client work... instantaneous!

SQL> select sysdate from dual@fvb;


It's getting weirder

I stumbled upon this, trying to find a workable solution on maintainting two TNS_ADMIN locations on the server. A similar problem has been logged as Bug 5054469: "DATABASE AUTHENTICATED USERS REQUIRE KERBEROS CREDENTIAL CACHE TO CONNECT", which has been closed as duplicate of (internal) bug 9056628.
Explain: how can a bug# 5 million-and-something be closed as a duplicate of bug# almost-twice-as-high? Makes me wonder.
And the best is this: Doc ID 972896.1 (TNS-12638 when database authentication tried on Kerberos setup) states "This has been an issue in the past that the authentication services are not skipped to next when first fails".
Yeah right: has been... in the past...

Wednesday, September 22, 2010

Synchronize OID with Active Directory

There's quite a difference between (and earlier versions) and in the syncing business. You may now use multiple profiles quite easily and even have separate jobs for each profile. This would allow for a multi-AD-domain company to allow for different frequencies of synchronization: have one, well-know busy AD Domain synchronize very frequently, and other less frequent.
You may also enable or disable profiles by simply removing them from the running sync job(s); that process is called disassociation.


So, what do I need?
1 Active Directory Installation
1 Oracle Identity Management Installation

I go both, one running VMWare (MS AD - I had it shipped as VMWare image), one Oracle, installed under VirtualBox.

Start it up

As the Oracle installation is as basic as it can be, it needs starting up:
frank@frank-cs03:~$ ssh oracle@
oracle@'s password:
Last login: Fr Sep 10 16:43:24 2010
[oracle@oracleas ~]$ . oraenv
ORACLE_SID = [oracle] ? iasdb
[oracle@oracleas ~]$ lsnrctl start

LSNRCTL for Linux: Version - Production on 29-DEC-2010 09:46:40

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Starting /oracle/ias/10.1.4/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version - Production
System parameter file is /oracle/ias/10.1.4/network/admin/listener.ora
Log messages written to /oracle/ias/10.1.4/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracleas)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracleas)(PORT=1521)))
Version TNSLSNR for Linux: Version - Production
Start Date 29-DEC-2010 09:46:41
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
Listener Parameter File /oracle/ias/10.1.4/network/admin/listener.ora
Listener Log File /oracle/ias/10.1.4/network/log/listener.log
Listening Endpoints Summary...
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

I need a tcp based listener, because of the OID/ldap stack, that uses tcp. Otherwise, I would not have gone through all of the tcp stack, but use Direct Calls, or even better: bequeth, which does not need a listener at all. Anyway - open the database:

[oracle@oracleas ~]$ sqlplus / as sysdba

SQL*Plus: Release - Production on Wed Dec 29 09:47:33 2010

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 779000 bytes
Variable Size 229645576 bytes
Database Buffers 50331648 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, OLAP and Data Mining options

After all that, it is time to get ldap running. It needs to connect to the database, so this is the correct starting order:

[oracle@oracleas ~]$ $ORACLE_HOME/opmn/bin/opmnctl startall
opmnctl: starting opmn and all managed processes...

See if all is started:
[oracle@oracleas ~]$ $ORACLE_HOME/opmn/bin/opmnctl status

Processes in Instance: ias_1014.oracleas
ias-component | process-type | pid | status
DSA | DSA | N/A | Down
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | N/A | Down
OC4J | OC4J_SECURITY | 2811 | Alive
HTTP_Server | HTTP_Server | 2726 | Alive
OID | OID | 2731 | Alive

If you would like to know which ports are used by the http server, use the "-l" option of the opmnctl command (-l stands for "long").
Now, with the OID, there's another check you ought to be aware of. Similar to the way the Oracle Process Manager (opmn) monitors, OID has it's own set of monitors. Activity of these can be checked by using the ldapcheck command:
[oracle@oracleas ~]$ $ORACLE_HOME/ldap/bin/ldapcheck

Checking Oracle Internet Directory Processes ...ALL

Process oidmon is Alive as PID 2731
Process oidldapd is Alive as PID 2749
Process oidldapd is Alive as PID 2783
Not Running ---- Process oidrepld
Process odisrv is Alive as PID 2770

oidmon (PID 2731) is the OID Monitoring process itself; it is the process, that is actually monitored by opmn. The two oidldapd processes are two LDAP Daemons. Other than that, there's an inactive Replication process, oidrepld, that is only used in Enterprise-class setups, as I described here.
The final process is the one, I'm interested in: it's the Oracle Directory Integration service, odisrv for short.

What if I have no ldapcheck?

Well, I did not, either. You can download diptester from here, unzip it, and rename "ldapcheck_for_HPUX" to ldapcheck, do a chmod 740, and you're in business.

What id ldapcheck returns
ps: illegal option -- o

Apply same medicin as "What if I do not have ldapcheck"


Now, in order to get OID to synchronize from AD, I need a few things:
  • a highly privileged account on Active Directory
  • Names, or IP-addresses, of servers involved
  • an understanding of mapping, with base of OID as well as AD

As for the account, I'll use my own account, which is member of the administrators group - do not use this in production! But you do need an account, that is allowed to query the "Deleted users" section - and that requires quite some privileges. If you cannot see "Deleted Users", users can be removed from AD, but will always remain in OID. And that is a security issue...

IP addresses: The oracle iAS runs is called oracleas.home.local at, the PDC is located at

Mapping: well, whoever invented that should be prosecuted. As long as you keep it basic, and simple, there are few problems. As soon as you company is operating multiple domains, it is a RPITA to get things running smoothly.


Mapping is based on profiles. The fastest way to get things running is to take a copy of $ORACLE_HOME/ldap/odi/conf/ and use that as basis.
The base in OID is dc=bortel,dc=home, as you can see from this picture of the OID Aministration tool, oidadmin:

This is the correct entry, as this entry holds the users. Same is true for Active Directory, although I know no other way than to query the darn thing:

C:\Documents and Settings\frank>ldapsearch -Z -h w2k-pdc -p 3268 cn=frank cn
What I am doing here, is query my own credentials, using the built-in security (-Z), the localhost as ldap host (-h w2k-pdc), and query the Global Catalog port (-p 3268).
Anyway - my AD mapping is dc=home, dc=local.

Mapping rules

In the mapping file, this results in the first line being:


In words: maps everything, found under cn=users,dc=home,dc=local (in Active Directory, as it is left of the colon) to cn=users,dc=bortel,dc=home (in Oracle Internet Directory, as it is right of the colon).

These are the domain rules.

If you have more domains, just add mapping rules. If you have organized your domain in Units (Organizational Units, or "ou" in LDAP-speak), start mapping rules at that level, and add, or omit to your needs.


Now, a new tool needs to be used, the dispassistant. User is dipadmin - password of dipadmin is identical to the password of orcladmin:

New in this version is the use of Connector Profiles, and profile Sets.
Due to badly documented features, and some documentation bugs it is best to create a new Connector Group.
First of all, rename the Connector Groups "configset1" to "standardgroup". The original name will just create misunderstanding, as the are configsets in use with OID.

Then, create the group you will be using for your actions - the "standardgroup" will remain as the group holding all defaults, but will not actually be used. My new group is called "demo":

Next, go to the standardgroup, and disassociate ActiveChgImp:

Then, select the newly created group "demo", and associate the profile:

Note, the profile is still Disabled. Edit the profile, and fill in the connection defaults on the first tab:

On the third tab, make sure the filter properties do not have quotes around them - it's a bug:

On the last tab, I always change "Continue on Error" to Yes; I have no tools to monitor whether every change gets into OID correctly. Most of the errors I see, are on duplicated entries anyway.
You may also want to change the frequency of synchronization: every 60 seconds seems on the high side, escpecially if you have 7 profiles, like my customer, one of which connects to a Active Directory with 13,000 users.

Modify the profile mapping file to your liking, and load it into the profile:

[oracle@oracleas ~]$ dipassistant mp -D cn=orcladmin -profile=activechgimp
Profile successfully modified.
[oracle@oracleas ~]$


The first, initial (bulk) load of the accounts is call bootstrapping:

[oracle@oracleas ~]$ dipassistant mp -D cn=orcladmin -profile=activechgimp
Profile successfully modified.
[oracle@oracleas ~]$

Results of this process may be found in $ORACLE_HOME/ldap/odi/log/bootstrap.log and ~.trc

You can also inspect the OID with the oidadmin tool:

(and see there's a problem with krbPrincipalName...)


Now, all that's left is to start the process of syncs every x seconds. In order to do that, I'll need to start the connectorgroup:

[oracle@oracleas ~]$ $ORACLE_HOME/ldap/bin/ldapcheck

Checking Oracle Internet Directory Processes ...ALL

Process oidmon is Alive as PID 2513
Process oidldapd is Alive as PID 2519
Process oidldapd is Alive as PID 2526
Not Running ---- Process oidrepld
Process odisrv is Alive as PID 2521
[oracle@oracleas ~]$ oidctl connect=iasdb server=odisrv instance=2 configset=1 flags="grpid=demo" start
NLS_LANG not set in environment
oidctl:Waiting for oidmon to start ODISRV (instance=2)
oidctl:Waiting for oidmon to start ODISRV (instance=2)
oidctl:Started ODISRV (instance=2) with PID : 21496 successfully

[oracle@oracleas ~]$ $ORACLE_HOME/ldap/bin/ldapcheck

Checking Oracle Internet Directory Processes ...ALL

Process oidmon is Alive as PID 2513
Process oidldapd is Alive as PID 2519
Process oidldapd is Alive as PID 2526
Not Running ---- Process oidrepld
Process odisrv is Alive as PID 2521
Process odisrv is Alive as PID 21496
[oracle@oracleas ~]$

There are two odi processes now. The second one is the AD-OID synchronisation.
Logging of this process is in the same directory as the bootstrap: $ORACLE_HOME/ldap/odi/log.
You will see the timestamp changing (and the file will grow.

Now, after adding a new user in AD, I can quey these:

[oracle@oracleas ~]$ ldapsearch cn=frank* uid

cn=frank w.j.. van bortel,cn=users,dc=bortel,dc=home

And that proves, the synchronisation is working!

Groups, and all that

This is to be added: how to sync groups, and split these. I will also mention some considerations about syncing, especially on multiple domains.

Wednesday, September 15, 2010

Wall-to-wall SSO and EUS

After introducing Kerberos, and getting SSO to the database working, it's time for some excitement: combine Single Sign On (SSO) with Enterprise User Security (EUS).
Sure, I blogged about Enterprise Security a long time ago (See the "Enterprise Security" entries of 2005), but there I used certificates; in fact another identity.
In this entry, some of the 2005 stuff returns, but parts will differ.

The latest and the greatest.

For those of you how wonder why I bother to blog about this "ancient" version, read the statement of direction on SSO server (note 979951.1 on MOS). Basically, when you use Portal, Forms or Discoverer, you need OSSO 10GR3 (even Forms 11G).
It's not that ancient when it will be around for some time...


What I want, is the same a the previous entry on SSO, but I do not wish to create users: I want to use EUS instead.


In addition to what's already in place, I need a complete SSO infrastructure.
The brave may want to do this Enterprise-sized setup, for now I'll just go with the "all-in-one" Identity Management install. Even patched it to


In order to achieve all this, I would need to:
  1. Set up synchronization between Active Directory and OID
  2. Set up Enterprise Security in the database(s)
  3. Set up a trusted relation between OSSO Application Server and AD
  4. As a bonus: try to persuade Forms menu security to use EUS
I will blog about these - stay tuned...

Wednesday, September 08, 2010

Single Sign On to the Database (or WNA for SQL*Plus)

In this entry, I'll demonstrate how easy it is to do Kerberos authentication to the database, or -rephrasing that- how to do WNA in the database.
The goal of this exercise will be to allow users to log on to a remote database using the following syntax:
sqlplus /@db10g


I like to think major sites have serious machines and ditto operating systems. Workstations are uncannily often based on MS Windows, so I'll use MS Windows for a client, and Linux for database server.


Both client and server need to have the Advanced Security Option installed, for which Oracle wants to get paid. Just read the license conditions: if you want to do Kerberos, you need to pay.

Now, how do you check whether you have that installed? On *ixes, you can use the command adapters:
Installed Oracle Advanced Security options are:

RC4 40-bit encryption
RC4 56-bit encryption
RC4 128-bit encryption
RC4 256-bit encryption
DES40 40-bit encryption
DES 56-bit encryption
3DES 112-bit encryption
3DES 168-bit encryption
AES 128-bit encryption
AES 192-bit encryption
AES 256-bit encryption
MD5 crypto-checksumming
SHA-1 crypto-checksumming
Kerberos v5 authentication
RADIUS authentication
[ora10@db1 ~]$

According to the documentation, that is proof. I have found that it is not always the case, and you may find yourself without the Oracle Kerberos utilities. Better check if these are available, they sould be:
[ora10@db1 ~]$ ls $ORACLE_HOME/bin/ok*
/oracle/db10/10GRel2/bin/okdstry /oracle/db10/10GRel2/bin/okinit
/oracle/db10/10GRel2/bin/oklist /oracle/db10/10GRel2/bin/okdstry0
/oracle/db10/10GRel2/bin/okinit0 /oracle/db10/10GRel2/bin/oklist0

That concludes checks on the software stack on the server. If you miss anything: install the Advanced Security Option!
Client checks

For MS Windows clients, there is unfortunately not much else to do than to fire up OUI, and list the installed options. Fore mentioned Kerberos utilities should also be available on the client, so you may want to check that out as well.

Hands on

Service Principal

First thing to do, is to allow the database server to get Kerberos tickets. In order to do that, I need a Kerberos service principal. In order to do that, I need to create a domain user on my MS Windows 2000 Domain Controller:

I have chosen the name of the instance, but that does not really matter. You may want to use a more generic user name if you have an environment with many instances per machine.
Make sure you set "Use DES encryption" and "Do not require preauthentication", as shown here:

You may have to patch your MicroSoft Active Directory to support this. Check out Knowledge Base article 833708 on how to do this in the registry, or apply SP 1.
I think everybody has SP1 (or better) - I did not have to change anything on my PDC image, but this may cause problems.
Keytab file

Now that I created a user, I must transfer the credentials to the database server. That involves exporting keys to a file, and it is done using this statement on the PDC (or member domain controller):

ktpass -princ service/username@AD.DOMAIN -mapuser username -pass APassWord -DesOnly -crypto des-cbc-md5 -ptype KRB5_NT_PRINCIPAL -out c:\serv.keytab

This is an actual session:

The beauty is, that you may forget the password: you will never need it again. In fact, you need not know the password, unless things don't work, and you want to troubleshoot.
You should remember the service name: you need to specify that in configuration files. I use "orcl" in this example - you may want to use a more generic name, like krb5srv.
Oracle MOS Document states you can only use -crypto des-cbc-crc for encrytion; this is incomplete; you can also use -crypto des-cbc-md5, as I did. (you need md5 for HTTP principals, but that's an other story).

Prepare server

Binary transfer the keytab file to the server. These files are called keytab files, as they contain a table of keys - the keytab for short.
Logon to the database server an cd to your TNS_ADMIN directory. Which happens to be $ORACLE_HOME/network/admin, if you do not use $TNS_ADMIN.
Alter the sqlnet.ora file to look like:
[ora10@db1 admin]$ cat sqlnet.ora
sqlnet.authentication_services=(beq, kerberos5)

Make sure the variables point to the correct locations of the keytab file and the (general) Kerberos configuration file. This configuration file contains:
[ora10@db1 admin]$ cat /oracle/db10/admin/krb5.conf
default_realm = HOME.LOCAL
.home.local = HOME.LOCAL
home.local = HOME.LOCAL
Mind you, I am completely bypassing the fact, your server should be configured to support Kerberos, i.e. without support for Kerberos on OS level, this is less likely to succeed.

Prepare Client

Client side is about the same as the server, apart from the service principal/keytab file. Change sqlnet.ora:

# sqlnet.kerberos5_cc_name=C:\oracle\clt10g\network\admin\krbcache

Note the last line: it is MS specific, and allows the use of the MS Windows internal Kerberos cache, instead of a file based one (as specified the line above).
Note however, using the internal cache will disallow the use of Oracle Kerberos utilities; these will err off with "OSD error"

The Kerberos configuration file is similar to the one on the server, and connects realms to domains:
default_realm = HOME.LOCAL
kdc = w2k-pdc.home.local
.home.local = HOME.LOCAL
home.local = HOME.LOCAL

You may now test the Kerberos setup. You should be able to get a ticket from the Kerberos Ticket Granting Server (which is MS Active Directory, in this case).
Try to get a ticket for a known MS domain account, I can log on to the domain "home.local" as frank, that would make my account "frank@HOME.LOCAL" - and be careful, Kerberos is strangely case sensitive! The oklist utility reveals:
Ticket cache: win2kcc
Default principal: frank@HOME.LOCAL

Valid Starting Expires Principal
08-Sep-2010 12:56:03 08-Sep-2010 22:56:03 krbtgt/HOME.LOCAL@HOME.LOCAL renew until 15-Sep-2010 12:56:03
08-Sep-2010 12:56:03 08-Sep-2010 22:56:03 krbtgt/HOME.LOCAL@HOME.LOCAL renew until 15-Sep-2010 12:56:03
08-Sep-2010 13:28:28 08-Sep-2010 22:56:03 orcl/db1.home.local@HOME.LOCAL renew until 15-Sep-2010 12:56:03
08-Sep-2010 12:56:04 08-Sep-2010 22:56:03 W2K-PDC$@HOME.LOCAL renew until 15-Sep-2010 12:56:03
08-Sep-2010 12:56:04 08-Sep-2010 22:56:03 ldap/w2k-pdc.home.local/home.local@HOME.LOCAL renew until 15-Sep-2010 12:56:03

Now, create a database user "FRANK@HOME.LOCAL", in uppercase, and quoted, as Oracle uses the commercial at-sign for special purposes. You should now be able to login:

C:\Documents and Settings\frank>sqlplus /@db10

SQL*Plus: Release - Production on Wed Sep 8 13:51:56 2010
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> show user

Stay tuned

Of course, things start getting interesting when adding OID and EUS. Wall-to-wall SSO!

Monday, September 06, 2010

Over the top

As much as I like privacy (google changed it's privacy statement just now, btw), I hate any form of censorship on the internet.
RIM seems to give up on privacy, by allowing governments to decrypt Blackberry traffic.
Google is not just content with our search and surf behaviour on the internet, it now wants to know what we're talking about and introduces free phone services.
And Apple is censoring ping.

Sigh. What a world. Next entry will be on Oracle, and SSO to the database.
Update: Craigslist does censor, too.

[Edit]Seems DRM is broken... Someone published a master key...[/edit]

Thursday, August 26, 2010

An old fox looses hair...

but not it's tricks. MicroSoft's "new" IE9 interface looks suspiciously like Google's Chrome (or Iron, which lacks the privacy loopholes of Chrome).
How very innovative - oh no, MS has been doing that since they dubbed QDOS "MS-DOS".
And yes, QDOS meant Quick and Dirty Operating System, and yes, it was "bought" by MicroSoft. The 50k US$ was not the initial amount (that was considerably lower).


What is really worrying is the [quote]provisions for “recognized,” or “protected,” sites which will allow users to go straight from the Windows taskbar to these sites without having to open IE first.[/quote]
Knowing the way MicroSoft implements security, these [quote]recognized, protected sites will be treated more like traditional Windows applications[/quote] will open a whole new range of possibilities. For hackers, that is.

Saturday, July 31, 2010



The new, right wing government signed a treaty with the US on DNA and fingerprint exchange. Quote (in Dutch): Een woordvoerder van het ministerie benadrukt dat het om een versnelling van een bestaande procedure gaat die alleen geldt voor de gegevens van zware criminelen. Alleen mensen die een straf van minimaal vier jaar hebben gekregen komen terug in de database. Which roughly translates to: A spokesman from the ministry emphasized that is is just a speed-up of existing procedures, that only delas with data on heavy criminals. Only those with a punishment of four years or more in prison are in the database.

I say: create database link goodbye_privacy connect to X identified by Z, and my fingerprints are found, too.
See below: "I am a criminal"

No deal.

The other day, I got an email from the cable company I have internet, phone and (digital) television from. If I were interested to extend the number of digital television channels for a small amount.
Fair enough; I'd get motor channel, travel channels and science channels: all stuff that interests me, for just a few bucks monthly. I was ready to be served, and clicked the link.
However, the second of three screens I had to fill in, insisted on me indicating sex and date of birth. I fail to see why they needed that, and the more I started thinking about it, the stranger the concept became. I closed the browser.


Apart from pissing me off, asking details the company already knew (I have a subscription, damned!), I really, really wonder what a cable company wants with the fact whether I'm a boy or a girl. Or when I was born - maybe they toss in a free porn channel when I'm over 21? What do they do with all these data?
I want to be able to see what they know about me, and decide whether or not they need that data. Cable companies do not need birth dates.


Actually, there's a programme called Prime that handles just that. It helps deciding whether data is needed for a certain transaction (e.g. when ordering from Amazon, an address is handy, but birth date is superfluous), and -what's more- it tags data: "destroy after six months". Check it out.

I'm a criminal

Well, I guess I must be: the government insisted on having my fingerprints taken and stored in a central database. They really twist your arm to get these prints: no passport unless you deliver. I fail to see why they would need my fingerprints. Oh yeah - I heard about the (stupid!) European guideline for a digitally recorded fingerprint in your passport, "to make it safer". But that is one fingerprint, not four!
It has already been demonstrated that remote identity theft by reading the (ill encoded) chip in your passport, is possible, and a matter of minutes with sophisticated machinery. Costly, but that has never been stopping those parties interested in these documents. And no, contrary to popular belief, there's no need for close encounters: distances of up to 10 meters (30 ft) reading chip data, are achieved.
The danger is that most people believe that passports (or other identification means) are safe, and "unbreakable" and thus the bearer of the identity must be that identity. The opposite has been demonstrated.

Iris scan

Before introduction of the fingerprint on passports, security people were consulted, or given room to comment on the idea. I heard that one of the leading security people of Amsterdam Airport was amongst them. He (or she?) opposed to fingerprints as unsafe and too easy to forge. Just search for "forge fingerprint".
Instead, iris scans would be much safer. He/she was muffled: European guidelines say fingerprints. How stupid.
Interestingly, Schiphol Airport frequent flyers can bypass normal check-in and passport(!) procedures by enrolling into the Privium Club, and having their iris scanned upon entrance as a means of identification. I think I'll put scans of my fingers online soon.


A while ago, the government wanted people to install smart energy meters. By law, punishable by a hefty fine if rejected. It would allow energy distributors to better and more efficiently distribute electricity and it would allow for energy savings by giving insight in your electricity usage. Guess the fact it should be by law was inspired by the eco terrorists in this country.
This meter would upload data every 15 minutes to the power grid, which would then feed the data to the retailer. Consumers would check the retailers web site for their usage.
Of course, that would not only allow thiefs to find out when I was on vacation, it would also make it quite easy to find out where religious Muslims live: they would be up at 5 am in the morning for prayers. Function creep danger: instead of monitoring electricity usage, police can install energy taps in addition to telephone taps.

Now, with some reasoning, the smart meter concept can still be introduced:


The first thing to do is to make data, relevant to the power distributer (the gird), anonymous. This can be archived by combining data of a couple of hundred homes, or start metering at a block (of houses) level.

Store locally

The second thing to is, is to change the design of the meter to allow local storage - if I want insight in my electricity usage, I can download my meter readings and create a database. A personal database. And by the time the retailer needs the data for billing purposes, I can push the button, and condensed data will be sent to the retailer.
You need to keep your goals clear; do not try to use the electricity meter to archive energy savings as well as smarter grid control. Do not engineer
function creep.


The Dutch pay-per-ride ("rekeningrijden") system (if it ever gets realized) uses local storage. The minister involved was warned in the early stages of the project not to allow real-time positioning of vehicles. In stead, the unit only sends how long, and against what fee the car was driven every now and then. Data about when and where remains in the car. This is good policy, from privacy perspective. As long as no back doors are possible; function creep would allow the police to write speeding tickets based on historical data ("you cannot have driven from Amsterdam to The Hague in 40 minutes during rush hour!").


To minimize chance of function creep, hardware should be used as much as possible: no data means no privacy invasion. Licence plate recognition is commonly used by the police in The Netherlands. When matched against a license plate database (e.g. stolen car, car used with heist), it's a "hit", to be acted upon. All other data ("no-hits") should be destroyed, according to Dutch law. It has happened more than once, that this data was stored: "Very convenient for police cases".
When sensors would have been used that only transmit the hits, this function creep could not have happened.

Again: anonymize!

Very often, there's no need for personal data. The dreaded "OV-chip card" could (and should!) be anonymous. In stead, the default is a personal chip card, that allow the card company to register where and when you traveled. There's a so-called anonymous card, which isn't: you cannot pay anonymous cash: it needs a bank account, due to the nature of the crediting system: you pay the maximum amount upon check-in before the journey, and get restitution upon check-out. In order to be sure you will pay, there's a minimum deposit value that needs to be on your card. This means every card has an identity: either name (by default), or number (the "anonymous" card).
I liked those old train tickets; they worked by attributes. Blue ticket: first class. Yellowish-brown: second class. Anonymous. Simple. Transparent.

Call me old fashioned... I'd rather you'd call me prudent. Wary. Superstitious perhaps, especially when the government is involved

Monday, July 19, 2010

DNSSec, please

Or is it "just" an insecure server? Whole story is here.
Oracle TimesTen users may want to check the latest Patch set

Friday, June 11, 2010

ORA-28047 "database is not a member of any enterprise domain in OID"

Amazing... Two-and-a-half-thousand hits and no answer. Metalink: two hits and no answer. Why me?
OK; here's what is happening, and how to resolve the issue. I have not figured out what causes the problem - it seems intermittent.
Update: might have something to do with settings (stickyness?) on the loadbalancer.


You will be confronted with this error while trying Enterprise Security:
sqlplus s/s@<missing_alias>
SQL*Plus: Release - Production on Fri Jun 11 10:26:49 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ORA-28047: database is not a member of any enterprise domain in OID

Enter user-name:

You have registered the database, and all seems well. No errors were shown during the process, none to be found in the logs, and your database can be found, using LDAP name resolving.
It can also be found in your default Realm (LDAP root in Oracle slang) using:
ldapsearch -h <OID_hostname> cn=<missing_alias>

What causes ORA-28047

Exactly what is says: your database is not known in any Enterprise Domain.
You may check it, using this query:
ldapsearch -h <OID_hostname> -D cn=orcladmin -w <your_password> cn=OracleDefaultDomain uniquemember|findstr <missing_alias>

Change findstr to grep if you're on Unix or Linux; I was resolving this issue with a client that uses MS Windows workstations.
Change OracleDefaultDomain to something else, if you use multiple security domains (I doubt if any)

How to resolve

Well, that's obvious now: add a member to the OracleDefaultDomain. For some reason, Oracle's Directory Manager does not allow you to do that, but other tools do. If all else fails, you can still do it from the commandline, using ldapmodify:
ldapmodify <OID_hostname> -D cn=orcladmin -w <your_password> -f ora28047.ldif

The contents of the file ORA28047.ldif is:
dn: cn=OracleDefaultDomain,cn=OracleDBSecurity,cn=Products,cn=OracleContext,dc=<your_realm_here>
changetype: modify
add: uniquemember

Mind the formatting... lines cannot be broken (as blogger formatting does)


That should be the sign that all works: plus connects to the database, the database knows it needs to go to the OID, it cannot find the specified user ("s") in the OID:
sqlplus s/s@<missing_alias>
SQL*Plus: Release - Production on Fri Jun 11 10:26:49 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ORA-28273: No mapping for user nickname to LDAP distinguished name exists.

Taking it one step further: ORA-28274

If you use an actual OID-known account, you should get ORA-28274: No ORACLE password attribute corresponding to user nickname exists:
sqlplus bortel/s@<missing_alias>
SQL*Plus: Release - Production on Fri Jun 11 10:26:49 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ORA-28274: No ORACLE password attribute corresponding to user nickname exists.

Wednesday, May 19, 2010

Getting runInstaller to accept your version

Of course, you can start with the "-ignoreSysPrereqs" switch, but if you just want to see if all is OK, fool the installer by tweaking oraparam.ini (in the install subdirectory) by adding the correct version.

I am running CentOS 5.5 (Final), which is indicated by the contents of the file /etc/redhat-release:
[oracle@oracleas install]$ cat /etc/redhat-release
CentOS release 5.5 (Final)

The oraparam.ini file looks like:
[Certified Versions]
Linux=redhat-Red Hat Enterprise Linux AS release 4,redhat-2.1,redhat-3,SuSE-9,UnitedLinux-1.0

[Linux-redhat-Red Hat Enterprise Linux AS release 4-optional]

Change that to:(mind you: two places, although the second one is not very omportant)
[Certified Versions]
Linux=redhat-CentOS release 5.5 (Final),redhat-3,SuSE-9,UnitedLinux-1.0

[Linux-redhat--CentOS release 5.5 (Final)-optional]

and you have managed to trick the installer into believing this was the correct and supported version to start with:
[oracle@oracleas install]$ ./runInstaller -paramFile /home/oracle/oraparam.ini
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-CentOS release 5.5 (Final), redhat-3, SuSE-9 or UnitedLinux-1.0

All installer requirements met.

Sunday, May 16, 2010

Clone your Machine (VirtualBox)

It seemed so easy, but it turned out not to be. After using Export/Import once, your disk image gets the internal UUID from the original. You cannot clone your baseline once more, as the UUID is already being used.
You will have to fall back to the CLI, and issue
vboxmanage clonevdi

That will clone your virtual disk. You still have to create a new Virtual Machine (VM), and attach this disk to it. The cloning part takes some time, the rest is peanuts.

Saturday, May 08, 2010

Ubuntu 10.04 LTS and Oracle under VirtualBox


I'm running Sun VirtualBox V3.1.6 r59338. Got it installed via Synaptic, not the Ubuntu Software Center. The latter does offer VirtualBox, but there seems something wrong with the networking components: I could not get a bridged network working: horrible errors at boot time about missing stuff.

Guest Additions

In order to get the Guest Additions installed correctly, I needed
yum install binutils gcc make patch libgomp glibc-headers glibc-devel kernel-headers kernel-devel

Edit: in order to get the installer (of iAS 10.1.40 to work, I needed xorg-x11-deprecated-libs:
yum install xorg-x11-deprecated-libs
After telling VirtualBox to load the VBoxGuestAdditions.iso in the "CD Player", I could mount and install:
mount /dev/cdrom /media
cd /media

From here on, I basically followed these installation instructions.
[root@db10 ~]# groupadd dba10
[root@db10 ~]# groupadd oinstall
[root@db10 ~]# useradd oracle -g oinstall -G dba10 -c "Oracle Software Owner"
[root@db10 ~]# uname -r
[root@db10 ~]# umount /media
[root@db10 ~]# mount -t vboxsf shared /media

Here, I created a permanently Machine Share Folder called "shared", which points to /home/frank/Public. This is where Oracle Installation software resides (via an NFS mount, but that's another story:
sudo mount cubestation:/volume1/Public /home/frank/Public)

I did, however, still need to install openmotif
[root@db10 ~]# yum install openmotif

and add the following lines tot /etc/sysctl.conf:

Install fest

In order to prevent "Can't connect to X window", just issue the following:
frank@frank-cs03:~$ ssh -X oracle@
oracle@'s password:
Warning: No xauth data; using fake authentication data for X11 forwarding.
/usr/bin/xauth: creating new authority file /home/oracle/.Xauthority
[oracle@db10 ~]$ cd /media/Oracle/10GR2_Linux/10201_database_linux32/database/
[oracle@db10 database]$ ./runInstaller

After a while of installing there is another point in time to take s snapshot, and declare this a baseline. This is the result:
[oracle@db10 ~]$ sqlplus system

SQL*Plus: Release - Production on Sat May 8 17:53:06 2010

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options


And yes, patch 4 ( is out!

Sunday, May 02, 2010

Ubuntu 10.04 LTS is here... and Oracle?

And I'm somewhat disappointed.

Install woes

I tried installing on a separate disk, a 300GiB Samsung 321KJ.
The first installation attempt failed in the cleaning up phase, at 89%.
The second attempt failed with a read error, and the advice to clean my CD lens or move to a colder area.
Fourth attempt, after checking the installation CD for errors: ditto.
Fifth attempt, other disk (same brand, model and age): ditto.

Most annoying is the lack of feedback on the install proces, and even CTRL-[Funtion keys 1-4] will not display a text box with your error (or standard) output.

Alas...and an alternative install

Gave up on installing 10.04 off a CD. Installed 9.10 (which took about 30 minutes, and went OK), logged on, and allowed the system to update to 10.04 LTS. That took about one-and-a-half hour. Configuring NOT to play that hideous sound on startup, use colors I do NOT dislike, install Thunderbird and VirtualBox and remove IM, Chat and Evolution: another 5 minutes.

Oracle on Ubuntu

I gave up. I did not even try to install any Oracle product on 10.04. Oh, they probably will run, with some tweaking and stuff; after all I managed to get 10G database and Application Server running on 9.10.

But Oracle usually runs on stable versions; once released it looks like it's already falling behind. Therefor I abandoned the idea running Oracle directly under Ubuntu, but use CentOS/RHES instead in virtual machine environments. Ubuntu simply is too much "cuttin' edge" to my liking.
So - now I have a CentOS 5.4 basic Server install with XWindows and that's about it.

Installing CentOS 5.4

I used a net based install. CentOS 5.4 supports FTP and HTTP based installations using a special, only 8.9MB (yup - MegaByte!) bootable install ISO. I discarded about all options, apart from the Server install (no GUI) and XWindows (OUI needs XWindows, although using Ubuntu as Xserver would be possible, too).
Choose a non-expanding virtual disk, as the database and expanding volumes do not mix-and-match. 32GB should do the trick.

Clone your Box

Unfortunately, there's no 'Duplicate' option in VB, so you'll have to do it by hand, which is surprisingly simple: run Export Appliance, followed by Import Applicance. Change the name of the Virtual machine, so you won't end up with two machines with the same name.

Meet db10 - my 10GRel2 database server

Fire it (the cloned machine, not the baseline!) up, change ip-address (/etc/sysconfig/network-scripts/ifcfg-eth0, I used and hostname (/etc/hosts, /etc/sysconfig/network, sysctl kernel.hostname=db10) and Bob's your uncle.

Tuesday, April 13, 2010

Firefox no longer does WNA with OSSO

No, I'm not in some secret service, using all the acronyms. Firefox no longer does MS Windows Native Authentication in combination with Oracle Single Sign On. Somewhere between Firefox V1.5 and V2.0.11, they decided to change it's agent settings.
Result is that there is not an enhancement request with Oracle: "BUG:6803891 ORACLE SSO WNA SOULD BE ENHANCED TO SUPPORT FIREFOX BROWSER".

Please support that request.

Something similar seems to be the case for Vista and IE 7, by the way (bug 6795150)


Change the configuration; this works on the latest Firefox:
  • Get to the configuration screen; type 'about:config' in the address bar
  • Enter 'useragent' in the Filter box
  • Change general.useragent.extra.firefox to:
    Firefox/3.6.2 (compatible; MSIE 6.0;)

    (I had Firefox/3.6.2) - the last semi colon is needed!

Bottom line is the fact the kerberos authentication module searches for "Windows NT" and then for version >= "5". Next it looks for "MSIE", which is no longer (FF V2 and up) there.
You can take it from there, see this entry.

Friday, April 02, 2010

End of an era - Ed Roberts died

The DIY PC era, that is - Henry Edward Roberts, the designer of worlds' first Personal Computer, the Altair 8800, died yesterday.

Monday, March 22, 2010

SSO server (with WNA) fails to start with Kerberos errors

When you see this on the log:

10/03/22 12:06:22 Acquire TGT using AS Exchange
10/03/22 12:06:22 KerberosAuthenticator: GSSException raised in constructor - No valid credentials provided (Mechanism level: Attempt to obtain new ACCEPT credentials failed!)
10/03/22 12:06:22 GSSException: No valid credentials provided (Mechanism level: Attempt to obtain new ACCEPT credentials failed!)
10/03/22 12:06:22 at
10/03/22 12:06:22 ... 22 more
10/03/22 12:06:22 KerberosAuthenticator: Please check the error messages and fix it. Restart OC4J (OC4J_SECURITY instance) server
10/03/22 12:06:22 KerberosAuthenticator: Possible errors may be:
10/03/22 12:06:22 KerberosAuthenticator: 1.HTTP service name in $ORACLE_HOME/j2ee/OC4J_SECURITY/config/jazn-data.xml or $ORACLE_HOME/j2ee/OC4J_SECURITY/application-deployments/sso/orion-application.xml is wrong.
10/03/22 12:06:22 KerberosAuthenticator: 2.KDC Details (host/port) in $ORACLE_HOME/opmn/conf/opmn.xml are wrong.
10/03/22 12:06:22 KerberosAuthenticator: 3.KDC is down.
10/03/22 12:06:22 KerberosAuthenticator: 4.KDC Details in the keytab file are wrong or the keytab file path has been incorrectly specified.

then try adding the following to $ORACLE_HOME/j2ee/OC4J_SECURITY/config/jazn-data.xml, under the section <login-module>



Then, restart the OC4J_SECURITY process.

Inspite of

everything noted in the note, it works with:

Java(TM) 2 Runtime Environment, Standard Edition (build
Java HotSpot(TM) Server VM (build 1.4.2 IA64, mixed mode)
running on:
HP-UX [myhost] B.11.23 U ia64 4294967040 unlimited-user license

Friday, March 12, 2010

Grid Control & OID: a deadly combo

If you ever experience these symptoms, ask your self whether you have Grid Control (or OEM) running.
  • Single Sign On fails
  • OID Processes suddenly have stopped (which explains the above)
  • ODS database account is locked, which seems to cause the problem
  • Unlocking the ODS account resolves the problem, but it gets locked after a short period.
The cause is the fact you changed the ODS password. Now, you may have done this quite correctly by using the oidpasswd utility, ensuring the wallet gets updated, etc, but that does not update targets.xml... This means, Grid Control (or OEM) periodically tries to log on with the wrong password.

One solution is to update the ldap section of $ORACLE_HOME/sysman/emd/targets.xml, and update the password in plain text (and signal it is no longer encrypted by means of: ENCRYPTED="FALSE").
Then, restart Grid Control (or OEM)

More in Doc ID 567256.1

Wednesday, February 17, 2010

IAM 10.1.4 on Ubuntu

Starting off with a OID Repository, with is of the "Wrong Version" (Installed with REPCA 10.1.4 - trying to install 10.1.4...), now several wizards fail.

It starts with the Delegated Administration Service Configuration Wizard; Apache fails to start with:

Syntax error on line 1113 of /oracle/iam/Apache/Apache/conf/httpd.conf:
Cannot load /oracle/iam/Apache/Apache/libexec/ into server: /oracle/iam/Apache/Apache/libexec/ undefined symbol: dbm_fetch
and several others.
In spite of earlier actions, one needs to:
sudo apt-get install libdb1-compat

That still does not resolve everything - but at least I can use LDAP.

Friday, February 05, 2010

There's quite a leak in 11G

There's quite a security leak in Oracle 11G release 2. You are warned. No patch or workaround known (not installing Java - would that be an option?)
Found on c't (German magizine, see link in title), announced on the Black Hat conference by David Litchfield.


First line of defense: revoke all on DMBS_JAVA, DBMS_JAVA_TEST and DBMS_JVM_EXP_PERMS from PUBLIC.

Update 2

Here's a link to an English version of the original article. Note the "How-to" video is available (again).

Wednesday, January 27, 2010

Install APEX receipe

How to install APEX - shorthand.

Prepare the database

There are some options and tools to be installed. If you use the DBCA to generate scripts, make sure you at least run these:


$AH stands for my Administrative Home directory; usually something like $ORACLE_BASE/admin/{SID}


Not sure? You should at least get the following three, when running this:
select comp_id from dba_registry;



select owa_util.get_version from dual;
should result in or higher. It will need to get patched/upgraded if not. You can safely execute owainst.sql in the owa sub directory of the installation, as the OWA installation checks the version, and only patches if there's something to patch


Only when not using SGA_TARGET (10g and 11g) or MEMORY_TARGET (11g), define shared_pool_size 100MB or higher.


Now, you are ready to install:
@apexins sysaux sysaux temp /i/

Don't forget the last "/"... Change the password of the public account, and unlock the account:
alter user apex_public_user identified by ABC123DEF account unlock;


Assuming a MS Windows workstation, zip the images directory of the apex install. Transfer that to your OHS (Oracle Http Server). Now, create a directory and unzip:

$ mkdir $ORACLE_BASE/htdocs/apex
$ cd $ORACLE_BASE/htdocs/apex
$ unzip /.../


Add this entry to plsql.conf ($ORACLE_BASE points to /oracle/apex):
PlsqlMaxParameters 15000


Add this dads.conf:
Alias /i/ "/oracle/apex/htdocs/apex/images/"
AddType text/xml xbl
AddType text/x-component htc
<Location /demo>
SetHandler pls_handler
Order deny,allow
Allow from All
AllowOverride None
PlsqlDatabaseUsername apex_public_user
PlsqlDatabasePassword ABC123DEF
PlsqlDatabaseConnectString cs-frank03.home.local:1521:orcl.home.local ServiceNameFormat
PlsqlNLSLanguage American_America.UTF8
PlsqlAuthenticationMode Basic
PlsqlSessionStateManagement StatelessWithFastResetPackageState
PlsqlDocumentPath docs
PlsqlDocumentProcedure wwv_flow_file_mgr.process_downloadd
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDefaultPage apex
PlsqlAlwaysDescribeProcedure Off
PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize

Obfuscate password

It is not a good practice to leave cleartext passwords; obfuscate it:
cd $ORACLE_HOME/ohs/modplsql/conf
export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib
export PATH=$ORACLE_HOME/ohs/modplsql/conf:$ORACLE_HOME/perl/bin:$PATH

perl -o

$ Error: Could not obfuscate password 'ABC123DEF' for dad '/demo'
Number of failed obfuscations : 1

Nevermind - it is. Just check it. You're almost done, just make OHS aware of all changes:
$ opmnctl restartproc type=ohs

You are now ready to add workspaces and users, like I've done here.

Wednesday, January 20, 2010

OHS2 on Ubuntu (error loading shared libraries)

Trying to install the Oracle HTTP Server powered by Apache 2, I got the following errors:
/oracle/as/10.1.3/ohs/bin/apachectl startssl: execing httpd
/oracle/as/10.1.3/ohs/bin/httpd: error while loading shared libraries: cannot open shared object file: No such file or directory

That can be resolved by:
sudo ln -s /usr/lib/ /usr/lib/

The next error on the opmn http log will be:
/oracle/as/10.1.3/ohs/bin/apachectl startssl: execing httpd
/oracle/as/10.1.3/ohs/bin/httpd: error while loading shared libraries: cannot open shared object file: No such file or directory

In order to resolve that, execute:
sudo ln -s /usr/lib/ /usr/lib/

Oracle 10G R2 on Ubuntu 9.10 ( link error)

Installing Oracle 10G release 2 on Ubuntu 9.10 (Karmic Koala) is pretty much the same as installing on 8.04, except for the extra packages needed. In this entry, I used
apt-get install build-essential libaio1 gawk ksh libmotif3 alien libtool lsb-rpm libstdc++5
Problem with 9.10 is: libstdc++5 has been fased out and superseeded with libstdc++6.
When you try to install nevertheless, you'll get:
Package libstdc++5 is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source
E: Package libstdc++5 has no installation candidate

Trying to go along with the installation will get you:

Error in invoking target 'all_no_orcl' of makefile ''.


The solution is simple: create a link to the V6 library:

sudo ln -s /usr/lib/ /usr/lib/


I am, by the way, impressed about the speed of this install. I have the impression, this ext4 filesystsem is a lot faster than the 2-disk lvm I had on CentOS.

Sunday, January 17, 2010

Reaching out: the database as Web-browser

Many people may not know of the possibility to use the database as browser. Oracle dumped the RDBMS as database since the introduction of Oracle 8i, and made it some Jack-of-all-Trades. The browser capabilities were introduced even earlier, in release 7.3.3.


Indeed, you may want to ask: why? What use could this possibly have? Well, for one: gathering statistics about something you do not have direct access to. The weather, for example. Combined with PHP, you could deliver very sophisticated graphs on temperature, rain, moisture, etc, etc. In fact, I used it for such an application log time ago: prevent firefighters from getting asphyxiated by smoke from chemical fires by plotting a safe route to the fire.


OK, in order to have firefighters use safe access roads, I need to know the current wind direction - how to get that? Luckily, there's something called METAR (flyboy speak for Meteorological Aerodrome Report), and all (military) airfields provide one, as well as all automated weather stations, (weather-)ships and buoys.
The beauty of METAR is that it is released every half-hour, 24*7*365. I suspect the background of METAR was telex; these messages were distributed by telex, later by teletype machines. Just look how there pages are formatted:

SA171155 EHFS
METAR EHFS 171155Z AUTO 26015KT 9999NDV NCD 05/04 Q1016=

SA171325 EHAK
METAR EHAK 171325Z AUTO 13010KT 050V200 9999NDV SCT003/// OVC004///
04/04 Q1011=

SA171325 EHAM
METAR EHAM 171325Z 28015KT 9999 FEW012 07/05 Q1014 NOSIG=
Nowadays, these pages can be found on the internet. A couple of years ago, when I coded for this project, the teletype look-alike was still there. By now, those pages have to be "official", and carry links and graphical elements.


Let's take a look at a METAR, and take the last one from the previous example:
SA171325 EHAM
METAR EHAM 171325Z 28015KT 9999 FEW012 07/05 Q1014 NOSIG=

ZCZC is the attention signal, the (radio-)teletype "Start of Message" code. Is not a part of the METAR itself, but is a part of the communications protocol.
SA171325 EHAM SA used to signify this is a Surface Aviation observation, but the coding would differ from the METAR standard. Not sure what it signifies here. 171325 is the date (17th) and time (13:25) on which this message was released. Time is Zulu, or GMT.
EHAM is the international designation of the location, according to ICAO (International Civil Aviation Organization) standard. It just so happens to be Amsterdam Airport, Schiphol (or Ships Hell)
METAR Indicates this is METAR coded, as opposed to SA-format.
EHAM See above: designates origin.
171325Z See above: date and time (GMT)
28015KT Wind speed and direction: 15 Knots, from 280 degrees. North being 0 degrees, so this would be West-North-West-Western (WNWW), as West would be 270. Sometimes followed by G33, meaning Gusts of wind up to 33 knots.
9999 Visibility (in meters). 9999 is excellent, better than 10 Km. Sometimes entries like 1000SE 5000N: 1000 meters South East, 5000 meters in Northerly direction.
FEW012 Cloud indication: there are a few (12.5~25%) clouds from 1200 feet upwards. May be suffixed with type of cloud (like CB: cumulonimbus, or thunderstorm cloud)
07/05 Temperature readings (in Celsius): 07 is the current temperature, 05 is the thaw temperature. These can be used to deduct the Relative humidity. Possibly prefixed with the letter 'M' to indicate minus. Zero filled, two position numbers.
Q1014 Pressure in hPa. Current air pressure in Amsterdam is 1014 hPa. Zero filled, four position number.
NOSIG No Significant changes in weather. Civilian code; military use color codes.
= The End-of-Communication indicator. Used to be NNNN.

Eindhoven airport is a military airport; this is their METAR:
SA171425 EHEH
METAR EHEH 171425Z AUTO 27010KT 9999NDV SCT017 BKN020 07/04 Q1016
There's a nice table here, explaining the color codes.

Of course, there's much more with METAR. The internet has plenty examples, and this is always a nice start. At least, now I know where to look for wind direction, and possibly a forecast.


In order to get the internet downloaded into the database, I use utl_http. Basically, the code consisted of only a few lines:
procedure weerrapport is
l_piece utl_http.html_pieces;
l_url1 varchar2(80) := '';
l_url2 varchar2(80) := '';
l_tmp varchar2(32767);
l_line varchar2(256);
dummy boolean;
l_piece := utl_http.request_pieces (l_url1, 10);
for i in 1..l_piece.count
-- Piece the lot together again...
l_tmp := l_tmp||l_piece(i);
end loop;
-- Discard everything not in the BODY...
l_tmp := substr(l_tmp, instr(upper(l_tmp), '<BODY'),32676);
-- Get Deelen (EHDL) and Lelystad info (EHLE)
end weerrapport

Lots of unsafe programming here: I simply work on the basis (not an assumption, back then!) that the page returned will not exceed 20kB. Yes - that is 20kB, not 32kB, as I only fetch 10 pieces (of 2000 byte) of HTML code. By now, the page has increased in size, but it still is about 14kB.
Nevertheless, some mechanism to handle overflows should have been incorporated.

The into_weer procedure is simply a further stripping of unnecessary text, and saving the metar to the database:

procedure into_weer(p_string IN varchar2, p_station IN varchar2)
l_line varchar2(256);
if instr(upper(p_string),p_station) > 0
l_line := replace(substr(p_string,
chr(10),' ');
l_line := substr(l_line, 1, instr(upper(l_line),'ZCZC') - 1);
insert into weer (bron, site, dag, metar) values
('KNMI', p_station, to_date(substr(l_line,6,6),'DDHH24MI'), l_line);
when others then
end if;
end into_weer;

Again, very bad programming: an exception handler that hides any exception: "I do not wanna know about any mishaps"


Of course, there could be a follow-up: how to get from METAR to ordinary spoken weather forecast, or how to get from temperature and thew point to relative humidity. May be I will.

Special Characters - notes and comments

Reason for this entry is the rather disturbing observation, you can actually get different results from one and the same script.
The scenario is running a script, that uses embedded "special" characters (an €, in this case) and is executed using either the CLI version of SQL*Plus (sqlplus.exe), or the Windowed version (sqlplusw.exe).

Code Pages

It turns out that both versions use different MS Windows code pages: the Windowed version, sqlplusw.exe, picks up the code page of MS Windows, while the CLI version uses the code page of the CLI. I ended up with different binary values of the character.
By default, these differ on all non-US installations of MS Windows!


In the course of the blog entries, I have mentioned, but never made a list of tell-tale signs. A troubleshooting list so to say. Here's a start:
  • Remember: there's always at least one way to screw up. There's no recipe, no Rule-of-Thumb, or whatever to make sure you'll never end up with weird glyphs.
    Unless you control the whole, single-user, system, that is...
  • If you see a small diamond in web browser, start hitting your web designer/builder: the page encoding is incorrect for the characters used.
    You can change the Character Set Coding in most browsers as workaround.
  • If you see inverted ('"upside-down") question marks, your Oracle infrastructure is to blame: somewhere along the line, character set conversion takes place, for which the "to-character set" does not have a glyph defined (e.g. ISO8859P1 does not have a glyph for the Euro-sign)

[add Apr 2011] A rather good note on this is Doc ID 158577.1 on Metalink.

Wednesday, January 13, 2010

Ubuntu 9.10 (and Oracle's support for it)

It's official... CentOS is exit, I'm now running Ubuntu 9.10 (or Karmic Koala).

Of course, I'll set up Oracle again, which appears to be very easy, as Oracle provided a Debian (and thus Ubuntu) repository. This should allow Debian and Ubuntu users to install Oracle by using Synaptic Package Manager. Point an shoot installs... I am curious!

Add the repository

You do have to make Ubuntu aware of the fact Oracle is a source of software, too. For that, you need to add the location of the repository to your configuration. This is accomplished by:

frank@cs-frank03:~$ sudo gedit /etc/apt/sources.list
Scroll to the bottom of the file, and add:
# Oracle stuff
deb unstable main non-free

After that, update the libraries to allow Software Center to show Oracle, enter:

frank@cs-frank03:~$ sudo apt-get update

Now, fire up Synaptic, and click on the 'Origin' button; you should see listed. Point at it, and you will see XE (in two flavours) and the XE client.

Now, if only I just wanted XE...


For non-Debian, there's support for yum as well; check it out!

Tuesday, January 12, 2010

More APEX notes

A very, very quick way of housekeeping:


It will drop your code, database accounts, and tablespace associated with it.

If you ever want to create a workspace, and do some basic housekeeping:

set echo off verify off
accept apex_short_name prompt 'Enter the short APEX applicaton name: '
prompt ...Creating the APEX tablespace...
create tablespace &apex_short_name
datafile '/oracle//&apex_short_name.01.dbf'
size 10m
autoextend on
next 5m
maxsize 1000m
extent management local
uniform size 64k;
-- User and grants
prompt ...Creating user and granting rights...
grant connect, resource
to &apex_short_name._owner identified by &apex_app_db_password;
grant execute on dbms_pipe to &apex_short_name._owner;
alter user &apex_short_name._owner default atblespace &apex_short_name;
alter user &apex_short_name._owner quota unlimited on &apex_short_name;
-- APEX Workspace and schema
prompt ...Creating APEX Workspace &apex_short_name
p_workspace => '&apex_short_name',
p_primary_schema => '&apex_short_name._owner',
p_additional_schemas => null);
prompt ...Creating APEX Workspace administrator account...

wwv_flow_fnd_user_api.create_fnd_user (
p_user_name => '&apex_short_name._ADMIN',
p_web_password => '&apex_short_name._ADMIN',
p_group_ids => '',
p_default_schema=> '&apex_short_name._OWNER',
p_account_locked=> 'N',
p_account_expiry=> to_date('201001011300','YYYYMMDDHH24MI'),
p_failed_access_attempts=> 0,
p_change_password_on_first_use=> 'N',
p_first_password_use_occurred=> 'Y',
p_allow_access_to_schemas => '');


For a given value of "abc" for apex_short_name, this will create:
  • a tablespace ABC, with an associated datafile abc01.dbf
  • a database account abc_owner with a password of your choice
  • an APEX workspace abc, with an associated abc_owner schema
  • an APEX Workspace administrator abc_ADMIN, with a password abc_ADMIN

Mind you: APEX Passwords are case sensitive; account names are not.

Monday, January 11, 2010

RSA-768 cracked

Today I read the announcement, RSA-768 is factorized, that is, the prime numbers of which the 232-digit number consists, are known.
English, and original, announcement, and a Dutch comment.

It may have taken a lot of time, but it just shows: nothing is secret. I previously blogged about SHA1 and MD-5 being broken, and actually being used for forgery.

Nice date format

Today is (was) 54 decimal.

Wednesday, January 06, 2010

Special characters - part V

The end of the War of the Worlds?

Well... that remains to be seen. I predict the conclusion will be that there's always a possibility unwanted, or "wrong" characters get displayed. But when you start to understand why, my mission is accomplished. By the way - I found an excellent explanation on HTML right here


For the setup, I used the "we8" database instance (see this entry) I added the Oracle Http Server(based on Apache 2), of which Linux version used to be downloadable off the database download page. There was a bit of work to be done to get things running. The official documentation states:
For Apache 2.0 startup to succeed, link the current version to /usr/lib/

For example, if the current shared library is in /usr/lib is, then run the following command:

cd /usr/lib
ln -s /usr/lib/

Database Access Descriptor (DAD)

My DAD looks simple enough:

# ============================================================================
# mod_plsql DAD Configuration File
# ============================================================================
# 1. Please refer to dads.README for a description of this file
# ============================================================================
<Location /pls/myapp>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseConnectString cs-frank03:1521:we8
PlsqlAuthenticationMode Basic
PlsqlErrorStyle ModplsqlStyle
PlsqlNLSLanguage American_America.UTF8
PlsqlDefaultPage scott.home

I added my own html wrapper, and created the following procedure check if the DAD was configured correctly:

create or replace procedure home as
p_title=>'DAD Check page',
p_text=>'DAD Checked out successfully!')

This results in a "DAD Checked out successfully!" message, signifying the http server works, the connection to the database works, and my wrapper works.


Knowing all the basics are working, I created the following package to display the contents of the blah table:

create or replace package demochar as
procedure showblah;
prodedure editblah;
create or replace package body demochar
procedure showblah
htp.p(format.pagestart(p_title => 'Demonstration of Special Characters on the Web',
p_text => 'Welcome - contents of table is')
htp.p('<div class="content">');
htp.p('<table summary="blah" border="1" align="center">');
htp.p('<tr><th>Character<th>Hex value');
for i in (select b.a, dump(b.a,16) as d
from blah b)
htp.p('<td>' || i.a);
htp.p('<td>' || i.d);
end loop;
end showblah;
procedure editblah is
end editblah;


The blah table is there again, and it's contents is restored:

SQL> col dump(a,16) format a20
SQL> select a, dump(a,16) from blah;

A DUMP(A,16)
-------------------- --------------------
€ Typ=1 Len=1: 80
Æ Typ=1 Len=1: c6
ß Typ=1 Len=1: df

Results and observations

First of all, the UTF8, used in the DAD is standard APEX, to name one. But is it the correct one to use?
Displayed on the web, the page looks like this (Linux/Firefox):

Om MS Windows (XP) with Firefox, as well as using Konqueror on Linux, the dreaded diamond is displayed:

So - the fact that our character does not display in a web browser is depending on the browser and on what is used in the DAD. As we change the DAD characterset to be used to
PlsqlNLSLanguage American_America.WE8ISO8859P15
the page is displayed correctly in Konquerer, incorrect in Firefox on Linux, but that can be corrected by forcing the page to use the 1252 codepage (!). Firefox under MS Windows reacts the same: auto detection, Universal and ISO-8859-15 all do not work, changing the Character Encoding to West European(Windows 1252) displays the page correctly. Internet Explorer V7.0.5730.13: same story: auto detection does not work.

Now - the question arises: is this due to the coding of the page? Character sets are encoded in the header. The last screenshot is the same on all browsers/systems, and is not due to encoding settings, but purely Oracle - the DAD characterset is now WE8ISO8859P1, which indeed does not know how to map the € sign. That results in an upside-down question mark.

After inserting the euro character in the MS Windows encoding (0xa4, or 164), all browsers manage to show correct values (that is the € sign) for that when using UTF8 in the DAD, as well as browser Character Encoding. Using WE9ISO8859P15 in the DAD, the pages will display one € correct, based on Character Encoding in the Browser:
- Western ISO-8859-15 will display € for 0xa4
- Western (Windows-1252) will display € for 0x80

Only Konqueror mangages to display the € symbol for both codes, when the DAD uses WE8ISO8859P15, and the page is explicitly coded in ISO-8859-15 (using the META element Content-type in the header):


There's alwyas at least one way to screw up. If it's not the database, that was fed the wrong code points, it's the browser, that does a lousy job. Konqueror does an admirable job, though. Internet Explorer as well as Firefox have the possibility to switch Character Coding, which may result in correct display of the glyphs.
UTF-8 is not always the best PlsqlNLSLanguage!