Authorities and Privileges | DB2 9 Fundamentals: Certification Study Guide

Once a user has been authenticated and an attachment to an instance or a connection to a database has been established, the DB2 Database Manger evaluates any authorities and privileges that have been assigned to the user to determine what operations the user is allowed to perform. Privileges convey the rights to perform certain actions against specific database resources (such as tables and views). Authorities convey a set of privileges or the right to perform high-level administrative and maintenance/utility operations on an instance or a database. Authorities and privileges can be assigned directly to a user, or they can be obtained indirectly from the authorities and privileges that have been assigned to a group of which the user is a member. Together, authorities and privileges act to control access to the DB2 Database Manager for an instance, to one or more databases running under that instance's control, and to a particular database's objects. Users can only work with those objects for which they have been given the appropriate authorization-that is, the required authority or privilege. Figure 3-1 provides a hierarchical view of the authorities and privileges that are recognized by DB2 9.

image from book
Figure 3-1: Hierarchy of the authorities and privileges available with DB2 9.

Authorities

DB2 9 uses seven different levels of authority to control how users perform administrative and/or maintenance operations against an instance or a database. These levels are:

System Administrator (SYSADM) authority
System Control (SYSCTRL) authority
System Maintenance (SYSMAINT) authority
System Monitor (SYSMON) authority
Database Administrator (DBADM) authority
Security Administrator (SECADM) authority
Load (LOAD) authority

