Use and maintenance of Oracle password files

The ORACLE tutorial you are looking at is: use and maintenance of Oracle password files.

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

In the Oracle database system, users who want to log into the Oracle database as a privileged user (INTERNAL/SYSDBA/SYSOPER) can authenticate in one of two ways: using an authentication integrated with the operating system or using the password file of the Oracle database. Therefore, good management of password files is of great significance for controlling authorized users to log into Oracle database system remotely or locally and perform database management.  

The password file in the Oracle database contains the super user INTERNAL/SYS passwords and other privileged user names/passwords, which are stored in the ORACLE_HOME \ DATABASE directory.  

1. Password file creation:

When creating a 1 database instance using Oracle Instance Manager, a corresponding password file named PWDSID.ORA is also automatically created in the ORACLE_HOME \ DATABASE directory, where SID represents the corresponding Oracle database system identifier. This password file is the basis for the initial database administration effort. After that, the administrator can also manually create the password file using the tool ORAPWD.EXE as needed. The command format is as follows:

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

The meaning of each command parameter is:  
FILENAME: password file name;  

PASSWORD: set the password for the INTERNAL/SYS account;  

MAX_USERS: the maximum number of users that can be stored in the password file corresponds to the maximum number of users allowed to log into the database with the SYSDBA/SYSOPER permission. Since the password file will need to be rebuilt for future maintenance if the number of users exceeds this limit, this parameter can be set to 1 larger as needed.  

Once you have the password file, you need to set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to control the usage state of the password file. 2. Set the initialization parameter REMOTE_LOGIN_PASSWORDFILE:

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

NONE: indicates that the Oracle system does not use password files, and the login of privileged users is authenticated by the operating system;  

EXCLUSIVE: indicates that only one database instance can use this password file. Only password files with this setting can contain user information other than INTERNAL/SYS, which allows system permissions SYSOPER/SYSDBA to be granted to users other than INTERNAL/SYS.  

SHARED: indicates that multiple database instances can use this password file. In this setting, only the INTERNAL/SYS accounts can be identified by the password file. Even if the file contains the information of other users, they are not allowed to log in with the permission of SYSOPER/SYSDBA. This is set to the default value.

When the parameters of REMOTE_LOGIN_PASSWORDFILE are set to EXCLUSIVE and SHARED, the order of Oracle system to search for the password files is as follows: find the parameter value of ORA_SID_PWFILE in the system registry (it is the full path name of the password file); If not, find the value of the ORA_PWFILE parameter; If still not found, use the default value ORACLE_HOME \ DATABASE \ PWDSID.ORA; Where SID represents the corresponding Oracle database system identifier.  

3. Add or delete 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 in to the Oracle database system as remote or native administrators and perform database management. These usernames must exist in the password file for the system to recognize them. Since either the password file created automatically when creating the database instance, or the password file manually created using the tool ORAPWD.EXE, contains only the information of the INTERNAL/SYS user; To do this, in practice, you might need to add or remove other user accounts to the password file.  

Since only users granted SYSOPER/SYSDBA system permissions exist in the password file, when granting or withdrawing SYSOPER/SYSDBA system permissions to a user, their accounts will be added to or removed from the password file accordingly. Thus, adding or deleting a user to the password file is essentially granting or revoking the SYSOPER/SYSDBA system rights to a user.  

To do this, connect to the database using the SYSDBA permission (or INTERNAL account), and the initialization parameter REMOTE_LOGIN_PASSWORDFILE must be set to EXCLUSIVE. The specific steps are as follows:  

Create the corresponding password file;  

Set the initialization parameter REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE;  

Login with SYSDBA permissions:

CONNECTSYS/internal_user_passswordASSYSDBA;  

Start the database instance and open the database;  

Create the appropriate user account and authorize them (including SYSOPER and SYSDBA) :  
Grant permissions: GRANTSYSDBATOuser_name;  
Recover the permissions: REVOKESYSDBAFROMuser_name;  

These users can now log into the database system as administrators;   4. Login with password file:

Once the password file is available, the user can use the password file to log into the Oracle database instance with the SYSOPER/SYSDBA permission. Note that the initialization parameter REMOTE_LOGIN_PASSWORDFILE should be set to EXCLUSIVE or SHARED. Any user who logs in with SYSOPER/SYSDBA permissions will be placed under Schema for SYS users. Here are two examples of logins:

1. Log in as administrator:  

Assuming that user scott has been granted SYSDBA privileges, he can log in using the following command:  

CONNECTscott/tigerASSYSDBA  

2. Log in as INTERNAL:  

CONNECTINTERNAL/INTERNAL_PASSWORD  

5. Maintenance of password files:

1. View the members in the password file:

You can query the view V$PWFILE_USERS to get information about the user who has the system permissions of SYSOPER/SYSDBA. The value of the SYSOPER/SYSDBA column in the table, TRUE/FALSE, indicates whether the user has the appropriate permissions. These users are the members that exist in the password file accordingly.  

2. Number of users with extended password files:  

When the number of accounts added to the password file exceeds the limit set for creating the password file (the MAX_USERS parameter of ORAPWD.EXE tool), the password file needs to be rebuilt to extend the user limit of the password file. The steps are as follows:  

a) query view V$PWFILE_USERS, records the user information with the system permission of SYSOPER/SYSDBA;  

b) close the database;  

c) delete password file;  

d) create a new password file with ORAPWD.EXE;  

e) adds the user obtained in step a to the password file.  

3. Change the status of password file:  

The status information of 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 the database instance is started, the Oracle system reads the Settings of the REMOTE_LOGIN_PASSWORDFILE parameter from the initialization parameter file. When the database is loaded, the system compares this parameter with the state of the password file and, if not, updates the state of the password file. If the plan allows you to start a database instance from multiple clients, make sure that the initialization parameter files on each client are 1 first, because each client must have an initialization parameter file, so that you don't accidentally change the state of the password file and fail to log in to the database.  

[1]   [2]   next page

The ORACLE tutorial you are looking at is: the use and maintenance of Oracle password files. 4. Change the storage location of password file:  

The location of the password file can be moved as needed, but the Settings of the parameters or environment variables that point to the location of the password file in the system registry should be modified accordingly.  

5. Delete password file:  

Before deleting the password file, make sure that the initialization parameter REMOTE_LOGIN_PASSWORDFILE is set to NONE for each currently running database instance. If you want to connect to the database as an administrator after deleting the password file, you must log in using the operating system authentication method.

Previous page     [1]   [2]