Who said I was?

I already noted the nasty side effects, but instead of altering the once published blog, I decided to add this one. After all, the portal single-sign on (SSO) was just an example, and I want the originally registered applications still to be registered, including OIDDAS, orasso and both the infrastructure as well as the middle tier Oracle HTTP Servers (or OHS).

Inventory taking.
What is it I will have to move? Let's check with the Single Sign-On:orasso entry on the Infrastructure management console. Opening sub pages will get me to the Orasso SSO home page, in this exercise located at http://asinfra101202.mymachine.local:7777/pls/orasso/orasso.home.
This page provides a list of all current "partner applications" that rely on orasso to take care of the login procedure.
After logging in (with orcladmin) by clicking the modest link in the right upper hand corner, you will be shown another link: "Maintain the SSO server". Particulary interesting is the link about maintenance on partner applications!

History
Was did I already do? Actually, not a lot - the changing of the DAD is what was done, to allow the midtier to access /pls/orasso, but that was about it. Reread that thread first.
In the mean time, I do have access to the orasso home page, using the midtier link as well; however, the login link provides me with an error, and all page links refer to the infrastructure.

Moving the SSO server
Less of a move than fact - only the references will be changed to the middle tier instance, not the actual server.
First of all, I want to get rid of the references to the infrastucture on the orasso home page. I can do that by changing the home URL, success URL and logout URL in the Maintain Partner Applications screen of orasso. Unfortunately, I cannot change the login URL, nor the Single Sign-Off URL on this page, and those still point to the infrastructure.
So, the second step is to reregister orasso, by running this on the infrastructure: $ORACLE_HOME/sso/bin/ssoreg.sh -oracle_home_path $ORACLE_HOME -site_name "The SSO Server (OraSSO)" -config_mod_osso TRUE -mod_osso_url http://asmt101202.mymachine.local:7777/pls/orasso/orasso.home


After that, stop and start all opmn registered processes - both sides!

So, you're the security expert on Oracle iAS... or How To Relocate the SSO server

Well, uhm, actually I'm not, but how often do you find yourself in the position that the consulting firm you work for positions you as one?
Picture the following: a perfectly reasonable setup of Oracle's internet Application Server, with Oracle's LDAP implementation (Oracle Internet Directory, or OID) and Portal, Single Sign On (SSO), the Oracle Certificate Authority (OCA), Forms, Discoverer, the Oracle HTTP Server (OHS) and WebCache.
Basically, it looks like this:


What runs where?
Here's what the documentation has to say about the middle or Application Server tier:
The middle-tier is the part of an Oracle Application Server architecture that contains several components responsible for accepting requests from clients, validating the requests, and providing content, while using intelligent data caching for faster and reliable performance. For OracleAS Portal, the middle-tier handles all Web requests by forwarding them to the appropriate provider. This is also where Portal pages are assembled, and where the caching of Portal content is managed. The middle-tier also provides other functions for other Oracle Application Server components.

Components on the Middle tier are:

• The Oracle HTTP Server (powered by Apache), or OHS
  OHS handles all requests for Portal, either via mod_plsql, or via the Parallel Page Engine (PPE)
  
• Webcache
• the Application Server containers for J2EE (OC4J)
  

Same for the Infrastructure tier:
By default, the infrastructure tier handles all authentication requests and hosts the Oracle Application Server Metadata Repository, which contains schemas and business logic used by application server components (including OracleAS Portal) and other pieces of the infrastructure.
Mind the "By default"...

Components, running in the infrastructure are:

• Application Server Control
• Oracle Internet Directory (OID)
• Oracle Application Server Single Sign on (SSO)
• Oracle Application Server Metadata Repository
  some middle tier products, like Portal, store their metadata in this repository.
  

Simple enough, isn't it? Sure, until the network guys came in, as they demanded -and rightly so- this:

So - what's the problem? The middle tier is moved to the DMZ, and firewalls are in place. What can be the problem? Well, Portal doesn't work anymore, as nobody can login...
How comes?

A closer look.
First of all, Portal "runs" on the middle tier; the OHS on the middle tier uses mod_plsql and/or the Parallel Page Engine. Do'nt be mislead by Portal pages themselves, as there is a rewrite engine. A portal page reference (before the rewrite!) may look like:

http://asmt101202.mymachine.local:7777/pls/portal

So, there must be a DAD defined on the middle tier, because of the /pls/ :

[oracle@asmt101202 bin]$ cat $ORACLE_HOME/Apache/modplsql/conf/dads.conf
<Location /pls/portal>
SetHandler pls_handler
Order allow,deny
Allow from All
AllowOverride None
PlsqlDatabaseUsername portal
PlsqlDatabasePassword @BW01fa60ocxhrL9E2N88jEyS0bXgwimw==
PlsqlDatabaseConnectString cn=asdb,cn=oraclecontext NetServiceNameFormat
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode SingleSignOn
PlsqlDocumentTablename portal.wwdoc_document
PlsqlDocumentPath docs
PlsqlDocumentProcedure portal.wwdoc_process.process_download
PlsqlDefaultPage portal.home
PlsqlPathAlias url
PlsqlPathAliasProcedure portal.wwpth_api_alias.process_download
</Location>

So, there you have it - on the middle tier, there's a Database Access Descriptor, pointing to the (standard infrastrutcure setup named) database "asdb". Whenever someone logs on to Portal, SSO is activated, the login link on Portal looks like

http://asmt101202.mymachine.local:7777/pls/portal/PORTAL.wwsec_app_priv.login?p_requested_url=http%3A%2F%2Fasmt101202.mymachine.lo....

Following that link, you will get redirected to

http://asinfra101202.mymachine.local:7777/sso/jsp/login.jsp?site2pstoretoken=v1.2~458......

and that is where the trouble starts: asinfra101202 is not in the DMZ, it's host name cannot be resolved by the DNS-server, in short: you'll get a "page not found" response.

What SSO does.
The Single Sign On server uses mod_osso, which is configured on the infrastructure server. The flow of events is as follows:

1. The user requests a page; the Apache configuration is such that authentication is needed.
2. The mod_osso module kicks in, and looks for a cookie with an encrypted token. If found, the token is passed back to the application.
   If not found, the request gets redirected to the SSO server.
3. The SSO server requests it's own cookie - if not found, it produces a login page.
4. The user credentials are checked and validated against OID
5. The SSO server sets it's own cookie, and redirects the user identity in encrypted form back to mod_osso.
   
6. mod_osso sets it's own cookie, with a token, which holds the user identity in encrypted form (see step 2)

Also, one needs to realize that Oracle Portal is considered a "partner application".
SSO used to be incorporated in Portal, hence the -incorrect- saying 'portal is a partner app of itself'.
Not since iAS 9.0.2, where SSO and Portal were disconnected (and mod_osso was born).




What to do?
Now that the problem is clear, so is the solution. Basically, we need to reconfigure mod_osso, so that:

1. it can be accessed from the middle tier
2. redirection takes place to the middle tier

The SSO server Home Page is located at [host][:port]/pls/orasso, where the host is your infrastructure server, e.g.

http://asinfra101202.mymachine.local:7777/pls/orasso/orasso.home

We will have to change that for starters, so that is will become

http://asmt101202.mymachine.local:7777/pls/orasso/orasso.home