Four of these levels apply to the DB2 Database Manager instance (and to all databases that are under that instance's control), while three apply only to specific databases within a particular instance. The instance-level authorities can only be assigned to groups; the names of the groups that are assigned these authorities are stored in the DB2 Database Manager configuration file that is associated with the instance. Conversely, the database-level authorities can be assigned to individual users and, in some cases, groups; groups and users that have been assigned database-level authorities are recorded in the system catalog tables of the database to which the authority applies.

System Administrator Authority

System Administrator (SYSADM) authority is the highest level of administrative authority available. Users who have been given this authority are allowed to run any DB2 utility, execute any DB2 command, and perform any SQL/XQuery operation that does not attempt to access data that is protected by Label-Based Access Control (LBAC). Users with this authority also have the ability to control all database objects within an instance, including databases, database partition groups, buffer pools, table spaces, schemas, tables, views, indexes, aliases, servers, data types, functions, procedures, triggers, packages, and event monitors. Additionally, users who have been given this authority are allowed to perform the following tasks:

Migrate an existing database from a previous version of DB2 to DB2 Version 9.
Modify the parameter values of the DB2 Database Manager configuration file associated with an instance-including specifying which groups have System Administrator, System Control, System Maintenance, and System Monitor authority. (The DB2 Database Manager configuration file is used to control the amount of system resources allocated to a single instance.)
Give (grant) Database Administrator authority and Security Administrator authority to individual users and/or groups.
Revoke Database Administrator authority and/or Security Administrator authority from individual users and/or groups.

System Administrator authority can only be assigned to a group; this assignment is made by storing the appropriate group name in the sysadm_group parameter of the DB2 Database Manager configuration file associated with an instance. Individual membership in the group itself is controlled through the security facility provided by the operating system used on the workstation where the instance has been defined. Users who possess System Administrator authority are responsible both for controlling the DB2 Database Manager associated with an instance and for ensuring the safety and integrity of the data contained in databases that fall under the instance's control.

Users who hold System Administrator authority are implicitly given the rights granted by System Control, System Maintenance, System Monitor, and Database Administrator authority. However, they are not implicitly given the rights granted by Security Administrator authority.

System Control Authority

System Control (SYSCTRL) authority is the highest level of system or instance control authority available. Users who have been given this authority are allowed to perform maintenance and utility operations both on a DB2 Database Manager instance and on any databases that fall under that instance's control. However, because System Control authority is designed to allow special users to maintain an instance that contains sensitive data that they most likely do not have the right to view or modify, users who are granted this authority do not implicitly receive authority to access the data stored in the databases that are controlled by the instance. On the other hand, because a connection to a database is required in order to perform some of the utility operations available, users who are granted System Control authority for a particular instance also receive the privileges needed to connect to each database under that instance's control.

Users with System Control authority (or higher) are allowed to perform the following tasks:

Update a database, node, or database connection services (DCS) directory (by cataloging/uncataloging databases, nodes, or DCS databases).
Modify the parameter values in one or more database configuration files. (A database configuration file is used to control the amount of system resources that are allocated to a single database during normal operation.)
Force users off the system.
Create or destroy (drop) a database.
Create, alter, or drop a table space.
Make a backup image of a database or a table space.
Restore an existing database using a backup image.
Restore a table space using a backup image.
Create a new database from a database backup image.
Perform a roll-forward recovery operation on a database.
Start or stop a DB2 Database Manager instance.
Run a trace on a database operation.
Take database system monitor snapshots of a DB2 Database Manager instance or any database under the instance's control.
Query the state of a table space.
Update recovery log history files.
Quiesce (restrict access to) a table space.
Reorganize a table.
Collect catalog statistics using the RUNSTATS utility.

Like System Administrator authority, System Control authority can only be assigned to a group. This assignment is made by storing the appropriate group name in the sysadm_group parameter of the DB2 Database Manager configuration file that is associated with a particular instance. Again, individual membership in the group itself is controlled through the security facility that is used on the workstation where the instance has been defined.

System Maintenance Authority

System Maintenance (SYSMAINT) authority is the second highest level of system or instance control authority available. Users who have been given this authority are allowed to perform maintenance and utility operations both on a DB2 Database Manager instance on and any databases that fall under that instance's control. System Maintenance authority is designed to allow special users to maintain a database that contains sensitive data that they most likely do not have the right to view or modify. Therefore, users who are granted this authority do not implicitly receive authority to access the data stored in the databases they are allowed to perform maintenance on. However, because a connection to a database must exist before some utility operations can be performed, users who are granted System Maintenance authority for a particular instance automatically receive the privileges needed to connect to each database that falls under that instance's control.

Users with System Maintenance authority (or higher) are allowed to perform the following tasks:

Modify the parameter values of one or more DB2 database configuration files.
Make a backup image of a database or a table space.
Restore an existing database using a backup image.
Restore a table space using a backup image.
Perform a roll-forward recovery operation on a database.
Start or stop a DB2 Database Manager instance.
Run a trace on a database operation.
Take database system monitor snapshots of a DB2 Database Manager instance or any database under the instance's control.
Query the state of a table space.
Update recovery history log files.
Quiesce (restrict access to) a table space.
Reorganize a table.
Collect catalog statistics using the RUNSTATS utility.

Like System Administrator and System Control authority, System Maintenance authority can only be assigned to a group. This assignment is made by storing the appropriate group name in the sysmaint_group parameter of the DB2 Database Manager configuration file that is associated with a particular instance. Again, individual membership in the group itself is controlled through the security facility that is used on the workstation where the instance has been defined.

System Monitor Authority

System Monitor (SYSMON) authority is the third highest level of system or instance control authority available with DB2. Users who have been given this authority are allowed to take system monitor snapshots for a DB2 Database Manager instance and/or for one or more databases that fall under that instance's control. System Monitor authority is designed to allow special users to monitor the performance of a database that contains sensitive data that they most likely do not have the right to view or modify. Therefore, users who are granted this authority do not implicitly receive authority to access the data stored in the databases they are allowed to collect snapshot monitor information on. However, because a connection to a database must exist before the snapshot monitor SQL table functions can be used, users who are granted System Monitor authority for a particular instance automatically receive the privileges needed to connect to each database under that instance's control.

Users with System Monitor authority (or higher) are allowed to perform the following tasks:

Obtain the current settings of the snapshot monitor switches.
Modify the settings of one or more snapshot monitor switches.
Reset all counters used by the snapshot monitor.
Obtain a list of active databases.
Obtain a list of active applications, including DCS applications.
Collect snapshot monitor data.
Use the snapshot monitor SQL table functions.

Like System Administrator, System Control, and System Maintenance authority, System Monitor authority can only be assigned to a group. This assignment is made by storing the appropriate group name in the sysmon_group parameter of the DB2 Database Manager configuration file that is associated with a particular instance. Again, individual membership in the group itself is controlled through the security facility that is used on the workstation where the instance has been defined.

Database Administrator Authority

Database Administrator (DBADM) authority is the second highest level of administrative authority available (just below System Administrator authority). Users who have been given this authority are allowed to run most DB2 utilities, issue database-specific DB2 commands, perform most SQL/XQuery operations, and access data stored in any table in a database-provided that data is not protected by LBAC. (To access data protected by LBAC, a user must have the appropriate LBAC credentials.) However, they can perform these functions only on the database for which Database Administrator authority is held.

Additionally, users with Database Administrator authority (or higher) are allowed to perform the following tasks:

Read database log files.
Create, activate, and drop event monitors.
Query the state of a table space.
Update recovery log history files.
Quiesce (restrict access to) a table space.
Reorganize a table.
Collect catalog statistics using the RUNSTATS utility.

Unlike System Administrator, System Control, System Maintenance, and System Monitor authority, Database Administrator authority can be assigned to both individual users and groups. This assignment is made by executing the appropriate form of the GRANT SQL statement (which we will look at shortly). When a user is given Database Administrator authority for a particular database, they automatically receive all database privileges available for that database as well.

Tip

Any time a user with SYSADM or SYSCTRL authority creates a new database, that user automatically receives DBADM authority on that database. Furthermore, if a user with SYSADM or SYSCTRL authority creates a database and is later removed from the SYSADM or SYSCTRL group (i.e., the user's SYSADM or SYSCTRL authority is revoked), the user retains DBADM authority for that database until it is explicitly removed (revoked).removed (revoked).

Security Administrator Authority

Security Administrator (SECADM) authority is a special database level of authority that is designed to allow special users to configure various label-based access control (LBAC) elements to restrict access to one or more tables that contain data to which they most likely do not have access themselves. Users who are granted this authority do not implicitly receive authority to access the data stored in the databases for which they manage data access. In fact, users with Security Administrator authority are only allowed to perform the following tasks:

Create and drop security policies.
Create and drop security labels.
Grant and revoke security labels to/from individual users.
Grant and revoke LBAC rule exemptions.
Grant and revoke SETSESSIONUSER privileges.
Transfer ownership of any object not owned by the Security Administrator by executing the TRANSFER OWNERSHIP SQL statement.

No other authority provides a user with these abilities, including System Administrator authority.

Security Administrator authority can only be assigned to individual users; it cannot be assigned to groups (including the group PUBLIC). This assignment is made by executing the appropriate form of the GRANT SQL statement, and only users with System Administrator authority are allowed to grant this authority.

Load Authority

Load (LOAD) authority is a special database level of administrative authority that has a much smaller scope than DBADM authority. Users that have been given this authority, along with INSERT and in some cases DELETE privileges, on a particular table are allowed to bulk-load data into that table, using either the AutoLoader utility (db2atld command) or the LOAD command/API. Load authority is designed to allow special users to perform bulk-load operations against a database that they most likely cannot do anything else with. This authority provides a way for Database Administrators to allow more users to perform special database operations, such as Extraction-Transform-Load (ETL) operations, without having to sacrifice control.

In addition to being able to load data into a database table, users with Load authority (or higher) are allowed to perform the following tasks:

Query the state of a table space using the LIST TABLESPACES command.
Quiesce (restrict access to) a table space.
Perform bulk load operations using the LOAD utility. (If exception tables are used as part of a load operation, the user must have INSERT privilege on the exception tables used as well as INSERT privilege on the table being loaded.)
Collect catalog statistics using the RUNSTATS utility.

Like Database Administrator authority, Load authority can be assigned to both individual users and groups. This assignment is made by executing the appropriate form of the GRANT SQL statement.

Privileges

As mentioned earlier, privileges are used to convey the rights to perform certain actions on specific database resources to both individual users and groups. With DB2 9, two distinct types of privileges exist: database privileges and object privileges.

Database Privileges

Database privileges apply to a database as a whole, and in many cases, they act as a second security checkpoint that must be cleared before access to data is provided. Figure 3-2 shows the different types of database privileges available.

image from book
Figure 3-2: Database privileges available with DB2 9.

As you can see in Figure 3-2, eight different database privileges exist. They are:

CONNECT: Allows a user to establish a connection to the database.
QUIESCE_CONNECT: Allows a user to establish a connection to the database while it is in a quiesced state (i.e., while access to it is restricted).
IMPLICIT_SCHEMA: Allows a user to create a new schema in the database implicitly by creating an object and assigning that object a schema name that is different from any of the schema names that already exist in the database.
CREATETAB: Allows a user to create new tables in the database.
BINDADD: Allows a user to create packages in the database (by precompiling embedded SQL application source code files against the database or by binding application bind files to the database).
CREATE_EXTERNAL_ROUTINE: Allows a user to create user-defined functions (UDFs) and/or procedures and store them in the database so that they can be used by other users and applications.
CREATE_NOT_FENCED_ROUTINE: Allows a user to create unfenced UDFs and/or procedures and store them in the database. (Unfenced UDFs and stored procedures are UDFs/procedures that are considered "safe" enough to be run in the DB2 Database Manager operating environment's process or address space. Unless a UDF/procedure is registered as unfenced, the DB2 Database Manager insulates the UDF/procedure's internal resources in such a way that it cannot be run in the DB2 Database Manager's address space.)
LOAD: Allows a user to bulk-load data into one or more existing tables in the database.

At a minimum, a user must have CONNECT privilege on a database before he can work with any object contained in that database.

Object Privileges

Unlike database privileges, which apply to a database as a whole, object privileges only apply to specific objects within a database. These objects include table spaces, schemas, tables, views, indexes, sequences, routines, packages, servers, and nicknames. Because the nature of each database object available varies, the individual privileges that exist for each object can vary as well. The following sections describe the different sets of object privileges that are available with DB2 9.

Table space privileges: Table space privileges control what users can and cannot do with a particular table space. (Table spaces are used to control where data in a database physically resides.) Figure 3-3 shows the only table space privilege available.

image from book
Figure 3-3: Table space privilege available with DB2 9.

As you can see in Figure 3-3, only one table space privilege exists. That privilege is the USE privilege, which, when granted, allows a user to create tables and indexes in the table space. The owner of a table space (usually the individual who created the table space) automatically receives USE privilege for that table space.

Tip

The USE privilege cannot be used to provide a user with the ability to create tables in the SYSCATSPACE table space or in any temporary table space that might exist.

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 named using a naming convention that consists of a schema name, followed by a period, followed by the object name.) Figure 3-4 shows the different types of schema privileges available.

image from book
Figure 3-4: Schema privileges available with DB2 9.

As you can see in Figure 3-4, three different schema privileges exist. They are:

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

Objects that can be manipulated within a schema include tables, views, indexes, packages, user-defined data types, user-defined functions, triggers, stored procedures, and aliases. The owner of a schema (usually the individual who created the schema) automatically receives all privileges available for that schema, along with the right to grant any combination of those privileges to other users and groups.

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 is used to present data as a collection of unordered rows with a fixed number of columns.) Figure 3-5 shows the different types of table privileges available.

image from book
Figure 3-5: Table privileges available with DB2 9.

As you can see in Figure 3-5, eight different table privileges exist. They are:

CONTROL: Provides a user with every table privilege available, allows the user to remove (drop) the table from the database, and gives the user the ability to grant and revoke one or more table privileges (except the CONTROL privilege) to/from other users and groups. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.)
ALTER: Allows a user to execute the ALTER TABLE SQL statement against the table. In other words, allows a user to add columns to the table, add or change comments associated with the table or any of its columns, create a primary key for the table, create a unique constraint for the table, create or drop a check constraint for the table, and create triggers for the table (provided the user holds the appropriate privileges for every object referenced by the trigger).
SELECT: Allows a user to execute a SELECT SQL statement against the table. In other words, this privilege allows a user to retrieve data from a table, create a view that references the table, and run the EXPORT utility against the table.
INSERT: Allows a user to execute the INSERT SQL statement against the table. In other words, this privilege allows a user to add data to the table and run the IMPORT utility against the table.
UPDATE: Allows a user to execute the UPDATE SQL statement against the table. In other words, this privilege allows a user to modify data in the table. (This privilege can be granted for the entire table or limited to one or more columns within the table.)
DELETE: Allows a user to execute the DELETE SQL statement against the table. In other words, allows a user to remove rows of data from the table.
INDEX: Allows a user to create an index for the table.
REFERENCES: Allows a user to create and drop foreign key constraints that reference the table in a parent relationship. (This privilege can be granted for the entire table or limited to one or more columns within the table, in which case only those columns can participate as a parent key in a referential constraint.)

