Oracle7 Server Administrator's Guide

Password File Administration

You can create a password file using the password file creation utility, ORAPWD or, for selected operating systems, you can create this file as part of your standard installation.

This section includes the following topics:

Using ORAPWD

Setting REMOTE_LOGIN_PASSWORDFILE

Adding Users to a Password File

Connecting with Administrator Privileges

Maintaining a Password File

See Also: See your operating system-specific Oracle documentation for information on using the installer utility to install the password file.

Using ORAPWD

When you invoke the password file creation utility without supplying any parameters, you receive a message indicating the proper use of the command as shown in the following sample output:

> orapwd

Usage: orapwd file=<fname> password=<password> entries=<users>

  where

    file - name of password file (mand),

    password - password for SYS and INTERNAL (mand),

    entries - maximum number of distinct DBAs and OPERs (opt),

  There are no spaces around the equal-to (=) character.

For example, the following command creates a password file named ACCT.PWD that allows up to 30 privileged users with different passwords. The file is initially created with the password SECRET for users connecting as INTERNAL or SYS:

> ORAPWD FILE=acct.pwd PASSWORD=secret ENTRIES=30

Following are descriptions of the parameters in the ORAPWD utility.

FILE This parameter sets the name of the password file being created. You must specify the full pathname for the file. The contents of this file are encrypted, and the file is not user-readable. This parameter is mandatory.

On some platforms, the name of the password file is derived from the system identifier (SID). If so, you must specify this predefined name when using the ORAPWD utility. On other systems the name of the password file is stored in an environment variable, such as ORA_sid_PWFILE. If your system uses an environment variable, you must set this variable on the server platform to match the pathname that you specified for the file before starting the instance.

If you are running multiple instances of Oracle7 using the Oracle7 Parallel Server, the environment variable for each instance should point to the same password file.

Warning: It is critically important to the security of your system that you protect your password file, and environment variables that identify the location of the password file. Any user with access to these could potentially compromise the security of the connection.

PASSWORD This parameter sets the password for INTERNAL and SYS. If you issue the ALTER USER command to change the password after connecting to the database, both the password stored in the data dictionary and the password stored in the password file are updated. The INTERNAL user is supported for backwards compatibility only. This parameter is mandatory.

ENTRIES

This parameter sets the maximum number of entries allowed in the password file. This corresponds to the maximum number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. Entries can be reused as users are added to and removed from the password file. This parameter is required if you ever want this password file to be EXCLUSIVE.

Warning: If you ever need to exceed this limit, you must create a new password file. It is safest to select a number larger than you think you will ever need.

See Also: Consult your operating system-specific Oracle documentation for the exact name of the password file, or for the name of the environment variable used to specify this name for your operating system.

Setting REMOTE_LOGIN_ PASSWORDFILE

In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are described below.

Note: To STARTUP an instance or database, you must use Server Manager. You must specify a database name and a parameter file to initialize the instance settings. You may specify a fully-qualified remote database name using SQL*Net. However, the initialization parameter file and any associated files, such as a configuration file, must exist on the client machine. That is, the parameter file must be on the machine where you are running Server Manager.

NONE Setting this parameter to NONE causes Oracle7 to behave as if the password file does not exist. That is, no privileged connections are allowed over non-secure connections. NONE is the default value for this parameter.

EXCLUSIVE An EXCLUSIVE password file can be used with only one database. Only an EXCLUSIVE file can contain the names of users other than SYS and INTERNAL. Using an EXCLUSIVE password file allows you to grant SYSDBA and SYSOPER system privileges to individual users and have them connect as themselves.

SHARED A SHARED password file can be used by multiple databases. However, the only users recognized by a SHARED password file are SYS and INTERNAL; you cannot add users to a SHARED password file. All users needing SYSDBA or SYSOPER system privileges must connect using the same name, SYS, and password. This option is useful if you have a single DBA administering multiple databases.

Suggestion: To achieve the greatest level of security, you should set the REMOTE_LOGIN_PASSWORDFILE file initialization parameter to EXCLUSIVE immediately after creating the password file.

Adding Users to a Password File

When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information is added to the password file. If the server does not have an EXCLUSIVE password file, that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, you receive an error message if you attempt to grant these privileges.

A user's name only remains in the password file while that user has at least one of these two privileges. When you revoke the last of these privileges from a user, that user is removed from the password file.

To Create a Password File and Add New Users to It

2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.

3. Connect with SYSDBA privileges as shown in the following example:

	CONNECT SYS/change_on_install AS SYSDBA

4. Startup the instance and create the database if necessary, or mount and open an existing database.

5. Create users as necessary. Grant SYSOPER or SYSDBA privileges to yourself and other users as appropriate.

