Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Using the SSO Audit Log Table

Oracle Application Server Tips by Burleson Consulting

There is an important log tables inside the iasdb instance in the orasso schema called
wsso_audit_log_table_t  that you can use to extract SSO interaction information.  This table contain many detailed metrics about SSO interaction:


Name                          Null?    Type
 ----------------------------- -------- --------------------
 LOG_ID                        NOT NULL NUMBER
 USER_NAME                     NOT NULL VARCHAR2(256)
 AUDIT_TYPE                    NOT NULL VARCHAR2(32)
 ACTION_CODE                   NOT NULL NUMBER
 ACTION                        NOT NULL VARCHAR2(80)
 IP_ADDRESS                    NOT NULL VARCHAR2(32)
 APP_SITE                      NOT NULL VARCHAR2(80)
 MESSAGE                       NOT NULL VARCHAR2(256)
 LOG_DATE                      NOT NULL DATE
 PROCESS_DATE                           DATE
 EMAIL                                  VARCHAR2(80)
 MAINTAINER_ID                          VARCHAR2(80)

We can take the data from this table and create SSO summary report for execution in SQL*Plus.  Below we see a common SSO activity report:

set echo off
set feedback off
ttitle off
set heading on
set pages 999
set lines 80
prompt ***************************************************
prompt SSO Activity summary Report
prompt ***************************************************
alter session set nls_date_format = 'YYYY MM DD';
col c0 heading 'date'   format a15
col c1 heading 'action' format a20
col c2 heading 'Count'  format 99,999
break on c0 skip 2
compute sum of c2 on c0
   to_char(log_date,'yyyy-mm-dd hh24') c0,
   action                              c1,
   count(*)                            c2
group by
   to_char(log_date,'yyyy-mm-dd hh24'),
prompt ***************************************************
prompt SSO Message summary Report
prompt ***************************************************
col c1 heading 'message' format a20
   to_char(log_date,'yyyy-mm-dd hh24') c0,
   message                             c1,
   count(*)                            c2
group by
   to_char(log_date,'yyyy-mm-dd hh24'),
set lines 80
prompt ***************************************************
prompt SSO Activity Detail Report
prompt ***************************************************
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
col c1 Heading 'Date'    format a20
col c2 heading 'User'    format a10
col c3 heading 'Action'  format a10
col c4 heading 'Message' format a20
   log_date        c1,
   user_name       c2,
   action          c3,
   message         c4

Here is the output from this report.  Here we see a summary of all log-in operations, summed by hour of the day.  We also see counts of all SSO messages summed by hour of the day.  The last report in this section shows all SSO details (Listing 2.6).

SSO Activity summary Report

date            action                 Count                                    
--------------- -------------------- -------                                   
2003-06-04 09   LOGIN                      4                                   
***************                      -------                                    
sum                                        4                                   
2003-06-04 10   LOGIN                      1                                   
***************                      -------                                   
sum                                        1                                   
2003-06-04 11   LOGIN                      2                                   
***************                      -------                                   
sum                                        2                                   
2003-06-04 14   LOGIN                      1                                   
***************                      -------                                   
sum                                        1                                    
2003-06-04 20   LOGIN                      2                                    
***************                      -------                                   
sum                                        2                                   
2003-06-05 08   LOGIN                      1                                   
***************                      -------                                   
sum                                        1                                   
2003-07-08 14   LOGIN                      3                                   
***************                      -------                                   
sum                                        3                                   
2003-07-10 08   LOGIN                      4                                   
***************                      -------                                    
sum                                        4                                   
SSO Message summary Report

date            message                Count                                   
--------------- -------------------- -------                                   
2003-06-04 09   Login failed               4                                   
***************                      -------                                   
sum                                        4                                   
2003-06-04 10   Login Successful          11                                    
                Login failed               4            
***************                      -------                                   
sum                                       15                                   
2003-06-04 11   Login Successful         334                                   
***************                      -------                                   
sum                                      334                                   
2003-06-04 14   Login Successful         432                                   
                Login failed              14
***************                      -------                                   
sum                                      446                                   
2003-06-04 20   Login Successful          62                                   
                Login failed               3
***************                      -------                                   
sum                                       65                                    
2003-06-05 08   Login Successful         433   
                Login failed              61                                                                   
***************                      -------                                   
sum                                      494                                   
2003-07-08 14   Login failed               3                                   
***************                      -------                                   
sum                                        3                                   

2003-07-10 08   Login failed               4                                
***************                      -------                                   
sum                                        4                                    
SSO Activity Detail Report
Date                 User       Action     Message                             
-------------------- ---------- ---------- --------------------                

2003-06-04 09:45:42  GARMANYJ   LOGIN      Login failed                        
2003-06-04 11:46:27  GARMANYJ   LOGIN      Login Successful                    
2003-06-04 14:32:52  GARMANYJ   LOGIN      Login Successful                    
2003-06-04 20:58:44  GARMANYJ   LOGIN      Login Successful                    
2003-06-05 08:58:24  GARMANYJ   LOGIN      Login Successful                    
2003-07-08 14:28:20  GARMANYJ   LOGIN      Login failed                        
2003-07-08 14:28:26  GARMANYJ   LOGIN      Login failed                         
2003-07-08 14:28:37  GARMANYJ   LOGIN      Login failed                        
2003-07-10 08:29:49  GARMANYJ   LOGIN      Login failed                        
2003-07-10 08:29:53  GARMANYJ   LOGIN      Login failed                        
2003-07-10 08:30:00  GARMANYJ   LOGIN      Login failed                        
2003-07-10 08:30:05  GARMANYJ   LOGIN      Login failed                        
2003-06-04 09:42:24  IAS_ADMIN  LOGIN      Login failed                        
2003-06-04 09:42:12  ORACLADMIN LOGIN      Login failed                        
2003-06-04 09:42:44  ORACLADMIN LOGIN      Login failed                        
2003-06-04 10:22:18  ORCLADMIN  LOGIN      Login Successful                    
2003-06-04 11:39:45  ORCLADMIN  LOGIN      Login Successful                    
2003-06-04 20:53:24  ORCLADMIN  LOGIN      Login Successful                    

