Examples of granting privileges for routines

You can grant users the required privileges for implementing, defining, and using routines, including stored procedures and user-defined functions (UDFs).

About this task

The following examples describe how to use and assign the various authorizations and privileges that are required for various roles to get an external scalar user-defined function (UDF) up and running.

Tip: In this example scenario, the implementer and definer are not the same person, which is possible because the example is an external SQL scalar UDF. However, for compiled SQL scalar (SQL PL) UDFs , the same person always implements and defines the UDF because the package is created as part of the routine definition. For more information, see Creating user-defined functions.

1. The implementer defines the UDF and becomes the package owner of the UDF.
   1. For example, assume that the implementer codes the following external user-defined function in C and names the function C_SALARY:

      /**********************************************************************
      * This routine accepts an employee serial number and a percent raise. *
      * If the employee is a manager, the raise is not applied.  Otherwise, *
      * the new salary is computed, truncated if it exceeds the employee's  *
      * manager's salary, and then applied to the database.                 *
      **********************************************************************/
       void C_SALARY                         /* main routine               */
       ( char        *employeeSerial         /* in: employee serial no.    */
         decimal     *percentRaise           /* in: percentage raise       */
         decimal     *newSalary,             /* out: employee's new salary */
         short int   *niEmployeeSerial       /* in: indic var, empl ser    */
         short int   *niPercentRaise         /* in: indic var, % raise     */
         short int   *niNewSalary,           /* out: indic var, new salary */
         char        *sqlstate,              /* out: SQLSTATE              */
         char        *fnName,                /* in: family name of function*/
         char        *specificName,          /* in: specific name of func  */
         char        *message                /* out: diagnostic message    */
       )
       {
         EXEC SQL BEGIN DECLARE SECTION;
         char        hvEMPNO-7-;             /* host var for empl serial   */
         decimal     hvSALARY;               /* host var for empl salary   */
         char        hvWORKDEPT-3-;          /* host var for empl dept no. */
         decimal     hvManagerSalary;        /* host var,emp's mgr's salary*/
         EXEC SQL END DECLARE SECTION;
       
         sqlstate = 0;
         memset( message,0,70 );
         /*******************************************************************
         * Copy the employee's serial into a host variable                  *
         *******************************************************************/
         strcpy( hvEMPNO,employeeSerial );
         /*******************************************************************
         * Get the employee's work department and current salary            *
         *******************************************************************/
         EXEC SQL SELECT  WORKDEPT, SALARY
                    INTO :hvWORKDEPT, :hvSALARY
                    FROM  EMP
                   WHERE  EMPNO = :hvEMPNO;
         /*******************************************************************
         * See if the employee is a manager                                 *
         *******************************************************************/
         EXEC SQL SELECT  DEPTNO
                    INTO :hvWORKDEPT
                    FROM  DEPT
                   WHERE  MGRNO = :hvEMPNO;
         /*******************************************************************
         * If the employee is a manager, do not apply the raise             *
         *******************************************************************/
         if( SQLCODE == 0 )
           {
             newSalary = hvSALARY;
           }
         /*******************************************************************
         * Otherwise, compute and apply the raise such that it does not     *
         * exceed the employee's manager's salary                           *
         *******************************************************************/
         else
           {
             /***************************************************************
             * Get the employee's manager's salary                          *
             ***************************************************************/
             EXEC SQL SELECT  SALARY
                        INTO :hvManagerSalary
                        FROM  EMP
                       WHERE  EMPNO = (SELECT MGRNO
                                         FROM DSN8610.DEPT
                                        WHERE DEPTNO = :hvWORKDEPT);
             /***************************************************************
             * Compute proposed raise for the employee                      *
             ***************************************************************/
             newSalary = hvSALARY * (1 + percentRaise/100);
             /***************************************************************
             * Don't let the proposed raise exceed the manager's salary     *
             ***************************************************************/
             if( newSalary > hvManagerSalary
               newSalary = hvManagerSalary;
             /***************************************************************
             * Apply the raise                                              *
             ***************************************************************/
             hvSALARY = newSalary;
             EXEC SQL UPDATE  EMP
                         SET  SALARY = :hvSALARY
                       WHERE  EMPNO  = :hvEMPNO;
           }
       
         return;
      } /* end C_SALARY */

      The implementer requires the UPDATE privilege on table EMP and SELECT privilege on tables EMP and DEPT. Users with the EXECUTE privilege on function C_SALARY do not need the UPDATE privilege on the table.

   2. Because this program contains SQL, the implementer performs the following steps:
      1. Precompile the program that implements the user-defined function.
      2. Link-edit the user-defined function with DSNRLI (RRS attachment facility), and name the program's load module C_SALARY.
      3. Bind the DBRM into package MYCOLLID.C_SALARY.
      After performing these steps, the implementer is the function package owner.
   3. The implementer then grants EXECUTE privilege on the user-defined function package to the definer.

      GRANT EXECUTE ON PACKAGE MYCOLLID.C_SALARY
       TO definer

      As package owner, the implementer can also grant execute privileges to other users, which allows those users to execute code within the package. For example:

      GRANT EXECUTE ON PACKAGE MYCOLLID.C_SALARY
            TO other_user

2. The definer completes the following steps:
   1. Issue the CREATE FUNCTION statement. For example, the following CREATE FUNCTION statement defines a UDF named SALARY_CHANGE to Db2:

      CREATE FUNCTION
        SALARY_CHANGE(
          VARCHAR( 6 )
          DECIMAL( 5,2 ) )
        RETURNS
          DECIMAL( 9,2 )
        SPECIFIC schema.SALCHANGE
        LANGUAGE C
        DETERMINISTIC
        MODIFIES SQL DATA
        EXTERNAL NAME C_SALARY
        PARAMETER STYLE DB2SQL
        RETURNS NULL ON NULL CALL
        NO EXTERNAL ACTION
        NO SCRATCHPAD
        NO FINAL CALL
        ALLOW PARALLEL
        NO COLLID
        ASUTIME LIMIT 1
        STAY RESIDENT NO
        PROGRAM TYPE SUB
        WLM ENVIRONMENT WLMENV
        SECURITY DB2
        NO DBINFO;
      

      After issuing the CREATE FUNCTION statement, the person who defined the function owns the UDF. This person (the definer) can execute the UDF package. In this case, the owner of the UDF package (the implementer) granted to the definer the EXECUTE privilege on the package that contains the UDF.

   2. The definer also grants the EXECUTE privilege on SALARY_CHANGE to all invokers of the routine.

      GRANT EXECUTE ON FUNCTION SALARY_CHANGE
         TO invoker1, invoker2, invoker3, invoker4        

3. The invokers code application programs that invoke the UDF.
   1. For example, assume that the application program contains a static SQL statement that invokes the user-defined function SALARY_CHANGE. SALARY_CHANGE gives an employee a 10% raise if the employee is not a manager. The static SQL statement follows:

      EXEC SQL SELECT  FIRSTNME,
                       LASTNAME
                       SALARY_CHANGE( :hvEMPNO, 10.0 )
                 INTO :hvFIRSTNME,
                      :hvLASTNAME,
                      :hvSALARY
                 FROM  EMP
                WHERE  EMPNO = :hvEMPNO;                                  

   2. The invoker then precompiles, compiles, link-edits, and binds the DBRM for the invoking application into the invoking package. An invoking package or invoking plan is the package or plan that contains the SQL that invokes the user-defined function. After performing these steps, the invoker is the owner of the invoking plan or package.
      The invoker must also hold the SELECT privilege on the EMP table and the EXECUTE privilege on the SALARY_CHANGE function.