6. These users are now added to the password file and can connect to the database as SYSOPER or SYSDBA with a username and password (instead of using SYS). The use of a password file does not prevent OS authenticated users from connecting if they meet the criteria for OS authentication.

Granting and Revoking SYSOPER and SYSDBA Privileges

If your server is using an EXCLUSIVE password file, use the GRANT command to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example:

GRANT SYSDBA TO scott

Use the REVOKE command to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:

REVOKE SYSDBA FROM scott

Because SYSDBA and SYSOPER are the most powerful database privileges, the ADMIN OPTION is not used. Only users currently connected as SYSDBA (or INTERNAL) can grant SYSDBA or SYSOPER system privileges to another user. This is also true of REVOKE. These privileges cannot be granted to roles, since roles are only available after database startup. Do not confuse the SYSDBA and SYSOPER database privileges with operating system roles, which are a completely independent feature.

Listing Password File Members

Use the V$PWFILE_USERS view to determine which users have been granted SYSDBA and SYSOPER system privileges for a database. The columns displayed by this view are as follows:

USERNAME The name of the user that is recognized by the password file.
SYSDBA If the value of this column is TRUE, the user can log on with SYSDBA system privileges.
SYSOPER If the value of this column is TRUE, the user can log on with SYSOPER system privileges.

Connecting with Administrator Privileges

When you connect with SYSOPER or SYSDBA privileges using a username and password, you are connecting with a default schema of SYS, not the schema that is generally associated with your username.

Use the AS SYSDBA or AS SYSOPER clauses of the Server Manager CONNECT command to connect with administrator privileges.

Connecting with Administrator Privileges: Example

For example, assume user SCOTT has issued the following commands:

CONNECT scott/tiger
CREATE TABLE scott_test(name VARCHAR2(20));

Later, when SCOTT issues these commands:

CONNECT scott/tiger AS SYSDBA
SELECT * FROM scott_test;

He receives an error that SCOTT_TEST does not exist. That is because SCOTT now references the SYS schema by default, whereas the table was created in the SCOTT schema.

Non-Secure Remote Connections

To connect to Oracle7 as a privileged user over a non-secure connection, you must meet the following conditions:

The server to which you are connecting must have a password file.

You must be granted the SYSOPER or SYSDBA system privilege.

You must connect using a username and password.

Local and Secure Remote Connections

To connect to Oracle7 as a privileged user over a local or a secure remote connection, you must meet either of the following sets of conditions:

You can connect using a password file, provided that you meet the criteria outlined for non-secure connections in the previous bulleted list.

If the server is not using a password file, or you have not been granted SYSOPER or SYSDBA privileges and are therefore not in the password file, your operating system name must be authenticated for a privileged connection by the operating system. This form of authentication is operating system-specific.

Consult your operating system-specific Oracle documentation for details on operating system authentication.

See Also: "Password File Administration" .

Maintaining a Password File

This section describes how to expand, relocate, and remove the password file, as well as how to avoid changing the state of the password file.

Expanding the Number of Password File Users

If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user, you must create a larger password file and re-grant the privileges to the users.

To Replace a Password File

1. Note which users have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.

2. Shut down the database.

3. Delete the existing password file.

Relocating the Password File

After you have created the password file, you can relocate it as you choose. After relocating the password file, you must reset the appropriate environment variables to the new pathname. If your operating system uses a predefined pathname, you cannot change the password file location.

Removing a Password File

If you determine that you no longer need to use a password file to authenticate users, you can delete the password file and reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After removing this file, only users who can be authenticated by the operating system can perform database administration operations.

Warning: Do not remove or modify the password file if you have a database or instance mounted using REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE (or SHARED). If you do, you will be unable to reconnect remotely using the password file. Even if you replace it, you cannot use the new password file, because the timestamp and checksums will be wrong.

Changing the Password File State

The password file state is stored in the password file. When you first create a password file, its default state is SHARED. You can change the state of the password file by setting the parameter REMOTE_LOGIN_PASSWORDFILE. When you STARTUP an instance, Oracle7 retrieves the value of this parameter from the initialization parameter file stored on your client machine. When you mount the database, Oracle7 compares the value of this parameter to the value stored in the password file. If these values do not match, the value stored in the file is overwritten.

Warning: You should use caution to ensure that an EXCLUSIVE password file is not accidentally changed to SHARED. If you plan to allow instance STARTUP from multiple clients, each of those clients must have an initialization parameter file, and the value of the parameter REMOTE_LOGIN_PASSWORDFILE must be the same in each of these files. Otherwise, the state of the password file could change depending upon where the instance was started.

USERNAME	The name of the user that is recognized by the password file.
SYSDBA	If the value of this column is TRUE, the user can log on with SYSDBA system privileges.
SYSOPER	If the value of this column is TRUE, the user can log on with SYSOPER system privileges.