The perfect analysis of the insufficient rights error of Oracle report ORA-1031 logged in as Sysdba

On the Linux operating system database server, when you log on to an Oracle 10.2 DB instance using "Sqlplus/as sysdba", the login fails with the ORA-01031: insufficient permissions.

The operation that is logged on the server where the database is located is logged on with the operating system authentication method to the database instance. When "ORA-01031" error occurs, first check the configuration information of the Sqlnet.authentication_services item in the Sqlnet.ora file. This kind of analysis is recommended by many people, and many posts are mentioned on the Internet.

The general conclusion is that:

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 cannot use OS authentication.

2, under Linux, if the value of Sqlnet.authentication_services is set to all, or not set, OS authentication succeeds; no other value can be used for OS authentication.

First of all, there is no mistake in this method of analysis. When I encounter this problem, the first thing to analyze is the configuration of this file.

In this case, however, the contents of the Sqlnet.ora file are as follows:

[Email protected] ~]$ More/u01/app/oracle/product/10.0.2/db_1/network/admin/sqlnet.ora

# Sqlnet.ora Network Configuration File:/u01/app/oracle/product/10.0.2/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES. Directory_path= (TNSNames)

Do you think, also persist the initialization parameter of the instance Remote_login_passwordfile, also have the password file. These two points are only used when you log on to the DB instance using SYSDBA on a remote server, and it is now the case to log on to the database instance on the local database server.

(Miki West Tour @mikixiyou original link: mikitp://mikixiyou.iteye.com/blog/1584891)

Then analyze the behavior of the operating system user after logging into the operating system and then accessing the database instance.

Using the SYSDBA identity to connect to a DB instance, the authentication method used is the operating system authentication, which is the process by which the operating system verifies that the user can connect to the database based on the information it manages.

Therefore, further analysis is required if the operating system determines that the Oracle user meets the authentication criteria. Obviously, the permissions of the Oracle users here are denied by the operating system or the Oracle system.

Oracle believes that users who need to meet the following criteria can connect to a DB instance using System authentication.

1, the system user Oracle must belong to a specific group

The user must belong to the OSDBA group to log on as SYSDBA. The OSDBA group here will have different names on various types of operating systems. On UNIX systems, the name is "DBA" and the name on the Windows system is "ORA_DBA".

2, the parameters of Sqlnet.authentication_services must be set correctly

On UNIX systems, the parameter value must be set to all, or not, on Windows, it must be set to NTS.

We go back to the environment to check the status of the group that the system user Oracle belongs to.

[[email protected] ~]$ ID Oracle

uid=501 (Oracle) gid=501 (oinstall) groups=501 (Oinstall)

The user is found to belong to Oinstall only, without a DBA.

Therefore, we use USERMOD to make adjustments to the user in the root environment. As shown below:

[Email protected] ~]# usermod-g oinstall,dba-g oinstall Oracle

[[email protected] ~]# ID Oracle

uid=501 (Oracle) gid=501 (oinstall) groups=501 (Oinstall), 503 (DBA)

Verify the SYSDBA identity logon operation again, as follows:

[Email protected] ~]# su-oracle

[Email protected] ~]$ Sqlplus/as SYSDBA

Sql*plus:release 10.2.0.4.0-production on Wed Jul 11 09:47:50 2012

Copyrigmiki (c) 1982, Oracle. All Rigmikis Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the partitioning, OLAP, Data Mining and Real application testing options

The result is that the login is successful and the problem is that the Oracle user is not in the DBA group.

In summary, when you log in to a DB instance locally as SYSDBA, you encounter an error with insufficient ORA-01031 permissions, requiring the system user's group to have DBA and Sqlnet.ora files sqlnet.authentication_services is configured correctly.

If there is still a problem, there is another way to continue the analysis. Analyze the Sqlplus application using the operating system Analysis tool.

Linux:
Strace-o/tmp/strace_sysdba.output-cft Sqlplus/as SYSDBA

AIX, Solaris:
Truss-fea-o/tmp/truss_sysdba.output Sqlplus/as SYSDBA

HP-UX:
TUSC-AFPO/TMP/TUSC_SYSDBA.OUTPUT-AEF Sqlplus/as SYSDBA

Analysis tools can be used to refer to mikitp://mikixiyou.iteye.com/admin/blogs/1489090

Based on the analysis, the problem can be further judged.

The main references in this document are Oracle's "Troubleshooting ora-1031:insufficient privileges while connecting as SYSDBA [ID 730067.1]" article.

The perfect analysis of the insufficient rights error of Oracle report ORA-1031 logged in as Sysdba