Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle Password Management

Oracle Password Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.

Password Management Function

The HIPAA requirements, as well as good security management practices, demand that the hacker be discouraged as much as possible from guessing the password. This prevents the kind of attacks called brute force. In this approach, the hacker employs a dictionary of words which can be potentially used in the password, makes up combinations of them, creates users with that password and then matches the hashed version of the password with the target user's password, also hashed.

To discourage this, the password should not be too easy to guess. Some of the most commonly used passwords are words like "secret", "password", "topsecret", even "abc123". These are too obvious and should never be allowed in a password. Similar examples include the username itself; you would never want the user JUDY to have a password JUDY, would you?

Some Tips for Password Management

We need to have a solution to securely pass the password to the program to make it automated. Here are a few ways.

One option is to create an environmental variable, say, USERPASS passed to script as

sqlplus ?s judy/$USERPASS @report

This is still vulnerable to attack via the /usr/ucb/ps command. The problem with environmental variables is they are visible to all users on the system, without the user having to hack into the environment. Executing

/usr/ucb/ps uxgaeww

shows all the environmental variables and the values used by all the users currently logged in to the system, including root's. The /usr/ucb/ps call is present for compatibility with BSD.

This variable may be set in a file that could be hidden. It's simple but not very secure. Another option is to create a file of passwords named .passlist . Note the period at the beginning. This makes the file invisible in a routine examination. This file has a permission set as 600, i.e. no privileges to anyone other than the owner. Here are the contents of the file

judy 5ucc355
nathan fr33w!113y

and so on. As you see, it has the usernames and passwords of all users. Next we will create a shell script to use this file named Note the period at the beginning of the file. It makes it invisible in a regular ls ?l command, too. Here is how the script looks.

fgrep $1 $HOME/.passlist | cut ?d " " ?f2

When the user issues sqlplus, he or she would issue | sqlplus ?s judy @report

The program will retrieve the password and feed it to the sqlplus executable. An execution of ps ?aef on the UNIX prompt will not show the password.

* Tip: Make sure that no one types any kind of password in the command line. If passwords need to be passed from the command line, use a secured file to store the password and then use redirection to feed it to the program.

After we establish that all the users connecting to the database must be users with passwords, the next important question is how we can make sure that the password authentication can be enforced in an acceptable manner.

As we discussed in Section I, passwords are like keys to a house, just as a malicious person can break into your house if he or she can get the key, a hacker can enter into the database if he or she learns the password. It's even worse since the password theft may not be noticed by the user. Therefore, management of passwords becomes a key security issue.

HIPAA regulations clearly mandate that password management policies be established in order to ensure that they are safe. So, what are the potential threats in the management of passwords?

Passwords can be learned by the hacker in several ways. Typically, the blame lies with the lax attitude of the authorized user. Some users write their password on some sort of paper ? be it their diary or a yellow sticky note affixed to the monitor. All the thief has to do is go to the monitor and physically get it.

What are the other ways? The thief may be able to guess the password. Some users make up a password that is easy to remember ? names of their spouses, children, pets, parents, maiden names, or numbers like Social Security Numbers, the date of birth, house numbers, telephone numbers, or a combination of all these. In that case, it becomes easier to guess a password. Compare the user JUDY's password, "fred1211" as opposed to "Pr3tt1f@c3".

The first one is based on her husband's name (Fred) and his birthday, December 11th (1211). If a thief knows her even a little socially, he will be able to guess the password. The second one, based on a pet name her husband bequeathed to her (prettyface), is contorted to become a list of characters, numbers and symbols that are easy to remember but difficult to guess.

What if someone learns her password? It has to be changed immediately. In fact, it is a good policy to change it frequently so that even if someone has the persistence to go through all the combinations and eventually figure out the password, it might have changed in the meantime.

What if someone persistently tries to guess the password? It is normal for Judy to forget her password occasionally, so a failed login attempt with an invalid password may not be a sign of an attempted forced entry. However, a large number of failed attempts do indicate attacks. The best approach at that time is to lock the account, or disable it from any further access.

Profile Enforced Password Management

A password management policy can be established and this system can be built into the database so that all passwords are set according to the policy. In Oracle, this policy is enforced by a concept called a profile.

A profile can be created by the CREATE PROFILE command, and this profile can be attached to the policies of established password management. The parameters passed to the profile and their descriptions are explained in Table 4.3.




The number of failed attempts made to login to the account before the account is locked out. In our example, we set it to 3, i.e. after 3 failed attempts to logon, the account is locked.


The password must be changed frequently to discourage guessing. This parameter indicates the number of days a password can be used before it becomes invalid automatically. In our example, we set it to 30.


Some users cheat the system by using the same password used earlier. This parameter makes sure that the same password cannot be used within a designated number of days. In this example, we set it to 90.


To discourage the use of the same password by the users, a certain number of characters specified by this parameter must be changed before the password can be changed again. Please note, both password reuse time and max cannot be used at the same time, only one should be set. In this example, since the time is set, max is not set.


A function to verify that the password conforms to the security standards. This is described in detail later.


After an account is locked out, this much time must pass before it is unlocked. In our example, we have the DBA reset the password immediately after the investigation; however, to prevent the hacker from somehow tricking the system to reset it, we can set this to 30 minutes.


When a password is expired, the user must change his password; however, a grace period may be given during which the connection is allowed with the old password, but a warning is issued. In this example, we will set the grace period to 5 days.

Table 4.3 Password Features in Profile

Based on the above, we can establish a policy for the management of passwords and easily construct a profile that enforces this policy. Following is the SQL statement that can be used to create the policy.

* create_policy.sql

