Ora-01031 Solution

I encountered this error today, but I am not quite clear about the problem after the solution. I will understand it after I have searched the internet. I will repost my online articles as a record for your reference.

It is mainly affected by the sqlnet. ora file and the remote_login_passwordfile parameter.

One of the things I keep in mind is that sqlnet. authentication_services = (ETS) is a required condition for operating system authentication.
This conclusion is not entirely accurate only by chance today.

In this test, the value of remote_login_passwordfile is exclusive and the user group is set correctly.

First look at the test in Windows:
-- The OS is successfully verified when the value is set to ETS.
E: oracleora92in> CAT .. etworkadminsqlnet. ora
Sqlnet. authentication_services = (CNT)
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,200 2, Oracle Corporation. All rights reserved.

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

-- The content of sqlnet. ora is commented out below, and the OS verification is successful.
E: oracleora92in> CAT .. etworkadminsqlnet. ora
# Sqlnet. authentication_services = (CNT)

Log On 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,200 2, Oracle Corporation. All rights reserved.

Error:
ORA-01031: insufficient privileges

-- Set to none. OS Verification 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,200 2, Oracle Corporation. All rights reserved.

Error:
ORA-01031: insufficient privileges

-- Set it to all. OS Verification 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,200 2, Oracle Corporation. All rights reserved.

Connect:
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 Failed, indicating that operating system authentication is not allowed in the current settings.
This example also shows that in windows, sqlnet. authentication_services must be set to ETS or all to use OS authentication.

Next let's look at the situation in Linux:

