1        Introduction

1.1        Purpose

Company guidelines for managing passwords define the password security policy. In so doing, all personnel are responsible for maintaining good password practices in their systems.

The purpose of this document is to describe how the password management policy can be introduced and maintained in an Oracle database environment.

1.2        Scope

The scope of this document is the policy, which should be adapted by all Database Administrators, Application Support personnel and all users who own accounts on Oracle databases.

1.3        Responsibilities

The overall responsibility for managing a password policy belongs to the local Database Administrators who are responsible for maintaining user access to Oracle Databases.

There are three categories of Oracle password, for which a specific owner can be identified.

• System Passwords. These are the internal Oracle passwords required to maintain a database and are usually created during database installation. Such accounts are ‘SYS’, ‘SYSTEM’, ‘DBSNMP’ and ‘OUTLN’. The local DBA is specifically responsible for maintaining the password for these type of accounts.
• Application Passwords. These are for accounts that own objects making up an application. It might be a single account such as ‘hyperion’ or spread over several accounts such as ‘DWHO’, ‘DWHD’ , etc. Also included within the Application category are passwords for any tools or utilities that access the database, such as Oracle Enterprise Manager and Quest Toad. Application Support is responsible for ensuring the security of these schemas and those accounts accessed by multiple users.
• Individual Passwords. Accounts owned by individuals who have authorisation to access a database. It is the individual user who is responsible for choosing and maintaining a secure password that follows IT password policy guidelines on his or her own account.

2        Policy

The following scripts and code show how the password policy can be managed within an Oracle database environment.

2.1        Password Rules

Password maximum age

Objective: to counter password misuse

Setting: MUST be set to 90 days

This can be set in the users profile:

CREATE PROFILE prof LIMIT   PASSWORD_LIFE_TIME 90;ALTER USER <username> PROFILE prof;

Password minimum length

Objective: to counter password guessing

Setting: MUST be set to 8 characters

Oracle’s password complexity verification routine can be specified using a PL/SQL script, $ORACLE_HOME/rdbms/admin/utlpwdmg.sql, which sets the default profile parameters. This function requires the package DBMS_STANDARD to be installed and enabled.

The password complexity verification routine, see Appendix A, performs the following checks:

• The password has a minimum length of 4,

utlpwdmg.sql can be modified in the following way:

Password minimum length

Change

IF length(password) < 4 THEN

To

IF length(password) < 8 THEN

Password syntax checking

Objective: to avoid users choosing weak passwords

Setting: syntax setting MUST be enforced. Passwords MUST meet the following complexity rules:

–          At least 5 characters of the password MUST be different

–          The alphabet referred to in the rule above is the ISO standard 647 (US ASCII); for locales, which do not include this alphabet, no complexity rules are prescribed.

utlpwdmg.sql does not check for the following rule.

• At least 5 characters of the password MUST be different

Script, $ORACLE_HOME/rdbms/admin/utlpwdmg.sql can be modified to include a new test. Here is some anonymous PL/SQL code to do that.

m := length(password);

For i IN1..m LOOP

IF  nvl(instr(z,substr(password,i,1)),0)=0THEN

z := z||substr(password,i,1);

ENDIF;

ENDLOOP;

IF length(z)<5THEN

Raise_application_error(-20009,’Password should have at least 5 unique characters’);

ENDIF;

Objective: to avoid users choosing weak passwords

Setting: syntax setting MUST be enforced. Passwords MUST meet the following complexity rules:

–          The password MUST comprise at a minimum 1 character that is not an alphabetic character, a digit or a hyphen (-). At least one of such a character MUST appear at a position that is NOT the first, the second or the last and one but the last position of the password.

$ORACLE_HOME/rdbms/admin/utlpwdmg.sql provides for the following check

• The password has at least one alpha, one numeric, and one punctuation mark.

the script can be amended to include the extra code as follows:

/* Remove first, last and second from last characters from pwd, then test that at least one special character exists there.*/

m := length(password);

z := substr(password,2, m -2);

m := length(z);

isvalid:=FALSE;

FOR i IN1..length(punctarray)LOOP

FOR j IN1..m LOOP

IF substr(z,j,1)= substr(punctarray,i,1)THEN

isvalid:=TRUE;

GOTO endisvalid;

ENDIF;

ENDLOOP;

ENDLOOP;

<<endisvalid>>

IF isvalid=FALSETHENraise_application_error(-20010,’Password should contain one special character, not in the first, last, or second from last positions’);

ENDIF;

Objective: to avoid users choosing weak passwords

Setting: syntax setting MUST be enforced. Passwords MUST meet the following complexity rules:

–          The alphabet referred to in the rule above is the ISO standard 647 (US ASCII); for locales, which do not include this alphabet, no complexity rules are prescribed.

$ORACLE_HOME/rdbms/admin/utlpwdmg.sql conforms to ISO standard 647.

Weak Passwords

$ORACLE_HOME/rdbms/admin/utlpwdmg.sql has the following additional standard syntax checks:

• The password is not the same as the userid.
• The password differs from the previous password by at least 3 letters.
• The password does not match simple words like welcome, account, database, or user.

The administrator should add additional words to the matching pattern. For example:

‘manager, change_on_install,oracle123,admin,davidbeckham’ and other easily guessed passwords.

Password history

Objective: to counter password guessing

Setting: MUST be set to 6 passwords that are remembered

CREATE PROFILE prof LIMITPASSWORD_REUSE_MAX 6PASSWORD_REUSE_TIME UNLIMITED;

Password expiration warning

Objective: to inform the users on the need to change their password before it actually expires

Setting: MUST be set to 14 days before password expiration.

Oracle does not support this feature, except where the user has not logged in after the password expiration date and the grace period is invoked.

CREATE PROFILE prof LIMIT   PASSWORD_GRACE_TIME 14;

Password lockout

Objective: to counter (automated) password guessing

Setting: lockout MUST be enabled

See next paragraph.

Password lockout count

Objective: to allow users to re-enter their password in case of user errors

Setting: the users account MUST be locked after 3 failed authentication attempts

 

CREATE PROFILE prof LIMIT   FAILED_LOGIN_ATTEMPTS 3

Password lockout duration

Objective: to allow users to authenticate again after being locked out due to a number of mistakes

Setting: MUST be set to 5 minutes

In Oracle a user has three login attempts from an Oracle utility such as ‘sqlplus’, after which the connection command must be re-submitted. Client packages have their own authentication routines.

Safely modify a password

Objective: to avoid unauthorized password modification

Setting: users MUST specify their current password before they can change it

In Oracle this can be achieved by replacing the regular password change command with an external programmic construct that uses an Oracle Call Interface to the function OCIPasswordChange(). The development of these external programs (C, C++, Java, etc) is beyond the scope of this document.

Password must change

Objective: to force the users to change their password after they authenticate for the first time with the authentication service

Setting: password change MUST be enabled

CREATE USER <username> IDENTIFIED <identity clause> PASSWORD EXPIRE;

2.2        Display last logon

Objective: to inform users on a possible abuse of their account

 

Not always appropriate in a client program connection, particularly in multi-tiered applications where clients connect using a shared user identification, and user authorisation is handled outside of the database.

For individual accounts this feature may be implemented by use of a database login trigger, which captures login information into a user-defined table.

CREATE TABLE user_login_details (last_login date, new_login date);

CREATE OR REPLACE TRIGGER update_user_login AFTER LOGIN

BEGIN

UPDATE user_login_details

SET new_login = (select sysdate from dual);

SELECT ‘Last login date >> ‘||to_char(last_login,’DD-Mon-YYYY HH24:MI’)

FROM user_login_details;

UPDATE user_login_details

SET last_login = new_login;

COMMIT;

END;

This trigger will update a table in the user’s schema with the login time, and display the last login time of that user.

3        Using and Configuring Verify_Function

3.1        Usage Notes

It is recommended to use verify_function to enforce password policy. There are limitations with its use, however.

1. Only user SYS can change passwords for users with the verify_function parameter set in their profile.
2. This restriction may be overridden by granting users ‘ALTER USER’ privilege on their own accounts.
3. If ‘ALTER USER’ privilege is granted to users, they will have the ability to change their profile to another that does not use password validation features.
4. Therefore administrators who allow the ‘ALTER USER’ privilege to users (because users wish to maintain their own passwords), should check that users do not later change their profile.

3.2        Specification

The verify_function must have the following specification

verify_function(userid_parameter IN VARCHAR(30),password_parameter IN VARCHAR (30),old_password_parameter IN VARCHAR (30))RETURN boolean

After this routine is created, it must be assigned as the password verification routine using the user’s profile or the system default profile.

CREATE/ALTER PROFILE profile_name LIMITPASSWORD_VERIFY_FUNCTION routine_name

The password verify routine must be owned by SYS.

4        Oracle Listener Password

Oracle security alerts issues warnings against breaches in the security of certain Oracle products. Among these is the Oracle listener. Oracle Corporation recommends using a password to perform listen actions such as starting and stopping the listener.

In listener.ora

PASSWORDS_listener_name
Purpose:	Sets a non-encrypted password for authentication to the Listener Control Utility (LSNRCTL). Allows one or more passwords. If this optional parameter specifies one or more passwords, then the use of one of these passwords is required to perform DBA tasks against the network listener using the LSNRCTL.
Default:	oracle
Example:	passwords_listener=(oracle8)

When using LSNRCTL utility

CHANGE_PASSWORD
Purpose:	Allows you to dynamically change the encrypted password of a listener. This will not change unencrypted passwords already established in a listener configuration file. It only establishes a new password, or changes a password that has been encrypted in the listener configuration file.
Prerequisites:	None
Password required if one has been set:	YesIf a password is set, the SET PASSWORD command must be issued prior to this command.
Syntax:	From the operating system:lsnrctl change_password [listener name]From the LSNRCTL program: lsnrctl> change_password [listener name]
Arguments:	[listener_name]
Usage Notes:	The control utility prompts you for your old password, then for the new one. It asks you to re-enter the new one, then changes it. Neither the old nor the new password displays during this procedure.
Example:	lsnrctl> change_passwordOld password:New password:Reenter new password:Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=iris))Password changed for LISTENERThe command completed successfully

 

SET PASSWORD
Purpose:	Changes the password sent from the LSNRCTL utility to the listener process for authentication purposes only. To change the password on the listener itself, use the CHANGE_PASSWORD command.
Syntax:	From the operating system:lsnrctl set passwordFrom the LSNRCTL program: lsnrctl> set passwordThe syntax for unencrypted passwords is: set password password
Arguments:	password
Usage Notes:	You may enter this command when you start up the shell or any time during your session. (You must enter the SET PASSWORD command before you can stop the listener.)The preferred, secure way to enter your password is in interactive mode. The listener supports encrypted and unencrypted passwords.
Example:	lsnrctl> set passwordenter listener password: password

4.1        Running listener commands from a batch file

You will have to code your script to perform the same steps as if you were to stop the listener via command line.
You cannot perform the stop on a single command line entry it will take the multiple steps.
LSNRCTL <enter>
LSNRCTL> set current_listener listenename <enter>
LSNRCTL> set password password <enter>
LSNRCTL> stop listenername <enter>

4.2        Runtime Modifcation of listener

You can alse set ADMIN_RESTRICTIONS_listener_name=ON in listener.ora to completely disable the runtime modification of listener’s configuration parameters.