Source: ctocio In the Oracle database system, a user if you want to the privilege identity of the user(INTERNAL/SYSDBA/SYSOPER)log Oracle database can have two authentication methods: If used withoperating systemIntegrated Authentication or using the Oracle database password file for authentication. Therefore, the management of the password file, for the control of an authorized user from the remote or the machine log on the Oracle database system to perform database administration work, has important significance. Oracle database password file storage with super user INTERNAL/SYS password and the privileged user's user name/password, it is generally stored in the ORACLE_HOME\DATABASE directory.
A password file was created
In using the Oracle Instance Manager to create a database instance at the right time, in the ORACLE_HOME\DATABASE directory also automatically creates a corresponding password file, the file is named PWDSID. ORA, where SID represents the corresponding Oracle database system identifier. This password file is the initial database management work group Chu after that, the administrator can also according to need, use the tool ORAPWD. EXE manually create a password file, the command format is as follows:
C:\ >ORAPWDFILE=< FILENAME >PASSWORD =< PASSWORD > ENTRIES=< MAX_USERS >
Each of the command parameters meaning: FILENAME: password file name; PASSWORD: set the INTERNAL/SYS account password; MAX_USERS: password file can be stored in the maximum number of users, corresponding to allow SYSDBA/SYSOPER privileges log into the database maximum user count. Since after the Maintenance, If the number of users exceeded this limit, you need to rebuild the password file, so this parameter can be set much larger. With password file, you need to set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to control the password file using the state.
Second, set the initialization parameter REMOTE_LOGIN_PASSWORDFILE
In the Oracle database instance initialization parameter file, this parameter controls the password file use and their status. It can have the following options: NONE: indicates that the Oracle system does not use a password file, the privileged user login through theoperating systemauthentication; EXCLUSIVE: indicates that only one database instance can use this password file. Only in this setting the password file can contain in addition INTERNAL/SYS other than the user information, which allows the system privileges SYSOPER/SYSDBA granted except INTERNAL/SYS other than the user. SHARED: instruction may have more than one database instance can use this password file. In this setting only the INTERNAL/SYS account can be password file identification, even if the file contains other user information, also they are not allowed to SYSOPER/SYSDBA privileges login. This setting is the default value. In REMOTE_LOGIN_PASSWORDFILE parameter is set to EXCLUSIVE, SHARED case, Oracle system password search file the order is: in the system registration database to find ORA_SID_PWFILE parameter value(which is the password file the full path name);if not found, the search ORA_PWFILE parameter values;if still not found, then use the default value of ORACLE_HOME\DATABASE\PWDSID. ORA;where SID represents the corresponding Oracle database system identifier.
Third, to the password file to add, delete user
When the initialization parameter REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE, the system allows the addition INTERNAL/SYS other than the user as an administrator from a remote or native log on to Oracle database systems, performing database administration work;these user names must exist in the password file, the system can identify them. Because regardless of is in create when the dB instance is automatically created the password file, or use the tool ORAPWD. EXE to manually create the password file, only contains INTERNAL/SYS User Information;this, in the actual operation, you may need to the password file to add or delete other user accounts. Because only be granted SYSOPER/SYSDBA system privileges of the user exists in the password file, so when a user to grant or withdraw SYSOPER/SYSDBA system privileges, their account will accordingly be added to the password file or from the password file removed. Thus, the password file to add or remove a user, in fact it is for a user to grant or withdraw SYSOPER/SYSDBA system privileges. To carry out this authorization operation, Use the SYSDBA privilege(or INTERNAL accounts)connected to the database, and the initialization parameter REMOTE_LOGIN_PASSWORDFILE must be set to EXCLUSIVE in. The specific operation steps are as follows: Create the appropriate password file; Set the initialization parameter REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE; The use of the SYSDBA permissions to log in: CONNECTSYS/internal_user_passswordASSYSDBA; Start the database instance and open the database; Create a corresponding user account on its mandate(including the SYSOPER and SYSDBA): grant permissions: GRANTSYSDBATOuser_name; Recover permissions: REVOKESYSDBAFROMuser_name; Now these users can log in as the administrator of the database system;
Fourth, using the password file login
With the password file, the user can use the password file to SYSOPER/SYSDBA privileges log on Oracle database instance, note that the initialization parameter REMOTE_LOGIN_PASSWORDFILE should be set to EXCLUSIVE or SHARED. Any user with SYSOPER/SYSDBA permissions to log on after, will be located in the SYS user Schema below, the following two log examples: 1. As an administrator login:
Suppose user scott has been granted SYSDBA privileges, he can use the following command to login: 2. To INTERNAL identity login: