ORA-01031 occurs when connecting to Oracle 12c with Toad 11.6


#1

I am having an issue when I attempt to login via Toad(version 11.6) using the system account, to a newly created 12c database. When I enter my credentials and attempt to connect, I receive the error ORA-01031: insufficient privileges. When I click ok, it still seems to log me on to the database, but I receive the error anytime that I try to browse within Schema browser. It doesn’t seem to affect my ability to execute queries in Toad as I’ve yet to get the error this way.

This only happens when I am using Toad and using this account. I can connect using this Toad client while using the sys account without any issue.

I am able to connect using the system account without error using other methods on the same machine: SQL Developer, SQLPlus, and also Toad version 10 through a citrix app. All of these methods allow me to connect and browse the database without any type of error. So I know that there isn’t a connection issue between my machine and the database, but it seems maybe related to this version of Toad.


#2

I actually was able to find a solution to this problem. I used SQL Tracker while I attempted to browse within Toad’s Schema Browser and I found the error was occurring when trying to access the sys.user$ table. I granted the system account access to this table, and so far I haven’t seen any other issues while browsing within Toad.

I’m not sure why I needed to explicitly grant access to this one table when it seems like almost all other tables were already accessible. I also find it odd that Toad 11 is the only tool where I encountered this issue.


#3

Oracle made a change in 12c (or maybe 11gR2, I forget the exact version) where sys.user$ is no longer included in the SELECT_ANY_DICTIONARY system privilege. Before that, it used to be. Toad 11.6 (which came out before that version of Oracle) checks for SELECT_ANY_DICTIONARY before trying to query SYS.USER$, which is fine for older versions of Oracle but not 12c.

Newer versions of Toad won’t throw an error - if you don’t have privileges on SYS.USER$, we won’t try to query it.


#4

Thank you for explaining that, John. This must be new to 12c as 11gR2 databases have not had this issue with my Toad version. This is my first 12c installation and I have made a note of this permission change for future installs.


#5

Thanks, it worked fine with this suggestion


#6

I am using Toad 12.7.1.11 / Oracle 12.1.0.2.5, and got the same ORA-01031 error


#7

Go to the main menu, then Database -> Spool SQL -> Spool to screen. Then do whatever causes the error (making a connection, clicking some button, etc). Toad will display all of the SQL statements that it runs. Find the one with the error and post it here.


#8

Find the one with the error and post it here.

I take that back. Just post the entire output of the spool SQL window.


#9

Timestamp: 16:59:19.794

Connect: yng@testdb


Session: yng@testdb

Timestamp: 16:59:20.303

SELECT version, product, sysdate FROM sys.PRODUCT_COMPONENT_VERSION WHERE UPPER(PRODUCT) LIKE ‘%ORACLE%’


Session: yng@testdb

Timestamp: 16:59:20.327

Declare

debug_connect_session number;

alter_any_table number;

alter_session number;

alter_system number;

alter_database number;

alter_user number;

create_table number;

create_role number;

create_trigger number;

create_sequence number;

adm_db_trig number;

create_any_index number;

create_any_sequence number;

create_any_table number;

create_any_trigger number;

debug_any_procedure number;

execute_any_procedure number;

select_any_table number;

grant_any_object_priv number;

grant_any_role number;

grant_any_privilege number;

select_any_dictionary number;

update_any_table number;

create_public_syn number;

has_dba_synonyms number;

has_dba_objects number;

select_catalog_role number;

db_charset nls_database_parameters.value%type;

compatible_version varchar2(60);

sid number;

local_trans_id varchar2(60);

qry_str varchar2(4000);

has_dbms_java number;

has_dbms_debug number;

qu_version_owner varchar2(30);

CTRepositoryVersion number;

is_RAC number;

instance_id number;

instance_name varchar2(100);

server_name varchar2(100);

team_coding_schema varchar2(30);

team_coding_table varchar2(30);

team_coding_synonym varchar2(30);

team_coding_table_selectable number;

OS_USERNAME varchar2(60);

dbid varchar2(30);

authentication_type varchar2(30);

AmazonRDSPackage varchar2(30);

OraDBType number;

cx_table_count number;

Begin

– Put Toad data into v$session columns

Begin

SYS.DBMS_APPLICATION_INFO.SET_MODULE(:IN_MDL, null);

