In the final installment of this three-part series, learn about DB2's system, server, nickname, and variable privileges plus several more.
Editor's note: This article is an excerpt from the book DB2 10.1 Fundamentals: Certification Study Guide
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. Part 2 of this series reviewed DB2's sequence, distinct type, function, procedure, routine, collection, package, and plan privileges. In this article, we conclude our discussion of DB2 object privileges by examining the remaining privileges available to control user interactions with DB2 objects.
The JAR File Privilege (DB2 for z/OS Only)
The JAR privilege controls what users can and cannot do with a particular JAR file. (A JAR file is a file that contains a collection of classes for a Java routine.) Only one JAR privilege exists—the USAGE privilege, which, when granted, allows a user to use a certain JAR file.
System Privileges (DB2 for z/OS Only)
System privileges control what users can and cannot do with a DB2 system. The following system privileges are available:
- ARCHIVE: Allows a user to archive the current active log, provide information about input archive logs, modify the checkpoint frequency specified during installation, and control allocation and deallocation of tape units for archive processing.
- BINDADD: Allows a user to create new plans and packages.
- BINDAGENT: Allows a user to bind, rebind, or free a plan or package as well as copy a package on behalf of the grantor. (The BINDAGENT privilege is intended for separation of function, not for added security.)
- BSDS: Allows a user to recover the bootstrap data set (by executing the RECOVER BSDS command).
- CREATEALIAS: Allows a user to create an alias for a table or view.
- CREATEDBA: Allows a user to create a new database and have DBADM authority over it.
- CREATEDBC: Allows a user to create a new database and have DBCTRL authority over it.
- CREATESG: Allows a user to create a storage group.
- CREATE_SECURE_OBJECT: Allows a user to create secure objects, such as secure triggers or secure UDFs. (If a trigger is defined for tables that are enforced with row or column access control, it must be secure. Similarly, if a UDF is referenced in the definition of a row permission or column mask, it must be secure. In addition, if a UDF is invoked in a query and its arguments reference columns with column masks, the UDF must be secure.)
- CREATETMTAB: Allows a user to define a created temporary table.
- DEBUGSESSION: Allows a user to control debug session activity for SQL stored procedures, Java stored procedures, and non-inline SQL functions.
- DISPLAY: Allows a user to display system information using the DISPLAY ARCHIVE, DISPLAY BUFFERPOOL, DISPLAY DATABASE, DISPLAY LOCATION, DISPLAY LOG, DISPLAY THREAD, and DISPLAY TRACE commands.
- EXPLAIN: Allows a user to generate Explain query plans.
- MONITOR1: Allows a user to receive trace data that is not potentially sensitive.
- MONITOR2: Allows a user to receive all trace data.
- RECOVER: Allows a user to recover threads (by executing the RECOVER INDOUBT command).
- STOPALL: Allows a user to stop DB2.
- STOSPACE: Allows a user to obtain data about storage space usage.
- TRACE: Allows a user to control tracing using the START TRACE, STOP TRACE, and MODIFY TRACE commands.
The Server Privilege (DB2 for Linux, UNIX, and Windows Only)
The server privilege controls whether a user can work with a particular federated server data source. (A DB2 federated system is a distributed computing system that consists of a DB2 server, known as a federated server, and one or more data sources the federated server sends queries to. Each data source consists of an instance of some supported relational database management system—such as Oracle—plus the database or databases that the instance supports.) Only one server privilege exists—the PASSTHRU privilege, which, when granted, allows a user to issue DDL and DML SQL statements (as pass-through operations) directly to a data source via a federated database server.
Nickname Privileges (DB2 for Linux, UNIX, and Windows Only)
Nickname privileges control what users can and cannot do with a particular nickname. (When a client application submits a distributed request to a federated database server, the request is forwarded to the appropriate data source for processing. However, such a request does not identify the data source itself; instead, it references tables and views within the data source by using nicknames that map to specific table and view names in the data source. Nicknames are not alternative names for tables and views in the same way that aliases are; instead, they are pointers that a federated server uses to reference external objects.) The following nickname privileges are available:
- CONTROL: Provides a user with all nickname privileges available. With this privilege, a user can remove (drop) a certain nickname from the database as well as grant and revoke individual nickname 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 add, reset, or drop a column option for a certain nickname. It also lets a user change a nickname's column name or data type, as well as modify the comment associated with the nickname.
- SELECT: Allows a user to retrieve data from the table or view within a federated data source that a certain nickname refers to.
- INSERT: Allows a user to insert data into the table or view within a federated data source that a certain nickname refers to.
- UPDATE: Allows a user to modify data in the table or view within a federated data source that a certain nickname refers to. (This privilege can be granted for the entire table or limited to specific columns within the table.)
- DELETE: Allows a user to remove rows of data from the table or view within a federated data source that a certain nickname refers to.
- INDEX: Allows a user to create an index specification for a certain nickname.
- REFERENCES: Allows a user to create and drop foreign key constraints that reference a certain nickname in a referential integrity constraint.
Variable Privileges (DB2 for Linux, UNIX, and Windows Only)
Variable privileges control what users can and cannot do with a particular global variable. (A global variable is a named memory variable that can be retrieved or modified using SQL statements; global variables enable applications to share relational data among SQL statements, without the need for additional application logic to support such data transfers.) The following variable privileges are available:
- READ: Allows a user to read the value of a certain global variable.
- WRITE: Allows a user to assign a value to a certain global variable.
The XML Schema Repository (XSR) Object Privilege (DB2 for Linux, UNIX, and Windows Only)
The XML schema repository (XSR) object privilege controls what users can and cannot do with a particular XSR object. (XSR objects are used to validate and process XML instance documents that are stored in an XML column.) Only one XSR object privilege exists—the USAGE privilege, which, when granted, allows a user to use a certain XSR object.
The Workload Privilege (DB2 for Linux, UNIX, and Windows Only)
The workload privilege controls what users can and cannot do with a particular workload. (Workloads are a key part of a DB2 workload management solution; workloads are used to identify a source of work.) Only one workload privilege exists—the USAGE privilege, which, when granted, allows a user to use a certain defined workload.
Note: Users with ACCESSCTRL, DATAACCESS, DBADM, SECADM, or WLMADM authority are implicitly granted the USAGE privilege on all available workloads.
LATEST COMMENTS
MC Press Online