As this is a /pls/ entry, too, it must be a DAD, just like Portal uses, so let's check the dads.conf file on the infrastructure machine:

[oracle@asinfra101202 ~]$ cat $ORACLE_HOME/Apache/modplsql/conf/dads.conf
#=========================================================== # mod_plsql DAD Configuration File
# ===========================================================
# 1. Please refer to dads.README for a description of this file
# ===========================================================
# Note: This file should typically be included in your plsql.conf file with
# the "include" directive.
# Hint: You can look at some sample DADs in the dads.README file
# ===========================================================
<Location /pls/orasso>
SetHandler pls_handler
Order deny,allow
Allow from All
AllowOverride None
PlsqlDatabaseUsername orasso
PlsqlDatabasePassword @BcyVNZv4W9rd5+bPajizTuISOz9AvJ3lLg==
PlsqlDatabaseConnectString cn=asdb,cn=oraclecontext NetServiceNameFormat
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode SingleSignOn
PlsqlSessionCookieName orasso
PlsqlDocumentTablename orasso.wwdoc_document
PlsqlDocumentPath docs
PlsqlDocumentProcedure orasso.wwdoc_process.process_download
PlsqlDefaultPage orasso.home
PlsqlPathAlias url
PlsqlPathAliasProcedure orasso.wwpth_api_alias.process_download
</Location>

Just add that to the existing DADs in the dads.conf file on the middle tier, and bounce the OHS:

[oracle@asmt101202 bin]$ opmnctl restartproc type=ohs
opmnctl: restarting opmn managed processes...

Then, try to access the SSO Server's home page:

http://asmt101202.mymachine.local:7777/pls/orasso/orasso.home

That works...[edit]But you cannot login; you will get: WWC-41439.[/edit]
However, the Portal/SSO login page still redirects you to the infrastructure. You will have to reregister the SSO server. Now, there are two ways of doing that:

1. Call the ssoreg.jar in $ORACLE_HOME/sso/lib
   
2. use the ssoreg script (or batch, when using MS), located in $ORACLE_HOME/sso/bin.

Some posts suggest to use the latter, as it sets some environment variables; so I will. What this registration does is create an encrypted osso.conf file.
[edit]There are some nasty side-effects... See Metalink Note:249408.1 What will happen, is that an empty obfuscated configuration file, osso.conf, is created, to which Portal is added. Portal may work OK after these actions, other vital SSO applications, like OIDDAS, will not.[/edit]

[oracle@asmt101202 bin]$ $ORACLE_HOME/sso/bin/ssoreg.sh -oracle_home_path $ORACLE_HOME -site_name asmt101202.mymachine.local:7777/pls/portal -config_mod_osso TRUE -mod_osso_url http://asmt101202.mymachine.local:7777/pls/portal

I did it on the infrastructure tier as well, seemed logical, but I have no real explanation as to why. I do remeber a note on metalink, mentioning it should be done on both.
Make sure all configs are updated: $ORACLE_HOME/dcm/bin/dcmctl updateconfig -d -v
stop and start all processes, both ends: opmnctl stopall, opmnctl startall
You will stil get an error page; make sure to alter the iasadmin.xml file on the middle tier ($ORACLE_HOME/portal.conf/iasadmin.xml - alter the Host entry on line 2), and run ptlconfig:

oracle@asmt101202 conf]$ ./ptlconfig -dad portal -site -pw welcome2
~/product/as101202/midtier/portal/conf ~/product/as101202/midtier/portal/conf

Portal Dependency Settings Tool

Processing Portal instance '/pls/portal' (cn=asdb,cn=oraclecontext)
Processing complete

Now - following the "login" link on the orasso home page, I get:
You cannot login because there is either invalid or no configuration information stored in the enabler configuration table (WWSEC_ENABLER_CONFIG_INFO$ (WWC-41439))
The WWC-41439 is a link to an error page, that tells me:
WWC-41439 - You cannot login because there is either invalid or no configuration information stored in the enabler configuration table (WWSEC_ENABLER_CONFIG_INFO$).

Cause:

One or both of the following occurred:

• An alias defined in the Apache configuration caused Apache to translate host.domain.com to just host. If this is the case, the Login link only shows host:port (dropping the domain).
• The default domain was not set in the Apache configuration. When this occurs, only the hostname is shown in the Login link and the domain is not included.
• The Portal was configured with an incorrect host or port.

Action:

Do one or both of the following:

• Remove all such aliases from your Apache configuration.
• Include the domain in the ServerName parameter.
• Fix the Host in the IASInstance element and ListenPort in the WebCacheComponent element in iasconfig.xml and run ptlconfig -dad portal -site. The ptlconfig script and the iasconfig.xml file is normally located in the directory portal/conf under the OracleAS Portal and OracleAS Wireless middle-tier home.

Looks like we still have to explain to portal, that the url's are routed somewhere else, which makes sense, of course.
Just fixing the host entry in iasconfig.xml and running the ptlconfig utility does not seem to be enough; inspecting the table shows:

SQL> select LS_LOGIN_URL from portal.WWSEC_ENABLER_CONFIG_INFO$
/

LS_LOGIN_URL
------------------------------------------------------------------------------------------------------------------------------------
http://asinfra101202.mymachine.local:7777/pls/orasso/orasso.wwsso_app_admin.ls_login

SQL> update portal.WWSEC_ENABLER_CONFIG_INFO$
2 set LS_LOGIN_URL = 'http://asmt101202.mymachine.local:7777/pls/orasso/orasso.wwsso_app_admin.ls_login';

1 row updated.

SQL> commit;

That does the trick - no more redirects.
In a nutshell:

1. Enable the orasso DAD on the middle tier.
2. Reregister the SSO server (middle tier)
3. Fix iasconfig.xml
   
4. Update the portal table

Troubles....

About two weeks ago, my monitor went dead. I switched it on, and it did ''poof!" - lights out. Funny smell came from it, too. Anyway - no fuse to be seen, and as the main switch was dead as well (no power light), I decided to say farewell to my 19" Iiyama, that has served me for about 8 years.
So - something new had to be bought, but what? I wanted a 22" wide Samsung (the SyncMaster 225 BW), but after discussing the pro and cons, I decided for a 204B (20", 1600 by 1200 resolution), and a graphics card upgrade (my old card could neither handle the resolution, nor DVI - not that DVI is a requirement...).
Luckily, the "new" card fitted in my computer (which still uses AGP - I know, hopelessly old fashioned), so I'm typing away with a crisp, clear screen, and a lot more space on my desk.

mod_plsql and flexible parameter passing

This is a continuation on a previous entry, where I introduced a HTML wrapper. This wrapper has a nice way to produce a combo box:

htp.p(htmlform.selecttable(label => 'Directory:'
, name => 'p_directory'
, tablename => 'dba_directories'
, textcol => 'directory_name'
, listsize => 10
, orderby => '1'
, p_default => r_sel_pd.directory_name ) );

This little piece of code will actually display a combo box, 10 lines deep, based on the dba_directories table. It will display the column directory_name, and pass it back in a parameter, called p_directory. The list will be ordered on the (one) column displayed.
As the select_table function is overloaded, the fact that listsize is mentioned means it will be a multi select combo box. For the HTML coders amongst you, the select element will have multiple="multiple" added.