Exception

When others then null; – we can live without this.

End;

– Retrieve specific system privileges

select

sum(decode(Privilege, ‘ADMINISTER DATABASE TRIGGER’, 1, 0)) adm_db_trig,

sum(decode(Privilege, ‘ALTER ANY TABLE’, 1, 0)) alter_any_table,

sum(decode(Privilege, ‘ALTER DATABASE’, 1, 0)) alter_database,

sum(decode(Privilege, ‘ALTER SESSION’, 1, 0)) alter_session,

sum(decode(Privilege, ‘ALTER SYSTEM’, 1, 0)) alter_system,

sum(decode(Privilege, ‘ALTER USER’, 1, 0)) alter_user,

sum(decode(Privilege, ‘CREATE ANY INDEX’, 1, 0)) create_any_ind,

sum(decode(Privilege, ‘CREATE ANY SEQUENCE’, 1, 0)) create_any_seq,

sum(decode(Privilege, ‘CREATE ANY TABLE’, 1, 0)) create_any_table,

sum(decode(Privilege, ‘CREATE ANY TRIGGER’, 1, 0)) create_any_trig,

sum(decode(Privilege, ‘CREATE PUBLIC SYNONYM’, 1, 0)) create_public_syn,

sum(decode(Privilege, ‘CREATE ROLE’, 1, 0)) create_role,

sum(decode(Privilege, ‘CREATE SEQUENCE’, 1, 0)) create_sequence,

sum(decode(Privilege, ‘CREATE TABLE’, 1, 0)) create_table,

sum(decode(Privilege, ‘CREATE TRIGGER’, 1, 0)) create_trigger,

sum(decode(Privilege, ‘DEBUG ANY PROCEDURE’, 1, 0)) debug_any_proc,

sum(decode(Privilege, ‘DEBUG CONNECT SESSION’, 1, 0)) debug_connect_session,

sum(decode(Privilege, ‘EXECUTE ANY PROCEDURE’, 1, 0)) execute_any_procedure,

sum(decode(Privilege, ‘GRANT ANY OBJECT PRIVILEGE’, 1, 0)) grant_any_object_priv,

sum(decode(Privilege, ‘GRANT ANY ROLE’, 1, 0)) grant_any_role,

sum(decode(Privilege, ‘GRANT ANY PRIVILEGE’, 1, 0)) grant_any_privilege,

sum(decode(Privilege, ‘SELECT ANY DICTIONARY’, 1, 0)) select_any_dictionary,

sum(decode(Privilege, ‘SELECT ANY TABLE’, 1, 0)) select_any_table,

sum(decode(Privilege, ‘UPDATE ANY TABLE’, 1, 0)) update_any_table

into adm_db_trig, alter_any_table, alter_database, alter_session, alter_system, alter_user,

create_any_index, create_any_sequence, create_any_table, create_any_trigger, create_public_syn, create_role,

create_sequence, create_table, create_trigger, debug_any_procedure, debug_connect_session,

execute_any_procedure, grant_any_object_priv, grant_any_role, grant_any_privilege, select_any_dictionary, select_any_table, update_any_table

from sys.session_privs

where privilege in (‘DEBUG CONNECT SESSION’,

‘ALTER ANY TABLE’,

‘ALTER SESSION’,

‘ALTER SYSTEM’,

‘ALTER DATABASE’,

‘ALTER USER’,

‘ADMINISTER DATABASE TRIGGER’,

‘CREATE TABLE’,

‘CREATE ROLE’,

‘CREATE TRIGGER’,

‘CREATE SEQUENCE’,

‘EXECUTE ANY PROCEDURE’,

‘SELECT ANY TABLE’,

‘UPDATE ANY TABLE’,

‘GRANT ANY OBJECT PRIVILEGE’,

‘GRANT ANY ROLE’,

‘GRANT ANY PRIVILEGE’,

‘SELECT ANY DICTIONARY’,

‘CREATE ANY INDEX’,

‘CREATE ANY SEQUENCE’,

‘CREATE ANY TABLE’,

‘CREATE ANY TRIGGER’,

‘DEBUG ANY PROCEDURE’,

‘CREATE PUBLIC SYNONYM’);

– Check for SELECT_CATALOG_ROLE. It must be a default role to appear in the result of this query.

Select

sum(decode(role, ‘SELECT_CATALOG_ROLE’, 1, 0)) select_catalog_role

into select_catalog_role

from sys.session_roles

where role = ‘SELECT_CATALOG_ROLE’;

– determine database character set

select value into db_charset from sys.nls_database_parameters where parameter = ‘NLS_CHARACTERSET’;

– check for other ways dba_objects and dba_synonyms could be granted

if (select_any_dictionary = 1) or (select_catalog_role = 1) then

has_dba_synonyms := 1;

has_dba_objects := 1;

else

– just try to select from dba_synonyms and dba_objects as a privilege check

begin

qry_str := ‘select 1 from DBA_SYNONYMS where rownum = 1’;

execute immediate qry_str into has_dba_synonyms;

exception

when others then

has_dba_synonyms := 0;

end;

begin

qry_str := ‘select 1 from DBA_OBJECTS where rownum = 1’;

execute immediate qry_str into has_dba_objects;

exception

when others then

has_dba_objects := 0;

end;

end if;

– retrieve OS Username

begin

qry_str := ‘select SYS_CONTEXT(’‘USERENV’’,’‘OS_USER’’) from dual’;

execute immediate qry_str into OS_USERNAME;

exception

when others then

OS_USERNAME := null;

end;

– retrieve SID if possible

begin

qry_str := ‘select SYS_CONTEXT(’‘USERENV’’,’‘SID’’) from dual’;

execute immediate qry_str into sid;

exception

when others then

sid := -1;

end;

– get authentication type

begin

qry_str := ‘Select SYS_CONTEXT(’‘USERENV’’, ‘‘IDENTIFICATION_TYPE’’) from dual’;

execute immediate qry_str into authentication_type;

exception

when others then

authentication_type := null;

end;

– get compatible version

begin

qry_str := ‘select value from v$parameter where name = ‘‘compatible’’’;

execute immediate qry_str into compatible_version;

exception

when others then

compatible_version := null;

end;

– check for privileges on sys.dbms_transaction

begin

execute immediate ‘select sys.dbms_transaction.local_transaction_id from dual’ into local_trans_id;

exception

when others then

local_trans_id := ‘’;

end;

– determine if certain packages are installed

qry_str := ‘Select sum(decode(object_name, ‘‘DBMS_JAVA’’, 1, 0)) jv,’ ||

’ sum(decode(object_name, ‘‘DBMS_DEBUG’’, 1, 0)) dbg ';

if has_dba_objects > 0 then

qry_str := qry_str || 'from sys.dba_objects ';

else

qry_str := qry_str || 'from sys.all_objects ';

end if;

qry_str := qry_str || 'where owner = ‘‘SYS’’ ’ ||

'and object_type = ‘‘PACKAGE’’ ’ ||

‘and object_name in (’‘DBMS_JAVA’’, ‘‘DBMS_DEBUG’’)’;

execute immediate qry_str into has_dbms_java, has_dbms_debug;

– determine if code tester repository is installed

begin

Select user into qu_version_owner

FROM sys.user_objects

WHERE object_name = ‘QU_VERSION’

AND object_type in (‘PACKAGE’, ‘SYNONYM’)

AND rownum = 1;

exception

when no_data_found then

qu_version_owner := null;

end;

– if not found then look in other schemas

if qu_version_owner is null then

begin

qry_str := 'Select owner ';

if has_dba_objects > 0 then

qry_str := qry_str || 'from sys.dba_objects ';

else

qry_str := qry_str || 'from sys.all_objects ';

end if;

qry_str := qry_str || 'where object_name = ‘‘QU_VERSION’’ ’ ||

‘and object_type in (’‘PACKAGE’’,’‘SYNONYM’’) and rownum = 1’;

execute immediate qry_str into qu_version_owner;

exception

when others then

qu_version_owner := null;

end;

end if;

– if it is installed, determine code tester version

CTRepositoryVersion := 0;

if qu_version_owner is not null then

begin

qry_str := ‘select (10 * QU_VERSION.major_number) + QU_VERSION.minor_number from dual’;

execute immediate qry_str into CTRepositoryVersion;

exception

when others then

CTRepositoryVersion := 0;

end;

end if;

– find the team coding schema, if any

begin

– all_objects is generally faster than all_synonyms, so first look there to see if the synonym exists.

qry_str := 'Select object_name ';

if has_dba_objects > 0 then

qry_str := qry_str || 'from sys.dba_objects ';

else

qry_str := qry_str || 'from sys.all_objects ';

end if;

qry_str := qry_str || 'where owner = ‘‘PUBLIC’’ and object_name = ‘‘DELL_COM_TEAM_CODING’’ and object_type = ‘‘SYNONYM’’ ';

execute immediate qry_str into team_coding_synonym;

exception

when others then

team_coding_synonym := null;

end;

– now look to find team coding table name only if we know synonym exists.

if team_coding_synonym is not null then

begin

qry_str := 'Select table_owner, table_name ';

if has_dba_synonyms > 0 then

qry_str := qry_str || 'from sys.dba_synonyms ';

else

qry_str := qry_str || 'from sys.all_synonyms ';

end if;

qry_str := qry_str || 'where synonym_name = ‘‘DELL_COM_TEAM_CODING’’ and owner = ‘‘PUBLIC’’ ';

execute immediate qry_str into team_coding_schema, team_coding_table;

exception

when others then

team_coding_schema := null;

team_coding_table := null;

end;

end if;

– if we found a table, make sure we can select from it.

if team_coding_table is not null then

begin

qry_str := ‘SELECT 1 FROM ’ || team_coding_schema || ‘.’ || team_coding_table || ’ where rownum = 1’;

execute immediate qry_str into team_coding_table_selectable;

exception

when no_data_found then

team_coding_table_selectable := 1;

when others then

team_coding_table_selectable := 0;

end;

end if;

– find the team coding schema, if any

begin

– all_objects is generally faster than all_synonyms, so first look there to see if the synonym exists.

qry_str := 'Select object_name ';

if has_dba_objects > 0 then

qry_str := qry_str || 'from sys.dba_objects ';

else

qry_str := qry_str || 'from sys.all_objects ';

end if;

qry_str := qry_str || 'where owner = ‘‘PUBLIC’’ and object_name = ‘‘DELL_COM_TEAM_CODING’’ and object_type = ‘‘SYNONYM’’ ';

execute immediate qry_str into team_coding_synonym;

exception

when others then

team_coding_synonym := null;

end;

– now look to find team coding table name only if we know synonym exists.

if team_coding_synonym is not null then

begin

qry_str := 'Select table_owner, table_name ';

if has_dba_synonyms > 0 then

qry_str := qry_str || 'from sys.dba_synonyms ';

else

qry_str := qry_str || 'from sys.all_synonyms ';

end if;

qry_str := qry_str || 'where synonym_name = ‘‘DELL_COM_TEAM_CODING’’ and owner = ‘‘PUBLIC’’ ';

execute immediate qry_str into team_coding_schema, team_coding_table;

exception

when others then

team_coding_schema := null;

team_coding_table := null;

end;

end if;

– if we found a table, make sure we can select from it.

if team_coding_table is not null then

begin

qry_str := ‘SELECT 1 FROM ’ || team_coding_schema || ‘.’ || team_coding_table || ’ where rownum = 1’;

execute immediate qry_str into team_coding_table_selectable;

exception

when no_data_found then

team_coding_table_selectable := 1;

when others then

team_coding_table_selectable := 0;

end;

end if;

– determine if this is a RAC instance

qry_str := ‘begin if dbms_utility.is_cluster_database’;

qry_str := qry_str || ’ then :isRAC := 1; else :isRAC := 0; end if; end;’;

– determine if this is a RAC DB

begin

execute immediate qry_str using out is_RAC;

exception

when others then

:isRAC := 0;

end;

– determine instance id

begin

execute immediate ‘select dbms_utility.current_instance from dual’ into instance_id;

exception

when others then

instance_id := 1;

end;

– determine DBID if possible

begin

qry_str := ‘select dbid from v$database’;

execute immediate qry_str into dbid;

exception

when others then

null;

end;

– determine instance name and server name if possible

qry_str := ‘select sys_context(’‘USERENV’’,’‘INSTANCE_NAME’’), sys_context(’‘USERENV’’,’‘SERVER_HOST’’) from dual’;

begin

execute immediate qry_str into instance_name, server_name;

