20090821 a simple orasso audit script - plembo/onemoretech GitHub Wiki

title: A simple ORASSO audit script link: https://onemoretech.wordpress.com/2009/08/21/a-simple-orasso-audit-script/ author: lembobro description: post_id: 266 created: 2009/08/21 19:49:35 created_gmt: 2009/08/21 19:49:35 comment_status: open post_name: a-simple-orasso-audit-script status: publish post_type: post

A simple ORASSO audit script

The following is a fairly rough perl script that uses the SQL described in my last post for reporting out the last login date of each SSO user in an OAS (Oracle Application Server) 10g environment. While it doesn’t actually do much in the form reproduced here, I’ve sacrificed efficiency and style to make the logic easier to follow for those who might be new to this sort of thing.

#!/usr/bin/perl
# Query OAS 10g ORASSO Audit Log to see what last login date is for each user
# and do stuff with that info.
# Created 8/21/09 by Phil Lembo
	
use strict;
use DBI;
use Date::Calc qw(Today Decode_Month Add_Delta_Days Date_to_Days);
	
# Setting the user environment for the script, just in case it isn't
#  in the user's .bash_profile (your values will be different).
my $HOME = $ENV{'HOME'};
my $ORACLE_HOME = "/opt/oracle/instantclient_11_1";
$ENV{'TNS_ADMIN'} = "$HOME/etc";
$ENV{'LD_LIBRARY_PATH'} = "$ORACLE_HOME";
	
# Variables for ORACLE_SID, DB user, OID user and password
my $orclsid = "ebsdb01";
my $orclusr = "ORASSO";
my $oidHost = "oidprod.example.com";
my $oidUsr = "cn=orcladmin";
my $oidPass = "secret1234";
	
# Variables for LDAP search that retrieves the ORASSO schema password.
# Note the "" character indicates line continuation in this narrow window.
# $ssodn is the full distinguished name of the database entry where  the
# password is found.
my $ssodn = "OrclResourceName=$orclusr,orclReferenceName=$orclsid
,cn=IAS Infrastructure Databases,cn=IAS,cn=Products,cn=OracleContext";
my @ssoattrs = qw(cn orclresourcename orclpasswordattribute description);
my $ssoquery = "(objectclass=*)";
	
# Set up the LDAP Search
my $ldap = Net::LDAP->new($oidHost);
my $mesg = $ldap->bind($oidUsr, password =>$oidPass) or die $!;
$mesg = $ldap->search(
		base => $ssodn,
		scope => 'base',
		filter => $ssoquery,
		attrs =>@ssoattrs
  );
	
# Retrieve the entry from the stack
my $entry = $mesg->shift_entry();
# Get the password value
my $orclpw = $entry->get_value('orclpasswordattribute');
# Disconnect from the LDAP directory
$ldap->unbind;
	
# Connect to the infrastructure database
my $dbh = DBI->connect("dbi:Oracle:$orclsid",
			  	    "$orclusr",
				    "$orclpw",
	
			) or die "Database not connected: $DBI::errstr";
	
# Define the SQL query
my $sql = "select USER_NAME, MAX(LOG_DATE) from
           orasso.wwsso_audit_log_table_t
           where ACTION = 'LOGIN' group by USER_NAME";
	
# Make DBD::Oracle format the query for the Oracle RDBMS
my $sth = $dbh->prepare($sql);
# Fire away!
$sth->execute();
# Initialize the variable for the hash that will store query results.
my %loginfo;
# Loop through the results
while ( my($user_name,$log_date) = $sth->fetchrow()) {
	
     # Break out the date elements for further manipulation
     my($day,$month,$year) = split('-', $log_date);
	
     # Make year 4 digit (warning, only good until the year 2999!).
     $year = ($year + 2000);
	
     # Convert month string to numeric
     $month = Decode_Month($month);
	
     # Format month to two digit
     $month = sprintf("%02d", $month);
	
     # Format day to two digit
     $day = sprintf("%02d", $day);
	
     # Put the date data into YYYYMMDD format
     my $last_login = $year ."".$month."".$day;
	
     # Add user info to hash where user_name is key, last_login is value
     $loginfo{$user_name} = $last_login;
	
 }
	
# Finish the session
$sth->finish;
# Disconnect from the DB
$dbh->disconnect;
	
# Sort the hash of users and their last login dates, and print
foreach my $user_name(sort {$loginfo{$a}  $loginfo{$b}} keys %loginfo) {
	my $last_login = $loginfo{$user_name};
	print $last_login, “,”, $user_name, “n”;
}
	
__END__;

Just two quick comments on the above.

Some may wonder why I used so many different date formatting techniques here. Well, for one thing the DD-MM-YY default date format didn’t sort too good, so I converted it the cleaner YYYYMMDD format. Of course I could have put some SQL in to change the output format to something easier to sort, and I’ll probably do just that in the final version to avoid complicating the script more than it has to be.

Dumping the query results to a hash and then reporting out by sorting and then iterating over it may seem a little odd at first, but its a useful technique if you want to break up your code into smaller, specialized, units. The routine that sorts and parses the hash could actually go into a separate subroutine in a console or cgi script. In the final script that this code forms the basis for, the subroutine that actually prints the report will use at least 50-60 lines of code to test for various conditions like if the last login date qualifies the account as dormant under our security policy. A separate subroutine will handle messaging functions such as e-mailing the results to my security analysts.