Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Location
    UK
    Posts
    1

    Unanswered: Stored Procedures

    I have created a sql stored procedure which executes several update statments. It compiles fine, nut when I try to run it I get the following message:

    [IBM][CLI Driver][DB2/6000] SQL0727N An error occurred during implicit system action type "5". Information returned for the error includes SQLCODE "-551", SQLSTATE "42501" and message tokens "DVSXK|UPDATE|DBAWD001.T0150ZDAT_FILE". SQLSTATE=56098

    What am I doing wrong?

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Stored Procedures

    The SQLSTATE references insufficient privileges either on the procedure or on the underlying tables for which you are updating. From IBM documentation: 'The authorization ID does not have the privilege to perform the specified operation on the identified object. '


    Originally posted by speke
    I have created a sql stored procedure which executes several update statments. It compiles fine, nut when I try to run it I get the following message:

    [IBM][CLI Driver][DB2/6000] SQL0727N An error occurred during implicit system action type "5". Information returned for the error includes SQLCODE "-551", SQLSTATE "42501" and message tokens "DVSXK|UPDATE|DBAWD001.T0150ZDAT_FILE". SQLSTATE=56098

    What am I doing wrong?

  3. #3
    Join Date
    Jul 2003
    Posts
    5

    Re: Stored Procedures

    Originally posted by dmmac
    The SQLSTATE references insufficient privileges either on the procedure or on the underlying tables for which you are updating. From IBM documentation: 'The authorization ID does not have the privilege to perform the specified operation on the identified object. '

    I'm assuming that permissions given to a group are not sufficient. It seems like explicit permissions to the user is needed. True? If so, can you please point me to where in the DB2 documentation this is? My DBA doesn't believe me.

  4. #4
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Stored Procedures

    We have users assigned to groups for which we then set privileges on objects to those groups and do not have an issue. It might be the proc itself. In reviewing your original message, I see 2 SQLSTATEs. The other makes reference to package not binding. Below are links to the documentation I reference to before (note: my perspective of DB2 is UDB):

    http://www-3.ibm.com/cgi-bin/db2www/...n=rsql0700.htm


    http://www-3.ibm.com/cgi-bin/db2www/...n=r0sttmsg.htm


    Originally posted by bfinley6
    I'm assuming that permissions given to a group are not sufficient. It seems like explicit permissions to the user is needed. True? If so, can you please point me to where in the DB2 documentation this is? My DBA doesn't believe me.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Stored Procedures

    IMHO, your DBA is absolutely right ...

    Your DBA would have given execute permission on the package to the group ... But, here, your execution of stored proc does an incremental bind ... Ask your DBA to check why the execution is doing incremental bind ....

    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jul 2003
    Posts
    5

    Re: Stored Procedures

    Originally posted by sathyaram_s
    IMHO, your DBA is absolutely right ...

    Your DBA would have given execute permission on the package to the group ... But, here, your execution of stored proc does an incremental bind ... Ask your DBA to check why the execution is doing incremental bind ....

    Cheers

    Sathyaram
    I was talking about permissions to the objects in the procedure and not the procedure itself.

  7. #7
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Stored Procedures

    If you haven't tried it already, log-in as the user you are trying to execute the proc and try to perform an update on the object(s) stated in the proc using command line processor or another tool. The user that you log-in as needs to be associated to the group that has permissions to perform actions on the objects.



    Originally posted by bfinley6
    I was talking about permissions to the objects in the procedure and not the procedure itself.

  8. #8
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Stored Procedures

    If you haven't tried it already, log-in as the user you are trying to execute the proc and try to perform an update on the object(s) stated in the proc using command line processor or another tool. If there are multiple objects, then I would have the DBA verify what group has permissions then verify that the user that you log-in as is associated to the group that has permissions to perform actions on the objects.



    Originally posted by bfinley6
    I was talking about permissions to the objects in the procedure and not the procedure itself.

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Stored Procedures

    If there is no implicit rebind, then there is no need for the user/group to have privilege on the table at all ...

    ie, generally, permissions to the objects in the package are checked at bind time against the permissions the 'bind'er holds ... If necessary privileges are held, then only execute permission on the package needs to be given to the user/group ... No explicit permissions on the object referred by the package ....

    Also, for the objects (table,view etc) group privileages work ... No explicit user privileges are required ...

    But, in this case, as there is an implicit rebind at run time, the user executing the proc is expected to have privileges on the table ....

    (If you look at the GRANT Statements, permissions can be given to the group .... )

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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