exception

– if we don’t have privs on v$instance and not rac, then try to get db name

when others then

if is_rac = 0 then

begin

qry_str := ‘select sys_context(’‘USERENV’’,’‘DB_NAME’’) from dual’;

execute immediate qry_str into instance_name;

exception

when others then

instance_name := null;

end;

end if;

end;

– Is this an Amazon RDS Instance?

begin

execute immediate ‘select object_name from sys.all_procedures where owner = ‘‘RDSADMIN’’ and object_name = ‘‘RDSADMIN_UTIL’’ and object_type = ‘‘PACKAGE’’ and subprogram_id = 0’ into AmazonRDSPackage;

exception

when others then

AmazonRDSPackage := null;

end;

– What kind of database is this? (pluggable, container, normal)

begin

qry_str := ‘select’ || CHR(13) || CHR(10) ||

’ Case’ || CHR(13) || CHR(10) ||

’ when sys_context(’‘USERENV’’,’‘CON_NAME’’) = ‘‘CDB$ROOT’’ then 1 – container,’ || CHR(13) || CHR(10) ||

’ when sys_context(’‘USERENV’’,’‘CDB_NAME’’) is null then 0 – normal,’ || CHR(13) || CHR(10) ||

’ else 2 – pluggable’ || CHR(13) || CHR(10) ||

’ end db_type’ || CHR(13) || CHR(10) ||

‘from dual’;

execute immediate qry_str into OraDBType;

exception

when others then

OraDBType := 0;

end;

– check for existence of Codexpert tables

Select count(‘x’)

into cx_table_count

from sys.all_tables

where owner in (‘TOAD’, USER)

and table_name in (‘CX_XPERTRUN’, ‘CX_XPERTLINES’, ‘CX_XPERTITEMS’, ‘CX_TYPES’, ‘CX_SEVERITYS’, ‘CX_SETRULES’, ‘CX_RULESETS’, ‘CX_METRICS’, ‘CX_METRICRANGES’, ‘CX_CATEGORYS’, ‘CX_BASERULES’);

– Assign results to output variables

:OUT_DEBUG_CONNECT_SESSION := debug_connect_session;

:OUT_ALTER_ANY_TABLE := alter_any_table;

:OUT_ALTER_SESSION := alter_session;

:OUT_ALTER_SYSTEM := alter_system;

:OUT_ALTER_DATABASE := alter_database;

:OUT_ALTER_USER := alter_user;

:OUT_CREATE_TABLE := create_table;

:OUT_CREATE_ROLE := create_role;

:OUT_CREATE_TRIGGER := create_trigger;

:OUT_CREATE_SEQUENCE := create_sequence;

:OUT_ADM_DB_TRIG := adm_db_trig;

:OUT_EXECUTE_ANY_PROCEDURE := execute_any_procedure;

:OUT_CREATE_ANY_INDEX := create_any_index;

:OUT_CREATE_ANY_SEQUENCE := create_any_sequence;

:OUT_CREATE_ANY_TABLE := create_any_table;

:OUT_CREATE_ANY_TRIGGER := create_any_trigger;

:OUT_DEBUG_ANY_PROCEDURE := debug_any_procedure;

:OUT_SELECT_ANY_TABLE := select_any_table;

:OUT_UPDATE_ANY_TABLE := update_any_table;

:OUT_GRANT_ANY_OBJECT_PRIV := grant_any_object_priv;

:OUT_GRANT_ANY_ROLE := grant_any_role;

:OUT_GRANT_ANY_PRIVILEGE := grant_any_privilege;

:OUT_SELECT_ANY_DICTIONARY := select_any_dictionary;

:OUT_CREATE_PUBLIC_SYN := create_public_syn;

:OUT_SELECT_CATALOG_ROLE := select_catalog_role;

:OUT_USER := user;

:OUT_DB_CHARSET := db_charset;

:OUT_CX_TABLE_COUNT := cx_table_count;

:OUT_OSUSERNAME := OS_USERNAME;

:OUT_COMPATIBLE_VERSION := compatible_version;

:OUT_TEAM_CODING_SCHEMA := team_coding_schema;

:OUT_TEAM_CODING_TABLE := team_coding_table;

:OUT_TC_TABLE_SELECTABLE := team_coding_table_selectable;

:OUT_SID := sid;

:OUT_LOCAL_TRANS_ID := local_trans_id;

:OUT_HAS_DBMS_JAVA := has_dbms_java;

:OUT_HAS_DBMS_DEBUG := has_dbms_debug;

:OUT_QU_VERSION_OWNER := qu_version_owner;

:OUT_QU_VERSION := CTRepositoryVersion;

:OUT_IS_RAC := is_rac;

:OUT_INSTANCE_ID := instance_id;

:OUT_INSTANCE_NAME := instance_name;

:OUT_SERVER_NAME := server_name;

:OUT_DBID := dbid;

:OUT_AUTHENTICATION_TYPE := authentication_type;

:OUT_IS_AMAZON_RDS := AmazonRDSPackage;

:OUT_DB_TYPE := OraDBType;

end;

:IN_MDL(VARCHAR[14],IN)=‘TOAD 12.7.1.11’

:isRAC(Unknown,IN)=

:OUT_DEBUG_CONNECT_SESSION(INTEGER,OUT)=

:OUT_ALTER_ANY_TABLE(INTEGER,OUT)=

:OUT_ALTER_SESSION(INTEGER,OUT)=

:OUT_ALTER_SYSTEM(INTEGER,OUT)=

:OUT_ALTER_DATABASE(INTEGER,OUT)=

:OUT_ALTER_USER(INTEGER,OUT)=

:OUT_CREATE_TABLE(INTEGER,OUT)=

:OUT_CREATE_ROLE(INTEGER,OUT)=

:OUT_CREATE_TRIGGER(INTEGER,OUT)=

:OUT_CREATE_SEQUENCE(INTEGER,OUT)=

:OUT_ADM_DB_TRIG(INTEGER,OUT)=

:OUT_EXECUTE_ANY_PROCEDURE(INTEGER,OUT)=

:OUT_CREATE_ANY_INDEX(INTEGER,OUT)=

:OUT_CREATE_ANY_SEQUENCE(INTEGER,OUT)=

:OUT_CREATE_ANY_TABLE(INTEGER,OUT)=

:OUT_CREATE_ANY_TRIGGER(INTEGER,OUT)=

:OUT_DEBUG_ANY_PROCEDURE(INTEGER,OUT)=

:OUT_SELECT_ANY_TABLE(INTEGER,OUT)=

:OUT_UPDATE_ANY_TABLE(INTEGER,OUT)=

:OUT_GRANT_ANY_OBJECT_PRIV(INTEGER,OUT)=

:OUT_GRANT_ANY_ROLE(INTEGER,OUT)=

:OUT_GRANT_ANY_PRIVILEGE(INTEGER,OUT)=

:OUT_SELECT_ANY_DICTIONARY(INTEGER,OUT)=

:OUT_CREATE_PUBLIC_SYN(INTEGER,OUT)=

:OUT_SELECT_CATALOG_ROLE(INTEGER,OUT)=

:OUT_USER(VARCHAR[0],OUT)=

:OUT_DB_CHARSET(VARCHAR[0],OUT)=

:OUT_CX_TABLE_COUNT(INTEGER,OUT)=

:OUT_OSUSERNAME(VARCHAR[0],OUT)=

:OUT_COMPATIBLE_VERSION(VARCHAR[0],OUT)=

:OUT_TEAM_CODING_SCHEMA(VARCHAR[0],OUT)=

:OUT_TEAM_CODING_TABLE(VARCHAR[0],OUT)=

:OUT_TC_TABLE_SELECTABLE(INTEGER,OUT)=

:OUT_SID(VARCHAR[0],OUT)=

:OUT_LOCAL_TRANS_ID(VARCHAR[0],OUT)=

:OUT_HAS_DBMS_JAVA(INTEGER,OUT)=

:OUT_HAS_DBMS_DEBUG(INTEGER,OUT)=

:OUT_QU_VERSION_OWNER(VARCHAR[0],OUT)=

:OUT_QU_VERSION(INTEGER,OUT)=

:OUT_IS_RAC(INTEGER,OUT)=

:OUT_INSTANCE_ID(INTEGER,OUT)=

:OUT_INSTANCE_NAME(VARCHAR[0],OUT)=

:OUT_SERVER_NAME(VARCHAR[0],OUT)=