-- The OS verification fails when the value is set to ETS.
[Oracle @ primary admin] $ cat sqlnet. ora
Sqlnet. authentication_services = (CNT)
[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,200 6, Oracle. All rights reserved.

Error:
ORA-01031: insufficient privileges

-- Comment out, which means that nothing is set, and the OS verification is successful.
[Oracle @ primary admin] $ cat sqlnet. ora
# Sqlnet. authentication_services = (CNT)
[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,200 6, Oracle. All rights reserved.

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

SQL>

-- Set to none. OS Verification 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,200 6, Oracle. All rights reserved.

Error:
ORA-01031: insufficient privileges

-- A random value is set, and OS verification fails.
[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,200 6, Oracle. All rights reserved.

Error:
ORA-01031: insufficient privileges

-- Set it to all. OS Verification 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,200 6, Oracle. All rights reserved.

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

SQL>

From the test above, we know that in Linux, if the value of sqlnet. authentication_services is set to all or not, OS verification can be successful.

From the test, we can see that the Configuration Requirements for sqlnet. authentication_services are different, or even the opposite, for OS verification in Windows and Linux. Why?

Let's take a look at how Oracle explains 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 that this parameter be set to either none or to one of the authentication methods.
Default
None

Values
Authentication methods available with Oracle Net Services:

* None for no authentication methods. A valid username and password can be used to access the database.
* All For all authentication methods
* ETS for Windows NT native Authentication

Windows NT native Authentication

An authentication method that enables a client Single login access to a Windows NT server and a database running on the server.

From the Oracle explanation, we can know that sqlnet. authentication_services = (ETS) is dedicated to Windows systems and is not applicable to Linux/Unix.

Finally, let's make a simple summary:
1. In Windows, sqlnet. authentication_services must be set to ETS or all to use OS authentication. If it is not set or set to any other value, OS authentication cannot be used.
2. in Linux, if the value of sqlnet. authentication_services is set to all or is not set, OS authentication can be successful. If it is set to any other value, OS authentication cannot be used.

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

1. OS Authentication
After oracle is installed, OS authentication is enabled by default. The OS authentication mentioned here refers to the server-side OS authentication. OS authentication means that the user and password for database login are verified at the operating system level. If you log on to the OS when installing Oracle, no authentication is required when you log on to the Oracle database, for example:
SQL> connect/As sysdba
Connected.
SQL> connect sys/AAA @ test as sysdba
Connected.
SQL> connect sys/BBB as sysdba
Connected.
SQL> connect AAA/BBB as sysdba
Connected.
SQL> show user
Sys
SQL>
No matter which user you enter (even if this user, such as AAA, does not exist in the Database), you can connect to the database as long as you connect to the database with the sysdba permission and the user is sys, which is very convenient, sometimes, if you forget the Database Password and want to log on to the database, you can use this method on the database server.

But convenience also brings some security risks, so many people want to shield OS authentication,

In win, you only need to set ORACLE_HOME/Network/admin/sqlnet. sqlnet. authentication_services = (CNT) the system should be set to none or comment out this sentence (add # above) to block the OS function. to connect a sys user to a database, you must enter the correct sys password, you can also delete the Oracle installer from the ora_dba group. Of course, you can also delete the ora_dba group directly to disable the OS function. for example:
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
Connected.
SQL>

In Unix/Linux, the file sqlnet. add sqlnet. authentication_services = (none) and delete DBA (groupdel dBA) groups or delete oracle users from DBA groups can shield OS authentication. The use of these two methods to block OS functions seems a little unreassuring, or unconvincing, because after all, the system administrator can still create ora_dba or DBA groups and modify sqlnet. ora file ......

2. Password File
The Oracle password file is used to store the passwords of all users who connect to the database with the sysdba or sysoper permission. to remotely connect to the database with the sysdba permission, you must use the password file. Otherwise, you cannot connect to the database, because the Sys user must use the sysdba or sysoper method to connect to the database, that is, the Sys user must use a password file to connect to the database, therefore, I think it makes no sense to store the password of the Sys user in the database! The advantage of using the password file is that even if the database is not in the open state, you can still connect to the database through password file verification. After installing Oracle, the common user is not granted the sysdba permission. The password file only stores the Sys password. If the sysdba permission is then granted to the common user, at this time, the password of a common user will be read from the database and saved in the password file. Of course, the database must be in the open state. For example:
SQL> grant sysdba to test;
Authorization 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
Connected.
SQL> alter database close;
The database has been changed.
SQL> grant sysdba, sysoper to test;
Grant sysdba, sysoper to test
*
Row 3 has an error:
ORA-01109: the database is not open

Several users have been granted sysdba or sysoper permissions, which can be obtained by querying the following v $ pwfile_users. The information of V $ pwfile_users is obtained from the password file.

SQL> select * from V $ pwfile_users;
Username sysdb sysop
----------------------------------------
Sys true
Test True False

Several users can be granted sysdba or sysoper permissions, which is determined by the number of entries specified when the password file is created. To be precise, it is also related to the size of the OS block. If entries specifies 5, an OS block can store the passwords of eight users, then eight users can be granted sysdba or sysoper.

Another problem is that the password length is increased, and the space occupied by the password is too large. The fact is that no matter how long the password is, the encrypted length is almost the same, that is to say, the size of the password file is almost irrelevant to the length specified by the password!

C:> orapwd file = databasepwd. ora Password = system entries = 5
OPW-00005: files with the same name exist-please delete or rename
C:> orapwd file = databasepwd. ora Password = system entries = 5 force = y

Note that there are no spaces before and after creating the password file! It is also worth mentioning that a new force default value n is added for 10 Gb, which is similar to the reuse function when creating a tablespace and whether to overwrite a file with the same name when it exists.

Whether to use the password file is controlled by using the remote_login_passwordfile parameter provided by Oracle. remote_login_passwordfile has three values: None, shared, and exclusive,
None indicates that the password file is not used. Password File authentication is disabled. The Oracle database does not allow remote sysdba/sysoper logon.
Exclusive indicates that the instance exclusively uses the password file, that is, each instance uses a separate password file,
Shared indicates that multiple instances share one password file. By default, the password file format in win is pwdsid. the format of orapwsid in Ora and UNIX is orapwsid (case sensitive). 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, orapw password file. If the password file is named orapw, multiple databases can share the file.

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

3. Change User Password

// View the user
SQL> select username, password from dba_users;

SQL> alter user system identified by manager;

4. How to Deal with sys/system password loss:

1) query the view v $ pwfile_users, select * from V $ pwfile_users;
Record user information with sysoper/sysdba system Permissions
2) shut down the database shutdown immediate
3). Delete the password file. The file path is ORACLE_HOME \ database and the file name is PWD <Sid>. ora.
4). Create a password file
Orapwd file = <FILENAME> Password = <password>
5) Add a user to the password file
Connect sys/internal_user_passsword as sysdba;
Start the database instance and open the database; create a corresponding user account and authorize it
Grant permissions: grant sysdba to user_name (if the previous database only has sysdba permissions for sys, skip this step)
6) modify the password file status. The default password file status is shared.
Remote_login_passwordfile is set to exclusive

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

Appendix:

 

Sqlplus "/As sysdba" cannot connect, report ora-01031: insufficient privileges Solution

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

1. Check whether sqlnet. ora (in the % ORACLE_HOME % networkadmin directory in Windows) contains this sentence: sqlnet. authentication_services = (ETS). If not, add

2. Check whether the user logging on to Windows (administrator or the user used when installing Oracle) is included in the ora_dba group. This may occur when the domain user does not connect to the domain server.

3. Ensure that the remote_login_passwordfile parameter is exclusive.

4. Check whether you need to use orapassw to generate a password file.

One solution case:

1. Check System parameters:
SQL> show parameter Password

Name type value
-----------------------------------------------------------------------------
Remote_login_passwordfile string exclusive
2,
Select * from V $ pwfile_users;
SQL>
Null

3,
SQL> grant sysdba to SYS;
Grant sysdba to sys
*
Error at line 1:
ORA-01994: grant failed: password file missing or disabled
4. Create a password file
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
Sys is displayed normally.
6. log on remotely with sysdba and use it properly.

If the password file is created incorrectly, the following error is reported:

Ora-01031: insufficient privileges

The password file must be named in orapwsid, And the SID is case sensitive. Because the target database needs to verify the password when connecting to the auxiliary database,
If the above rule is violated, a prompt is displayed for ORA-01031: insufficient privileges.

This error occurs when I create a duplicate database in Linux because the path and name of the password file are incorrect.

------------ End ----------------