Oracle Password File Management

The Oracle password file is very important. Next we will introduce you to Oracle password file management. If you are interested in this, take a look.

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 Oracle 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 an Oracle 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. After that, the administrator can use ORAPWD. EXE to manually create a password file as needed. The command format is as follows:

C: \> ORAPWDFILE = <FILENAME> PASSWORD = <PASSWORD> ENTRIES = <MAX_USERS>

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.

Ii. 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 Oracle System Searches for password files in the order of ORA_SID_PWFILE, which is the full path name of the password file, in the system registration database ); 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.

3. add and delete users to and 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 permission to log on to: CONNECTSYS/internal_user_passswordASSYSDBA;

Start the database instance and open the database;

Create a user account, including SYSOPER and SYSDBA): grant permissions: GRANTSYSDBATOuser_name;

Revoke permission: REVOKESYSDBAFROMuser_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:

CONNECTscott/tigerASSYSDBA

2. Log On As INTERNAL:

CONNECTINTERNAL/INTERNAL_PASSWORD
 

Entire Process of creating Oracle Materialized View

ORACLE instance creation process

Statement syntax for oracle time addition and subtraction

How to check the oracle deadlock

How to Implement Oracle fuzzy search