Wednesday

11i and Listener Passwords

A standard practice for any Oracle database should be to enable passwords on your listeners. EBS is no exception and recently Oracle published a metalink note: 386374.1 on how to do this for 11.5.x environments.

There are some pre-reqs, specifically:

3453499 (11i.ADX.F)
5225940 (Post ADX.F Fixes)
5107107 - AUTOCONFIG ENGINE & CONFIG TOOLS ROLLUP PATCH N or higher

The note has detailed steps on how to set the password at the application and database tiers.

In my opinion, just as important as setting a password, is to monitor for invalid attempts after. Otherwise whats the point? At best you will only slow down a determined intruder. Once you have set a password, any attempt to execute a command via lsnrctl (without setting your password first) will result in the following error showing up in your listener log:

TNS-01169: The listener has not recognized the password

Each DBA has his preferred language for scripts. Some prefer shell, others perl. Myself, I prefer perl. Below is a perl script I have used to monitor the listener log for the above error.


#!/usr/bin/perl -w

if ($#ARGV !=1) {
die "Usage: check_alert.pl ex. hostname /var/opt/oracle/listener.log.\n";
}
sleep 2;

$interval=60; # How many seconds before we check to see if data has been written to the logfile;
$email_threshold=5; # How many errors within the interval before an email gets sent;
$hostname=$ARGV[0];
$file=$ARGV[1];
open(filePtr, $file) or die "Can't find $file\n";


for (;;) {
@errors=("Subject: Listener Password Errors for $hostname\n");
$currTime = localtime(time);
push(@errors,"Here are some errors found at $currTime for $hostname.\n");

while () {
chop $_;
if (/TNS-01169/) {
push(@errors, "$_\n");
}
}

if ($#errors > $email_threshold) {
$rndExt = time;
$rndFile = "alert_errors_$rndExt";
open (TMPFILE, ">/tmp/$rndFile");

foreach $error (@errors) {
print TMPFILE $error;
}
close(TMPFILE);
system ("mail username\@mycompany.com < /tmp/$rndFile");
system ("rm /tmp/$rndFile");
}

sleep $interval;
seek filePtr, 0, 1;
}




The script takes two parameters: <hostname> <path_to_listener_log>

A couple of key things you may want to customize in the above script are the $interval and $email_threshold variables. As well, there is an email address towards the bottom where alerts will be sent.

Now, I am by no means a perl expert but the above works for me. I'm sure there are some more efficient ways to monitor the log file. Feel free to post a comment with your preferred method. If you cycle your logfiles keep in mind you'll need to restart this script. I've monitored resource consumption and its pretty minimal but I know some DBA's will prefer not having a process run constantly.

Note: I've only tested this script on a 9i listener log. Its on my todo list to test it on 8.0.6.

UPDATE: Thanks to Howard Rogers for informing me of the 10g changes. Security has been enhanced such that the TNS listener administrator has to be a member of the local dba group. Unless you plan on administering your listener remotely then you don't need a password. Please see the comments and Metalink note: 340009.1

Most of our databases are still 9i but i'll be sure to remove the listener passwords as part of the upgrades.

5 comments:

Howard Rogers said...

You are actually recommended NOT to set a password for your listener in 10g and above, because by default it's only accessible by those with membership of the ORA_DBA group (or dba group on Unix/Linux). Setting a password means it's now open to abuse by anyone who happens to know the password.

Passwords are easy to guess/crack. Group memberships are a bit of a tougher nut to crack.

Pete Finnigan said...

Thanks for your very useful script. I seem to remember a script a few years ago posted by Steve Perry (i think that was his name) that analysed listener logs written in Perl but he didnt make it public, he invited those intrested to email him i think.

Howard: The post is about APPS and 11i not 11g, Dave says he has tested on 9i only and not the 8.0.6 listeners yet, so for him setting a password is good advice as there is no local authentication on an 8.0.6 listener or a 9i listener.

cheers

Pete

Dave said...

Hey guys, thanks for the feedback. Howard, thanks for informing me of the changes in 10g. Pretty much all of our environments are still on 9i and I wasn't aware that the behavior and best practices have changed.

While I was typing up that post I reviewed a few metalink notes on setting a listener password and not one mentioned the 10g changes. Thats kinda disappointing.

A google search tho turned up Andrew Frasers blog which mentions metalink note: 340009.1. I believe you helped him as well. I'll update my post.

Tnx again!

Sridevi Koduru said...
This comment has been removed by the author.
Sridevi Koduru (Oracleappstechnical.com) said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.