-- Copyright ? 2003 by Rampant TechPress Inc.
-- Free for non-commercial use.
-- For commercial licensing, e-mail
-- *********************************************

CREATE PROFILE senior_claim_analyst LIMIT

Note the use of the password features we decided to use in our example. Now we can assign this policy to a specific user.

alter user judy
profile senior_claim_analyst

In the same manner, we can easily set up profiles for junior and middle level claim analysts, and assign them to the appropriate users based on their roles. Why did we set up different profiles for different levels of analysts? Shouldn't it be same for all users? In theory, the profile should indeed be the same for all analysts. However, some of the resource limits are different for different types of claim analysts, and therefore the profiles are different.

Simply by creating a profile and attaching it to a user, or a set of users, you achieved the first requirement in secure password management, a very important HIPAA requirement. For the sake of other types of security, you shouldn't stop here, you should categorize all the users and create profiles for each group. Nevertheless, for HIPAA this should be enough.

* Achieve immediate compliance of the law by creating and documenting the password features of the user profiles.


Verifying Oracle passwords script

Oracle provides a way to verify that the password chosen by the user satisfies your norm. First, you have to define what is or is not a good password. Some of the rules could be:

* Should not be the same as the username.

* Should not be one of "secret", "topsecret", "abc123", "password", "oracle", "database" or a variant of those words, such as "secret7".

* Should have at least one number, one character and one special character (e.g. w!nst0n is a good password, easy to remember as "Winston", but with the variants of characters as numbers, it is difficult to guess).

* Should be at least 6 characters (the longer passwords are difficult to crack by automated programs, since they have more permutations to go through).

* Should not be close to the old password. In this case, we specify that any three characters of the old password should not be used in the new one.

With these restrictions in mind, we will create a password complexity enforcement function as follows.

* password_check.sql

create or replace function password_check
   (p_username varchar2,
   p_new_password varchar2,
   p_old_password varchar2)
return boolean is
   i               number;
   j               number;
   scanlen         number;
   l_differ        number;
   numpresent      boolean := false;
   alphapresent    boolean := false;
   scpresent     boolean := false;
   sclist        varchar2(25) :=
   alphalist       varchar2(52) :=

-- check 1: is password same as the username
if p_new_password = p_username then
         'password cannot be the same as the userid');
end if;

-- check 2: is the password in the list of banned words
-- or a variant.
if (
   upper(p_new_password) like  '%SECRET%'
   or upper(p_new_password) like  '%PASSWORD%'
   or upper(p_new_password) like  '%DATABASE%'
   or upper(p_new_password) like  '%ORACLE%'
   or upper(p_new_password) like '%ABC%123%'
) then
      'password too simple');
end if;

-- check 3: is the length of the password >= 6
if length(p_new_password) < 6 then
       'password should be at least 6 characters long');
end if;

-- check 4: does the password contain at least one
-- letter, one digit and one special character.
scanlen := length(p_new_password);
-- Is number present?
for i in 1..scanlen loop
    if substr(p_new_password,i,1) in
               '6','7','8','9')  then
        numpresent := true;
    end if;
end loop;
if numpresent = false then
     'password should contain at least one digit, one character and one special
end if;
-- Is character present?
for i in 1..length(alphalist) loop
  for j in 1..scanlen loop
    if substr(p_new_password, j, 1) =
       substr(alphalist, i, 1) then
       alphapresent := true;
    end if;
   end loop;
end loop;

if alphapresent = false then
     'password should contain at least one digit, one character and one special
end if;

--. Is a special character present?

for i in 1..length(sclist) loop
    for j in 1..scanlen loop
        if substr(p_new_password,j,1) =
            substr(sclist,i,1) then
                scpresent := true;
        end if;
    end loop;
end loop;

if scpresent = false then
     'password should contain at least one digit, one character and one special
end if;

-- check 5: does the password differ from the
-- previous password by at least 3 letters
l_differ :=
   length(p_old_password) - length(p_new_password);
if abs(l_differ) < 3 then
    if l_differ < 0 then
        scanlen := length(p_new_password);
        scanlen := length(p_old_password);
    end if;
    l_differ := abs(l_differ);
    for i in 1..scanlen loop
        if substr(p_new_password,i,1) !=
             substr(p_old_password,i,1) then
                  l_differ := l_differ + 1;
        end if;
    end loop;
    if l_differ < 3 then
            'password should differ by at least 3 characters');
    end if;
end if;

This function should be created under the user sys. Let's dissect this function a little. Most of the code is self-explanatory with the help of the inline comments. It accepts the username, the old and the new passwords, checks the conditions, and returns TRUE or FALSE depending on the password checked against the compliance. This format cannot be changed.

Once this password check function is created, it is enforced by assigning it to a profile. Remember the profile we created earlier, SENIOR_CLAIM_ANALYST? This profile will have this password checker function.

alter profile senior_claim_analyst limit
    password_verify_function password_check;

When Judy changes her password to 43v3r

SQL> password
Changing password for JUDY
Old password:
New password:
Retype new password:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should be at least 6 characters long
Password unchanged

the function does not allow her to change it to something less than six characters.

All users assigned to the profile of SENIOR_CLAIM_ANALYST will have their passwords enforced against the password check function. This achieves another measure of compliance with security requirements. The password function ensures that the complexity of the password makes it difficult for the hacker to guess it. In the same manner, this password check function can be assigned to all of the defined profiles.

If you don't want a password management function for a certain profile, you can turn it off by

alter profile senior_claim_analyst limit
    password_verify_function NULL;

* Tip: Use the password check function and assign it to the profiles to enforce the policy of passwords being difficult to guess.


This is an excerpt from the book "Oracle Privacy Security Auditing".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.