Resolving ORA-1031 while connecting as “/ as sysdba”

In this post, we are discussing about issue which I faced on fresh oracle software install.


[oracle@test1.test.com ] Ora:TEST2 $ sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Mon May 30 12:30:31 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges

Normally this issue points to incorrect permissions on oracle binaries OR incorrect value for SQLNET.AUTHENTICATION_SERVICES in sqlnet.ora. But in my case both were correctly set.

After checking on metalink, I found following note related to the same issue, which explains different scenarios, on of them is if the OS user is part of the OSDBA group.

Troubleshooting ORA-1031 or ORA-1017 Errors When Connecting As SYSDBA (Doc ID 730067.1)

I tried to compare file “$ORACLE_HOME/rdbms/lib/config.s” with other oracle home where sqlplus “/ as sysdba” was working fine.

Note: config file name vary from OS to OS on some OS it is config.c and on some OS it is config.s


[oracle@test1.test.com ] Ora: TEST1 $ diff config.s /u01/app/oracle/TEST2/11.2.0.4/rdbms/lib/config.s
23c23
< .ascii "oinstall\0"
---
> .ascii "\0"

I found the culprit!!! In my new installation, Library file was missing group details. After making all necessary changes, I had to relink all oracle binaries.

For binary relink, perform following steps:

– Make sure that no oracle processes running
– Login as oracle
– Make sure LD_LIBRARY_PATH and ORACLE_HOME are set properly
– $ORACLE_HOME/bin/relink all

After binary relink, sqlplus “/ as sysdba” started working as expected.

Done!!!!!!!!!!

Hope so u will find this post very useful:-)

Cheers

Regards,

Adityanath

Advertisements

ORA-01031: Insufficient Privileges while creating mview in different schema.

Recently I faced very interesting issue, when user was complaining that he is getting (SCHEMA:TEST1) ORA-01031: insufficient privileges while creating materialized view under different schema (SCHEMA:TEST2) though TEST1 has DBA role assigned.

For TEST1:

SQL> select * from dba_sys_privs where grantee='TEST1';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TEST1 UNLIMITED TABLESPACE NO
SQL> select * from dba_role_privs where grantee='TEST1';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST1 DBA NO YES
TEST1 CONNECT NO YES
TEST1 RESOURCE NO YES

For TEST2:

SQL> select * from dba_sys_privs where grantee='TEST2';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TEST2 UNLIMITED TABLESPACE NO
SQL> select * from dba_role_privs where grantee='TEST2';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST2 CONNECT NO YES
TEST2 RESOURCE NO YES

 

When user tried creating mview in TEST2 schema by connecting schema TEST1 (though having DBA role assigned) was getting ORA-01031: insufficient privileges. I also tried same using SYSTEM user but same error:

SQL> conn test1/test1
Connected.
SQL> create materialized view test2.mv_t as select * from test2.t;
create materialized view test2.mv_t as select * from test2.t
 *
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn system/rmbman08
Connected.
SQL> create materialized view test2.mv_t as select * from test2.t;
create materialized view test2.mv_t as select * from test2.t
 *
ERROR at line 1:
ORA-01031: insufficient privileges

After searching on Metalink, I found very interesting note : ORA-01031: Insufficient Privileges When Create A Mview In Different Schema (Doc ID 749112.1). It clearly stats:

This is not a code bug; this is expected behavior. As explained above, the owner of the base table should be granted with the ‘create table’ privilege explicitly and not through a role. Granting the create table privilege explicitly to the owner of the base table allows us to create the materialized view in another schema.

After this I tried provided solution:

SQL> conn / as sysdba
Connected.
SQL> grant create table to test2;
Grant succeeded.
SQL> conn test1/test1
Connected.
SQL> create materialized view test2.mv_t as select * from test2.t;
Materialized view created.

Solved 🙂

Just to put some light on this “expected behavior“, Mview creation also creates one table under mview owner with same name.

SQL> @obj
Enter value for object_name: MV_T
old 3: where object_NAME = upper('&object_name')
new 3: where object_NAME = upper('MV_T')
OBJECT_NAME OWNER OBJECT_TYPE
-------------------------------------------------- ------------------------------ -------------------
MV_T TEST2 TABLE
MV_T TEST2 MATERIALIZED VIEW

 

This means TEST2 should have create table privilege which had been already assigned to TEST2 though role RESOURCE.

In metalink note : Role Restrictions (Doc ID 11740.1) u will find following information:

“USERS CANNOT PERFORM DDL BASED ON PRIVILEGES RECEIVED THROUGH A ROLE”:
This restriction is necessary because roles can be enabled or disabled dynamically, making security checking at runtime ambiguous. For example, if a user is granted SELECT on a table via a role, enables that role, creates a procedure to select from the table, and then disables that role,should the procedure be allowed to execute? The user still has the privileges, but they are not currently active.Roles are intended for use as a tool to administer privileges for end users; they are not intended for use by application developers.

So here we got solution with perfect explanation 🙂


Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath