BUG: Stored Procedure Fails if Rights Are Revoked and Regranted

• Microsoft ODBC for Oracle, version 2.0 (Build 2.73.7269)

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

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this bug and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create table table1.foo(c1 varchar(20)).

2. Grant user2 "INSERT" privileges to table1.foo.

3. Create or replace procedure user2.sp_infoo(p1 in varchar) as

         Begin
         INSERT INTO user1.foo values ( p1);
         end;
   

4. Execute user2.sp_infoo to make sure the procedure works.

5. Revoke "INSERT" privileges on table1.foo to user2.

6. Grant "INSERT" privileges on table1.foo to user2.

7. Execute user2.sp_infoo(p1).