ORA-00942 with MS OLE DB Provider but works with Oracle Provider
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:
- SQLPlus: Works OK.
- Java application via JDBC: Works OK.
- VB and IIS via Oracle OLE Provider: Works OK.
- 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