 |
|
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:
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)
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:
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 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
select
log_date c1,
user_name c2,
action c3,
message c4
from
ORASSO.WWSSO_AUDIT_LOG_TABLE_T
;
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.
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 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:
- The user requests a URL through a Web
browser. This URL is intercepted by the Oracle HTTP server.
- 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.
- If the cookie does not exist on the HTTP
server, mod_osso redirects the user to the Single Sign-On server.
- 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.
- Upon successful sign-on, the SSO server then
returns the user's encrypted information to mod_osso.
- 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.
and include the URL for the page.
