In this three-part series, we examine the various object privileges provided in DB2.

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

In DB2 for Linux, UNIX, and Windows and DB2 for z/OS, privileges convey the right to perform certain actions against specific database resources. Two distinct types of privileges exist: database and object. Database privileges apply to a database as a whole and control which actions a user is allowed to perform against a particular database. Object privileges, on the other hand, apply to specific database objects (for example, tables, indexes, and views).

Because the nature of each database object varies, the individual privileges that exist for each object differ. In this three-part series, we examine the various object privileges provided in DB2.

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

The authorization ID privilege lets a user set the session authorization ID to one of a set of specified authorization IDs available (by executing the SET SESSION AUTHORIZATION statement). Only one authorization ID privilege exists: the SETSESSIONUSER privilege.

Buffer Pool Privileges (DB2 for z/OS Only)

Buffer pool privileges control what users can and cannot do with a particular buffer pool. (A buffer pool is a portion of memory that has been allocated to DB2 for the purpose of caching table and index data as it is read from disk.) The following buffer pool privileges are available:

• USE OF BUFFERPOOL: Allows a user to use a certain buffer pool.
• USE OF ALL BUFFERPOOLS: Allows a user to use every buffer pool available.

The Table Space Privilege

The table space privilege controls what users can and cannot do with a particular table space. (Table spaces control where data in a database physically resides.) Only one table space privilege exists—the USE (or USE OF TABLESPACE) privilege, which, when granted, lets a user use a certain table space.

The Storage Group Privilege (DB2 for z/OS Only)

The storage group privilege controls what users can and cannot do with a particular storage group. (With DB2 for z/OS, a storage group refers to a set of volumes on disks that holds the data sets in which tables and indexes are stored.) Only one storage group privilege exists—the USE (or USE OF STOGROUP) privilege, which, when granted, lets a user use a certain storage group.

Schema Privileges

Schema privileges control what users can and cannot do with a particular schema. (A schema is an object that is used to logically classify and group other objects in the database; most objects are identified by using a naming convention that consists of a schema name, followed by a period, followed by the object name.) The following schema privileges are available:

• CREATEIN: Allows a user to create objects within a certain schema.
• ALTERIN: Allows a user to change the comment associated with any object in a certain schema or alter any object that resides in the schema.
• DROPIN: Allows a user to remove (drop) any object within a certain schema.

With DB2 for Linux, UNIX, and Windows, the objects that can be manipulated within a schema include tables, views, indexes, packages, data types, functions, triggers, procedures, and aliases. With DB2 for z/OS, those objects consist of distinct data types, UDFs, triggers, and procedures.

Table Privileges

Table privileges control what users can and cannot do with a particular table in a database. (A table is a logical structure that presents data as a collection of unordered rows with a fixed number of columns.) The following table privileges are available:

• CONTROL: Provides a user with all table privileges available. With this privilege, a user can remove (drop) a certain table from the database, execute the RUNSTATS and REORG commands against the table, execute the SET INTEGRITY statement against the table, and grant and revoke individual table privileges (with the exception of the CONTROL privilege) to/from others. (This privilege is available with DB2 for Linux, UNIX, and Windows only.)
• ALTER: Allows a user to change a certain table’s definition and/or the comment associated with the table as well as create or drop a table constraint.
• SELECT: Allows a user to retrieve data from a certain table as well as create a view that references the table.
• INSERT: Allows a user to add data to a certain table.
• UPDATE: Allows a user to modify data in a certain table. (This privilege can apply to the entire table or be limited to specific columns within the table.)
• DELETE: Allows a user to remove data from a certain table.
• INDEX: Allows a user to create an index for a certain table.
• REFERENCES: Allow a user to create and drop foreign key constraints that reference a certain table in a referential integrity constraint. (This privilege can apply to the entire table or be limited to specific columns within the table, in which case a user can only create and drop referential constraints that reference the columns identified.)
• TRIGGER: Allows a user to create triggers for a certain table. (This privilege is available with DB2 for z/OS only.)

View Privileges

View privileges control what users can and cannot do with a particular view. (A view is a virtual table that provides an alternative way of working with data that physically resides in one or more base tables; views are frequently used to limit access to specific columns in a table.) The following view privileges are available:

• CONTROL: Provides a user with all view privileges available. With this privilege, a user can remove (drop) a certain view from the database as well as grant and revoke individual view privileges (with the exception of the CONTROL privilege) to/from others. (This privilege is available with DB2 for Linux, UNIX, and Windows only.)
• SELECT: Allows a user to use a certain view to retrieve data from its underlying base table(s).
• INSERT: Allows a user to use a certain view to add data to its underlying base table(s).
• UPDATE: Allows a user to use a certain view to modify data in its underlying base table(s). (This privilege can apply to the entire view or be limited to specific columns within the view.)
• DELETE: Allows a user to use a certain view to remove data from its underlying base table(s).

It is important to note that with DB2 for Linux, UNIX, and Windows, the owner of a view will receive CONTROL privilege for that view only if they hold CONTROL privilege for every base table the view references.

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

The index privilege controls what users can and cannot do with a particular index. (An index is an ordered set of pointers that refer to one or more key columns in a base table; use of indexes can improve query performance.) Only one index privilege exists—the CONTROL privilege, which, when granted, allows users to remove a certain index from a database.

Unlike the CONTROL privilege for other objects, the CONTROL privilege for an index does not automatically give users the ability to grant and revoke index privileges to/from others. That is because the only index privilege available is the CONTROL privilege, and only users with ACCESSCTRL or SECADM authority are allowed to grant and revoke CONTROL privilege.

To Be Continued

In Part 2, we will examine several more DB2 object privileges, including those that govern access to procedures, packages, collections, and more.