4 Replies Latest reply: Jan 15, 2013 11:13 AM by user10430831 RSS

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

      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
      UNC Chapel Hill
        • 1. Re: MS Access 2007 - 11g - ODBC - ORA-01013
          gdarling - oracle
          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,
          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
          • 2. Re: MS Access 2007 - 11g - ODBC - ORA-01013

            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!
            • 3. Re: MS Access 2007 - 11g - ODBC - ORA-01013
              gdarling - oracle
              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.object_type,'TABLE','SYSTEM TABLE','VIEW',
                                 'SYSTEM VIEW', o1.object_type),
                               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
                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.