PRB: Stored Procedure Fails if Rights Are Revoked and Regranted (180853)


The information in this article applies to:

  • Microsoft ODBC for Oracle version 2.0 Build 2.73.7283.01
  • Microsoft ODBC for Oracle version 2.0 Build 2.73.7283.03
  • Microsoft ODBC for Oracle version 2.5 Build 2.573.4403
  • Microsoft Data Access Components 2.0
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
This article was previously published under Q180853

SYMPTOMS

The Microsoft Oracle driver returns the following error message when user rights are granted and then revoked on a table accessed by a stored procedure:
Return: SQL_ERROR=-1
szErrorMsg="[Microsoft][ODBC driver for Oracle]Wrong number of parameters"
szErrorMsg="[Microsoft][ODBC driver for Oracle]Syntax error or access violation"

CAUSE

The call to the Oracle OCI function odessp() fails in this scenario, but is needed in order to implement default parameters.

RESOLUTION

You must recompile your stored procedure after modifying underlying table permissions before you run the stored procedure again.

STATUS

This is a problem with the return value from the odessp() Oracle OCI function, not with the Microsoft Oracle ODBC driver.

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create table table1.test(c1 varchar(20)).
  2. Grant user2 "INSERT" privileges to table1.test.
  3. Create or replace procedure user2.sp_intest(p1 in varchar) as 
          Begin
      INSERT INTO user1.test values ( p1);
      end;
  4. Execute user2.sp_intest to make sure the procedure works.
  5. Revoke "INSERT" privileges on table1.test to user2.
  6. Grant "INSERT" privileges on table1.test to user2.
  7. Execute user2.sp_intest(p1).
Modification Type:MajorLast Reviewed:12/3/2003
Keywords:kbDatabase kbDriver kbnofix kbOracle kbprb KB180853
©2003 Microsoft Corporation. All rights reserved.