Role and description of password file in Oracle

Source: Internet
Author: User

The password file of Oracle is located in the $ ORACLE_HOME/DBS directory. The naming rule is orapw <Sid>. If sid = linuxdb, the file name is orapwlinuxdb. The file is encrypted, if you use the text editing tool to open it, you will see Garbled text.

Before the database is started, the database's built-in users cannot use the database to authenticate their identities. The password file stores the username and password of sysdba/sysoper users. Users can use the password file for verification and log on to the database before it is started. If there is no password file, you can only pass operating system authentication before the database is started. when RMAN is used, the database needs to be processed in nomount, mount, and other States. Therefore, sysdba permissions are usually required. If the database belongs to a local DBA group, you can log on to the database through operating system authentication. If the database is remotely logged on to sysdba, passwordfile authentication is required.
  
1. remote_login_passwordfile = none Password File verification is disabled at this time. Oracle database does not allow remote sysdba/sysoper logon to manage local operations such as database start/stop:

[Oracle @ jumper Oracle] $ sqlplus "/As sysdba" SQL * Plus: Release 9.2.0.3.0-production on Thu Apr 15 09:58:45 2004 copyright (c) 1982,200 2, Oracle Corporation. all rights reserved. connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0-productionwith the partitioning, OLAP and Oracle Data Mining optionsjserver release 9.2.0.3.0-productionsql> alter user SYS identified by Oracle; user altered. SQL> show parameter passname type value --------------------- ----------- -------------------------------- remote_login_passwordfile string none

Remote:

E:/Oracle/ora92/bin> sqlplus/nologsql * Plus: Release 9.2.0.4.0-production on Thursday April 15 09:39:22 2004 copyright (c) 1982,200 2, Oracle Corporation. all rights reserved. SQL> connect sys/Oracle @ hsjf as sysdbaerror: ORA-01017: invalid username/password; logon denied

The password file cannot be verified.

2. remote_login_passwordfile = exclusive

SQL> alter system set remote_login_passwordfile = exclusive scope = spfile; System altered. SQL> startup force; Oracle instance started. total system global area 131142648 bytesfixed size 451576 bytesvariable size 104857600 bytesdatabase buffers 25165824 bytesredo buffers 667648 bytesdatabase mounted. database opened. SQL> show parameter passname type value types ------------- ------------------------------ remote_login_passwordfile string exclusivesql> alter user SYS identified by Oracle; user altered.

Remote:

E:/Oracle/ora92/bin> sqlplus/nologsql * Plus: Release 9.2.0.4.0-production on Thursday April 15 09:47:11 2004 copyright (c) 1982,200 2, Oracle Corporation. all rights reserved. SQL> connect sys/Oracle @ hsjf as sysdba is connected. SQL> show useruser is "sys" SQL>

This is actually the login verification through the password file

3. Further test if passwdfile is deleted at this time, sysdba/sysoper will not be able to authenticate and log on to the database.

Server:

SQL>! [Oracle @ jumper Oracle] $ CD $ ORACLE_HOME/DBS [Oracle @ jumper DBS] $ ls orapwhsjforapwhsjf [Oracle @ jumper DBS] $ MV orapwhsjf. Bak [Oracle @ jumper DBS] $

Remote:

E:/Oracle/ora92/bin> sqlplus/nologsql * Plus: Release 9.2.0.4.0-production on Thursday April 15 09:50:14 2004 copyright (c) 1982,200 2, Oracle Corporation. all rights reserved. SQL> connect sys/Oracle @ hsjf as sysdbaerror: ORA-01031: insufficient privilegessql>

This is actually unable to verify the identity through the password file.

4. If passwdfile is lost and passwdfile is used, the database cannot be started.

SQL> startup force; Oracle instance started. total system global area 131142648 bytesfixed size 451576 bytesvariable size 104857600 bytesdatabase buffers 25165824 bytesredo buffers 667648 bytesORA-01990: error opening Password File '/opt/Oracle/product/9.2.0/dbs/orapw' ora-27037: unable to obtain file statuslinux error: 2: no such file or directoryadditional information: 3

In this case, you can use orapwd to recreate the password file.

SQL>! [Oracle @ jumper Oracle] $ MV $ ORACLE_HOME/dbs/orapwhsjf. Bak orapwhsjf [Oracle @ jumper Oracle] $ exitexitsql> alter database open; database altered. SQL>

