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.

Save an re-set an Oracle user password

As an Oracle DBA you sometimes need to sign-on as a specific user to understand the exact nature of their problem.  While it is easy to alter the user ID to make a new password, this is an inconvenience to the end-user because they have to re-set a new password.

However, as DBA you can extract the encrypted password from the dba_users view, save it, and re-set the password after you have finished your testing.
 
For example, assume that you need to sign-on as FRED and test their Oracle privileges:

STEP 1:  First, we extract the encrypted password:

select
'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password
from
   dba_users
where
username = ‘FRED’;
 
OLD_PASSWORD
--------------------------------------------------------------------
alter user "FRED" identified by values '15EC3EC6EAF863C';

STEP 2:  You can now change FRED’s password and sigh-on for testing:

alter user FRED identified by FLINTSTONE;
connect fred/flintstone;
select stuff from tables;

STEP 3:  When you have completed your testing you can set-back the original encrypted password using the output from the query in step 1:

alter user "FRED" identified by values '15EC3EC6EAF863C';