Monday, October 30, 2006

Oracle Passwords

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).

Comments:
I'm honored to be just below Tom AND Jonathan :)
 
Nice blog - interesting topics, some of which I would have never come across yet after reading the blog I find myself digging deeper and deeper.

I started using Oracle about 5 years ago when I first joined the company I work for. Having never used Oracle before all my learning has stemmed from the use of Oracle in this company. Being a fashion retail company and not an IT company I have found the extent of my Oracle learning has slowed down somewhat - not because I have run out of things to learn in Oracle, but because I was trained by having to look over someone's shoulder while they coded - I learnt what they knew, and therefore of course you don't know what you don't know.

So it is refreshing when I read blogs like yours to find out stuff I didnt know existed in Oracle. Even if I never use it at least I'll know about it and understand to some extent the uses of it.

Oracle is a massive beast - I learn that every day.

keep up the great work!!

Gary,
Cape Town, South Africa.
 
Robert,
Good write up. I would also like to add that Oracle uses one-way encryption to encrypt basically the username and password to produce that 16-character string.

SQL@O9.2.0.7> create user coal identified by mineisdirty;

User created.

SQL@O9.2.0.7> create user coalminer identified by isdirty;

User created.

SQL@O9.2.0.7> create user coalmine identified by isdirty;

User created.

SQL@O9.2.0.7> select username, password from dba_users where username like 'COAL%';

USERNAME PASSWORD
------------------------------ ------------------------------
COAL 6FCDA69BF440408F
COALMINE 6FCDA69BF440408F
COALMINER 0753A36B9F404829

SQL@O9.2.0.7>


One trick to totally secured the account with the use of IDENTIFIED BY VALUES is to give it a string that the encryption will never encrypt to. E.g. ALTER USER scott IDENTIFIED BY VALUES 'Totally secured' or embed hidden characters like "\n" or "\cr"
 
This comment has been removed by a blog administrator.
 
Security Tools
When sensitive information is transmitted outside of trusted systems, it should be encrypted to preserve confidentiality. An example of this is the information gathered from the credit cards.
 
Hey is there any keyword to encrypt this password field in Oracle.
As in MS Sql Server we can us

SELECT pwdencrypt('passwordvalue')
can directly encrypt in hexa decimal value.
 
Hey,

nice article and I had gained deeper insight in these area. By the way, is there a way to retrieve a log describing the changes made in username and password in Oracle DB?

Thanks in advanced.
 
Hi,

I forgot user name and password both.How can i get it. Please help.

Deepti
 
Hi,

I didn't specify other username than SYSTEM,SYS. . . and specified password to it.But when am trying to log in its giving error as "protocol adapter error".
please give solution for this as soon as possible.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?