Privileges

When an SQL procedure is created, its procedure body is converted into DB2 bytecode and a corresponding package will be created. This bytecode is then executed by the database engine when the stored procedure is called. A package is an object in the database that contains the access method which DB2 will use for an application (or a procedure in this case). Privileges can be given to users to execute, bind, and control a package. This will be discussed in more detail later in the appendix.

Now that you understand that SQL procedures are actually database packages, let's talk about what privileges are required to create and execute them. There are basically two groups of users who need some kind of privileges to work with SQL procedures: developers and users.

Privileges Required by Developers

The first group is development, which includes developers who write and create the procedures. Developers need to have the privilege to create procedures.

Privileges Required by LUW Stored Procedure Developers

The developer, or group of developers, can be given the necessary privileges by using the DB2 GRANT command. In order to create a package, they need the CREATE_NOT_FENCED_ROUTINE privilege, a database-level privilege that allows them to add a procedure to a database. Refer to the SQL Reference Guide for the complete syntax of the GRANT statement. A user can be granted this privilege by issuing this command:

 GRANT CREATE NOT_FENCED_ROUTINE ON DATABASE TO USER db2dev 

The user will also need to have BINDADD privileges to allow him or her to create new packages in the database. Each procedure needs a package so the procedure cannot be created without this authority. Here is an example of how to grant the BINDADD privilege to a user:

 GRANT CREATE_NOT_FENCED_ROUTINE ON DATABASE TO USER db2dev 

Besides the BINDADD privilege, developers also need proper privileges to access database objects referenced within the procedures. Privileges can be granted to each developer individually or to the developer groupfor example:

 GRANT SELECT, DELETE ON TABLE employee TO USER db2dev GRANT SELECT, UPDATE ON TABLE employee TO GROUP db2dgrp 

In DB2, table privileges granted to groups only apply to dynamic statements. This means that if the procedure contains static SQL statements, table privileges must be granted to each developer individually or to PUBLIC. Otherwise, creation of such an SQL procedure will fail with insufficient privilegesfor example:

 GRANT SELECT, DELETE ON TABLE employee TO USER db2dev GRANT SELECT, UPDATE ON TABLE employee TO PUBLIC 

PUBLIC is not considered as a group but it instead represents every user who accesses the database.

If the underlying objects that the stored procedure referencessuch as a table or vieware dropped or are changed, then the package will have to be re-bound. The rebinding will occur implicitly when the package is next used. If, however, the package was dependent on a UDF that was dropped and re-created, an explicit REBIND command will have to issued for the package.

Privileges Required by iSeries Stored Procedure Developers

Figure E.1 shows what occurs when a create procedure is issued on iSeries. It will help you understand what privileges are required to create a stored procedure. Recall that a stored procedure is converted to C code, and compiled into an executable program.

Figure E.1. Stored procedure creation on iSeries.

When a stored procedure is created, it is precompiled. The precompile step changes the SQL procedure into a C program, replacing the SQL statements in the original procedure with linkages to the iSeries system services which implement the statement (1). Directives to the compiler are passed via the SET OPTION clause of the CREATE PROCEDURE statement. A program object is then created using the CRTPGM command, taking the C source code as input (2). The system catalog tables and views are updated to register the stored procedure creation (3). In addition, the associated space for the program object is updated to include the same information that was added to the catalog tables. This information is used when invoking commands such as SAVOBJ/SAVLIB and RSTOBJ/RSTLIB.

In order for all the steps to complete without errors, the developer must have administrative authority or all of the following:

• *USE privilege on the CRTPGM command.

• Create in schema privileges for the target schema in which the proc is created. If the target schema (library) name is also the name of a user profile, then system authority *ADD is required on the library.

• INSERT privilege on the SYSPARMS table and *EXECUTE authority on the QSYS2 library.

In addition to the privileges listed here, if any distinct types are referenced, the developer must have USAGE privileges on the distinct type and *EXECUTE authority on the library where the distinct type resides.

Privileges Required by zSeries Stored Procedure Developers

The development of stored procedures and functions on zSeries is handled by two main groups of users, implementers and definers. The implementer is the user who will be writing the SQL procedure code. The definer is the user who will execute the CREATE PROCEDURE statement to define the SQL procedure in the DB2 subsystem. Normally, the implementer and the definer are the same user. A third group, the invokers, is covered in the user privileges section because it deals with those who actually execute the procedures.

The implementer needs to have the required privileges to perform any SQL statement in the procedurefor example, to update a table, UPDATE privilege is required on the particular table. The implementer also needs the BINDADD privilege to bind the package associated to the procedure. After binding the package, the implementer becomes the package owner. The implementer also needs to grant the EXECUTE privilege to the definer on the package containing the stored procedure.

When the definer issues the CREATE PROCEDURE statement, he becomes the procedure owner. Recall that the definer can execute the procedure because the EXECUTE privilege on the corresponding package is granted by the implementer.

In addition, for dynamic SQL in DB2 for zSeries, two main factors need to be considered: DYNAMICRULES bind/rebind options, and the runtime environment of the package. These are covered in the zSeries specific section at the end of the chapter.

Privileges Required by Users

The other group is the user groupthose users who execute the procedure. The different privileges required in each environment are covered in the following sections.

Privileges Required by LUW Users

Users on LUW need the EXECUTE privilege on the procedure. EXECUTE is a procedure-level privilege that allows the grantee to run the stored procedure. GRANT EXECUTE requires the name of the stored procedure. Examples of granting execute privilege include the following:

 GRANT EXECUTE ON PROCEDURE call_to_caller TO USER db2user GRANT EXECUTE ON PROCEDURE  db2admin.test_proc TO GROUP db2ugrp 

Notice that altering stored procedures is not mentioned in this book because stored procedures are one of the few database objects that cannot be altered. If a procedure requires code changes, it has to be dropped and re-created. In other words, if you rebuild a stored procedure, you will need to once again grant the EXECUTE privilege on the new procedure to the users and groups.

Privilege considerations for users on dynamic and static SQL statements coded in the SQL procedure are very straightforward. For static SQL statements, EXECUTE is the only privilege required by the users. With dynamic SQL statements, users also need appropriate access to each object referenced in the procedure, either explicitly to the user or implicitly to the user group:

 GRANT SELECT, DELETE ON TABLE employee TO USER db2user GRANT SELECT, DELETE ON TABLE employee TO GROUP db2ugrp 

Privileges Required by iSeries Users

In order to be able to execute a stored procedure on iSeries, the authorization ID of the caller must have the EXECUTE privilege on the procedure being called and *EXECUTE authority on the library containing the stored procedure. The GRANT statement can be used to provide EXECUTE privilege on the procedure:

 GRANT EXECUTE ON PROCEDURE MYLIB.PROCNAME TO PUBLIC 

Additionally, as is the case in LUW, for dynamic SQL, the invoker must also have access to the individual objects referenced in the the dynamic SQL statement.

Privileges Required by zSeries Users

Typically, the invoker is the user who executes the SQL procedure. The definer needs to grant EXECUTE privilege for the procedure to the invoker. If anyone can be the invoker, the EXECUTE privilege can be given to PUBLIC as shown:

 GRANT EXECUTE ON PROCEDURE MYSCHEMA.PROCNAME TO PUBLIC