This means the call to the database will pass an unknown number of parameters. how to handle this?
Well, this is where the flexible parameter passing of mod_plsql kicks in. First of all, we'll have to tell mod_plsql we're passing an unknown number of parameters. this is done by changing the call to the procedure that will handle the request. in stead of this:

HTP.p(htmlform.formstart(p_action => 'fablogspr.bsavepd'));

the call is changed to this:

HTP.p(htmlform.formstart(p_action => '!fablogspr.bsavepd'));

Note the exclamation mark. That's all there is to change the mode of mod_plsql.

Procedure changes.
The original procedure just had three parameters, id, profile and directory name. The declaration part looks like:

procedure bsavepd (
p_id IN VARCHAR2,
p_profiel IN VARCHAR2,
p_directory IN VARCHAR2 );

The new procedure declaration is:

procedure bsavepd(
name_array IN OWA.vc_arr,
value_array IN OWA.vc_arr)

As I designed the page to use three parameters originally, and the first two do not change, the combo box procedding can be:

begin
for i in 3 .. name_array.LAST
loop
savenewpd(l_prf, value_array(i));
end loop;
EXCEPTION
WHEN extra_page
-- Just return the generated error or warning page.
THEN
null;
WHEN others
THEN
htp.p(format.errorpage(SQLERRM));
END;

I do not need the name_array(i) in the update loop, as it will always contain the name of the parameter I defined in the htmlform.selecttable above, p_directory.
Enjoy modding!

Space advisor - ORA-20000

Ran against a bug today, with dbms_space:

ORA-12012: error on auto execute of job 8897
ORA-20000: ORA-20000: Content of the tablespace specified is not permanent or tablespace name is invalid
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1344
ORA-06512: at "SYS.DBMS_SPACE", line 1560

Turns out to be bug 4707226, which "will be fixed in release 11.0", explained in Note:343795.1. One of the symptoms is that you have a tablespace referenced in table DBA_AUTO_SEGADV_CTL, that no longer exists.
In my case, I used drop tablespace xxxx including contents and datafiles;
The note ends with "No workaround available". Well, here is one that worked for me:
- recreate the tablespace with a minimal sized datafile (100k will do)
- exec dbms_space.auto_space_advisor_job_proc
That should now run without problems.
- use drop tablespace xxx;
- exec dbms_space.auto_space_advisor_job_proc
Should still run without errors.
You may check DBA_AUTO_SEGADV_CTL, so verify your tablespace is not mentioned anymore.

Help save €200,000,000

Although I like France, I think this should stop.
Wasting €200 million each year... Just take a moment and sign the petition:

Formatting blogs

Today, I finally got around some nagging problem with formatting. Just reread some old posts to see the difference... Code snippets now actually look like code snippets - I'm in the process of updating all old posts, but it requires editing the HTML code directly, so it's error prone.
Anyway - that's my current backlog for now: reformat old posts...

Authentication in mod_plsql

Ever wondered what all these possibilites in your DAD configuration meant? I did, and I have used 'Per package' since.
Introduction
Let me assume you have your database (9i Release 2, or 10g, Release 1 or 2) installed and configured, and you have the Oracle Webserver by Apache installed, too. After you started the http server, you should be able to fire up your favourite browser, and browse to

localhost:7778.

If that does not work, look in the httpd.conf file (ORACLE_HOME\Apache\Apache\conf) for the Listen directive. If it reads something else that 7778 (e.g. 7777), use that - mine uses 7778, so I will continue to use that in this entry.
Now that we have the standard Oracle HTTP server home page, look at the entries. Fourth from the bottom, there is this 'Mod plsql configuration menu'. Click it, and click the second option on the following page, the link reading 'Gateway Database Access Descriptor Settings'. By the way - that is what the acronym DAD stands for: Database Access Descriptor.
New DAD entry.
Let's create a new entry, and leave the defaults for what they are. From top to bottom, the entries are:
DAD Name: the name you want to give this configuration. It will be visible to the world; when in use, you will access your application via a browser bij pointing to

[your_host:7778]/pls/[your_dad]/[package.procedure]

, unless we do some fancy things, which I will show later. For this demonstration, let's use TEST for a name.
Oracle user name, password and connectstring: you have a choice here. You can fill in the username, and leave the password blank, or leave username and password blank, or fill in both. The connectstring must always be filled in.
What are the consequences? Well, that depends somewhat on the next entry, Authentication Mode.
Assuming the default Authentication mode of 'Basic', just filling in the username, and leaving the password blank will present you a grey login popup, requesting username and password. Just filling in the password will not help; you must provide a valid database user and it's password.
The beauty of this appraoch is the fact user and password are cached, and sent every time by mod_plsql, so once a session is established, it remains "active". Once disconnected, and recalled, mod_plsql sends user and password, so the session is reestablished. But how to really log off, you ask? Simple - call the logmeoff page of mod_plsql:

localhost:7778/pls/test/logmeoff.

It's built in, and hidden, but documented.
Leaves the option to fill in username and password. Should I explain? Credentials are stored in the DAD configuration, and thus always sent. Logging off doesn't help much, it does clear the cache, but as user and password are stored in the configuration...
By the way, here's a simple procedure to test your DAD:

CREATE OR REPLACE PROCEDURE home IS
begin
htp.p('This is a test page to verify login:');
htp.p('Current user: '||user);
END home;
/
grant execute on home to public;
create public synonym home for home;

Test it, using

localhost:7778/pls/test/home

Session cookie: as the explanation shows, leave it blank. It's only used in clustered environments.
Package/State management: Stateless (Fast Reset). We are using 9i, or 10g, better than 8.1.7.2, anyway.
Connection pooling: Leave it on - it means multiple requests can be handled through one database session.
Default page: now it is getting interesting again. If you get fed up by typing localhost:7778/pls/test/home, you can enter home here. Instead of localhost:7778/pls/test/home, you can now enter localhost:7778/pls/test - big deal!
Document Access and Path aliasing: out of scope for this entry. Maybe in a later one.
For now, let's see how you can get rid of the localhost:7778/pls/test...
One step can be easy, but will only work if you plan to use just one DAD. That involves changing the Default DAD. It's the first entry on the 'Mod pl/sql configuration settings' menu page: Gateway Global Settings. Change the Default DAD descriptor to TEST, and you can now reach the

localhost:7778/pls/test/home

page by just entering

localhost:7778/pls.

This works, because:
You changed the default DAD to test, so all references to mod_plsql are routed to this DAD.
You changed the default page for the TEST DAD to HOME.

So far so good.
Now, let's see what the other Authorization Modes do.
Single Sign On: If you don't have portal (more correctly: LDAP), forget about this option.
Global OWA, Custom OWA and Per package have so much in common, I'll treat them as one entry. All three are grouped into "Custom Authentication" in the manual as opposed to Oracle standard authentication, either by the database (Basic) or by Portal LDAP (Single Sign On). the differences are:
Global OWA: Access control applies to all packages, governed by the owa_custom.authorize function in the OWA package schema.
Custom OWA: Access control applies to all packages, governed by the owa_custom.authorize function in the user's package schema.
Per Package: Access control applies to the specified package, governed by the authorize function in that package.
Let's see what happens if we change the TEST DAD Authentication mode from Basic to Per Package. It results in a 404: Not Found error. A glance at the Apache log file reveals:

