Use and Maintenance of Oracle Database Password files

The ORACLE tutorial is to use and maintain the password file of the Oracle database.

Summary: Oracle relational database system has been widely used for its excellent performance. Ensuring database security is an important part of database management. This article provides a detailed introduction to the creation, use, and maintenance of the password file of the Oracle database system based on the summary of the Oracle database security management work.

Keywords: Oracle Database Password File

In the Oracle database system, if you want to log on to the Oracle database as a privileged user (INTERNAL/SYSDBA/SYSOPER), there are two authentication methods: that is, authentication is performed using the Integrated Identity Authentication with the operating system or using the password file of the Oracle database. Therefore, managing the password file is of great significance for controlling authorized users to log on to the Oracle Database System from the remote end or local end and perform database management.

The password file of the Oracle DATABASE is stored in the Super User INTERNAL/SYS password and the user name/password of other privileged users. It is generally stored in the ORACLE_HOME \ DATABASE directory.

I. Create a password file:

When you use Oracle Instance Manager to create a DATABASE Instance, a password file is automatically created under the ORACLE_HOME \ DATABASE directory. The file name is PWDSID. ORA, where SID represents the corresponding Oracle database system identifier. This password file is the basis for initial database management. Then, the administrator can use ORAPWD. EXE to manually create a password file as needed. The command format is as follows:

The meanings of command parameters are as follows:

FILENAME: password file name;

PASSWORD: Set the PASSWORD of the INTERNAL/SYS account;

MAX_USERS: the maximum number of users in the password file, which corresponds to the maximum number of users allowed to log on to the database with SYSDBA/SYSOPER permissions. In future maintenance, if the number of users exceeds this limit, the password file needs to be rebuilt. Therefore, this parameter can be set to be larger as needed.

With the password file, you need to set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to control the Usage Status of the password file.

2. Set the initialization parameter REMOTE_LOGIN_PASSWORDFILE:

In the initialization parameter file of the Oracle database instance, this parameter controls the usage and status of the password file. It can have the following options:

NONE: indicates that the Oracle system does not use password files. Privileged users can log on to the Oracle System for authentication;

EXCLUSIVE: indicates that only one database instance can use this password file. Only the password file under this setting can contain user information except INTERNAL/SYS, that is, the system permission SYSOPER/SYSDBA can be granted to other users except INTERNAL/SYS.

SHARED: indicates that multiple database instances can use this password file. In this setting, only the INTERNAL/SYS account can be identified by the password file. Even if the file contains information from other users, they are not allowed to log on with the SYSOPER/SYSDBA permission. This is the default value.
---- When the REMOTE_LOGIN_PASSWORDFILE parameter is set to EXCLUSIVE and SHARED, the order of password files searched by the Oracle system is: Search for the ORA_SID_PWFILE parameter value in the System Registration Database (which is the full path name of the password file ); if not, search for the ORA_PWFILE parameter value. If not, use the default value ORACLE_HOME \ DATABASE \ PWDSID. ORA; the SID represents the corresponding Oracle database system identifier.

[NextPage]

3. add or delete users to or from 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 as administrators from the remote end or local end to perform database management; these usernames must exist in the password file before the system can recognize them. Whether it is the password file automatically created when you create a database instance or the tool ORAPWD. the password files manually created by EXE only contain the information of INTERNAL/SYS users. To do this, you may need to add or delete other user accounts to the password file.

Because only users granted SYSOPER/SYSDBA system permissions exist in the password file, when a user is granted or revoked SYSOPER/SYSDBA system permissions, their accounts will also be added to or deleted from the password file. Therefore, adding or deleting a user to or from the password file actually grants or revokes SYSOPER/SYSDBA system permissions to a user.

To perform this authorization, you must use the SYSDBA permission (or INTERNAL account) to connect to the database and set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. The procedure is as follows:

Create a password file;

Set the initialization parameter REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE;

Use SYSDBA permissions to log on:

Start the database instance and open the database;

Create a user account and authorize it (including SYSOPER and SYSDBA ):
GRANT permissions: grant sysdba to user_name;
REVOKE permissions: revoke sysdba from user_name;

Now these users can log on to the database system as administrators;

4. Log On with the password file:

With the password file, you can use the password file to log on to the Oracle database instance with the SYSOPER/SYSDBA permission. Note that the initialization parameter REMOTE_LOGIN_PASSWORDFILE should be set to EXCLUSIVE or SHARED. After a user logs on with the SYSOPER/SYSDBA permission, the user will be located under the SYS user's Schema. The following are two logon examples:

1. Log On As an administrator:

Assume that scott has been granted SYSDBA permissions, he can use the following command to log on:

2. Log On As INTERNAL:

[NextPage]

　5. Password File Maintenance:

1. view the members in the password file:

You can query view V $ PWFILE_USERS to obtain information about users with SYSOPER/SYSDBA system permissions. The values of TRUE/FALSE in the SYSOPER/SYSDBA column in the table indicate whether the user has the corresponding permissions. These users are members of the password file.

2. Number of users of the Extended Password File:

When the number of accounts added to the password file exceeds the limit set when the password file is created (that is, ORAPWD. when the MAX_USERS parameter of the EXE tool is used, to limit the number of users of the Extended Password File, You need to recreate the password file as follows:

A) query view V $ PWFILE_USERS, which records the user information with SYSOPER/SYSDBA system permissions;

B) shut down the database;

C) delete the password file;

D) Use ORAPWD. EXE to create a password file;

E) add the user obtained in step a to the password file.

3. Change the password file status:

The status information of the password file is stored in this file. When it is created, its default status is SHARED. You can change the password file status by changing the setting of the initialization parameter REMOTE_LOGIN_PASSWORDFILE. When the database instance is started, the Oracle System reads the setting of the REMOTE_LOGIN_PASSWORDFILE parameter from the initialization parameter file. When the database is loaded, the system compares this parameter with the status of the password file. If it is different, the status of the password file is updated. If you plan to allow the database instance to be started from multiple clients, because each client must have an initialization parameter file, ensure the consistency of the initialization parameter files on each client, to avoid unexpected changes to the password file status, resulting in database login failure.

4. Change the storage location of the password file:

The storage location of the password file can be moved as needed. However, after this modification, you should modify the system registration database settings related to the parameters or environment variables pointing to the storage location of the password file.

5. Delete the password file:

Before deleting the password file, make sure that the initialization parameter REMOTE_LOGIN_PASSWORDFILE of the currently running database instances is set to NONE. After deleting the password file, if you want to connect to the database as an administrator, you must use the operating system authentication method to log on.

Previous Page &

[1] [2] Next page

The ORACLE tutorial is to use and maintain the password file of the Oracle database. Nbsp;

Previous Page [1] [2]