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.

Roger E. Sanders is a Principal Sales Enablement & Skills Content Specialist at IBM. He has worked with Db2 (formerly DB2 for Linux, UNIX, and Windows) since it was first introduced on the IBM PC (1991) and is the author of 26 books on relational database technology (25 on Db2; one on ODBC). For 10 years he authored the “Distributed DBA” column in IBM Data Magazine, and he has written articles for publications like Certification Magazine, Database Trends and Applications, and IDUG Solutions Journal (the official magazine of the International Db2 User's Group), as well as tutorials and articles for IBM's developerWorks website. In 2019, he edited the manuscript and prepared illustrations for the book “Artificial Intelligence, Evolution and Revolution” by Steven Astorino, Mark Simmonds, and Dr. Jean-Francois Puget.

From 2008 to 2015, Roger was recognized as an IBM Champion for his contributions to the IBM Data Management community; in 2012 he was recognized as an IBM developerWorks Master Author, Level 2 (for his contributions to the IBM developerWorks community); and, in 2021 he was recognized as an IBM Redbooks Platinum Author. He lives in Fuquay Varina, North Carolina.

---

MC Press books written by Roger E. Sanders available now on the MC Press Bookstore.

		QuickStart Guide to Db2 Development with Python Discover how Python, SQL, and Db2 can successfully be used with each other. List Price $9.95 Now On Sale
		DB2 10.5 Fundamentals for LUW (Exam 615) Don't even think about attempting to take the DB2 Fundamentals exam without this indispensable study guide. List Price $79.95 Now On Sale
		DB2 10.1 Fundamentals (Exam 610) Let one of the world's leading DB2 authors and a participant in the exam development help you succeed. List Price $79.95 Now On Sale
		Artificial Intelligence: Evolution and Revolution Operational AI has become available to the masses, setting the wheels in motion for a worldwide AI revolution that has never been seen before. List Price $16.95 Now On Sale
		DB2 10.5 DBA for LUW Upgrade from DB2 10.1: Certification Study Notes Here's everything you need to know to take and pass Exam 311, complete with a practice exam and study key. List Price $21.95 Now On Sale
		From Idea to Print Here's everything you need to know to turn your technical knowledge and expertise into a published article or book. List Price $49.95 Now On Sale
		DB2 9 Fundamentals (Exam 730) Use this review before taking the test to prove you've mastered the basics of DB2 9. List Price $59.95 Now On Sale
		DB2 9 for Linux, UNIX, and Windows Database Administration (Exam 731) Use this indispensable study guide to prepare to take, and pass, Exam 731. List Price $64.95 Now On Sale
		DB2 9.7 for Linux, UNIX, and Windows Database Administration (Exam 541) Get ready to take the DB2 9.7 certification exam with this handy study guide. List Price $21.95 Now On Sale
		DB2 9 for Linux, UNIX, and Windows Advanced Database Administration (Exam 734) Review all exam topics and take the included practice test to be sure you're ready on testing day. List Price $64.95 Now On Sale
		DB2 9 for Linux, UNIX, and Windows Database Administration Upgrade (Exam 736) Prep for success with the master of DB2 certification study guides! List Price $34.95 Now On Sale
		Data Fabric: An Intelligent Data Architecture for AI This book explains the concepts and values that a data fabric approach can deliver to both technical and business communities. List Price $19.95 Now On Sale