Using the SSO Audit Log Tables - Oracle

If you need to handle the administration and management of Oracle Application Server 10g, this article covers the metadata repository (iasdb), the Single Sign-On (SSO) security framework, and the Oracle Application Server 10g Management Services. It is excerpted from chapter 2 of the book, Oracle Application Server 10g Administration Handbook, written by John Garmany and Donald Burleson (McGraw-Hill/Osborne, 2004; ISBN: 0072229586).

TABLE OF CONTENTS:

By: McGraw-Hill/Osborne

Poor Best

Rating:

/ 12

May 05, 2005

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

There is an important log table 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 contains many detailed metrics about SSO interaction:

SQL> desc ORASSO.WWSSO_AUDIT_LOG_TABLE_T;

Name                    Null?               Type
-----------------------------------------------------------SUBSCRIBER_ID          NOT NULL NUMBER
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)

You can take the data from this table and create an SSO summary report for execution in SQL*Plus. Here is a common SSO activity report:

sso_audit_log.sql
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
select
   to_char(log_date,'yyyy-mm-dd hh24')c0,
action                             c1,
   count(*)                           c2
from
    ORASSO.WWSSO_AUDIT_LOG_TABLE_T
   group by
   to_char(log_date,'yyyy-mm-dd hh24'),
action;
prompt *************************************************** prompt SSO Message summary Report prompt*************************************************** col c1 heading 'message' format a20
select
to_char(log_date,'yyyy-mm-dd hh24') c0,
message                                            c1,
count(*)                                            c2
from
ORASSO.WWSSO_AUDIT_LOG_TABLE_T
group by
to_char(log_date,'yyyy-mm-dd hh24'),
message;
set lines 80
prompt ********************************************
prompt SSO Activity summary 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 a20select
log_date                                c1,
user_name                   c2,
action                      c3,
message                    c4
from
ORASSO.WWSSO_AUDIT_LOG_TABLE_T
;

The following listing shows the output from this report. Here you see a summary of all login operations, summed by hour of the day. You also see counts of all SSO messages summed by hour of the day. The last report in this section shows all SSO details.

*************************************************** 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

You can also write a script to check the availability of SSO. As noted earlier, if the infrastructure is down or SSO cannot accept connections, no users can access your system. Hence, frequently checking SSO connectivity is an important Application Server 10g administration task.

Here is a Perl script that you can use to check SSO availability. This script checks to see if the Single Sign-On (SSO) Server is accessible and is responding to HTTP requests.

check_sso.pl

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 found";
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" ;
if(!$this_socket){
    $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"); $returncode="POK";
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 can accept HTTP requests. Many Application Server 10g administrators 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 administrator. Now, let’s look at using the mod_osso utility for SSO administration.

Next: SSO Administration Using the mod_osso Utility >>