:OUT_DBID(VARCHAR[0],OUT)=

:OUT_AUTHENTICATION_TYPE(VARCHAR[0],OUT)=

:OUT_IS_AMAZON_RDS(VARCHAR[0],OUT)=

:OUT_DB_TYPE(INTEGER,OUT)=


Session: yng@testdb

Timestamp: 16:59:20.962

SELECT null

FROM SYS.USER$

WHERE 0=1

Error: ORA-01031: insufficient privileges


Session: yng@testdb

Timestamp: 16:59:21.009

Select username

from sys.DBA_USERS

order by username


Session: yng@testdb

Timestamp: 16:59:21.268

DECLARE

TYPE tc_syn_type IS VARRAY (2) OF VARCHAR2 (30);

syns CONSTANT tc_syn_type := tc_syn_type (‘QUEST_COM_TEAM_CODING’, ‘SQLNAV4_VCS_CONFIG’);

local_owner VARCHAR2 (30);

local_table VARCHAR2 (30);

local_options VARCHAR2 (16);

local_version VARCHAR2 (16);

TABLE_MISSING EXCEPTION;

PRAGMA EXCEPTION_INIT (TABLE_MISSING, -942);

BEGIN

local_owner := NULL;

local_table := NULL;

local_options := NULL;

local_version := NULL;

– Look for Team Coding synonym

FOR i IN syns.FIRST … syns.LAST

LOOP

BEGIN

Select table_owner, table_name

INTO local_owner, local_table

FROM sys.DBA_SYNONYMS

WHERE owner = ‘PUBLIC’ AND synonym_name = syns (i);


#10

Are you actually see the ORA-01031 error popup in Toad, or just in the sql output above? I recognize the error in the sql output above - that’s just Toad checking to see if you have the select privilege on SYS.USER$ so it know if it can continue using it or not (notice right after that, we switch to DBA_USERS). And that error isn’t shown.

I don’t see any other error in your sql output above.


#11

The error didn’t popup in Toad, this error was captured from our internal auditing system.

According your message at 2015-Jan-8, newest version shouldn’t have this error. I am using Toad 12.7, and still get error from sys.user$


#12

Well, I meant that the error wouldn’t popup. I didn’t mean that we wouldn’t try to query against sys.user$ to see if the privilege to select from it exists.

If it makes your auditors feel any better - look at the query - we’re trying to select NULL (as opposed to actual data in the table) and there is a where clause that will make 0 rows get returned. What’s more, we’re not actually executing the query, just doing an OCI describe.

SELECT null

FROM SYS.USER$

WHERE 0=1;


#13

thanks for the clarification, I will mark it as known process.

Thanks again


#14

Great. Sorry for the confusion.


#15

John, I tried 12.9.0.71 version of toad and it still gives ORA-1031 error for all users who try logging into database using TOAD tool. It is toad problem becase whenI login using sqlplus or any other tool it is fine.


#16

Sanjay, does the ORA-01031 message actually pop up or is it just logged in the audit records? If it’s just in the audit records, go back and see my message after the SQL Output was posted earlier in this thread.

If that’s not it, please post your Spool SQL output so we can identify the query that’s causing the problem.

For what it’s worth, one possible cause of ORA-01031 popping up is if you have SysDBA selected on the login window and you don’t have the SYSDBA privilege.


#17

Here is test case scenario :

  •      Create user in 12c oracle db with connect privileges and typical that we have been using in 11g/10g databases.
    
  •       12c oracle users (without sysdba) are experiencing this problem when connecting to 12c database in toad 11.6. and 12.9.0.71.     11g db and 10g db connecting via toad is fine.
    

Following is the error from toad spool that is causing :

Session: SYSTEM@TEST12cDB.world

Timestamp: 16:39:22.111

SELECT u.NAME

FROM sys.USER$ u

WHERE u.TYPE# = 1

ORDER BY 1

Error: ORA-01031: insufficient privileges

  •      FIX : When I grant the privilege as sys :
    

GRANT select on sys.user$ TO PUBLIC;

But the above GRANT creates a security hole as this table owned by SYS.user$ has passwords in it.

  •      When I connect sqlplus to 12c database, it is fine and no error.  Therefore, it is toad problem even I tried latest version of toad 12.9.0.71.
    