[Mon Jul 17 14:52:25 2006] [warn] mod_plsql:
/pls/test/home HTTP-404 Custom Authentication Failure.
[authorize] oerr = 6550
ORA-06550: line 7, column 6:
PLS-00201: identifier 'AUTHORIZE' must be declared
ORA-06550: line 7, column 2: PL/SQL: Statement ignored

Now what was it about Custom Authorization? It needs a package (oops... we still have a procedure), and that package needs to have a function, called authorize. That function must return a boolean; true means, OK - you're authorized, false means no go.
So, here are the changes:

create or replace package test as
function authorize return boolean;
procedure home;
end test;
/

create or replace package body test is
function authorize return boolean is
begin
return true;
end authorize;
procedure home is
begin
htp.p('This is a test page to verify login:');
htp.p('Current user: '||user);
END home;
end test;
/

Change the default page on the TEST DAD to read

test.home ,

and retry

http://localhost:7778:/pls

Change the authorize function to return false, and you will receive the a 403: Forbidden page.

So far, so good...
Where does this lead to? Well, you can now have an application, that has a public part and a private part. You can design your own login screens (which should be public!). In fact, there are two ways of doing that:

1. Use the owa_sec package
2. use your own package

Authentication, using owa_sec.
Basically, you can use the owa_sec.set_protection_realm procedure. In takes a character string as input, which is displayed on this ugly grey popup. Let's give it a try:

create or replace package body test is
function authorize return boolean is
begin
owa_sec.set_protection_realm('TestSec');
if (owa_sec.get_user_id = 'guest') then
 return true;
else
 return false;
end if;
end authorize;

procedure home is
begin
htp.p('This is a test page to verify login:');
htp.p('Current user: '||user);
END home;
end test;
/

You must make a call to the owa_sec package in order to activate the logon popup.
The nice thing about this package is that the username and password are cached, and you do not need to worry about cookies - it's all taken care of.
However, I'm not really thrilled by the look and feel of all this, and would like a custom login page, which I can format to my own look and feel. Also, I would like some self provisioning in my applications, so there would be a link to a "I forgot my password" page, to name one.
Obviously, you cannot reach that page if you would have to login.

Custom Authentication.
So, let's do something else, and split the packages into a public part, and a private part.
The public part would display:

• self provisioning pages, like "I want access, too", "I forgot my password"
• a customized login page
• a basic menu with the above options

The private part would hold all, well, shielded off screens of the application.
Of course, the whole lot of session state, cookies and session id's needs to be taken care of, so it is getting more complicated. But hey, you choose to read this far, so let's continue!

HTML Wrapper.
I think, it's about time to introduce a wrapper tool. The original was Sten Vesterli's work and appeared in his book Web Apps 101. Sadly, his page is no longer maintained. Update jan-2009: checking links, I did find a working link, where you supposedly can download his original code. Didn't work for me... I made some changes (I'd like to call it improvements), so that the wrapper now produces XHTML. The basic idea is to have a wrapper package that does a lot of htp.p in a single call, e.g. format.pagestart will send the stylesheet, open html, set the header, close the header and open the body, or htmlform.selecttable, taht will take a table name as input and produce a drop down box with all values from that table. Drop me a line (comment) and I'll email the packages to you.
That said, let's do some inventory; what we need is:

• session state
• password encryption
• logon and logoff procedures
• private and public authorization

