Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22

    Unanswered: unusual user error running stored procedure

    I'm running DB2 9.1.5 on the Z/os. I'm getting an unusual user privilege error running a stored procedure created as one user and run as another. Basically it goes like this:

    * log in as USER111
    * create procedure MAIN.PROC1 { procedure updates table MAIN.TABLE }
    * log in as USER222
    * call MAIN.PROC1()
    SQL0551N "USER111" does not have the privilege to perform operation "UPDATE" on object "MAIN.TABLE". SQLSTATE=42501

    This is correct. USER111 does not have update privileges, but USER222 does and that is who is logged in and running the procedure. Any idea on why I am getting this error?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I might be wrong with respect to Z/OS, but I think this is exactly the expected behaviour. The SP package runs with the authorization ID that was used to bind it. You could try rebinding the package as user222 before running the SP.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    davebert99, I think you just need to grant USER222 Execute authorization on the Stored Procedure. I have also seen some places that USER222 might need Execute authorization on the Package but I have had mixed results (most of the time I do NOT grant to the Package and the Stored Procedure can be executed with no problem.

  4. #4
    Join Date
    Jun 2009
    Location
    Pennsylvania
    Posts
    22
    Thanks all. I'll research those suggestions.

    USER222 must already have Execute or they couldn't run the procedure to get that error. They would get:
    SQL0551N "USER222" does not have the privilege to perform operation "CALL" on object "MAIN.PROC1".

    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •