Home arrow Oracle arrow Page 10 - The Oracle Application Server 10g Infrastructure

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:
  1. The Oracle Application Server 10g Infrastructure
  2. The Infrastructure Repository
  3. Workflow iasdb Schemas
  4. Viewing the Whole iasdb Instance
  5. The Infrastructure Log Tables
  6. Writing Your Own Infrastructure Repository Log Scripts
  7. Infrastructure Log Reports
  8. Repository Administration and Management
  9. Single Sign-On (SSO)
  10. Using the SSO Audit Log Tables
  11. SSO Administration Using the mod_osso Utility
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 12
May 05, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: