ORA-01031 Insufficient Privilege While Connecting As SYSDBA

Troubleshooting ORA-1031 Insufficient Privilege While Connecting As SYSDBA [ID 730067.1]

Generic Concepts

1. A local user can connect to the database as sysdba using either OS authentication or by using password file authentication.

2. A user from a remote machine can connect to the database as sysdba using password file authentication .
From 11g a remote user can also connect to the database as sysdba using kerberos

For more details on remote sysdba authentication using kerberos please refer to 11g New Features Guide.

Facts About Operating System Authentication

1. The following syntax is used while OS Authentication

sqlplus / as sysdba

2. A SQLNET.AUTHENTICATION_SERVICES controls the operation system authentication.

On unix platforms setting the value of this parameter to " ALL " or removing this parameter from SQLNET.ORA will allow the Operating system users to authenticate to the database with out specifying the password in other case[setting the value to NONE] the authentication will fail with ORA- 1031.

eg : SQLNET.AUTHENTICATION_SERVICES=(ALL) will allow the OS authentication

SQLNET.AUTHENTICATION_SERVICES=(NONE) will fail the OS authentication

 " Connect internal " is not supported now , instead of which one should use " connect / as sysdba " to authenticated to the database .

3. The Operating system user should belong to OSDBA group in order to login as sysdba and the user    should belong to OSOPER in order to login a sysoper.

On unix based platforms these groups by default would be DBA, OPER and in windows these would be ORA_DBA ,ORA_OPER.

Please refer to the operating system specific Oracle documentation for information about creating the OSDBA and OSOPER groups

Facts About Password File Authentication

1. Remote sysdba connections attempted with a user name and password uses password file authentication

2. The following syntax is used while using a password file authentication

sqlplus <sysdba user>/<password> as sysdba

The following syntax is used while using a password file authentication connecting to the database as remote user

sqlplus <sysdba user>/<password>@<NET SERVICE NAME> as sysdba


2. Password file authentication is enabled by setting the database parameter remote_login_password file to "shared" or "exclusive".

SQL> alter system set remote_login_password file=exclusive scope=spfile;

3. On unix based platforms a password file is created by default during database installation with an entry of sys under directory $ORACLE_HOME/dbs/ and on windows the file is created under                        %ORACLE_HOME%\database\
4. Granting each database user a sysdba or sysoper privilege adds the user to the password file in the background.

For more information on password file setup and maintenance please refer the following oracle documentation

" Creating and Maintaining a Password File " section of " Chapter 1 " of " Oracle Database Administrator's Guide "

OS Authentication Overrides Password File  

When both OS authentication and password file authentication is enabled than OS Authentication overrides password file authentication.

i.e sysdba can be logged with any user name and password, only the operating system user will be verified.

sqlplus harry/harry as sysdba will be able to login to the database though harry is not a database user.

Troubleshooting ORA-1031 with OS Authentication

1. Check whether the OS user is part of DBA group and OPER group if not add the user to these groups.

2. Check the SQLNET.AUTHENTICATION_SERVICES parameter in the SQLNET.ORA .

On unix based platforms either this parameter should not be present or should be set to ALL.

On windows this parameter should be set to NTS.

3. If the OS user is a domain user in the windows domain than check whether the database service is started with a domain user , if not start the database service with the
domain user.

Check whether the domain is added to the ORA_DBA or ORA_<SID>_DBA group.

Check if a non domain[Local] user can able to login to the database. If so there could be problem with the domain settings ,contact the system administrator and the network
administrator reporting the same.

If the local user is also failing to login than follow the remaining steps specified in this troubleshooting document.

4. If a scheduled script on windows is causing the error than the user calling the script must be a privileged user.The AT command, by default, runs as the NT SYSTEM
account.The SYSTEM account is not a privileged Oracle user.

The Task Scheduler and the AT command are not the same thing.They work with each other.The Task Scheduler service must be started for any AT tasks to run.

When specifying the user account to use for tasks scheduled by AT, this must be set in the "Scheduled Tasks" folder and not through the Task Scheduler service in the
service control panel.

Do this to set the AT service logon account in the "Scheduled Tasks" folder

1) Open "Control Panel"
2) Click on "Scheduled Tasks"
3) On the tool bar, click on "Advanced"
4) Click on "AT Service Account"
5) Choose "This Account:"
6) Specify a Windows account name and password that is part of the ORA_DBA.

5. There are chances that a confusion in windows service might me causing this issue, so to isolate the cause create a new windows service for the instance in any of the
failing machine and check if this fails by following the below steps :

Delete the SID and services:

You MUST be logged into the Windows NT system as the user Administrator -OR- a user within the Windows NT Administrative Group with full
administrative rights to perform the following steps.

1). Open a Command Prompt window:
- Click on the START button.
- Click on RUN.
- Type in: command
- Press OK.

2). Type in the following command: ORADIMxx -DELETE -SID <sid>
where: xx stands for the Oracle release (i.e., 73 or 80)
<sid> is the sid name of your database (i.e., ORCL)
In 8i and 9i, the command is just "oradim" (i.e., no xx)

3). Recreate the sid and services: ORADIMxx -NEW -SID sid -INTPWD password
-STARTMODE AUTO -PFILE <full path to initsid.ora>

6. There is a Chance that improper SGA might be causing the issue,if the size of the SGA was too large for the amount of physical memory than reducing the size of the SGA
eliminates the errors.

so please perform the below checks to troubleshoot the same :

Review the INIT.ORA file. You will find that there are some large entries affecting the size of memory used. Lower those parameters.

The SGA items to look at are:

SORT_AREA_SIZE
DB_BLOCK_BUFFERS
SHARED_POOL_SIZE
LOG_BUFFER.

7. If the failing user is the root user on UNIX platforms than check the userid and primary group to which  the user belongs to by using the operating system command " id " .
     In general a Unix root user will not have its primary group as DBA and more over the id of the root user would be 0, changing the id of the root user is not recommended as it may effect other application, so modify the root user inorder to make the primary group as dba.

8. If the issue is happening only on HP UX platforms than you may be hitting a know issue which is discussed in the below metalink document
Note 308151.1 Connect / AS SYSDBA Results In Ora-01031

9. There are cases where though a user name is manually added to the ORA_DBA group and is visible in the ORA_DBA group , a underlying windows interpretation of the username might
be different for unknown reasons .

But this can be verified by using a Microsoft API " NetUserGetLocalGroups " Function. by using this API we can be able to check if the username failing is actually a part of
the ORA_DBA group with respect to the underlying OS.

The following Microsoft knowledge base document speaks about this API :

http://msdn.microsoft.com/en-us/library/aa370655(VS.85).aspx

Please ask you system administrators to use the sample code specified in the document to validate the user by running the code on the failing machine as well as on the
domain controller.

The API returns all the groups the user belongs to , and if it is found that the appropriate groups [ like DBA ] is not reflected in the output than the corresponding
subscription has to be reissued and if the problem persists than you may have to contact the Microsoft support team.

10. On Unix based platforms a diagnostic C program will help in retrieving all the groups a user belongs to, the following metalink document contains the diagnostic program :

Note 67984.1 UNIX: Diagnostic C program for ORA-1031

Troubleshooting ORA-1031 with password file authentication

If a local or remote sysdba connection fails with ORA-01031 than consider following factors :

1. The database parameter remote_login_passwordfile has to be set to either EXCLUSIVE or SHARED .

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

restart the database for the change to take effect.

2. A password file is created in $ORACLE_HOME/dbs with option " nosysdba=n " . In general the name of the file would be in the format orapw<SID> .
In case of uncertainity recreate the password file using the below syntax :

$ > orapwd file=$ORACLE_HOME/dbs/orapw<sid> password=<password> force=y nosysdba=n

 restart the database .

The path to the password file %ORACLE_HOME%\database\PWD%ORACLE_SID%.ORA on a Microsoft Windows machine.

3. The connecting user needs to have sysdba privilege in the target database . The same can be confirmed by querying V$PWFILE_USERS

Troubleshooting ORA-1031 as non sysdba user

1. A database user
2. An external user
3. A global user
4. A user authenticated by middle tier

For more details please refer to Chapter10 of Oracle Database Security Guide , which can be accessed by using the following link :

http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/admnauth.htm#i1006738

If a non sysdba user is failing to authenticate to the database with ORA-01031 than :

1. Check whether the user has a "CONNECT" role or "CREATE SESSION" system privilege granted by using the following SQL statements
If not granted to the user than ask the DBA to grant the same

2. If a datavault is installed and the user is trying to access a realm protected schema than check whether the user is the participant of the realm , if not add the user as realm participant .

The below link explains the steps to add a participant to a realm.

http://download.oracle.com/docs/cd/B19306_01/server.102/b25166/cfrealms.htm#CHDFGFJJ

3. In case of a remote database user check whether the target database is the appropriate one. In most of the case in an environment hosting multiple databases there are chances that the NET SERVICE NAME used may resolve to inappropriate database resulting in ORA-1031 .

Use command line tools like TNSPING, ipconfig and nslookup to check the hostname name and the ip resolved corresponding to the hostname. Take the help of network administrator if necessary.

4. In case of an external user authenticated by an operating system check whether the following database parameter is set to true :

SQL> show parameter remote_OS_authent.

If set to false than set the same to true by using the following SQL statement

SQL>Alter system set remote_os_authent=true scope=spfile;

restart the database.

Setting REMOTE_OS_AUTHENT to TRUE can cause a security exposure, because it lets someone using a non-secure protocol, such as TCP, perform an operating system-authorized login .
REMOTE_OS_AUTHENT is a depriciated parameter from 11g

Apart from the above parameter also check whether the SQLNET.ORA parameter SQLNET.AUTHENTICATION_SERVICES is set to the appropriate value as discussed earlier in this document

Discussion on Network Authenticated external users, Proxy users, Global users is out of the scope of this document.

SELECT * FROM USER_ROLE_PRIVS ;
SELECT * FROM USER_SYS_PRIVS ;

Troubleshooting ORA-1031 while or after upgrading the database

If ORA-1031 is seen while or after upgrading database than performs the below checks :

a). Check the groups to which the user belongs to .

SQL> select * from V$PWFILE_USERS;

USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
SCOTT TRUE FALSE FALSE

b). Check the actual names of the ORDBA group in $ORACLE_HOME/rdbms/lib/config.s [ the file can be config.c in some operating systems]

If the ORADBA group in the config.s/config.c is not reflected in the groups list to which the usr belongs to than add the user to the group seen in config.s/config.c

Remove and move the CONFIG.O and relink the oracle, using the following steps :

1. Shutdown the database and other processes related to the ORACLE_HOME
2. Navigate to $ORACLE_HOME/rdbms/lib
3. Take a backup of the current CONFIG.O and delete it :
mv config.o to config.o.bkp
4. Check the config.s or config.c to verify whether the name of the 'dba' and 'oper' groups are appropriate, if not correct them accordingly
5. MAke a new CONFIG.O by using the below command :
make -f ins_rdbms.mk config.o ioracle
6. Relink oracle

Troubleshooting ORA-1031 while executing a DDL statement:

1. Check whether the user has appropriate privilege to execute the failing SQL statement by using the following SQL statement:

SQL> select * from session_privs;

If any of the required privilege is missing than request your DBA to grant the same.
For example in order to create a table a user needs to create table or create any table privilege.
2. If a DDL statement like create table is failing with ORA-1031 than check the following :

sql>select guard_status from v$database;
guard_status
-------
ALL

The DDLs fail if the above parameter is set to ALL, the solution is to set this to NONE by executing the following statement

sql>alter database guard none;

Though guard_status is a Data guard related parameter setting this to ALL will also affect a stand alone database.

Troubleshooting ORA-1031 while executing a Explain Plan on a query

If an explain plan on a query is failing with ORA-1031 than :

SQL>conn test/test
SQL>explain plan for select * from scott.emp;
ORA-1031 Insufficient Privilege

1. Check whether the user issuing the explain plan  has a select privilege on the objects used if not grant a select privilege

SQL>conn sys/<pwd> as sysdba
SQL>SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='<user name>';
SQL>SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='<user name>';

2. Check whether the user has  appropriate privileges on PLAN_TABLE if not grant the same

SQL>conn sys/<pwd> as sysdba
SQL>grant all on PLAN_TABLE to <user>;

3. If the above steps did not help than create a plan table in the user schema

SQL> conn <user>/<pwd>
SQL>@?/rdbms/admin/utxplan.sql

---

4. A difficult situation is the one when the EXPLAIN PLAN fails with ORA-01031 while the corresponding SELECT statement runs fine. Equally, the situation is not caused by the lack of privileges on PLAN_TABLE, since the EXPLAIN PLAN succeeds on other statements.
In such situation, it is likely that the FROM clause of the SELECT includes views, which in turn may rely on other views. In such situation, it is needed to grant the select privilege on all the underlying views and tables. This is an additional privilege requested by the EXPLAIN PLAN, because otherwise it would display the schema structure with only the SELECT privilege. As indicated in: Bug 205557:

"Describing the execution plan and underlying objects of a view to an unprivileged user would be a security hole.  When you GRANT SELECT on a view to another user, they are given access to your data based upon the query that you composed, but they aren't given access to the details of your schema.  A reasonable enhancement to EXPLAIN PLAN might be to have it stop explaining the branch of the execution plan that references an object for which you don't have privileges."