ORA-00942 with MS OLE DB Provider but works with Oracle Provider

From: Jerry Brown <jerry_at_jwbrown.co.uk.RemoveThisBitToReply>
Date: Tue, 14 Jan 2003 23:35:57 +0000
Message-ID: <ls592vk2phohvim3fjid9spekamvio6f6v_at_4ax.com>


I'm calling a procedure within a package that returns a ref cursor. I am the owner of the table and the package, and I have granted the end users' role execute privilege on the package and tested against the following environments using a userid that is in the role:

  1. SQLPlus: Works OK.
  2. Java application via JDBC: Works OK.
  3. VB and IIS via Oracle OLE Provider: Works OK.
  4. VB and IIS via Microsoft OLE Provider: Fails with ORA-00942 -
    'table or view does not exist'.

If I explicitly grant select on the table to the role, then option 4 works, but I would prefer the users in the role to be restricted to accessing the table only via the procedure.

As far as I can make out, the Microsoft provider is forcing the permissions on the table referenced within the procedure to be reevaluated and enforced at invocation time as if I had included
'AUTHID CURRENT_USER' in the package definition (which I have not). I
have looked through various SQL and PL/SQL documents to see if there is any clue as to how it is doing this so that I can attempt to override this behaviour, but have had no luck so far. I am using Oracle version 8.1.6.

I would be most grateful for any recommendation as to how this can be solved. I do not have the option of using the Oracle OLE DB provider in the production environment, as the Microsoft one is required by our technology standards.

Thanks in advance,

Jerry Brown

-- 
A cat may look at a king
(but probably won't bother)

<http://www.jwbrown.co.uk>
Received on Wed Jan 15 2003 - 00:35:57 CET

Original text of this message