Use and maintenance of Oracle database password files

oracle| Data | Database Overview: Oracle relational database system has been widely used for its excellent performance, and ensuring database security is an important part of database management work. On the basis of summarizing the security management of Oracle database, this paper introduces the creation, use and maintenance of password files of Oracle database system in detail, for everyone's reference.

Key words: Oracle database Password file

In Oracle database systems, There are two ways to authenticate users if they want to log on to an Oracle database as a privileged user (Internal/sysdba/sysoper): Authenticate with an operating system-integrated authentication or a password file that uses an Oracle database. Therefore, the management of password files, for the control of authorized users from remote or local login Oracle database system, the implementation of database management work, has important significance.

The Oracle database password file holds the Superuser internal/sys password and the user name/password of other privileged users, which is generally stored in the Oracle_home\database directory.

First, the creation of the password file:

When you create a database instance using Oracle Instance Manager, a corresponding password file is created automatically under the Oracle_home\database directory. The file name is Pwdsid.ora, where the SID represents the corresponding Oracle database system identifier. This password file is the basis for the initial database management effort. After this, the administrator can also use tool ORAPWD.EXE to create the password file manually as needed, the command format is as follows: C:\>orapwd file=< FILENAME > PASSWORD
=< PASSWORD > entries=< max_users >


The meaning of each command parameter is:

FileName: password filename;

PASSWORD: Set the password of the Internal/sys account;

Max_users: The maximum number of users that can be stored in the password file, corresponding to the maximum number of users allowed to log on to the database with Sysdba/sysoper permissions. Because in future maintenance, if the number of users exceeds this limit, the password file needs to be rebuilt, so this parameter can be set larger as needed.

After you have the password file, you need to set the initialization parameter Remote_login_passwordfile to control the use status of the password file.

Second, set initialization parameters Remote_login_passwordfile:

In the initialization parameter file for an Oracle database instance, this parameter controls the use of the password file and its status. It can have several options:

NONE: Instructs the Oracle system not to use a password file, and the privileged user's login is authenticated through the operating system;

EXCLUSIVE: Indicates that only one instance of the database can use this password file. Only the password file under this setting can contain user information other than Internal/sys, allowing system permission sysoper/sysdba to be granted to users other than Internal/sys.

SHARED: Indicates that multiple database instances can be used with this password file. Only the Internal/sys account can be identified by the password file under this setting, even if there are other users ' information in the file, they are not allowed to log in with SYSOPER/SYSDBA privileges. This setting is the default value.
----When the Remote_login_passwordfile parameter is set to exclusive, shared, the Oracle system searches for password files in the order that they are found in the system registry Ora_sid_ Pwfile the value of the parameter (it is the full pathname of the password file) or, if not found, the value of the Ora_pwfile parameter, or the default value if it is still not found Oracle_ Home\database\pwdsid.ora, where the SID represents the corresponding Oracle database system identifier.

Add and remove users to the password file:

When the initialization parameter remote_login_passwordfile is set to exclusive, the system allows users other than Internal/sys to log on to the Oracle database system from the remote or local computer, performing database management work These user names must exist in the password file before the system can recognize them. Because either the password file that is created automatically when the database instance is created, or the password file created manually by using the tool ORAPWD.EXE, contains only the information of the Internal/sys user; In practice, you may need to add or remove other user accounts from the password file.

Because only users granted SYSOPER/SYSDBA system privileges exist in the password file, their account number will be added to the password file or deleted from the password file when the SYSOPER/SYSDBA system permissions are granted or retracted to a user. By adding or removing a user to a password file, you are actually granting or recovering SYSOPER/SYSDBA system privileges to a user.

To do this authorization, you use the SYSDBA permission (or internal account) to connect to the database, and the initialization parameter remote_login_passwordfile must be set to exclusive. The specific steps are as follows:

Create the corresponding password file;

Set initialization parameter remote_login_passwordfile=exclusive;

To log on using SYSDBA permissions:



CONNECT Sys/internal_user_passsword as SYSDBA;



Start the database instance and open the database;

Create appropriate user accounts and authorize them (including Sysoper and SYSDBA):
Grant permission: Grant SYSDBA to user_name;
Recall permission: REVOKE SYSDBA from user_name;

These users can now log on to the database system as an administrator;

Login with password file:

With a password file, users can use the password file to log on to an Oracle database instance with SYSOPER/SYSDBA privileges, and note that initialization parameters Remote_login_passwordfile should be set to exclusive or shared. After any user has logged on with SYSOPER/SYSDBA privileges, it will be located under the schema of the SYS user, and the following are two examples of logins:

1. Log in as an administrator:

Assuming that user Scott has been granted SYSDBA permission, he can log on using the following command:



CONNECT Scott/tiger as SYSDBA



2. Log in as internal:

CONNECT Internal/internal_password

V. Maintenance of password files:

1. View the members in the password file:

You can query view V$pwfile_users to obtain information about the user who owns SYSOPER/SYSDBA system permissions, and the SYSOPER/SYSDBA column in the table True/false indicates whether the user has the appropriate permissions. These users are the members of the password file that are appropriate.

2. Number of users of the extended password file:

When the number of accounts added to the password file exceeds the limit set for creating the password file (that is, the max_users parameter of the ORAPWD.EXE tool), the password file needs to be rebuilt by the user limit for the extended password file, as follows:

(a) Query view v$pwfile_users, record user information with SYSOPER/SYSDBA system privileges;

(b) Closure of the database;

(c) Deletion of the password file;

D use ORAPWD.EXE to create a new password file;

E Add the user obtained in step A to the password file.

3. Modify the status of the password file:

The status information for the password file is stored in this file, and when it is created, its default state is shared. You can change the state of the password file by changing the setting of the initialization parameter remote_login_passwordfile. When a database case is started, the Oracle system reads the setting of the Remote_login_passwordfile parameter from the initialization parameter file, and when the database is loaded, the system compares this parameter to the state of the password file, and if it is different, updates the status of the password file. If you plan to allow database instances to be started from multiple clients, you should ensure that the initialization parameter files on each client are consistent to avoid accidentally changing the state of the password file, resulting in the failure of the database login.

4. Modify password file storage location:

The location of the password file can be moved as needed, but after making this modification, the system registry should be modified to set the parameters or environment variables that point to the location where the password file is stored.

5. Delete password file:

Before deleting a password file, make sure that the initialization parameters for each of the currently running database instances are set to none remote_login_passwordfile. After you delete a password file, if you want to connect to the database as an administrator, you must log on using the operating system authentication method.