OracleBlog

Hallowe'en is a time of fright and mystery, and some of the most common, mysterious Oracle questions I collect involve passwords. Here are some tricks and treats:

How do you change an Oracle password? ... If you/the user forgot it?
How are Oracle passwords stored?
Does it look the same for two different users using the same password?
Do the SQL Trace files reveal your secret password?
How do you hide your password from others when running scripts?
How do you set/enforce your password policy in Oracle?
Which password-related error messages can you get in Oracle, and what do they mean?

The scary truth is that there is no mystery to any of the answers. Each answer can be readily found in either the Oracle Documentation (more specifically, the Oracle Administrator's Guide), or among the plentiful resources of the Internet. It's all in knowing where to look! I've collected a few answers to get you started.

Changing Your Oracle Password

If you already know your Oracle password, you can change it using an SQL*Plus session by simply typing "password" and following the instructions.

If you have the privileges, you can also change a password using ALTER USER.

ALTER USER username IDENTIFIED BY password;

That is also the trick if you have lost your password. Of course, you must have the privileges.

Hopefully its not the privileged user you've lost the password for, but if that's the case, you can always try to the default Oracle usernames and passwords (but presumably you changed it already). Perhaps you want to allow more users SYSDBA privileges? I'll get to that ...

How Oracle Passwords Are Stored

You can see the usernames and encrypted passwords in the DBA_USERS table (assuming you have privileges).

SELECT username, password FROM dba_users WHERE username = 'SCOTT';

If two users have the same password, will it mean they'll have the same encrypted password? NO. You can try it yourself, or just ask my featured blogger Steve Callan. Steve also explains how a DBA can't determine your secret password by reading SQL TRACE of your session.

Steve also turns us onto a little trick where we can save that encrypted password and re-set it after it was (accidentally or deliberately) changed using ALTER USER.

ALTER USER username IDENTIFIED BY VALUES 'encrypted password';

Check out the Dizwell wiki for a cleaner example of restoring an encrypted password.

Your Oracle Password Policy

Steve also mentioned a certain password policy script, which you should find here:

$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

Oracle supports some fairly sophisticated password policies, not just requiring a certain length or requiring numerics. I won't elaborate on the details, which you can find in the Oracle Administrator Guide.

There are plenty of (non-Oracle-specific) articles out there on the need for a strong password policies. For instance, according to a fact sheet from Red Database Security, changing the minimum length of a password from 8 characters to 9 can make the different between 2 days and 57 for a brute force attack!

Oracle Password Error Messages

Speaking of Red Database Security, they did the job of summarizing the various error messages you can get when you try to log in, and what they mean.

ORA_28000: The account is locked
Wait for PASSWORD_LOCK_TIME or contact your DBA

ORA-28001: The password has expired
Change the password or contact your DBA

ORA-00988: Missing or invalid password(s)
Use double quotes for the password (e.g. alter user scott identified by "!alex";)

ORA-01017: Invalid username/password; logon denied
Contact your DBA if the problem still persists

The Password File

I mentioned two things above:
1. Your encrypted passwords are stored in the database, accessible through DBA_USERS.
2. You can grant SYSDBA privileges to other users.

If my first point is correct, what is the password file? And what does the second point have to do with anything? Well, put 1 and 2 together. The password file is how you grant those SYSDBA privileges to other users.

How? By using ORAPWD: The Oracle Password File Utility. You'll find it in $ORACLE_HOME/bin, and you can create the file like so:

orapwd file=filename password=syspassword entries=maxsysdbas force=Y

You can find more information in the Oracle Administrator's Guide, right at the top.

Speaking of which, here is another error you might get: ORA-01996. In this case, you were trying to grant SYSDBA privileges to a user when you already have assigned this to the maximum number of users. Use this utility to increase your limit.

Finally ...

You wrote a script which connects to Oracle, and you're worried that other users can see the login credentials you used to execute the script. What is the correct way around this?

Connor McDonald discusses four ways, using OS accounts, internal accounts, /NOLOG and a last-resort sqlplus trick.

Alternatively we can do what we always do when we want to know something about Oracle, we Ask Tom. Or, more precisely, we assume someone already has and find out what he said. In this case, he instructs us to use the 'identified externally' directive.

Normally I make a reference to Tom Kyte the final word, but this time the honour will go to someone equally worthy: Jonathan Lewis. His blog is long overdue, and you can find his link second from the top, right next to Tom's (sorry Eddie).