Listing 2.6: SSO Repository Log Table Report

We can also write script to check the availability of SSO.  As we have noted, if the infrastructure is down or SSO cannot accept connections, no users can access your system.  Hence, frequently checking SSO connectivity is an important Oracle9iAS administration task.

Here is a Perl script that you can use to check SSO availability.  This script checks if the Single Sign-On (SSO) Server is accessible and is responding to HTTP requests.
PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1:$ORACLE_HOME/perl/lib/site_perl/5.6.1 ;
export PERL5LIB ;
$ORACLE_HOME/perl/bin/perl -e '
$returncode = "NOK";
$oraclehome =  $ENV{'ORACLE_HOME'};
use IO::Socket;
 $url = $ARGV[0];
 $host = $ARGV[1];
$searchstring = $ARGV[2];
open FILE, "$oraclehome/install/portlist.ini" or die "File portlist.ini not
while ($line = <FILE>) {
 $i = index $line, $searchstring;
 if ( $i == 0 ) {
        if ($line =~ /(=)([ ]*)(\S+)/) {
                $port =  $3;
close FILE;
 $this_socket = new IO::Socket::INET PeerAddr => $host, Timeout  => "9",
PeerPort => $port, Proto    => "tcp" ;
   $returncode = "NOK";
 } else {
 $get_request = ("GET $url HTTP/1.0\r\n" );
 $this_socket->print ($get_request);
 $this_socket->print("Accept: text/plain\n");
 $this_socket->print("Accept: text/html\n");
 $this_socket->print("UserAgent: LoogBrowser/1.0\n\n");
 while ($line=($this_socket->getline()))
   if ( $line =~ /(HTTP\/1.1 200 OK)/) {
      $returncode = "POK";
   if ( $line =~ /(Access Partner Applications)/) {
      $returncode = "OK";
 print $returncode
' "/pls/orasso/orasso.home" "localhost"  "Oracle HTTP Server port"

If this script returns the standard output of ?OK?, then SSO is able to accept HTTP requests.  Many Oracle9iAS administrator place this script into a cron task and run it every five minutes.  If there is a failure in SSO, a pager alert is immediately sent to the Oracle9iAS administrator.  Next, let?s look at using the mod_osso utility for SSO administration.

SSO Administration using mod_osso

As SSO expanded into the Oracle9iAS architecture, Oracle recognized that the Oracle HTTP Server (OHS) should be included in the SSO framework.  Starting with Oracle9iAS version 2, the mod_osso module was created to allow SSO to function within OHS.

Before mod_osso, specific logic would have to be embedded into the Java application if the application was to use SSO.  The mod_osso module now makes it easy for incoming users to connect directly to SSO, become authorized, and get the required information to access their applications (Figure 2.6).  The mod_osso utility also allows for a single security point, thereby relieving the tedious and cumbersome problem of maintaining multiple securities for each Oracle9iAS component.
Figure 2.6: Using SSO to connect to Oracle9iAS

To see SSO in action, let?s look at the steps that happen when an Oracle9iAS client connects to their application:

  1. The user requests a URL through a Web browser.  This URL is intercepted by the Oracle HTTP server. 
  2. The HTTP server calls mod_osso to locate a cookie for the user on the HTTP server. If the cookie exists, the Web server extracts the user's information and uses it to log the user in to the requested application. At this point the connection is established.
  3. If the cookie does not exist on the HTTP server, mod_osso redirects the user to the Single Sign-On server.
  4. The Single Sign-On server makes a request back to the users browser to see if a local cookie exists on the users PC. If it finds no remote cookie, SSO tries to authenticate the user with a user name and password. If authentication is successful, the Single Sign-On server creates a cookie in the browser as a reminder that the user has been authenticated. If a cookie exists, the Single Sign-On server will authenticate using the cookie.
  5. Upon successful sign-on, the SSO server then returns the user's encrypted information to mod_osso.
  6. Mod_osso creates a cookie for the user and send it to the browser PC.  It then redirects the user to their original URL page.

As we see, mod_osso simplifies external authentication and removes the need to write custom code for connection and authentication purposes.  Now that we see how mod_osso is used, let?s drill-down and look at how the Oracle9iAS administrator installs, manages and configures SSO.

Roles of the SSO Administrator

The SSO administrator is responsible for all access controls and must manage all users who will connect to an application, all applications within the system, and the assignment of users to applications.  There are three basic areas of SSO administration, server configuration, user management and application management, and we will be focusing on the server installation and configuration of SSO.

It?s important to note that SSO should run seamlessly once it has been configured, and the focus of this text will be on the installation and configuration of SSO.  Once the software is installed and working, the ongoing management of applications and users becomes trivial. 

If you are using Oracle Portal or external applications, there are additional administrative interfaces to SSO.  This is because Portal and external application must have customized authentication code.  Because SSO controls the security for the entire Oracle9iAS enterprise, it is critical that the Oracle9iAS administrator ensure that proper security is maintained. 

For more details on the daily operational use of SSO, see Chapter 12, Oracle9iAS Security.  Next, let?s conclude this chapter with a summary of the most important infrastructure details.


This is an excerpt from "Oracle 10g Application Server Administration Handbook" by Don Burleson and John Garmany.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational