Using Password Files


Password files allow you to set passwords that are stored outside the database and that are used for authenticating administrators. These passwords are stored in an external file that is encrypted by Oracle. Password files can be even used if the database is down-so. You can use this passwords even if database is down .

To create a password file use the ORAPWD utility:

[oracle@orcl ~]$ orapwd file=./test_pwd entries=100 ignorecase=n
Enter password for SYS: 
[oracle@orcl ~]$ 

After creating the password file, you must set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to an appropriate value (  NONE, EXCLUSIVE and SHARED ).
NONE-causing Oracle to behave as though a password file does not exist. EXCLUSIVE means that the password file is being used only by your database and that you can modify it from within the database . SHARED-allows you to use a single password file for multiple databases, but none of them can update the password file. If you need to update the password file then you need to switch this parameter to EXCLUSIVE in one of the databases, change the password file and then change it back to be used as SHARED.

SQL> show parameter remote_login_passwordfile;

NAME				        TYPE	   VALUE
--------------------------  ----------- ---------------
remote_login_passwordfile	  string	   EXCLUSIVE

Now try connect to oracle as sysdba from other machine

C:UsersValeh>sqlplus sys@db11g as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jul 5 00:17:21 2015
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Secure access to listener


Listener-ə müraciətin təhlükəsiz təşkili listenerin vacib təhlükəsizlik tədirləridən biridir, çunki serverinizə şəbəkə vasitəsilə hücum təşkil edib listeneri ələ keçirən şəxslər listeneri idarə edərək sizin sizin DB-nı sondürə bilər. Lsnrctl vasitəsilə listeneri idarə etməyin qarşısını almaq üçün şifrə təyin olunmalıdır. Əgər lsnrctl status komandasının nəticəsində Security sətrində OFF yazılıbsa deməli listener üçün şifrə təyin olunmamışdır.Oracle 10g and 11g-də listener  üçün susmaya görə Local OS authentication adlanan təhlükəsizlik növü təyin olunur. Local OS authentication o deməkdir ki, siz listeneri  idarə edə bilmək üçün listenerin işlədiyi host-a login olmuş olmalısınız. Biz bunu lsnrctl status komandasını işlədərək  aşağıdakı nəticədə görə bilərik

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-JUN-2015 01:44:59

::::::::::::::output trimmed::::::::::::::

------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                09-JUN-2015 01:17:51
Uptime                    0 days 0 hr. 27 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
::::::::::::::output trimmed::::::::::::::

[/pcsh]

Local OS Authentication-un ləğv edilməsi

Local OS Authentication ləğv etmək üçün listener.ora faylını redaktə edərək local_os_authentication_LISTENER parameterinə OFF  qiymətini mənimsətmək lazımdır:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
#---- OUTPUT TRIMMED ----#
local_os_authentication_LISTENER=off
 

[/pcsh]

Listener-ə açıq tekst formatında şifrənin təyini

Listener-ə açığ text tipli şifrə təyin etmək üçün $ORACLE_HOME/network/admin/listener.ora faylını redaktə edib PASSWORDS_{LISTENER_ADI}=sizinşifrə sətrini əlavə etmək lazımdır.
Biz həmşinin listener-ə bir neçə şifrədə təyin edə bilərik:   PASSWORDS_LISTENER=(password1,password2)

Nümunə:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.localdomain)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

#clear type password
PASSWORDS_LISTENER=(test123,test654)

[/pcsh]

Listenerə heşlənmiş şifrənin təyin olunması

Listenerə heşlənmiş şifrə təyin etmək və ya redaktə etmək və yadda saxlamaq üçün set password, change_password və save_config komandalarından aşağıdakı qaydada istifadə etmək lazımdır :

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

#using set password
LSNRCTL> set password
Password: 
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain)(PORT=1521)))
No changes to save for LISTENER.
The command completed successfully
LSNRCTL> 

#using change password
LSNRCTL> change_password 
Old password:  
New password:  
Reenter new password:  
Connecting to 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain)(PORT=1521))) 
Password changed for LISTENER 
The command completed successfully 
LSNRCTL> save_config 
Connecting to 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain)(PORT=1521))) 
Saved LISTENER configuration parameters. 
Listener Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora 
Old Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.bak 
The command completed successfully 

[/pcsh]

Şifrə təyin edildikdən sonra  security sətri ​Password or Local OS authentication yazısına dəyişir.

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

LSNRCTL> status 
::::::::::::::output trimmed:::::::::::::: 
Security                  ON: Password or Local OS Authentication 
SNMP                      OFF 
::::::::::::::output trimmed:::::::::::::: 

[/pcsh]

Şifrə listener.ora faylında heşlənmiş formatda saxlanılır, listener.ora faylının işinə baxmaqla bunu görmək olar:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

 
[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora 
 
LISTENER = 
  (DESCRIPTION_LIST = 
    (DESCRIPTION = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.localdomain)(PORT = 1521)) 
    ) 
  ) 
ADR_BASE_LISTENER = /u01/app/oracle 
 
#----ADDED BY TNSLSNR 09-JUN-2015 01:56:41--- 
PASSWORDS_LISTENER = 9BD20802761D432E 
#-------------------------------------------- 

[/pcsh]

Listener üçün təyin olunmuş şifrəni ləğv etmək üçün aşağıdakı addımları etmək lazımdır:

İlk olaraq listeneri aşağıdakı komanda vasitəsilə dayandırıq

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
The command completed successfully
LSNRCTL>   

[/pcsh]

Daha sonra listener.ora faylını redaktə edərək şifrə olan sətri silirik və listeneri restart edirik.
Securing access to the listener is a one of the important aspects of listener security, because if the attacker can access to control the lsnrctl he can shut down your database. To limit access to the ability to control the listener through the lsnrctl you must set a password. If a security row is OFF in the result of the lsnrctl status command it means that there is no password set for the listener. In Oracle 10g and 11g the listener is securing by default using an option called Local OS authentication. Local OS authentication means that, you can control the listener if you are logged on to the account on the host where the listener is running. You can see it with lsnrctl status command:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-JUN-2015 01:44:59

::::::::::::::output trimmed::::::::::::::

------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                09-JUN-2015 01:17:51
Uptime                    0 days 0 hr. 27 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
::::::::::::::output trimmed::::::::::::::

[/pcsh]

Disable Local OS Authentication

To disable Local OS Authentication you must set local_os_authentication_LISTENER parameter to OFF in listener.ora file:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
#---- OUTPUT TRIMMED ----#
local_os_authentication_LISTENER=off
 

[/pcsh]

Setting clear text format  password for listener

To set a listener password in clear text format open the  $ORACLE_HOME/network/admin/listener.ora file and add a line to this file with the following format: PASSWORDS_{LISTENER_NAME}=yourpassword
If you want, you  can set multiple passwords for listener:   PASSWORDS_LISTENER=(password1,password2)

for example:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.localdomain)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

#clear type password
PASSWORDS_LISTENER=(test123,test654)

[/pcsh]

Setting an encrypted  password for listener

To set, change and save password you can use set password or change_password and save_config commands as shown above :

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

#using set password
LSNRCTL> set password
Password: 
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain)(PORT=1521)))
No changes to save for LISTENER.
The command completed successfully
LSNRCTL> 

#using change password
LSNRCTL> change_password 
Old password:  
New password:  
Reenter new password:  
Connecting to 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain)(PORT=1521))) 
Password changed for LISTENER 
The command completed successfully 
LSNRCTL> save_config 
Connecting to 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl.localdomain)(PORT=1521))) 
Saved LISTENER configuration parameters. 
Listener Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora 
Old Parameter File   /u01/app/oracle/product/11.2/db_1/network/admin/listener.bak 
The command completed successfully 

[/pcsh]

After setting password  security option of the listener changes to ​Password or Local OS authentication

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

LSNRCTL> status 
::::::::::::::output trimmed:::::::::::::: 
Security                  ON: Password or Local OS Authentication 
SNMP                      OFF 
::::::::::::::output trimmed:::::::::::::: 

[/pcsh]

The password is saved in listener.ora file as a hashed entry, you can check what was generated by looking in the listener.ora file:

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

 
[oracle@orcl ~]$ vi /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora 
 
LISTENER = 
  (DESCRIPTION_LIST = 
    (DESCRIPTION = 
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl.localdomain)(PORT = 1521)) 
    ) 
  ) 
ADR_BASE_LISTENER = /u01/app/oracle 
 
#----ADDED BY TNSLSNR 09-JUN-2015 01:56:41--- 
PASSWORDS_LISTENER = 9BD20802761D432E 
#-------------------------------------------- 

[/pcsh]

To remove the listener password you should  do the following:

First stop the listener

[pcsh lang=”plain” tab_size=”4″ message=”” hl_lines=”” provider=”manual”]

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
The command completed successfully
LSNRCTL>   

[/pcsh]

Then edit the listener.ora file and remove the lines added above and restart the listener.