Oracle OS certified password file loss handling

Oracle OS certified password file password loss processing classification: Oracle Basic knowledge2009-10-19 14:24 5031 people read reviews (9) Favorites report oracleos database SQL Telnet Login

One. OS Authentication

1.1 OS Certification Introduction

After Oracle is installed, OS authentication is enabled by default, and the OS authentication mentioned here refers to server-side OS authentication. OS authentication means that the user and password checksum of the login database is placed at the operating system level. If you log on to the OS as a user who is installing Oracle, then no validation is required when you log in to the Oracle database, such as:

Sql> Connect/as SYSDBA

is connected.

Sql> connect Sys/[email protected] as Sysdba

is connected.

Sql> Connect sys/bbb as Sysdba

is connected.

Sql> Connect aaa/bbb as Sysdba

is connected.

Sql> Show User

SYS

Sql>

No matter what user input (even if this user, such as AAA in the database does not exist), as long as the SYSDBA permissions to connect to the database, and connect the user is the SYS, this is very convenient, sometimes, if you forget the password of the database, but also want to log into the database, you can do this way, The premise is on the database server.

1.2 OS authentication-related parameters

The Oracle database implements OS authentication with the following 3 parameters:

(1) The sqlnet in Sqlnet.ora. The authentication_services parameter.

(2) parameters in the PFILE (or spfile) file Remote_login_passwordfile

(3) Password file Pwdsid.ora (Windows) or Orapwsid (Linux, Case sensitive).

1.2.1 Sqlnet.ora file parameters

File location: $ORACLE _home/network/admin/sqlnet.ora

Sqlnet. Authentication_services= (NTS)

NAMES. Directory_path= (TNSNames, Ezconnect)

Parameters can have the following values:

Sqlnet. authentication_services= (NTS) | (NONE)

which

(NTS): Indicates the operating system authentication method, does not use the password file, the default value.

(NONE): Password file authentication method

1.2.2 Remote_login_passwordfile Parameters

The parameter can have the following values:

Remote_login_passwordfile = ' NONE ' | ' EXCLUSIVE ' | ' SHARED '

(1) NONE: Do not use password file login, do not allow remote users to log into the system with SYS, you can modify the sys password online;

(2) EXCLUSIVE: Default value. Allow only one database to use the password file, allow remote logins, allow non-SYS users to manage the database as SYSDBA, and modify the sys password online. In this mode, the password file can contain passwords for multiple privileged Oracle accounts. This is the recommended mode of operation, especially when running Rman. If you want to connect Rman to a database from a remote client, you must use this parameter setting.

(3) SHARE: You can use a password file for multiple databases. In fact, when the Oracle database starts, it looks for the orapw<sid> password file first, if the file does not exist, it starts to find, ORAPW password file if the password file is named ORAPW, multiple databases can be shared, remote login allowed, You can only use SYS for SYSDBA management and to modify the sys password online. In this setting, only the Internal/sys account can be identified, even if the file contains information about other users, they are not allowed to log in with SYSOPER/SYSDBA.

Modify:

Sql>alter SYSTEM SET remote_login_passwordfile=exclusive scope=spfile;

It is important to note that this parameter is not a dynamic parameter. The database needs to be modified in the Mount state, and the database will need to be restarted after the change, and the parameters will be set to take effect.

1.3 Disabling OS Authentication

OS authentication has a certain security risk, we can block OS authentication.

1.3.1 under Windows

