Topic
4 replies Latest Post - ‏2013-02-04T15:47:54Z by SystemAdmin
SystemAdmin
SystemAdmin
17917 Posts
ACCEPTED ANSWER

Pinned topic Preventing stored procedures and UDFs direct invocation

‏2013-02-01T11:45:09Z |
Hello

I wonder if there is any way of limiting the invocation of SQL stored procedures and UDFs only from another stored procedures.

I know DB2 modules provides this functionallity, with the publish and add options when deploying the routine. However, modules are not a valid option for me since structured and xml types are not fully supported.

Many thanks in advanced
Updated on 2013-02-04T15:47:54Z at 2013-02-04T15:47:54Z by SystemAdmin
  • nivanov1
    nivanov1
    231 Posts
    ACCEPTED ANSWER

    Re: Preventing stored procedures and UDFs direct invocation

    ‏2013-02-01T14:49:18Z  in response to SystemAdmin
    > way of limiting the invocation of SQL stored procedures and UDFs only from another stored procedures

    Grant the EXECUTE permission on the invoked routines to the binder of the calling stored procedure only.
    • SystemAdmin
      SystemAdmin
      17917 Posts
      ACCEPTED ANSWER

      Re: Preventing stored procedures and UDFs direct invocation

      ‏2013-02-01T16:06:31Z  in response to nivanov1
      Many thanks for your answer

      Could you please give me some more guidance? I know how to grant execute on a function or procedure to an user, role or group, but no idea about how to grant to the binder of the calling stored procedure

      Thanks again
      • nivanov1
        nivanov1
        231 Posts
        ACCEPTED ANSWER

        Re: Preventing stored procedures and UDFs direct invocation

        ‏2013-02-04T15:34:54Z  in response to SystemAdmin
        It should go something like this.

        GRANT EXECUTE ON restrictedproc TO superusr

        • this allows superusr to run your "restricted" routine

        CONNECT TO yourdb USER superusr
        CREATE PROCEDURE wrapperproc ... BEGIN CALL restrictedproc; END

        • superusr will the the binder of that procedure's package

        REVOKE EXECUTE ON restrictedproc FROM PUBLIC -- just in case

        GRANT EXECUTE ON wrapperproc TO PUBLIC

        • everyone can now call wrapperproc, which will in turn call restrictedproc with
        • the superusr authority.
        • SystemAdmin
          SystemAdmin
          17917 Posts
          ACCEPTED ANSWER

          Re: Preventing stored procedures and UDFs direct invocation

          ‏2013-02-04T15:47:54Z  in response to nivanov1
          Perfect!

          Clear now

          Many thanks for your clarification