This is roughly the case.

5. remote_login_passwordfile = shared

Let's take a look at the instructions in Oracle9i:

Shared

More than one database can use a password file. However, the only user recognized by the password file is sys.

It means that multiple databases can share one password file, but only one user can be identified: SYS

SQL> select * from V $ pwfile_users;

Username sysdb sysop ------------------------------ ----- sys true

SQL> grant sysdba to eygle; grant sysdba to eygle * error at line 1: ORA-01994: grant failed: cannot add users to public Password File

SQL> show parameter Password

Name type value ------------------------------------ ----------- -------------------------------- remote_login_passwordfile string shared

We can see that users cannot be added to the password file at this time.

Many people have doubts that the default password file name is orapw <Sid>. How can this problem be shared?

In fact, when the Oracle database is started, it first looks for the password file of orapw <Sid>. If the file does not exist, it starts searching, if the password file of orapw is named orapw, multiple databases can share the file.

Let's take a look at the test:

[Oracle @ jumper DBS] $ sqlplus "/As sysdba" SQL * Plus: Release 9.2.0.3.0-production on TUE Jul 6 09:40:34 2004 copyright (c) 1982,200 2, Oracle Corporation. all rights reserved. connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0-productionwith the partitioning, OLAP and Oracle Data Mining optionsjserver release 9.2.0.3.0-productionsql> shutdown immediatedatabase closed. database dismounted. Oracle instance shut down. SQL>! [Oracle @ jumper DBS] $ lshsjf initdw. ora inithsjf. ora init. ora lkhsjf orapwhsjf spfilehsjf. ora [Oracle @ jumper DBS] $ MV orapwhsjf. bak [Oracle @ jumper DBS] $ exitexitsql> startuporacle instance started. total system global area 235999908 bytesfixed size 451236 bytesvariable size 201326592 bytesdatabase buffers 33554432 bytesredo buffers 667648 bytesORA-01990: error opening Password File '/opt/Oracle/product/9.2.0/dbs/orapw' -- this is the final file ORA-27037: unable to obtain file statuslinux error: 2: no such file or directoryadditional information: 3

We have created an orapw password file. At this time, we can open the database.

SQL>! [Oracle @ jumper DBS] $ lshsjf initdw. ora inithsjf. ora init. ora lkhsjf orapwhsjf. bak spfilehsjf. ora [Oracle @ jumper DBS] $ CP orapwhsjf. bak orapw [Oracle @ jumper DBS] $ exitexitsql> alter database open; database altered. SQL> show parameter passwname type value ------------------------------------ ---------------------------------------- remote_login_passwordfile string sharedsql>

So you may have this question: Can multiple exclusive databases share one password file (orapw?

Let's continue this experiment:

SQL> show parameter Password

Name type value ------------------------------------ ----------- -------------------------------- remote_login_passwordfile string shared

[Oracle @ jumper DBS] $ strings orapw]/[zoracle Remote Password fileinternalab27b53edc5fef418a8f025737a9097a

Note that only the internal/sys password is recorded here.

Remote_login_passwordfile = exclusive

SQL> alter system set remote_login_passwordfile = exclusive scope = spfile;

System altered.

SQL> startup force; Oracle instance started.

Total system global area 235999908 bytesfixed size 451236 bytesvariable size 201326592 bytesdatabase buffers 33554432 bytesredo buffers 667648 bytesdatabase mounted. database opened. SQL>! [Oracle @ jumper bin] $ CD $ ORACLE_HOME/DBS [Oracle @ jumper DBS] $ strings orapw]/[zoracle remote password login [Oracle @ jumper DBS] $ exitexit

Note that after the instance is started in exclusive mode, the Instance name information is written into the password file.

If other instances start in exclusive mode, you can still use this password file. The instance name in the password file is rewritten at the same time.

That is to say, the database only reads the password file during the startup process. The file is not locked during the database operation, similar to the pfile/spfile file.

SQL> select * from V $ pwfile_users;

Username sysdb sysop ------------------------------ ----- sys true

Supplement:
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:

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

2. Exclusive: indicates that only one database instance can use this password file. Only the password file under this setting can contain user information except sys, that is, the system permission sysoper/sysdba can be granted to other users except sys. The default value is set in Oracle 9i. Some friends say shared, but not actually.

3. Shared: indicates that multiple database instances can use this password file. In this setting, only the 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.

---- 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.