Under win just change the Oracle_home/network/admin/sqlnet.ora in the sqlnet.authentication_services= (NTS) nts to none or comment out this sentence (in front add #), You can block the OS function, you must enter the correct sys password to connect the database with the SYS user, or you can remove the Oracle installation user from the group ORA_DBA, or you can also delete the ORA_DBA group directly, all of which can block the OS function.

Such as:

Sql> Connect/as SYSDBA

ERROR:

ORA-01031: Insufficient Permissions

Sql> Connect sys/aaa as Sysdba

ERROR:

ORA-01017: Invalid username/password; Login is denied

Sql> Connect aaa/bbb as Sysdba

ERROR:

ORA-01031: Insufficient Permissions

Sql> Connect Sys/system as Sysdba

is connected.

Sql>

1.3.2 Linux/unix

Adding sqlnet.authentication_services= (None) to the file Sqlnet.ora and removing the DBA (Groupdel dba) group or removing the Oracle user from the DBA group can mask OS authentication.

Attention:

With this masking method, the system administrator can create a ORA_DBA or DBA group and modify the Sqlnet.ora file.

Two. password file

2.1 Password File description

The purpose of the Oracle password file is to store all users who connect to the database with SYSDBA or sysoper permissions, if you want to remotely connect to the database with SYSDBA permissions, you must use a password file, otherwise you cannot connect, because the SYS user must connect to the database with the SYSDBA or Sysoper mode, that is, SYS users want to connect to the database must use a password file, so I think the database stored in the SYS user's password does not make any sense! The advantage of using a password file is that even if the database is not in the open state, you can still connect to the database through password file validation. Start the installation of Oracle, do not give ordinary users the SYSDBA permissions, password file only stored in the SYS password, if the SYSDBA permissions granted to ordinary users, then the ordinary user's password from the database read into the password file saved, Of course, you must require the database to be in open state. Such as:

Sql> Grant SYSDBA to test;

Authorization is successful.

Sql> connect Test/[email protected] as Sysdba

ERROR:

ORA-01017: Invalid username/password; Login is denied

Warning: You are no longer connected to ORACLE.

Sql> connect Test/[email protected] as Sysdba

is connected.

Sql> ALTER DATABASE close;

The database has changed.

Sql> Grant Sysdba, sysoper to test;

Grant SYSDBA, Sysoper to test

*

An error occurred on line 1th:

ORA-01109: Database is not open

2.2 Viewing users with SYSDBA permissions

The V$pwfile_users view can be queried to see how several users have been granted Sysdba or Sysoper permissions, and the V$pwfile_users information is derived from the password file.

Sql> select * from V$pwfile_users;

USERNAME Sysdb SYSOP

------------------------------ ----- -----

SYS true True

TEST TRUE FALSE

In the end there are several users can be granted SYSDBA or Sysoper permissions, is determined by the number of entries specified when the password file was created, not exactly, and ultimately the size of the OS block, if the entries specified 5, an OS Block can hold 8 user's password, then can be granted by 8 users sysdba or Sysoper.

Note: The fact is that the password is too long, the length of the encryption is almost the same, that is, the size of the password file and the length of the password specified is almost insignificant.

C:>orapwd File=databasepwd.ora Password=system entries=5

OPW-00005: Files with the same name-please delete or rename

C:>orapwd File=databasepwd.ora Password=system entries=5 force=y

To create a password file, note that there are no spaces before and after. A new parameter, force default value N, is added to the 10g, which acts like the reuse function when creating a tablespace, overwriting when a file of the same name exists.

Whether the password file is used is controlled by the Remote_login_passwordfile parameter. This parameter is described above.

2.3 Password file format

The format of the win under Password file is Pwdsid.ora (case sensitive).

The format under UNIX is orapwsid (case sensitive),

When the Oracle database starts, it first looks for the orapw<sid> password file, if the file does not exist, it starts to find, ORAPW's password file, if the password file is named ORAPW, multiple databases can be shared. After the password file is created, the database needs to be restarted for the new password file to take effect.

2.4 Sys/system Password Loss processing method

(1). Query view V$pwfile_users,select * from V$pwfile_users;

Records the user information that has SYSOPER/SYSDBA system permissions.

(2) Close the database shutdown immediate

(3) Renaming the password file

(4) Re-create password file with orapwd command

Orapwd FILE=/U01/APP/ORACLE/PRODUCT/10.2.0/DB_1/DBS/ORAPWORCL Password=admin

Entries=5 Force=y

(5) Add the first step of the user to the password file

Grant Sysdba, sysoper to user;

------------------------------------------------------------------------------

Blog:http://blog.csdn.net/tianlesoftware

Oracle OS certified password file loss handling