Session State.
I have tried several things to maintain state, but as HTTP is essentially stateless, I just have to use a table here. This is my session table definition (as well as Tom Kyte's...)

create table session_table
( session_id    varchar2(32) primary key,
timestamp     date,
username      varchar2(30),
userprofile     varchar2(16)
userid    number
)
/

And the basic user table:

create table webapp_users
(  id number not null,
identification varchar2(16) not null,
email varchar2(80) not null,
passwd varchar2(20),
userprofile varchar2(16) not null ,
valid_until date not null,
status varchar2(3)  default 'REQ' not null,
remarks varchar2(4000)
)
/

Authorize does not change, as this is the public part. What I do is:

procedure showlogin
is
begin
begin
g_cookie := owa_cookie.get ('demoapp'); -- try to get the cookie
exception
when others
then
null;  -- no problem if we could not get the cookie
end;

if g_cookie.num_vals > 0 -- if we did get the cookie, remove it
then
begin
owa_util.mime_header ('text/html', false);
owa_cookie.remove (name => 'demoapp', val => g_cookie.vals (1));
owa_util.http_header_close;
exception
when others
then
   null;
end;
end if;

delete from session_table
where timestamp < p_title      =""> 'Demo application - Login',
           p_text       => 'Welcome
Authorised access only!</span>' ));
HTP.p ('</div>');
HTP.p (   '<div class="menu">'
          || format.hyperlink (p_text             => 'Login page',
                               p_destination      => 'test.showLogin',
                               p_title            => 'Back to login page'
                              )
          || '</div>' );
HTP.p ('<div class="content">');
HTP.p (htmlform.formstart (p_action => 'test.Validate_User'));
HTP.p (htmlform.tablestart (p_summary => 'Login'));
HTP.p (htmlform.textfield (p_label    => 'User name',
                         p_name     => 'p_username',
                          p_maxlength => 16));
HTP.p (htmlform.passwordfield (p_label => 'Password',
                             p_name  => 'p_password',
                             p_maxlength => 20));
HTP.p (htmlform.buttonsstart);
HTP.p (htmlform.submitbutton (p_label => 'Login'));
HTP.p (htmlform.buttonsend);
HTP.p (htmlform.formend);
HTP.p ('</div>');
HTP.p (format.pageend);
END showlogin;

PROCEDURE validate_user (p_username IN VARCHAR2, p_password IN VARCHAR2)
is
BEGIN
IF p_username IS NOT NULL AND p_password IS NOT NULL
   THEN
      IF validate_usr (p_username, p_password)
      THEN
         OWA_UTIL.mime_header ('text/html', FALSE);
         owa_cookie.send ('demoapp', g_sid, NULL);
         IF g_profile IN ('ADMIN', 'MODERATOR')
         THEN
            OWA_UTIL.redirect_url ('testpr.apage1', bclose_header => TRUE);                                    ELSE
            OWA_UTIL.redirect_url ('testpr.upage1', bclose_header => TRUE);
         END IF;
      ELSE
         HTP.p (format.infopage ('Invalid login'));
      END IF;
   ELSE
      OWA_UTIL.redirect_url ('test.showLogin', bclose_header => FALSE);
   END IF;
END validate_user;

Based on the profiles, I switch to the user pages in the private area (testpr.upage1), or the administrative pages (testpr.apage1). If there is an invalid attempt, I display an error page; if something else is wrong (e.g. no password given), I simply redisplay the login page. The validate_usr function is:

function validate_usr (p_uid in varchar2, p_pswd in varchar2) return boolean
is
l_crypted_psw   raw (32);
l_id            gebruikers.id%type;
begin
l_crypted_psw := crypt (p_pswd);
begin
 select w.userprofile, w.id
 into g_profiel, l_id
 from webapp_users g
 where w.identification = upper (p_uid)
 and w.passwd = l_crypted_psw
 and w.status not in ('REQ', 'EXP', 'DEL');
exception
when no_data_found
then
 return false;
end;

select cast (sys_guid () as varchar2 (32))
into g_sid
from dual;

insert into session_table
  (session_id, timestamp, username, userprofile, userid)
values
  (g_sid, sysdate + 1 / 48, p_uid, g_profiel, l_id);

commit;
return true;
exception
when others
then
htp.p (format.errorpage (sqlerrm));
return false;
end validate_usr;

The function crypt is derived from examples by Tom Kyte:

FUNCTION crypt (p_str IN VARCHAR2)
RETURN RAW
AS
l_data   VARCHAR2 (80);
BEGIN
l_data := RPAD (p_str, (TRUNC (LENGTH (p_str) / 8) + 1) * 8, CHR (0));
DBMS_OBFUSCATION_TOOLKIT.desencrypt
                                 (input_string          => l_data,
                                  key_string            => 'SomeStrAgeCharATersH3re',
                                  encrypted_string      => l_data
                                 );
RETURN UTL_RAW.cast_to_raw (l_data);
END crypt;

What remains, is the private code to authorize, get and set a cookie and maintain session state. Remember, this code all comes from the private package (TESTPR):

function get_cookie (p_str in varchar2)return varchar2 is
begin
g_cookie := owa_cookie.get (p_str);
if g_cookie.num_vals > 0
then
 return g_cookie.vals (1);
else
 return null;
end if;
end get_cookie;

function authorize return boolean
is
l_sid   varchar2 (32);
begin
-- this part is private, so, validate..
l_sid := get_cookie ('demoapp');
debug.log (1, 'cookie=> ' || l_sid);

update    session_table
set timestamp = sysdate + 1 / 48
where session_id = l_sid and timestamp > sysdate - 1 / 48
returning username, userprofile
into g_session_rec.username, g_session_rec.userprofile;

if (sql%rowcount = 0)
then
 return false;
else
 return true;
end if;
end authorize;

procedure set_cookie (p_str in varchar2, p_val in varchar2)
is
begin
dbms_application_info.set_module ('DemoApp', 'set_cookie');
owa_util.mime_header ('text/html', false);
owa_cookie.send (name => p_str, value => p_val, expires => null);
owa_util.http_header_close;
end set_cookie;

Enjoy your experimenting with mod_plsql.
Remember, Oracle changed Metalink from using JSP to PL/SQL (ApEx, to be precise)

Happy days are here again!

Isn't that what it's all about?
Me, wearing protective clothing... Not that it mattered - ended up having a nice sunburn anyway...



Havana skyline - the Malecon, seen from Old Havana. Of course, the obligatory old Studebaker(?) in front. Actually met a guy, as old as me, owning an even elder car: 54 years old, all original, he told me proudly, and rightly so. He revved her up to let me enjoy the sound of six or eight cylinders, probably a 4.2-liter block.

Great people; very friendly (one guy and two women offering help when I had a flat tyre in the middle of nowhere!), and great drinks - all based on rum (Habana Club being "the real thing") Mojitos, Cuba Libres & Daiquiris.
Seen Havana, Cienfuegos, Trinidad, a great little island whose name shall remain unknow to protect the habitat, and Varadero.

Better avoid Varadero; it's not Cuba.

Network Blues

I really, really do not understand why so many people are having problems
with Oracle Networking. Lots of questions on the usenet about it. And, I am
getting bored of it, so here's a receipe for Oracle networking.

I have installed Oracle, and it worked OK, but since [I booted|I tried at Home] it doesn't.

Well, since you ask, I assume you have the server software installed on your own system. I'll come to remote systems later, don't you worry. I also assume your SID is ORCL. It is a default, used a lot by Oracle. Of course, your installation could be different, but remember it. It's 8 characters max, so it should not be hard to remember.

And it means, if you recall orcl.cs.edu as your identifier, you're wrong.
Make it orcl.

If you're on MS Windows, there is a way of
finding out, if you would have forgotten your SID:

• Open your Services window
• Check for services, starting with OracleService
• What's following OracleService, that's the SID.

I know of no such trick for Linux or Unix (when the instance is shut down). There should be a file, called oratab, that contains SID and Oracle Home entries for those platforms. Location of this file varies, /etc/oratab and /var/opt/oratab are two options.

OK - I know my SID, what has that got to do with the network problems I have?

I'll come to that in a minute.
First of all, there is absolutely no reason why you need networking when all you want to do is access you locally installed Oracle. Just make sure you have an environment variable "oracle_sid" set, and you're done.

But I'm on Windows.

So? There is no difference in how Oracle behaves, just the setting of variables is different:

D:\>net start OracleServiceORCL
Starting service OracleServiceORCL........
Service OracleServiceORCL is started
D:\>set oracle_sid=ORCL

D:\>sqlplus scott/tiger

SQL*Plus: Release 10.1.0.4.0 - Production on Sat Mar 4 10:53:51 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL>

And on Linux:

[oracle10@csdb01 oracle10]$ export oracle_sid=o10gR1
[oracle10@csdb01 oracle10]$ sqlplus scott/tiger

SQL*Plus: Release 10.1.0.4.0 - Production on Sat Mar 4 10:45:23 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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

SQL>

Okay? That's all there's to it. Works all the time, because you need it, Oracle needs it. It's the only way, during a fresh install, the instance can be brought up, and a database created.

Okay, got it. Command prompt local connections work.
But I would rather use the @-way of connecting, in stead of remembering SID and "set".

Okay, that still isn't a problem. What you are referring to, are network
aliases, or tns aliases. This "tns" stands for Transparent Network Substrate. You may forget that, but it's Oracle networking.
What you type after the @, is called an alias, and the aliases are stored in a flat file by the name of tnsnames.ora.

The type of connection used in the above example is called a Bequeath connection. It's a call directly to the program, specifying parameters needed. On Linux and Unix, you can see the process, using ps:

oracleo10gR1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))).

This protocol used to be a standard option in the network configuration
assistant, which allowed to get a bequeath entry stored in the tnsnames.ora
file. It isn't anymore, but the protocol is still supported, though a bit hard
to configure. There are some rules to respect; I already mentioned program
calls and parameters.

Here's an example of a bequeath tns-alias entry in tnsnames.ora:

ORCL.CS.NL =
 (DESCRIPTION=
    (ADDRESS = (PROTOCOL = BEQ)(PROGRAM=oracle)
      (ARGV0=oracleORCL)
      (ARGS='(DESCRIPTION=(LOCAL=yes)(ADDRESS=(PROTOCOL=BEQ)))')
    )
    (CONNECT_DATA=(SID = ORCL))
 )

Mind all the quotes; they belong there! And the spaces at the beginning of the lines, too! The only thing you may ever want to change are on the following lines.

Line 1: ORCL.CS.NL =
again, the tns alias (no spaces here, btw). It is what you type after the @: like sqlplus scott/tiger@orcl.cs.nl

Line 3: (PROGRAM=oracle)
it's the oracle executable. An ancient version had a different name under windows, once.
You had to rename the oracle bit to oracle73. Looking at the multiple home option, and naming convention I doubt you ever want to change this. I thought I just mention it as a reference.

Line 4: (ARGV0=oracleORCL):
it's where the SID comes in play. the first argument, passed to the execuable, is -once more- the program name (oracle), with the SID (ORCL) appended.
If you had two installations, one 9i release2, and one 10g, release 1, and each install had a SID, named db920 and db1002 respectively, you would have entries with oracleDB920 and oracleDB1002.
Oh - the upper case is not needed, It's just there to make it easier to read.

