Use and maintenance of Oracle database password files

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 password file corresponding to the Oracle_home\database directory is created automatically, with the file name 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, with the following command format: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 the Ora_sid_pwfile parameter value is found in the system registry (it is a password file , if not found, finds the Ora_pwfile parameter value, or, if not found, uses the default value 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, and perform database management work; these The user name 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;