I need this fix or workaround with no security compromise as we are migrating our data center to 12c Oracle and toad is used quite a bit as choice of tool for connecting to oracle databases.

Thank you

Sanjay Sethi

Phone 651-366-4078

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: Friday, December 02, 2016 4:29 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] ORA-01031 occurs when connecting to Oracle 12c with Toad 11.6

RE: ORA-01031 occurs when connecting to Oracle 12c with Toad 11.6

Reply by John Dorlon

Sanjay, does the ORA-01031 message actually pop up or is it just logged in the audit records? If it’s just in the audit records, go back and see my message after the SQL Output was posted earlier in this thread.

If that’s not it, please post your Spool SQL output so we can identify the query that’s causing the problem.

For what it’s worth, one possible cause of ORA-01031 popping up is if you have SysDBA selected on the login window and you don’t have the SYSDBA privilege.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.


#18

So the error is just in spool SQL, right? And Toad is not raising the error? If so, Toad is just operating normally. Toad checks for the SELECT privilege on this view, and if you don’t have it, Toad will work fine without it. If you do have the SELECT privilege on SYS.USER$, Toad can use it to make more efficient background queries. If you don’t have the privilege, then Toad will use the DBA_, ALL_, or USER views as appropriate.

Nobody is asking you to grant select on SYS.USER$ to PUBLIC.


#19

Here’s a little more detail:

Toad 11.6 came out before Oracle 12. Before Oracle 12c, if you had the SELECT ANY DICTIONARY privilege, that meant you could select from the SYS.USER$ table. So if Toad 11.6 detects that you have SELECT ANY DICTIONARY, it will use the SYS.USER$ table.

Then Oracle 12c came along and they took SYS.USER$ out of the SELECT ANY DICTIONARY privilege. But of course Toad 11.6 doesn’t know that, so Toad 11.6 throws an error about the SYS.USER$ view if you have the SELECT ANY DICTIONARY privilege. So if you want to use Toad 11.6 with Oracle 12, to get around this problem, then either revoke SELECT ANY DICTIONARY from the users that connect to the database with Toad, or grant them SELECT on SYS.USER$.

The next Toad version was 12.0. Starting in Toad 12.0, we make a separate check to select from the SYS.USER$ table. If there is an error, it is not displayed and Toad will not try to select from SYS.USER$ again for the duration of that connection. You should not notice this check unless you are spooling the SQL.

I hope this clears things up. If you see any deviation from the above, check Help -> About to be sure that you are running the Toad version that you think you are running. If you still see a problem, please post the ‘create user’ script to create your user so I can try to recreate the problem.


#20

I also tried toad 12.9.0.71 and with same ERROR ORA-1031 experience ?


From: John Dorlon [bounce-jdorlon@toadworld.com]

Sent: Monday, December 05, 2016 8:57 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] ORA-01031 occurs when connecting to Oracle 12c with Toad 11.6

RE: ORA-01031 occurs when connecting to Oracle 12c with Toad 11.6

Reply by John Dorlon
Here’s a little more detail:

Toad 11.6 came out before Oracle 12. Before Oracle 12c, if you had the SELECT ANY DICTIONARY privilege, that meant you could select from the SYS.USER$ table. So if Toad 11.6 detects that you have SELECT ANY DICTIONARY, it will use the SYS.USER$ table.

Then Oracle 12c came along and they took SYS.USER$ out of the SELECT ANY DICTIONARY privilege. But of course Toad 11.6 doesn’t know that, so Toad 11.6 throws an error about the SYS.USER$ view if you have the SELECT ANY DICTIONARY privilege. So if you want to use Toad 11.6 with Oracle 12, to get around this problem, then either revoke SELECT ANY DICTIONARY from the users that connect to the database with Toad, or grant them SELECT on SYS.USER$.

The next Toad version was 12.0. Starting in Toad 12.0, we make a separate check to select from the SYS.USER$ table. If there is an error, it is not displayed and Toad will not try to select from SYS.USER$ again for the duration of that connection. You should not notice this check unless you are spooling the SQL.

I hope this clears things up. If you see any deviation from the above, check Help -> About to be sure that you are running the Toad version that you think you are running. If you still see a problem, please post the ‘create user’ script to create your user so I can try to recreate the problem.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.