Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    58

    Question Unanswered: Privilege required to execute stored procedures

    We have problem executing stored procedures after granting EXECUTE to the application user. It is only after DBADM privilege is granted to the appl user the stored procedures can be executed successfully. Isn't this risky though?

    Is DBADM privilege required in executing all stored procedures?
    If so, is there any white paper available documenting this requirement?

    Thanks in advance.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You only need to grant EXECUTE on that procedure and, if it's not a java SP, also EXECUTE on the corresponding package.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    On DB2 8.1 FP4a for AIX, I have created a stored procedure (language SQL) and have only granted execute access on the procedure, without any execute authority on the package, and it works fine. The stored procedure is executed by a java application using a user authid which is not instance owner, dbadm, etc.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Right, that's how it works in v8. Before that you had to grant EXECUTE rights for the package too.

    By the way, pinecone: check that the user under whose authority the procedure runs has permissions to access and/or update the objects that are referenced in the procedure.

  5. #5
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow Permissions

    n_i

    I'm curious about your last comment, I thought it was
    sufficient to have execute privileges. In other words
    you are saying that its not possible to run procedures
    without having permissions on the underlying objects ?

    - not very good for delegation
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I meant to say that the "definer" of an SP must have the rights to execute every statement in the procedure. I believe it's under definer's authority that the statements are executed at run time. So yes, you can "delegate" but to do that you'll need appropriate rights yourself.

  7. #7
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Smile

    OK now I understand

    Cheers
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

Posting Permissions

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