Oracle Password Management

Database Administration Users

The SYS and the SYSTEM schema are the default database administrator accounts. All the meta-data (data dictionary) objects are stored in the SYS schema. The SYSTEM is granted the privelages that SYS is granted, ie. all privelages.

In the past the DBA would usually connect to the database as a user called INTERNAL for the highest level of administration functions, for instance the creation of a new database. The INTERNAL user has been superceded by operating system authentication and password files.

Operating System Authentication

The REMOTE_LOGIN_PASSWORD parameter must be set to NONE in the init.ora parameter file. Operating system level authenticated users are able to connect to the database as administrators as shown below.

CONNECT / AS SYSOPER 
CONNECT / AS SYSDBA

Password File Authentication

Database administrator users can be restricted using password file authentication. This is implemented using the ORAPWD utility as shown below.

ORAPWD FILE=<filename> PASSWORD=<password> ENTRIES=<max_users>

With password file authentication the REMOTE_LOGIN_PASSWORDFILE parameter must be set to EXCLUSIVE. Database administration users can be added to the password file using the grant command as show below.

GRANT SYSDBA TO JIM; GRANT SYSOPER TO JIM;

Thus privelaged database users can connect to the database as follows.

CONNECT jim/<password> AS SYSDBA;

Note that the maximum size of the authentication password file one operating system block. A new password file will be automatically be created when the maximum number of passwords abale to fit into a block is exceeded.

The REMOTE_LOGIN_PASSWORD Parameter

There are three settings for the REMOTE_LOGIN_PASSWORD file parameter.

  1. NONE - the password file is no utilised even if existing, the default setting.
  2. EXCLUSIVE - this setting allows the DBA to grant SYSDBA and SYSOPER system privelages to users. Thus users can login as themselves and act as DBAs.
  3. SHARED - a multiple database setting for a single DBA user. Thus any DBA must login as the SYS user.

Changing Password File Entries

Add users to the password file.

GRANT SYSDBA TO <username>

Remove users from the password file.

REVOKE SYSDBA FROM <username>

Never remove the password file with any database instance, local or remote, where the REMOTE_LOGIN_PASSWORD parameter is set to EXLCUSIVE. If so the database will not be connectable, even with a new password file.