The owner of a table (usually the individual who created the table) automatically receives all privileges available for that table (including CONTROL privilege), along with the right to grant any combination of those privileges (except CONTROL privilege) to other users and groups. If the CONTROL privilege is later revoked from the table owner, all other privileges that were automatically granted to the owner for that particular table are not automatically revoked. Instead, they must be explicitly revoked in one or more separate operations.

View privileges: View privileges control what users can and cannot do with a particular view. (A view is a virtual table residing in memory that provides an alternative way of working with data that resides in one or more base tables. For this reason, views can be used to prevent access to select columns in a table.) Figure 3-6 shows the different types of view privileges available.

image from book
Figure 3-6: View privileges available with DB2 9.

As you can see in Figure 3-6, five different view privileges exist. They are:

CONTROL: Provides a user with every view privilege available, allows the user to remove (drop) the view from the database, and gives the user the ability to grant and revoke one or more view privileges (except the CONTROL privilege) to/from other users and groups. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.)
SELECT: Allows a user to retrieve data from the view, create a second view that references the view, and run the EXPORT utility against the view.
INSERT: Allows a user to execute the INSERT SQL statement against the view. In other words, allows a user to add data to the view.
UPDATE: Allows a user to execute the UPDATE SQL statement against the view. In other words, this privilege allows a user to modify data in the view. (This privilege can be granted for the entire view or limited to one or more columns within the view.)
DELETE: Allows a user to execute the DELETE SQL statement against the views. In other words, this privilege allows a user to remove rows of data from the view.

