Setting Password for database listener

Introduction

This is a simple post which explains you how to secure your listener by setting the password authentication.
By default in Oracle 8i/9i database, we can remotely administer the listener using lsnrctl command line utility. In Oracle Database 10g, this feature is disabled by default and we cannot by default administer the listener remotely. However in database 10g, there is a parameter LOCAL_OS_AUTHENTICATION_<listener_name> which can be set to OFF in order to enable remote listener administration.
How to administer listener remotely

Following are the activities we can do in remote listener administration
  • Stop the Listener
  • Set a password and prevent others from controlling the Listener
  • Write trace and log files to any file accessible to the process owner of tnslnsr (usually oracle)
  • Obtain detailed information on the Listener, database, and application configuration
Lets say I have a database sitst01 and listener name is same as DB name.
-bash-3.00$ lsnrctl start sitst01
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:09:36

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /slot/ems3079/oracle/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production

System parameter file is /slot/ems3079/oracle/db_1/network/admin/listener.ora
Log messages written to /slot/ems3079/oracle/db_1/network/log/sitst01.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))

STATUS of the LISTENER
------------------------
Alias                     sitst01
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                13-MAY-2010 11:09:36
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      ON
Listener Parameter File   /slot/ems3079/oracle/db_1/network/admin/listener.ora
Listener Log File         /slot/ems3079/oracle/db_1/network/log/sitst01.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))
Services Summary...
Service "sitst01.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
Service "sitst01_DGMGRL.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
My listener.ora file looks like this
-bash-3.00$ cat listener.ora
# listener.ora Network Configuration File: /slot/ems3079/oracle/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SITST01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adc60006sems.us.oracle.com)(PORT = 1600))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCsitst01))
)
)

SID_LIST_sitst01=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=sitst01.us.oracle.com)
(SID_NAME=sitst01)
(ORACLE_HOME=/slot/ems3079/oracle/db_1))
(SID_DESC=
(GLOBAL_DBNAME=sitst01_DGMGRL.us.oracle.com)
(SID_NAME=sitst01)
(ORACLE_HOME=/slot/ems3079/oracle/db_1))
)

LOCAL_OS_AUTHENTICATION_SITST01=OFF

Note that I have enabled remote listener authentication using LOCAL_OS_AUTHENTICATION_SITST01=OFF
On second server (sitst02), I am setting this listener alias (sitst01) of first server in listener.ora
listener.ora on second server looks like this
<span style="font-family: monospace;">
</span>

-bash-3.00$ cat listener.ora
# listener.ora Network Configuration File: /slot/ems6826/oracle/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SITST02 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adc60011sems.us.oracle.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCsitst02))
)
)

SID_LIST_sitst02=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=sitst02.us.oracle.com)
(SID_NAME=sitst02)
(ORACLE_HOME=/slot/ems6826/oracle/db_1))
(SID_DESC=
(GLOBAL_DBNAME=sitst02_DGMGRL.us.oracle.com)
(SID_NAME=sitst02)
(ORACLE_HOME=/slot/ems6826/oracle/db_1))
)

SITST01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adc60006sems.us.oracle.com)(PORT = 1600))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCsitst01))
)
)

I added sitst01 alias enter in listener.ora of second server
Reload the listener on second server, so that it will know the new listener alias that we have registered
-bash-3.00$ lsnrctl reload sitst02

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:13:23

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60011sems.us.oracle.com)(PORT=1521)))
The command completed successfully

Now lets administer the listener on first server from second server
On the second server, check the status of first listener (sitst01)

-bash-3.00$ lsnrctl status sitst01
LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:14:11
Copyright (c) 1991, 2007, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
STATUS of the LISTENER
------------------------
Alias                     sitst01
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                13-MAY-2010 11:09:36
Uptime                    0 days 0 hr. 4 min. 35 sec
Trace Level               off
Security                  OFF
SNMP                      ON
Listener Parameter File   /slot/ems3079/oracle/db_1/network/admin/listener.ora
Listener Log File         /slot/ems3079/oracle/db_1/network/log/sitst01.log

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))
Services Summary...
Service "sitst01.us.oracle.com" has 2 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
Instance "sitst01", status READY, has 1 handler(s) for this service...
Service "sitst01_DGMGRL.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

You can also stop the listener on first server, from second server

-bash-3.00$ lsnrctl stop sitst01

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:15:44

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
The command completed successfully

Password Protecting listener

To avoid the above scenario, you can protect the listener using password authentication. You have 2 methods of doing the same
1) Setting the clear text password in listener.ora file
2) Setting the encrypted password using LSNRCTL prompt
We will see both the methods now.
1) Setting clear text password in listener.ora file

This is not a recommended method for setting password. Here is how it works.
1) stop the listener
-bash-3.00$ lsnrctl stop sitst01

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:15:44

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
The command completed successfully

2) Add following parameter in listener.ora
PASSWORD_<listener_name>=<password>
Example:
PASSWORDS_SITST01=welcome
3) start the listener
-bash-3.00$ lsnrctl start sitst01

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:20:11

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /slot/ems3079/oracle/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /slot/ems3079/oracle/db_1/network/admin/listener.ora
Log messages written to /slot/ems3079/oracle/db_1/network/log/sitst01.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
STATUS of the LISTENER
------------------------
Alias                     sitst01
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                13-MAY-2010 11:20:11
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Password
SNMP                      ON
Listener Parameter File   /slot/ems3079/oracle/db_1/network/admin/listener.ora
Listener Log File         /slot/ems3079/oracle/db_1/network/log/sitst01.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))
Services Summary...
Service "sitst01.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
Service "sitst01_DGMGRL.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Now try doing any activity from remote server or even this server. It will give error
-bash-3.00$ lsnrctl status sitst01

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 13-MAY-2010 11:20:54

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
TNS-01169: The listener has not recognized the password

In order to carry out any activity on the listener, you need to first set the password at LSNRCTL prompt using “set password” command as shown below
LSNRCTL> set current_listener sitst01
Current Listener is sitst01
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
TNS-01169: The listener has not recognized the password
LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
STATUS of the LISTENER
------------------------
Alias                     sitst01
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                13-MAY-2010 11:22:45
Uptime                    0 days 0 hr. 0 min. 34 sec
Trace Level               off
Security                  ON: Password
SNMP                      ON
Listener Parameter File   /slot/ems3079/oracle/db_1/network/admin/listener.ora
Listener Log File         /slot/ems3079/oracle/db_1/network/log/sitst01.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))
Services Summary...
Service "sitst01.us.oracle.com" has 2 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
Instance "sitst01", status READY, has 1 handler(s) for this service...
Service "sitst01_DGMGRL.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
The command completed successfully

So unless we use “set password” and enter the password we have set in listener.ora we cannot perform any activity.
2) Setting the encrypted password using LSNRCTL prompt
LSNRCTL> set current_listener sitst01
Current Listener is sitst01
LSNRCTL> change_password
Old password:
New password:
Reenter new password:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
Password changed for sitst01
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
TNS-01169: The listener has not recognized the password
LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
STATUS of the LISTENER
------------------------
Alias                     sitst01
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                13-MAY-2010 11:22:45
Uptime                    0 days 0 hr. 0 min. 34 sec
Trace Level               off
Security                  ON: Password
SNMP                      ON
Listener Parameter File   /slot/ems3079/oracle/db_1/network/admin/listener.ora
Listener Log File         /slot/ems3079/oracle/db_1/network/log/sitst01.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adc60006sems.us.oracle.com)(PORT=1600)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROCsitst01)))
Services Summary...
Service "sitst01.us.oracle.com" has 2 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
Instance "sitst01", status READY, has 1 handler(s) for this service...
Service "sitst01_DGMGRL.us.oracle.com" has 1 instance(s).
Instance "sitst01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Use “change_password” command to set the password for listener.
Now if you see listener.ora file it will have encrypted password updated in it
</pre>
</span></div>
<div>#----ADDED BY TNSLSNR 13-MAY-2010 11:27:55---</div>
<div>PASSWORDS_sitst01 = 125916DFCEFE8F08</div>
<div>#--------------------------------------------</div>
<div><span style="font-family: monospace;">
<pre>

Hope this helps !!
Reference:

One Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s