ora-01031 resolution Method

Today encountered this error, the solution is not clear after the principle of the Internet after the inquiry to understand some, reproduced online articles as records, but also for your reference.

The main is Sqlnet.ora file and Remote_login_passwordfile parameter influence.


One of the things that I have always remembered is that sqlnet.authentication_services= (NTS) is one of the prerequisites for using OS authentication.
Today a chance to know that the conclusion is not completely accurate.


In the tests in this article, the values for Remote_login_passwordfile are exclusive and the groups associated with the user are set correctly.

First look at the tests under Windows:
--Set to Nts,os validation successful
E:oracleora92in>cat.. Etworkadminsqlnet.ora
Sqlnet. Authentication_services= (NTS)
E:oracleora92in>sqlplus "/as sysdba"

Sql*plus:release 9.2.0.1.0-production on Wednesday August 15 22:34:56 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connect to:
Oracle9i Enterprise Edition Release 9.2.0.1.0-production
With the partitioning, OLAP and Oracle Data Mining options
Jserver Release 9.2.0.1.0-production

Sys@oracle9i>

--Comment out the contents of Sqlnet.ora, OS validation successful
E:oracleora92in>cat.. Etworkadminsqlnet.ora
#SQLNET. Authentication_services= (NTS)

Sign in again:
E:oracleora92in>sqlplus "/as sysdba"

Sql*plus:release 9.2.0.1.0-production on Wednesday August 15 22:36:09 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
Ora-01031:insufficient Privileges

--Set to None,os validation failed
E:oracleora92in>cat.. Etworkadminsqlnet.ora
Sqlnet. Authentication_services= (NONE)
E:oracleora92in>sqlplus "/as sysdba"

Sql*plus:release 9.2.0.1.0-production on Wednesday August 15 22:50:33 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
Ora-01031:insufficient Privileges

--Set to All,os validation successful
E:oracleora92in>cat.. Etworkadminsqlnet.ora
Sqlnet. Authentication_services= (All)
E:oracleora92in>sqlplus "/as sysdba"

Sql*plus:release 9.2.0.1.0-production on Wednesday August 15 22:51:21 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connect to:
Oracle9i Enterprise Edition Release 9.2.0.1.0-production
With the partitioning, OLAP and Oracle Data Mining options
Jserver Release 9.2.0.1.0-production

Sys@oracle9i>

Logon failure indicates that the current setting does not allow operating system authentication.
This example also illustrates the following: under Windows, sqlnet. Authentication_services must be set to NTS or all to use OS authentication.

And then look at the situation under Linux:

--Set to Nts,os validation failed
[Oracle@primary admin]$ Cat Sqlnet.ora
Sqlnet. Authentication_services= (NTS)
[Oracle@primary admin]$ sqlplus "/as sysdba"

Sql*plus:release 10.2.0.3.0-production on Wed Aug 15 23:08:53 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
Ora-01031:insufficient Privileges

--Comment out, equivalent to nothing set, OS validation successful
[Oracle@primary admin]$ Cat Sqlnet.ora
#SQLNET. Authentication_services= (NTS)
[Oracle@primary admin]$ sqlplus "/as sysdba"

Sql*plus:release 10.2.0.3.0-production on Wed Aug 15 23:06:17 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-production
With the partitioning and Data Mining options

Sql>

--Set to None,os validation failed
[Oracle@primary admin]$ Cat Sqlnet.ora
Sqlnet. Authentication_services= (NONE)
[Oracle@primary admin]$ sqlplus "/as sysdba"

Sql*plus:release 10.2.0.3.0-production on Wed Aug 15 23:07:07 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
Ora-01031:insufficient Privileges

--Set a value randomly, OS validation failed
[Oracle@primary admin]$ Cat Sqlnet.ora
Sqlnet. Authentication_services= (AAA)
[Oracle@primary admin]$ sqlplus "/as sysdba"

Sql*plus:release 10.2.0.3.0-production on Wed Aug 15 23:14:45 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
Ora-01031:insufficient Privileges

--Set to All,os validation successful
[Oracle@primary admin]$ Cat Sqlnet.ora
Sqlnet. Authentication_services= (All)
[Oracle@primary admin]$ sqlplus "/as sysdba"

Sql*plus:release 10.2.0.3.0-production on Wed Aug 15 23:07:54 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-production
With the partitioning and Data Mining options

Sql>

From the above test know: Under Linux, the Sqlnet.authentication_services value is set to all, or not set, the OS authentication can succeed.

From the test you can see that Windows and Linux to implement OS validation, Sqlnet. Authentication_services set requirements are not the same, or even the opposite, why.

Let's take a look at Oracle's explanation of this setting:
Sqlnet. Authentication_services
Purpose

Use the parameter sqlnet. Authentication_services to enable one or more authentication SERVICES. IF authentication has been installed, it is recommended which this parameter are set to either none or to one of the authent Ication methods.
Default
None

Values
Authentication Methods Available with Oracle Net Services:

* None for no authentication methods. A valid username and password can is used to access the database.
* All to all authentication methods
* NTS for Windows NT native authentication

Windows NT native Authentication

An authentication method so enables a client single login access to a Windows NT server and a database running on the SE RVer.

The explanation from Oracle can be known, sqlnet. Authentication_services= (NTS) is dedicated to Windows systems and is not applicable to Linux/unix.

Finally, make a simple summary:
1, under Windows, Sqlnet. Authentication_services must be set to NTS or all to use OS authentication, not set or set to any other value to use OS authentication.
2, in Linux, the Sqlnet.authentication_services value is set to all, or not set, the OS authentication can be successful, set to any other value can not use the OS authentication.


===============


1.OS Certification
OS authentication is enabled by default after Oracle installation, and the OS authentication mentioned here refers to server-side OS authentication. OS authentication means that the user and password checksums of the login database are placed at the operating system level. If you log on to the OS when you install Oracle, you do not need any authentication when you log on to the Oracle database, such as:
Sql> Connect/as SYSDBA
is connected.
Sql> Connect Sys/aaa@test 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 access to the database, can connect, and the connection user is sys, so convenient, sometimes, if you forget the password of the database, and want to log in to the database, you can through this way The premise is on the database server.

But the convenience also brought some security risks, so many people want to block the OS certification,

