Password File Authentication

Recall that when a database is created, the users SYS and SYSTEM are created automatically and are granted the DBA role. This is to enable you to administer the database from the beginning of its creation and to allow you the ability to create more users and roles as your needs require. If you create a pre-seeded database, you also get the SCOTT schema in which you or your developers can play without adversely impacting your company's data. The default passwords are well known and well documented, and Oracle strongly suggests that you change the default passwords (at least to SYS and SYSTEM, if not to SCOTT), but they also leave the onus on the DBA to change them. Unfortunately, this does not always happen.

When newly installed, the default password for SYS is change_on_install (I personally think that they make this password particularly long so that you really want to change it), and the default password for SYSTEM is manager. SYS is the owner of the database and the data dictionary. SYSTEM is the owner of all other internal tables and all views used by the Oracle tools.

Oracle has begun to address this security risk in Oracle 9i by locking and expiring many of the default accounts and by starting to eliminate the default passwords.

Locked and Expired Default Accounts

Depending on how you created your database, you may have had it installed with a couple to several default accounts. If you created the database with the DBCA, all default accounts, with the exception of the SYS, SYSTEM, and SCOTT schemas, are locked and expired. This forces the DBA to deliberately unlock and unexpire the accounts before they can be used. This adds to the security of your database because many of these accounts have significant privileges but rarely, if ever, need to be accessed by anything other than Oracle itself.

Elimination of Default Passwords

Another step that Oracle took, in Oracle 9i, is to force the DBA installing the database to provide the passwords for SYS and SYSTEM when the database is created through the DBCA. Although this forces you to take one extra step when creating the database, it also makes your database more secure after it has been created.

The passwords still take on the default values when the database is created manually.

You can further secure your database by using password files to ensure that those accessing the database as SYSDBA or SYSOPER do so with fully granted authority.

Password File

Another way to help to secure your database is to use a password file. There are two ways to authenticate users to the database. One way is to use OS authentication and put the responsibility on the operating system to make sure that users logging in are the correct users and that they have the appropriate privileges. The other way is to use a password file.

Oracle provides a password utility (called orapwd) that helps the DBA to create a password file. This file holds the connection information relevant to users who have been granted the SYSOPER or SYSDBA role. Users logging in as SYSDBA will connect to the SYS schema, not their own. Those logging in as SYSOPER are connected, simply, to the PUBLIC schema (see Chapter 17, "Managing Privileges").

Password authentication for SYSOPER and SYSDBA users requires the use of a password file. To create this file, enter the following command:

 orapwd file=filename password=password entries=max_num_users 

filename is the name of the password file.

password is the password for either the SYSOPER or the SYSDBA.

entries is the maximum number of distinct users who can be allowed to connect either as SYSDBA or as SYSOPER. If the number is exceeded, you need to create a new password file to accommodate the additional users. It is safer to double (or more) the number that you ever think you will need; people will quit, people will be hired, the needs will expand, and you will soon find that you have exhausted the number of users that you allotted.

After the password file has been created, you need to set the REMOTE_LOGIN_PASSWORDFILE parameter to either EXCLUSIVE or SHARED. EXCLUSIVE indicates that only the given instance can use the password file in question and that the file in question contains usernames other than SYS. EXCLUSIVE allows you to grant either SYSDBA or SYSOPER to individual users, allowing those users to connect using their own usernames and passwords, adding to security and traceability. SHARED indicates that you are allowing more than one instance to use the password file. The only user who can be added to a SHARED password file is SYS.

After you have set the REMOTE_LOGIN_PASSWORDFILE and created the password file, you need to connect to the database as the SYS user and grant relevant and appropriate users the privileges required by your business rules.

You can delete the existing password file and create a new password file as necessary by simply rerunning the password utility.

There are several Oracle provided interfaces through which you can interact and maintain your database. The following section looks at these interfaces.