In this installment, find out about sequence, distinct type, function, procedure, routine, package, collection, and plan privileges.

Editor's note: This article is an excerpt from the book DB2 10.1 Fundamentals: Certification Study Guide (Exam 610).

In DB2 for Linux, UNIX, and Windows and DB2 for z/OS, object privileges convey the right to perform certain actions against specific database objects. Part 1 of this series reviewed DB2's authorization ID, buffer pool, table space, storage group, schema, table, view, and index privileges. In this article, we continue our look at DB2 object privileges by examining the next set of DB2 privileges, beginning with sequence privileges.

Sequence Privileges

Sequence privileges control what users can and cannot do with a particular sequence. (A sequence is an object that can be used to generate values automatically; sequences are ideal for producing unique key values because they eliminate the concurrency and performance problems that can occur when unique counters residing outside a database are used for data value generation.) The following sequence privileges are available:

• USAGE: Allows a user to use the PREVIOUS VALUE and NEXT VALUE expressions that are associated with a certain sequence. (The PREVIOUS VALUE expression returns the most recently generated value for the specified sequence; the NEXT VALUE expression returns the next value for the specified sequence.)
• ALTER: Allows a user to perform administrative tasks, such as restarting a certain sequence or changing the increment value for a certain sequence. This privilege also lets a user add or change the comment associated with a certain sequence.

The Distinct Type Privilege (DB2 for z/OS Only)

The distinct type privilege controls what users can and cannot do with a particular distinct data type. (A distinct type is a user-defined data type that is based on an existing built-in DB2 data type.) Only one distinct type privilege exists—the USAGE OF TYPE (or USAGE OF DISTINCT TYPE) privilege, which, when granted, allows a user to use a certain distinct data type.

The Function Privilege (DB2 for z/OS Only)

The function privilege controls what users can and cannot do with a particular UDF or CAST function that has been generated for a distinct data type. (A function is a routine that can be invoked from within an SQL statement that returns a value or a table.) Only one function privilege exists—the EXECUTE privilege, which, when granted, lets a user run a certain function.

The Procedure Privilege (DB2 for z/OS Only)

The procedure privilege controls what users can and cannot do with a particular procedure. (A procedure, also known as a stored procedure, is a routine that can be called to perform operations; procedures often consist of multiple SQL statements.) Only one procedure privilege exists—the EXECUTE privilege, which, when granted, lets a user run a certain stored procedure.

The Routine Privilege (DB2 for Linux, UNIX, and Windows Only)

The routine privilege controls what users can and cannot do with a particular routine. (A routine can be a UDF, a stored procedure, or a method that different users can invoke.) Only one routine privilege exists—the EXECUTE privilege, which, when granted, allows a user to invoke a certain routine, create a function that is sourced from the routine (if the routine is a UDF), and reference the routine in a Data Definition Language (DDL) SQL statement.

Package Privileges

Package privileges control what users can and cannot do with a particular package. (A package is an object that contains information that DB2 uses to process SQL statements embedded in an application as efficiently as possible.) The following package privileges are available:

• CONTROL: Provides a user with all package privileges available. With this privilege, a user can remove (drop) a certain package from the database as well as grant and revoke individual package privileges (with the exception of the CONTROL privilege) to/from others. (This privilege is available with DB2 for Linux, UNIX, and Windows only.)
• BIND: Allows a user to bind or rebind (recreate) a certain package, as well as add new versions of a package that has already been bound, to a database. (With DB2 for z/OS, the BIND privilege also allows a user to execute both the BIND, REBIND, and FREE PACKAGE subcommands and the DROP PACKAGE SQL statement.)
• COPY: Allows a user to copy a certain package. (This privilege is available with DB2 for z/OS only.)
• EXECUTE: Allows a user to execute or run a certain package. (Users who hold EXECUTE privilege for a particular package can execute that package even if they do not have the privileges needed to execute the SQL statements stored in the package. That is because all privileges needed to execute the SQL statements in a package are implicitly granted to the user at run time.)

The Collection Privilege (DB2 for z/OS Only)

The collection privilege controls what users can and cannot do with a particular collection. (A collection is an object that is used to logically classify and group packages.) Only one collection privilege exists—the CREATE IN privilege, which, when granted, allows a user to name a certain collection as well as execute the BIND PACKAGE subcommand.

Plan Privileges (DB2 for z/OS Only)

Plan privileges control what users can and cannot do with a particular plan. (Whereas a package contains control structures that DB2 uses to run SQL statements, a plan relates an application process to a local instance of DB2 and specifies processing options; an application plan contains a list of package names, the bound form of the SQL statements used, or both.) The following package privileges are available:

• BIND: Allows a user to bind, rebind, or free a certain plan (by executing the BIND, REBIND, and FREE PLAN subcommands as well as the DROP PACKAGE statement.)
• EXECUTE: Allows a user to execute a certain plan when running the corresponding application.

Until Next Time

In Part 3, we will examine the final set of DB2 object privileges, including DB2's system, server, nickname, and variable privileges plus several more.