In order to create a view, a user must hold appropriate privileges (at a minimum, SELECT privilege) on each base table the view references. The owner of a view (usually the individual who created the view) automatically receives all privileges available-with the exception of the CONTROL privilege-for that view, along with the right to grant any combination of those privileges (except CONTROL privilege) to other users and groups. A view owner will only receive CONTROL privilege for a view if they also hold CONTROL privilege for every base table the view references.

Index privileges: Index privileges control 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; indexes are used to improve query performance.) Figure 3-7 shows the only index privilege available.

image from book
Figure 3-7: Index privilege available with DB2 9.

As you can see in Figure 3-7, only one index privilege exists. That privilege is the CONTROL privilege, which, when granted, allows a user to remove (drop) the index from the database. Unlike the CONTROL privilege for other objects, the CONTROL privilege for an index does not give a user the ability to grant and revoke index privileges to/from other users and groups. That's because the CONTROL privilege is the only index privilege available, and only users who hold System Administrator or Database Administrator authority are allowed to grant and revoke CONTROL privileges for an object.

The owner of an index (usually the individual who created the index) automatically receives CONTROL privilege for that index.

It is important to note that this index privilege is available only with DB2 for Linux, UNIX, and Windows; CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.

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 generating unique key values, and they can be used to avoid the possible concurrency and performance problems that can occur when unique counters residing outside the database are used for data generation.) Figure 3-8 shows the different types of sequence privileges available.

image from book
Figure 3-8: Sequence privileges available with DB2 9.

As you can see in Figure 3-8, two different sequence privileges exist. They are:

USAGE: Allows a user to use the PREVIOUS VALUE and NEXT VALUE expressions that are associated with the 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 the sequence, changing the increment value for the sequence, and adding or changing the comment associated with the sequence.

The owner of a sequence (usually the individual who created the sequence) automatically receives all privileges available for that sequence, along with the right to grant any combination of those privileges to other users and groups.

Routine privileges: Routine privileges control what users can and cannot do with a particular routine. (A routine can be a user-defined function, a stored procedure, or a method that can be invoked by several different users.) Figure 3-9 shows the only routine privilege available.

image from book
Figure 3-9: Routine privilege available with DB2 9.

As you can see in Figure 3-9, only one routine privilege exists. That privilege is the EXECUTE privilege, which, when granted, allows a user to invoke the routine, create a function that is sourced from the routine (provided the routine is a function), and reference the routine in any Data Definition Language SQL statement (for example, CREATE VIEW and CREATE TRIGGER).

The owner of a routine (usually the individual who created the routine) automatically receives EXECUTE privilege for that routine.

Package privileges: Package privileges control what users can and cannot do with a particular package. (A package is an object that contains the information needed by the DB2 Database Manager to process SQL statements in the most efficient way possible on behalf of an embedded SQL application.) Figure 3-10 shows the different types of package privileges available.

