Oracle passwords - changing, expiring and locking users

Change Oracle passwords, expire, and lock unnecessary users

There are many users on a full installation of Oracle, most of which you probably won't need. To lock an Oracle user account, you can use the following command:

alter user username account lock;

To unlock the user, simply replace 'lock' with 'unlock.'

Oracle comes with a few default accounts that should never be locked or dropped. These include: SYS, SYSTEM, SYSMAN (Oracle 10g), OUTLN. However, you do want always to change the password for these users. The default password for SYS is change_on_install. It is important that you follow these directions. To change the password for a user:

alter user username identified by new_password;

For non-critical users, you can always lock and expire the account. We can change the password for, lock, and expire the account CTXSYS all at once:

alter user ctxsys identified by 0bscur3 account lock password expire;

This will ensure that CTXSYS password has been changed from the self evident default of CTXSYS; on a successful login the password will have to be changed (because it is expired); and because it is locked, he account can't be logged into under any circumstances.

To aid you in this task, here is a list of users that come with a full database installation that are safe to expire and lock if you are not using their functionality:

1. AURORA$ORB$UNAUTHENTICATED - Jserver user
2. BI - demo user
3. CTXSYS - Oracle Text/interMedia administrator
4. DBSNMP - Oracle Intelligent Agent
5. DSSYS - Dynamic Services and Syndication Server
6. HR - demo user
7. MDSYS - Spatial administrator
8. ORDSYS/ORDPLUGIN - Object Relational Data user
9. OE - demo user
10. PERFSTAT - Statspack administrative user
11. SCOTT - demo user
12. SH - demo user
13. TRACESVR - Oracle trace server
14. WKSYS - Ultrasearch administrator

The best rule of thumb is to install only the features you need when you install Oracle. If you are not going to use Spatial, interMedia, or UltraSearch, don't install them. Then you will not have to worry about those three users.

Once an account has been expired, it can only be revived by assigning it a new password.  You can save the old password.

ALTER USER scott IDENTIFIED BY tiger;

You can then re-add the original password, provided that password_reuse_max is greater than zero.

Also see our notes on:

• Oracle Listener Security & password management

• TNS-01190: The user is not authorized to execute the requested listener command lsnrctl

• Blocking Oracle access by IP Addresses

• Assigning Oracle user security roles