Line 7:(CONNECT_DATA=(SID=ORCL);
This is what the client passes to the server. Old notation, it's better to use the
SERVICE_NAME= notation, but this usually is easier to setup.

And it works:

D:\>sqlplus scott/tiger@orcl

SQL*Plus: Release 10.1.0.4.0 - Production on Sat Mar 4 11:47:26 2006
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL>

Hold it! Above you said I had to type orcl.cs.nl, but you just type orcl. How come?

Well spotted! Almost forgot about that. It's an option in a second file, sqlnet.ora. It's your default domain, and the option that takes care of that looks like this. Remember, this is not the file, where the tns aliases are kept, but sqlnet.ora:

NAMES.DEFAULT_DOMAIN = cs.nl

This option in sqlnet.ora takes care of the fact I do not have to type
orcl.cs.nl, but that just orcl will be sufficient. You do not have to use it; that is entirely up to you. And remember, the tns alias is just that! If you would like to use scott/tiger@test.home, and scott/tiger@test.school, you could have the following entries in your tnsnames.ora file:

TEST.HOME =
  (DESCRIPTION=
    (ADDRESS = (PROTOCOL = BEQ)(PROGRAM=oracle)
    (ARGV0=oracleORCL)
    (ARGS='(DESCRIPTION=(LOCAL=yes)(ADDRESS=(PROTOCOL=BEQ)))')
  )
    (CONNECT_DATA=(SID=ORCL))
  )

TEST.SCHOOL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora3.ux.mit.edi)(PORT = 1521))
    (CONNECT_DATA =  (SID = dev))
  )

Please note the fact that SIDs (orcl and dev, respectively) and aliases have nothing in common, although I must confess, these entries (SID and alias used) are fairly often the same.

Summary:

So, what have we got?

• No need for networking when you are working local. Use the bequeath protocol, almost no overhead, and it's fast!


• In order to use bequeath as protocol, set the oracle_sid environment variable.
  


• If you want, you can define a bequeath connection in tnsnames.ora - the central file where all tns-aliases are stored.
  In fact, every installation I do, has such an entry, to allow for lighting fast local connections. It allows for the well-known, familiar "user-id@tns-alias" type of connections, without the need for oracle_sid to exist as environment variable.
  

I see - but what about this TEST.SCHOOL entry?

Now, that is what I called a remote entry: it references a remote server.
Remote servers are most reliably connected to, using the Transmission Control
Protocol/Internet Protocol, TCP/IP. Yes, several other protocals are possible,
but today, TCP/IP is such a standard, anyone has the stacks installed for their
Operating System. Because if you had not, you could not read this... Other
protocols would be IPX on Novell, named pipes on MS Windows, DECNet, or IBM's
LU6.2.

In order to set up a connection to a remote server, you need to know a couple
of things about this server:

• You need the name of the server, or it's IP-address
• You need the port, the listener responds to
• You need the name of the SID, or service name of the Oracle instance

Let's work out an example, suppose you got an email with something like this:

Your account has been set up,
your username on the com4 database on csdb01.cs.nl is scott,
your initial password is tiger.
You are required to change the password upon first login....

So, we have the following information:

• Your IT department stinks - they should have mailed you the TNS entry!
• Name of the server is csdb01.cs.nl
• Listener port is missing, as well as
• the protocol we need to use
• the database SID is com4

Let's create an entry in our tnsnames.ora file:

com4.work =
(description =
(address = (protocol = tcp)(host=csdb01.cs.nl)(port=1521))
(connect_data = (sid=com4))
)

This time, I have choosen to use the SID in the tns alias. And, as this is work, I complete the alias with .work. My tns alias is therefore:
com4.work. I expect to be able to connect with sqlplus scott/tiger@com4.work

On the line address = , the protocol protocol = tcp to be used (TCPIP) is defined, as well as the machine ("host"), we need to connect to host=csdb01.cs.nl.

The protocol, which Oracle abbreviates to TCP, is an assumption (well, an educated guess), as well as the port number of the listener, 1521, in the port=1521 part
of the address =
line.
It is the default port for Oracle listeners.

If, for whatever reason, this does not work, we have the following options:

• ping: can we resolve the name to an IP-address, and can we reach out to the server?



• tnsping: similar to ping, but on Oracle level: resolves the alias, and contacts the listener.

See here:

D:\>ping csdb01.cs.nl
Pingen naar csdb01.cs.nl [192.168.1.199] met 32 byte gegevens:

Antwoord van 192.168.1.199: bytes=32 tijd<10 ttl="64">

D:\>tnsping com4.work
TNS Ping Utility for 32-bit Windows: Version 10.1.0.4.0 - Production on 04-MAR-2006 12:34:48
Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:
D:\oracle\ora92\network\ADMIN\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (description = (address = (protocol = tcp)(host=csdb01.cs.nl)(port=1521)) (connect_data = (sid=com4)))
TNS-12541: TNS:no listener

Looks like you IT department is making backups, or has some work to do... It may very well be, they do not use the default port 1521. Contact them, and ask for clarification; as mentioned, the email you had, was incomplete: protocol, as well as port number were missing.
You can keep guessing a long time, port numbers go up to 65535.

By the time you get the following as a reply to your tnsping:

Attempting to contact (description = (address = (protocol = tcp)(host=csdb01.cs.nl)(port=1521)) (connect_data = (sid=com4)))
OK (10 ms)

you are OK - at least there is a listener out there, the port number you defined is correct, as is the protocol used. So, let's see if we can get some work done:

D:\>sqlplus scott/tiger@com4.work

SQL*Plus: Release 10.1.0.4.0 - Production on Sat Mar 4 13:30:33 2006

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

ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Hmmmm... Now this can be caused by two things:

1. The IT department indeed made backups, and had shut down the listener. It can take some time for an instance to register with a listener, so try again after at least 3 minutes.


2. Your email contains yet another error: the SID com4 is wrong!

In this case, the SID turned out to be wrong; changing it cased to error to disappear, and work could be done.

Another flavor of this error is:

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

this is caused by the fact no SID= was used in the connect descriptor
(that is the official name of the connect_data = part in your tns alias), but SERVICE_NAME=
The listener passes that info, but there is no such service. You can ask the IT
department to verify the services info you received by asking to execute the
lsnrctl services
command on the server. It should have your entry, so if you had
db920.csdb01.cs.nl
as a
service entry, and IT can only find Service "db920.cs.nl" has 1 instance(s), it means you better change to db920.cs.nl.

However, it teaches one important lesson: a successful tnsping does not guarantee a successfull connect! Tnsping contacts the listener, and that's it!

Summary:
So, what have we got?

• For a successful connection to a remote server, you need to know a few things about the (remote) machine:

1. name (or IP-address) of the server
2. the listener (I'll try the default of 1521 first, in case I don't know)
3. service_name or SID of the database
4. protocol used (I'll go for TCP, in case I don't know)

• use ping and tnsping to resolve naming issues
• SID and tns alias may be totally different (but are often the same)
• There are two configuration files involved, tnsnames.ora, which holds my tns aliases, and (possibly) sqlnet.ora, that hold my default domain - that's the bit I have defined in tnsames.ora for the alias domain, but refuse to type.

Oh, since you now are your own IT department, you should have a listener.ora file, that looks like:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = ORCL))
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  )
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
  (SID_NAME = ORCL)
  (ORACLE_HOME = c:\oracle\DB92)
)
)

You still haven't explained why some things do not work after I rebooted.

Ask yourself: "What changed?". I see this scenario often, when new Oracle components have been installed, like a Forms/Reports (Developer Suite) installation after the database has been installed. Especially on MS Windows, that cannot distinguish between environments.

What happened is very simple: the Developer install added an entry to your path. It made sure the path to the developer executables came first.

But... Developer also comes with a network stack: Forms and Reports usually operate upon data, stored within databases, so these components need to connect.

And it is this new network environment, that is used, because it is first in line.

And the remedy is just as simple: reverse you path: open the Configuration screen, Click on System, go to the Advanced tab; and click the middle button, marked Environment Variables. In the bottom half, the system variables screen, scroll down until you find 'Path'. Double-click to edit, cut (Ctrl-X) the path to your Oracle developer environment (it is the first, left-most entry), and paste it back after the second entry, the one to your server environment.

Now, if you don't like doing that for each and every install, there's two other
options:

1. Maintain one network location
2. Synchronize (all your) network environments.

I like option one best, so I'll explain option 2 first:
Each network environment is maintained under %ORACLE_HOME%\network\admin (that's $ORACLE_HOME/network/admin for the Unix/Linux folk).
Your -previously working- server environment has it's tnsnames.ora, and so has your developer environment. Point is, %ORACLE_HOME% is different for both.

Locate those, and copy the working tnsnames.ora and sqlnet.ora from HOME1 (your first, working install) to HOME2 (and HOME3 and HOME4, etc. etc).

Or... (option 1)

Pick a location (any location, mine is d:\tns), put your oracle network configuration files there, and make sure all your environments understand where to look.

How do I get different Oracle network environments to look in one location?

Oh, quite simple, Oracle has introduced an environment variable for that, called tns_admin. Just define that, and give it the value you want, that is the directory where Oracle networking can find the configuration files. It is quite common to do so in Linux/Unix environments, not so common in MS Windows.

Show me how to define tns_admin in MS Windows.

You will have to use the registry for that:

Start-> Run, type: regedit and press return

You will see a split-window
editor, left side having My Computer. Navigate to HKEY_LOCAL_MACHINE,
and open it. Within that, navigate to Software, and open that. Don't be alarmed here - there's a lot of stuff! Navigate down to the Oracle entry, and open
that (hint: click anything in the left hand window, and press "o" - the
cursor jumps to the first 'O' entry).

Now, suppose I just added the Oracle Developer Suite in HOME8. In order to add
a tns_admin entry there, I will have to put the cursor on that entry, and the
press the right-mouse-button on the right half of the screen. Select to
add a New Value, New string
Value, and change the default New value #1 to TNS_ADMIN. Press return to enter the change, and again, to change the value from nothing to the location where you want your network configuration files stored.

That's all; just close the regedit window - all changes are instantanious.

Broken code (SHA, MD5)

Just when you thought you had it all secured, it turns out that the encryption method, regarded most safe and created by the NSA, is broken. Not just now, but about a year ago.
A chinese mathematician, Xiaoyun Wang, uses collision search to break SHA-0, SHA-1 and MD5, which she successfully did. [Edit nov-2007: About the whole world refers to the link I used as well, but it's invalid; I believe this is the correct one, if not, browse this for all publications]

Hashes
What Xiaoyun ('Little Cloud') Wang and her associates Yiqun Lisa Yin, and Hongbo Yu did, was looking into the hash-algorithms. These are mathematical formulas, that, when applied to a file, create a stream of a couple of hundred bits (a string), that is characteristic for the file the algorithm was applied to. In fact, it's some sort of fingerprint: changing one bit in the file causes the hash to change, too. That is why hashes allow you to determine whether files have been tampered with: send the file/document, send the hash. Compute the hash at the receiver's end, and check whether the hashes match.
In order to facilitate this, a hashing algorith must forfill certain prerequisites:

• The result (the hash) is unique; no two different documents or files generate the same hash.
• It may not allow to reconstruct the original data from the hash

When two different files result in the same hash value, this is known as a collision.
Collisions
What Wang does, is looking for collisions. And she found them. For the MD5 as well as for the SHA1 hashing algorithms. MD5 is old (developed in 1991), and supposedly not used very much anymore. It is, however, remarkably often found.
Xiaoyun Wang detects collisions by looking carefully at the original data when it is being hashed. She develops a feeling for that data, and thus "feels" when collisions might occur. All in all, she reduces the original 2^80 combinations, via a subset (reduction to 2^64) to 2^39 possible sequences. Breakable in about a day, on a fast PC.
The standing ovation when this was presented at Eurocrypt, was deserved, I'd say! As well as the best paper award. Nice detail about the Eurocrypt site: their certificate is untrusted...
Soon after the publication, German mathematicians tried sign a PDF document and a Postscript file with a forged digital signature.

Panic
A Dutch researcher, De Weger, created a forged certificate, based on signatures, provided by Wang. Two different certificates had the same hash value, even though only one was genuine.

Wang went on, and announced to have broken SHA1 as well. That caused a bit of a stir, as it is often used hashing method for securing HTTP traffic, the Secure Socket Layer, or SSL.

She was not granted a visum to speak in the USA about her work. Now, who's an austrich here? Getting typical for the US, it seems, this kind of reaction.
[edit feb 2017: Google and CWI hacked SHA-1 160 bits. "Again?, you would say]

Hier is een link naar een Nederlands artikel over haar. Uitleg over botsingen.

Security, yup! No really!

I hardly dare to commence again....
Started with checking the configuration and testing the stuff that used to work. You don't know, nowadays, with these gremlins.
And sure enough:

D:\>tnsping o1003
TNS Ping Utility for 32-bit Windows: Version 10.1.0.4.0 - Production on 16-FEB-2006 15:36:29
Copyright (c) 1997, 2003, Oracle. All rights reserved.

Used parameter files:
D:\oracle\ora92\network\ADMIN\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = 192.168.1.199)(PORT = 2484))) (CONNECT_DATA = (SERVICE_NAME = o10gR1))
( SECURITY = (SSL_SERVER_CERT_DN = cn=o10gR1,cn=OracleContext,dc=cs,dc=nl)))
TNS-12560: TNS:protocol adapter error

So, what is wrong here? That used to work!
Same here; this test worked, too!

D:\>sqlplus system@o10ssl
SQL*Plus: Release 10.1.0.4.0 - Production on Thu Feb 16 15:35:44 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-28759: failure to open file

Okay - I know that one: the wallet is not set for autologin. Fired up the wallet manager, and changed that; the error is now more what I expected:

ORA-29003: SSL transport detected mismatched server certificate.

Okay - I know that one, too: change SSL_SERVER_DN_MATCH in SQLNET.ORA from YES to NO, and you're done! And in fact, I already hinted that would be a problem.
So, I am going to continue this, without the server authentication (which you can see, works! or rather, fails, and thus proves to work).
Let's create a demouser on the server:

[oracle10@csdb01 admin]$ sqlplus system/manager
SQL*Plus: Release 10.1.0.4.0 - Production on Thu Feb 16 16:43:10 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.

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

SQL> create user certdemo identified externally as 'CN=frankbo, O=CarrotSoft, C=NL';
User created.