image from book
Figure 3-10: Package privileges available with DB2 9.

As you can see in Figure 3-10, three different package privileges exist. They are:

CONTROL: Provides a user with every package privilege available, allows the user to remove (drop) the package from the database, and gives the user the ability to grant and revoke one or more table privileges (except the CONTROL privilege) to/from other users and groups. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.)
BIND: Allows a user to rebind or add new package versions to a package that has already been bound to a database. (In addition to the BIND package privilege, a user must hold the privileges needed to execute the SQL statements that make up the package before the package can be successfully rebound.)
EXECUTE: Allows a user to execute the package. (A user that has EXECUTE privilege for a particular package can execute that package, even if the user does not have the privileges that are needed to execute the SQL statements stored in the package. That is because any privileges needed to execute the SQL statements are implicitly granted to the package user. It is important to note that for privileges to be implicitly granted, the creator of the package must hold privileges as an individual user or as a member of the group PUBLIC-not as a member of another named group.)

The owner of a package (usually the individual who created the package) automatically receives all privileges available for that package (including CONTROL privilege), along with the right to grant any combination of those privileges (except CONTROL privilege) to other users and groups. If the CONTROL privilege is later revoked from the package owner, all other privileges that were automatically granted to the owner for that particular package are not automatically revoked. Instead, they must be explicitly revoked in one or more separate operations.

Tip

Users who have EXECUTE privilege for a package that contains nicknames do not need additional authorities or privileges for the nicknames in the package; however, they must be able to pass any authentication checks performed at the data source(s) in which objects referenced by the nicknames are stored and they must hold the appropriate authorizations and privileges needed to access all objects referenced.

Server privileges: Server privileges control what users can and cannot do with a particular federated database server. (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 to which the federated server sends queries. 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.) Figure 3-11 shows the only server privilege available.

image from book
Figure 3-11: Server privilege available with DB2 9.

As you can see in Figure 3-11, only one server privilege exists. That privilege is the PASSTHRU privilege, which, when granted, allows a user to issue Data Definition Language (DDL) and Data Manipulation Language (DML) SQL statements (as pass-through operations) directly to a data source via a federated server.

Nickname privileges: 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 server forwards the request 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 alternate names for tables and views in the same way that aliases are; instead, they are pointers by which a federated server references external objects.) Figure 3-12 shows the different types of nickname privileges available.

image from book
Figure 3-12: Nickname privileges available with DB2 9.

As you can see in Figure 3-12, eight different nickname privileges exist. They are:

CONTROL: Provides a user with every nickname privilege available, allows the user to remove (drop) the nickname from the database, and gives the user the ability to grant and revoke one or more nickname privileges (except the CONTROL privilege) to/from other users and groups. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.)
ALTER: Allows a user to execute the ALTER NICKNAME SQL statement against the nickname. In other words, this privilege allows a user to change column names in the nickname, add or change the DB2 data type that a particular nickname column's data type maps to, and specify column options for a specific nickname column.
SELECT: Allows a user to execute a SELECT SQL statement against the nickname. In other words, this privilege allows a user to retrieve data from the table or view within a federated data source that the nickname refers to.
INSERT: Allows a user to execute the INSERT SQL statement against the nickname. In other words, this privilege allows a user to add data to the table or view within a federated data source that the nickname refers to.
UPDATE: Allows a user to execute the UPDATE SQL statement against the nickname. In other words, this privilege allows a user to modify data in the table or view within a federated data source that the nickname refers to. (This privilege can be granted for the entire table or limited to one or more columns within the table.)
DELETE: Allows a user to execute the DELETE SQL statement against the nickname. In other words, allows a user to remove rows of data from the table or view within a federated data source that the nickname refers to.
INDEX: Allows a user to create an index specification for the nickname.
REFERENCES: Allows a user to create and drop foreign key constraints that reference the nickname in a parent relationship.

The owner of a nickname (usually the individual who created the table) automatically receives all privileges available for that nickname (including CONTROL privilege), along with the right to grant any combination of those privileges (except CONTROL privilege) to other users and groups. If the CONTROL privilege is later revoked from the nickname owner, all other privileges that were automatically granted to the owner for that particular table are not automatically revoked. Instead, they must be explicitly revoked in one or more separate operations.