Under win, simply change the sqlnet.authentication_services= (NTS) NTS in the Oracle_home/network/admin/sqlnet.ora to none or comment out the sentence (plus # in front), can block the OS function, to the SYS users to connect to the database must enter the correct sys password, or can be the installation of Oracle users from the group Ora_dba removed, of course, can also directly ora_dba this group also deleted, can screen OS features.
Sql> Connect/as SYSDBA
ERROR:
ORA-01031: Insufficient Permissions
Sql> Connect sys/aaa as Sysdba
ERROR:
ORA-01017: Invalid username/password; Login denied
Sql> Connect aaa/bbb as Sysdba
ERROR:
ORA-01031: Insufficient Permissions
Sql> Connect Sys/system as Sysdba
is connected.
Sql>

You can also add sqlnet.authentication_services= (none) to the file Sqlnet.ora and remove the DBA (Groupdel dba) under Unix/linux. Group or remove an Oracle user from the DBA group can block OS authentication. The use of these two methods to screen the OS functionality seems to be somewhat reassuring, or not entirely convincing, because, after all, the system administrator can create ORA_DBA or DBA groups and modify Sqlnet.ora files ...


2. password file
Oracle's password file is used to hold all users who connect to the database with SYSDBA or sysoper permissions, and if you want to connect to the database remotely with SYSDBA permissions, you must use a password file, otherwise you cannot connect because the SYS user must either SYSDBA or Sysoper means that the SYS user must use a password file to connect to the database, so I don't think it makes any sense to store the SYS user's password in the database. The advantage of using a password file is that even if the database is not open, you can still connect to the database through password file validation. Beginning the installation of Oracle, did not give ordinary users 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 to the password file saved, Of course, you must require the database to be in an open state. Such as:
Sql> Grant SYSDBA to test;
The authorization was successful.
Sql> Connect TEST/AAA@ORCL as Sysdba
ERROR:
ORA-01017: Invalid username/password; Login denied
Warning: You are no longer connected to ORACLE.
Sql> Connect TEST/TEST@ORCL as Sysdba
is connected.
Sql> ALTER DATABASE close;
The database has changed.
Sql> Grant Sysdba, sysoper to test;
Grant SYSDBA, Sysoper to test
*
Line 1th Error:
ORA-01109: Database not open

In the end, a few users have been granted Sysdba or Sysoper permissions, can be queried as follows V$pwfile_users obtained, 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


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

There is also a problem modified password, password length increased, supposedly occupy more space, the fact is that no matter how long our password, after the length of encryption is almost the same, that is, the password file size and password specified length of almost little relationship.

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 is no space before and after. It is also worth mentioning that 10g adds a new parameter force default value N, which acts like a reuse function when creating a tablespace, overwriting when a file of the same name exists.

Whether the password file is used is controlled by a parameter remote_login_passwordfile provided by Oracle, Remote_login_passwordfile has none,shared,exclusive3 value,
None means that password files are not used, password file authentication is disabled, and the Oracle database does not allow remote Sysdba/sysoper identity logins
Exclusive represents the instance exclusive use of the password file, which is the individual instance using a separate password file,
Shared means that multiple instances share a password file, and by default, the format of the password file under win is Orapwsid (case sensitive), and the Oracle database starts by Pwdsid.ora,unix the first Orapw<sid > Password file, if the file does not exist, then start looking, orapw password file, if the password file named ORAPW, multiple databases can be shared.

Sql> alter system set remote_login_passwordfile=exclusive Scope=spfile;

3. Modify User Password

View User
Sql> select Username,password from Dba_users;

sql> alter user system identified by manager;

4. Sys/system Password Loss processing method:

1. Query view V$pwfile_users,select * from V$pwfile_users;
Record user information with SYSOPER/SYSDBA system privileges
2). Close the database shutdown immediate
3). Delete password file, the file path is generally: oracle_home\database, file name is Pwd<sid>. ORA
4). Create a password file
Orapwd file=< FILENAME > PASSWORD =< PASSWORD
5. Add user to password file
CONNECT Sys/internal_user_passsword as SYSDBA;
Start the database instance and open the database, create the corresponding user account, authorize it
Grant permission: Grant SYSDBA to USER_NAME (this step is not done if the previous database has only sys SYSDBA permissions)
6. Modify password file status, the state of the default password file is shared, to initialize the parameters in the
Remote_login_passwordfile Set as Exclusive

Sql> alter system set remote_login_passwordfile=exclusive Scope=spfile;

also attached:

Sqlplus "/As SYSDBA" not even on, reported ora-01031:insufficient privileges solution

Note that when multiple database instances, set oracle_sid= ',

1, check whether the Sqlnet.ora (Windows located in the%oracle_home%networkadmin directory) contains this sentence: sqlnet.authentication_services= (NTS), no words plus

2. Check that users who log on to Windows (the administrator or the user who installed Oracle) are not included in the ORA_DBA group and this behavior may occur if the domain user is not connected to the domain server.

3. To ensure the remote_login_passwordfile parameter = EXCLUSIVE.

4. See if you need to use ORAPASSW to generate password files.

A workaround case:

1, check the system parameters:
Sql> Show parameter password

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Remote_login_passwordfile string EXCLUSIVE
2,
SELECT * from V$pwfile_users;
Sql>
is empty

3,
sql> Grant SYSDBA to SYS;
Grant SYSDBA to Sys
*
ERROR at line 1:
Ora-01994:grant failed:password file missing or disabled
4, the establishment of password documents
D:/>orapwd file= "D:/oracle/product/10g/db_1/database/pwdoratest.ora" PASSWORD=GP
Oswong entries=10
5,
Sql> select * from V$pwfile_users;

USERNAME Sysdb SYSOP
------------------------------ ----- -----
SYS true True
SYS is displayed normally.
6, the remote to SYSDBA login, can be used normally.

If the password file is created with a problem, it will also be reported as an error:

Ora-01031:insufficient Privileges

The password file should have a named format of Orapwsid and the SID is case-sensitive. Because the target database needs to authenticate the password when it connects to auxiliary database,
If the above rules are violated, ora-01031:insufficient privileges will be prompted.

I was using Linux to create a duplicate database because the password file created a wrong path and name to encounter this error------------end----------------