Archives of the TeradataForum

Message Posted: Fri, 10 Feb 2006 @ 10:14:05 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Thursday, February 09, 2006 23:27 -->

We occasionally audit such things on an ad-hoc basis. Our support staff do get access to the database user password via the ETL tool, but they don't have access to the operating system account that is used to initiate the ETL tool jobs.

So what we look for is logonoff records for the batch db user where the operating system username used for the batch processing is not in the logonsource text. The LAN logon of the support staff member is typically shown instead.

Preventing it - we haven't managed to automate yet.

Here is a (very) rough and ready short perl script we use for the audit. It takes 3 parameters - the database username, the os batch username and the date you want to audit from. It prints the date and time of each logon, the db username, the os (typically LAN) username and the utility used. We really should productionise it and run it more regularly.

     #!/opt/local/bin/perl -w
     use DBI;

     $dbh = DBI->connect("dbi:ODBC:devdwh", "auser", "apssword")
                                                             or die "Can't
     connect";

     $dbuser = shift;
     $osuser = '%' . (shift) . '%';
     $logdate = shift;

     $sql = "select logondate,
                    logontime,
                    username,
                    logonsource
             from dbc.logonoff where username = ?
             and logonsource not like ?
             and logondate >= ?
             and event = 'logon'
             order by logondate, logontime";

     $sth = $dbh->prepare($sql) or die "Couldn't prepare statement: " .
     $dbh->errstr;
     $sth->execute($dbuser, $osuser, $logdate)
                             or die "Couldn't execute statement: " .
     $sth->errstr;

     while (@data = $sth->fetchrow_array()) {
         ($logondate = $data[0]) =~ s/\s+$//;
         ($logontime = sprintf "%06d", $data[1]);
         ($username = $data[2]) =~ s/\s+$//;
         ($logonsource = $data[3]) =~ s/\s+$//;
         ($logonuser, $tool) = $logonsource
                             =~
     /\S+\s+\S+\s+\S+\s+\S+\s+\S+\s+(\S+)\s+(\S+)/;

         printf("%s\t%s:%s:%s\t%s\t%s\t%s\n", $logondate,
     substr($logontime,0,2),
                 substr($logontime,2,2),substr($logontime,4,2), $username,
                 $logonuser, $tool);
     }
     $sth->finish;
     $dbh->disconnect;

	<Prev	Next>		<<First	<Prev	Next>	Last>>