This discussion is archived
4 Replies Latest reply: Jan 15, 2013 9:13 AM by user10430831 RSS

MS Access 2007 - 11g - ODBC - ORA-01013

user10430831 Newbie
Currently Being Moderated
I have a client which accesses our databases using MS Access 2007.
We recently upgraded to Oracle 11g client on the host he accesses from and now he is getting ORA-01013 errors.
The ODBC connections are System DSNs.

Testing the ODBC connections directly through the ODBC Data Source Administrator with his account comes back with a "Success"!
The Oracle account he is using has permissions to the objects and can do what they need to at the SQL*Plus level.

From the same Windows client:
My DBA Oracle account can utilize the ODBC connection and pull back data.
His Oracle account receives an ORA-01013.

I attempted to grant him "select any dictionary" and that did not resolve the issue.
I granted him DBA privileges and his account is now able to use the ODBC connection and pull back the data required. I've removed that privilege for now since I will not be making that permanent, I just was trying to see what was different between his account and my account. So far it seems to be solely the Oracle level privileges that are different.

This worked under 10g Oracle client.
What changed between 10g and 11g clients which would require an ODBC connection to have elevated privileges in order to make a connection??
What are the newly required elevated privileges specifically?

Thank you,
Scott Crouch
DBA
UNC Chapel Hill
  • 1. Re: MS Access 2007 - 11g - ODBC - ORA-01013
    gdarling - oracle Expert
    Currently Being Moderated
    Hi Scott,

    I think you're confusing ora-1013 "user requested cancel of operation" with ora-1031 "insufficient privileges".

    1013 basically means the query took too long so the odbc driver cancelled it.

    To resolve that:
    a) You can disable query timeout in the DSN config,
    or
    b) make the operation go faster. Without knowing what the actual query that times out is, there are options like 'bind timestamp as date" and "bind number as float" that can change the explain plan for operations.

    Hope it helps
    Greg
  • 2. Re: MS Access 2007 - 11g - ODBC - ORA-01013
    user10430831 Newbie
    Currently Being Moderated
    Greg,

    Thank you for your response! I have gone down that road of treating it more like a 1031 instead of a 1013, solely because I can get results from using a DBA enabled account, and a non-DBA account gets the error. Same software, same hardware.

    And for clarification, the timeout occurs right after the user provides their login information. It times out trying to provide the list of objects that they can link to in MS Access.

    Maybe the DBA account barely squeaks in under the timeout since there is less security checking?

    I am having the Windows Admins change the DSN entry and I will test it without the timeout enabled. Hopefully that will work!

    Thank you again!
    -Scott
  • 3. Re: MS Access 2007 - 11g - ODBC - ORA-01013
    gdarling - oracle Expert
    Currently Being Moderated
    Hi Scott,

    Usually MSAccess issues a SQLTables call to get a list of everything the user can attach to. A lot of times that query runs slow and exceeds timeout. I'm not too sure why it would run faster for DBA, which presumably would return more rows.

    You might want to try it though, from sqlplus, as the two users to see if there's a difference. A 10046 would probably help to confirm the exact query, but it's probably this one:
      select *
      from (select  null table_qualifier,
                   o1.owner table_owner,
                   o1.object_name table_name,
                   decode(o1.owner,'SYS',
                     decode(o1.object_type,'TABLE','SYSTEM TABLE','VIEW',
                       'SYSTEM VIEW', o1.object_type),
                     'SYSTEM',
                     decode(o1.object_type,'TABLE','SYSTEM TABLE','VIEW',
                       'SYSTEM VIEW', o1.object_type),
                     o1.object_type) table_type,
                   null remarks
            from all_objects o1
            where o1.object_type in ('TABLE', 'VIEW')
              union select null table_qualifier,
                           s.owner table_owner,
                           s.synonym_name table_name,
                           'SYNONYM' table_type,
                           null remarks
                    from all_objects o3,
                         all_synonyms s
                    where o3.object_type in ('TABLE','VIEW')
                      and s.table_owner = o3.owner
                      and s.table_name = o3.object_name
                      union select null table_qualifier,
                                   s1.owner table_owner,
                                   s1.synonym_name table_name,
                                   'SYNONYM' table_type,
                                   null remarks
                            from all_synonyms s1
                            where s1.db_link is not null) tables
      WHERE 1=1
        AND (table_type = 'TABLE'
             OR table_type = 'SYSTEM TABLE'
             OR table_type = 'VIEW'
             OR table_type = 'SYNONYM' )
      ORDER BY 4,2,3
  • 4. Re: MS Access 2007 - 11g - ODBC - ORA-01013
    user10430831 Newbie
    Currently Being Moderated
    Thank you for that information!
    Changing the timeout value on the DSN fixed the issue, and the end-user is able to access his data again.

    -Scott

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points