SQL> grant create session to certdemo;
Grant succeeded.

SQL>

Okay, let's see if I can login from my (Windows) client:

D:\>sqlplus /nolog
SQL*Plus: Release 10.1.0.4.0 - Production on Thu Feb 16 15:56:39 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect / @o1003
ERROR:
ORA-01017: invalid username/password; logon denied

Hmmmm. This seems to be some weird mismatch between globally and externally defined users; the used syntax seems to be OK for 10g Release 2. Thanks to Fabrizio, who hinted that here.

SO, let's revert on the server:

SQL> drop user certdemo cascade;
User dropped.

SQL> create user certdemo identified globally as 'CN=frankbo, O=CarrotSoft, C=NL';
User created.

SQL> grant create session to certdemo;
Grant succeeded.

And try the client again:

SQL> connect /@o1003
Connected.
SQL> show user
USER is "CERTDEMO"
SQL>

So, finally, I'm able to say: finally!

Forgot OID administrator password?

In an environment, that already had the infrastructure install, I wanted to install the Application Server Middle tier. One thing Portal wants, is the connection to the LDAP server (Oracle Internet Directory server, or OID).
I was quite sure about the password, but always got errors during install. After checking (using netstat -a) I was actually using the correct port for LDAP (the default 389), I realized the password could have been expired.
This is the code to reset it:
D:\oracle\10.1\HTTP\BIN>oidpasswd connect=test1 unlock_su_acct=true
OID DB user password:
OID super user account unlocked successfully.

The OID DB user account is what you would enter as password here:
SQL> connect ods@test1
Enter password:
Connected.

Edit:

In addition to that, you cannot logon, using Single Sign On (SSO) anymore.
Some odd actions are to be taken here:

1. start Oracle Directory Manager
2. Log on, using the cn=orcladmin account, and the password you just reset...
3. Navigate to your realm, something like
   cn=orcladmin, cn=Users, dc=yourcompany, dc=yourcountry
4. scroll all the way down, to the userpassword entry, step over to the asterixes, and retype your password.
5. Apply

You can now use your SSO logon again.

And now for something different: scripts

It's been a while (year-end releases, 80-hour weeks...), but here's something I think more people will find useful.
It's a script, called check_alert, and it will inspect and rotate your alert log file(s). If you want just rotation, take a serious look at logrotate, a system utility that comes with most *nix distributions nowadays (except HP-UX, but you can download it - or search here).
I made check_alert as configurable as possible, by means of external files. Feel free to use and alter, though I'd appreciate that my name remains mentioned.
Anyway here's the link for the zipped file, and just to make you curious, here's the README:

README file for check_alert

Purpose: checl_alert will check the alert log files of Oracle
  databases for errors, and will notify DBAs by email

Written: Januari 2006, by Frank van Bortel

check_alert is configurable by means of two external files, check_alert.errors, and
check_alert.conf

check_alert.conf contains the following variables, used by check_alert: 

MAILLIST: 
 contains the list of people that are mailed with the findings of check_alert.
 It should be compliant with the mail program you have defined in MAILPROG.
 Example: MAILLIST=f.w.j.van.bortel@nospam.com


ERRORSONLY:
 a switch [Y,N] designating whether only errors should be mailed (Y), or 
 whether findings should always be mailed (N), even when no errors are found.
        The errors that are reported can be configured in check_alert.errors
 Example: ERRORSONLY=Y

KEEPREPORT:
 a switch [Y,N] designating whether the log reporting file should be kept (Y),
 or deleted (N), when done. 

TMPDIR:
 variable designates a temporary directory; no files will be left there, but
 check_alert must have read/write permissions there.
 Example: TMPDIR=/tmp

ORATAB: designates the location of the oratab file, as maintained by Oracle installation tools
 Note that different platforms use different locations
 Example: ORATAB=/etc/oratab

EXTDATE: 
 defines how long the logs should be kept; format mask according to the date 
 executable. Most date executables support these formats:
  +%m%d will append MMDD to the logfile, keeping the logfiles for a year
  +%d will append the day of the month (DD) format, keeping logfiles for a month
  +%w will append the day-of-the week [0-6], keeping the logfiles for a week
  +%u will append the day-of-the week [1-7], starting monday, rotating logfiles weekly
  see also man date for more formats
 Example: EXTDATE=`date '+%u'`

MAILPROG:
 The name of the executable to send email from this environment. The program must support 
 the -s (subject) switch. Must distributions will use mail, that also supports the -s switch,
 HP-UX does not, and has mailx for this purpose.
 Example: MAILPROG=mailx

ERRORS:
 The file where error patterns are kept. check_alert uses grep -if to scan for errors, making
 the matching case INsensitive.
 NOTE: DO NOT LEAVE THIS FILE EMPTY
 Example: ERRORS=check_alert.errors

Example of check_alert.errors:
ora-
error

check_alert will read the contents of the file, defined in ORATAB, ignore all empty lines, comment 
lines (lines, starting with the # sign), and undefined entries (lines, starting with *).
For each valid entry, the ORACLE_SID is determined, as well as the value of PACKAGE, if not defined.
Based on that, ORACLE_BASE is defined. ORACLE_BASE will be /oracle/$PACKAGE, if PACKAGE is defined,
or the first two subdirectories of ORACLE_HOME as defined in ORATAB.
According to the standards in place, the alert log file for ORACLE_SID can be found at
ORACLE_BASE/admin/dbORACLE_SID/bdump/alert_ORACLE_SID.log

If the alert log can not be found, an entry is made in the log report and the following
three possible locations are tried:
# $ORACLE_BASE/admin/oracle/db$ORACLE_SID
# $ORACLE_HOME/rdbms/log
# $ORACLE_HOME/admin/rdbms/log
If a directory exists, the log file is assumed to be in that directory.
If it still does not exist, an entry is made in the log report and checking is
skipped. 
The log report is a temporary file in TMPDIR, named TMPDIR/check_alert_ORACLE_SID.log

If the alert log file is found, it is checked for errors. The error patterns to be checked
on are defined in check_alert.errors. Matching lines are copied to the log report file.
If no errors are found, a note is made in the log report as well.

As a last step, the alert log file contents is copied to a backup file, with an extension,
defined in EXTDATE. This will govern how long rotated alert log files are kept: if a single
day-of-week number is used, files will be overwritten after a week. Subsequently, if a
day-of-month schema is used, logfiles will be rotated monthly.
Using a naming schema with month or day-of-year naming schema is not advised.
The original alert log file is deleted (as Oracle opens and closes the alert log file on
writes, this can be doen without the need of recreating the file, and setting ownership).

The log report is then sent to the receipties, defined in MAILLIST, using the program, defined
in MAILPROG, based on the fact whether ERRORSONLY is set to Y or N.
If ERRORSONLY is set to Y, the report will only be sent if errors are found, as defined in
check_alert.errors. This is the 'no news means good news' option.

If ERRORSONLY is set to N, the report will be sent regardless of any errors found.

When done, the log report file is removed when the KEEPREPORT flag is set to N.
When testing, or in new environments, it may be beneficial to leave ERRORSONLY=Y, but
set KEEPREPORT=Y. In that manner, relevant files can still be viewed on the system, but
the DBA is not overwhelmed by emails, most of which just indicate all is well (I hope).