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

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

    Re: Preventing stored procedures and UDFs direct invocation

    ‏2013-02-01T14:49:18Z  
    > 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

    Re: Preventing stored procedures and UDFs direct invocation

    ‏2013-02-01T16:06:31Z  
    • nivanov1
    • ‏2013-02-01T14:49:18Z
    > 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.
    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

    Re: Preventing stored procedures and UDFs direct invocation

    ‏2013-02-04T15:34:54Z  
    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
    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

    Re: Preventing stored procedures and UDFs direct invocation

    ‏2013-02-04T15:47:54Z  
    • nivanov1
    • ‏2013-02-04T15:34:54Z
    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.
    Perfect!

    Clear now

    Many thanks for your clarification