watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

          subject         part

sketch Oracle Password file in .


     
          Answer section          


 

( One ) Introduction to password file

Oracle The main function of password file is to SYSDBA and SYSOPER Authentication of authority . The password file holds the grant SYSDBA or SYSOPER The user name and password of the user with permission . It's an encrypted file , Users can't modify this file , But you can use strings Command to see the password HASH value , As shown below :

 1[[email protected] dbs]$ strings  orapwPROD1
 2]\[Z
 3ORACLE Remote Password file
 4INTERNAL
 5AB27B53EDC5FEF41
 6o5&W
 78A8F025737A9097A
 8[[email protected] dbs]$ 
 9
[email protected]> SELECT D.PASSWORD FROM USER$ D WHERE D.NAME='SYS';
11
12PASSWORD
13------------------------------
148A8F025737A9097A
     

stay Linux In the system , Password files are usually stored in $ORACLE_HOME/dbs Under the table of contents , The file named orapw$SID; stay Windows In the system , Password files are usually stored in %ORACLE_HOME%\database Under the table of contents , The file named PWD$SID.ora.

( Two )Oracle Two authentication methods

stay Oracle There are two special types of permissions in SYSDBA and SYSOPER, When DBA When you need to maintain and manage the database, you must have one of these two special permissions . When the database is not open , You can't log in to the database by using the account built in the database , But have SYSDBA or SYSOPER Users with permissions can log in . There are two ways to authenticate whether a user has these two types of special permissions :OS Authentication and password file Authentication .Oracle What is the use of the database OS Authentication or password file authentication depends on the following three factors :

① $ORACLE_HOME/network/admin/sqlnet.ora Parameters in the parameters file SQLNET.AUTHENTICATION_SERVICES Set up

② PFILE(SPFILE) Parameters in the parameters file REMOTE_LOGIN_PASSWORDFILE Set up

③ Password file :$ORACLE_HOME/dbs/orapw$ORACLE_SID( stay Linux in ) | %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora( stay Windows in )

Oracle The basic order of authority authentication is this , First of all SQLNET.AUTHENTICATION_SERVICES To determine whether to use OS Authentication or password file Authentication , If you use password file authentication, it depends on the parameters REMOTE_LOGIN_PASSWORDFILE Whether the settings and password files of exist : If REMOTE_LOGIN_PASSWORDFILE The parameter is set to not NONE And if the password file exists, you can use the password file authentication normally , Otherwise it will fail .

Using authentication integrated with the operating system , for example :sqlplus / as sysdba、sqlplus "/ as sysdba" 、sqlplus sys/lhrsasa as sysdba、sqlplus xx/xx as sysdba、sqlplus xx/xx as sysoper、sqlplus / as sysoper All belong to OS authentication , As long as it is used on this machine as sysdba or as sysoper Identity does not include TNS Way to log in , It's all done first OS verification , If not OS verification , Then log in with password file verification . It should be noted that , command “sqlplus / as sysdba” Always with OS Login by authentication .

Use Oracle Database password file for identity authentication , for example :sqlplus lhr/[email protected], Just use TNS In the way of sysdba or sysoper Identity login database , It's all password file verification . in addition , As long as the password is entered , It is possible to use password file for login authentication , for example :sqlplus sys/lhrsasa as sysdba.

( 3、 ... and )SQLNET.AUTHENTICATION_SERVICES Parameters

This parameter indicates how to log in to the database , Need to be in sqlnet.ora( be located $ORACLE_HOME/network/admin Directory ) Set this in the file . When it needs to be modified , You can directly open the file with a text editor to modify it . For different operating systems ,SQLNET.AUTHENTICATION_SERVICES It's going to be a little different , The following settings are usually used :

l SQLNET.AUTHENTICATION_SERVICES = (ALL)

Yes Linux In terms of system , Express support OS Authentication and remote login password file Authentication . however , If you listen on the local database server (tns) Connect to database , Then password file authentication will no longer be supported , It's a mistake :ORA-12641: Authentication service failed to initialize. therefore , stay Linux In the system , It is recommended that this parameter be commented out .

Yes Windows System , The actual experiment does not support this parameter , Validation failed . Report errors :ORA-12641: The authentication service failed to initialize (ORA-12641:Authentication service failed to initialize)

l SQLNET.AUTHENTICATION_SERVICES = (NTS)

This setting value is only used for Windows NT System , This setting also supports OS Authentication and password file Authentication , It's only in settings (NTS) After the value, run in Windows On the system Oracle To support OS authentication . if Linux This parameter is set on the system , Specifies the Oracle Only use password file Authentication .

l SQLNET.AUTHENTICATION_SERVICES = (NONE)

This setting is in the Windows and Linux It works the same way , Appoint Oracle Only use password file Authentication .

l  Do not set this parameter or sqlnet.ora The file does not exist or SQLNET.AUTHENTICATION_SERVICES =

Yes Linux System , The default support OS Authentication and password file Authentication . therefore , stay Linux In the system , It is recommended that this parameter be commented out .

Yes Windows System , Only password file authentication is supported by default , I won't support it OS authentication .

In the end, I'd like to make a conclusion , stay Linux Operating system , It is recommended that the parameter SQLNET.AUTHENTICATION_SERVICES Comment out . stay Windows Operating system , It is recommended to set parameters SQLNET.AUTHENTICATION_SERVICES = (NTS).

( Four )REMOTE_LOGIN_PASSWORDFILE Parameters

REMOTE_LOGIN_PASSWORDFILE Parameter setting specifies how the database uses the password file , This parameter can be set to three values :

l REMOTE_LOGIN_PASSWORDFILE = NONE  # Don't use password files

l REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE  # Use the password file , But only one database instance can use

l REMOTE_LOGIN_PASSWORDFILE = SHARED  # Multiple database instances share one password file , Under this setting, other database users cannot be added to the password file as users with special permissions .

REMOTE_LOGIN_PASSWORDFILE Parameter belongs to initialization parameter , Only in init.ora/pfile Or use the following statement to modify when the database is open , Then restart the database .

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE SCOPE = SPFILE ;

To check the current REMOTE_LOGIN_PASSWORDFILE The set value of is at login Oracle Then enter the following command

[email protected]> show parameter remote_login_passwordfile
2
3NAME                                 TYPE        VALUE
4
5------------------------------------ ----------- ------------------------------
6
7remote_login_passwordfile            string      EXCLUSIVE
     


( 5、 ... and ) The establishment of password file

have access to orapwd Command to rebuild the password file :

 1[[email protected] dbs]$ orapwd
 2
 3Usage: orapwd file=<fname> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>
 4
 5
 6
 7  where
 8
 9    file - name of password file (required),  
10
11    password - password for SYS will be prompted if not specified at command line,
12
13    entries - maximum number of distinct DBA (optional),
14
15    force - whether to overwrite existing file (optional),
16
17    ignorecase - passwords are case-insensitive (optional),
18
19    nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only).
20
21
22
23  There must be no spaces around the equal-to (=) character.
     

The following points need to be noted :

① Use orapwd After regenerating the password file , Previously saved granted to other users SYSDBA or SYSOPER Permissions will be lost , It needs to be renewed GRANT. Before generating the password file, you can pass V$PWFILE_USERS The view queries out the currently granted SYSDBA/SYSOPER Privileged user , Then, after the password file is regenerated, these users are granted SYSDBA/SYSOPER jurisdiction .

② entries How many can there be SYSDBA or SYSOPER Put the user with permission into the password file , Remove duplicate records . there entries But not the actual number , This is binary data . Set entries The value is not modifiable , If you want to modify entries You need to regenerate the password file .

( 6、 ... and ) There are several ways to modify the content of password file

The following operations will cause the password file to be modified :

① Use orapwd establish , Modify password file

② use alter user sys identified by <>

③ Use grant sysdba to <> or grant sysoper to <> or revoke sysdba |sysoper from <>

It should be noted that , Each time the Oracle The system uses GRANT SYSDBA/SYSOPER Grant new users special privileges or ALTER USER Command modification has SYSDBA/SYSOPER The password of the user with permission ,Oracle Will automatically change the password file , Add or modify the corresponding items , This ensures that users with special permissions can log in to the database for management operations when the data is not opened .

In the use of ALTER USER modify SYS Password time , The password in the password file will be changed at the same time , bring into correspondence with ; If it's a manually created password file , Then the password in the password file can be the same as SYS The same password can be different , Does not affect password file authentication login . But it's still recommended that the database SYS The password matches the password in the password file , In order to avoid the need for emergency remote login password instead of right , Cause maintenance problems . Inquire about MOS There are also related documents on :Password or SYS Doesn't Match the Hash Value in USER$ ( file ID 2139304.1).