Authorities and Privileges

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 (these can be assigned directly to a user, or they can be obtained indirectly from group privileges that have been assigned to a group the user is a member of) to determine what operations the user is allowed to perform. Authorities convey a set of privileges and/or the right to perform high-level administrative and maintenance/utility operations against an instance or a database. Privileges, on the other hand, convey the rights to perform certain actions against specific database resources (such as tables and views). 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-3 provides a hierarchical view of the authorities and privileges that are recognized by DB2 UDB.

Figure 3-3. Hierarchy of the authorities and privileges available with DB2 UDB.

Authorities

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

• System Administrator (SYSADM) authority

• System Control (SYSCTRL) authority

• System Maintenance (SYSMAINT) authority

• Database Administrator (DBADM) authority

• Load (LOAD) authority

The first three of these levels apply to the DB2 Database Manager instance (and to all databases that are managed by that instance), while the remaining two apply only to specific databases within an instance. Furthermore, the three 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 two database-level authorities can be assigned to an individual user and/or to a group of users; 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 with DB2 UDB. Users that have been given this authority are allowed to run any available DB2 UDB utilities, execute any DB2 UDB command, perform any SQL operation, and control all objects within an instance, including databases, database partition groups, buffer pools, tablespaces, tables, views, indexes, schemas, aliases, data types, functions, procedures, triggers, packages, servers, and event monitors . In addition, users who have been given this authority are allowed to perform the following tasks :

• Migrate an existing database to make it compatible with a new version of DB2 UDB.

• Modify the parameter values of the DB2 Database Manager configuration file associated with the instance ”including specifying which groups have System Control and/or System Maintenance 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 to groups and/or individual users.

• Take away (revoke) Database Administrator authority from groups and/or individual users.

SYSADM authority can only be assigned to a group, and this assignment is made by storing the appropriate group name in the sysadm_group parameter of the DB2 Database Manager configuration file associated with a particular instance. Individual membership in the group itself is controlled through the security facility used on the workstation where the instance has been defined.

System Control authority

System Control (SYSCTRL) authority is the highest level of system/instance control authority available with DB2 UDB. Users that have been given this authority are allowed to perform maintenance and utility operations against both a DB2 Database Manager instance and any databases that fall under that instance's control. However, because SYSCTRL authority is designed to allow special users to maintain an instance containing sensitive data that they most likely do not have the right to access, 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 and utility operations on. On the other hand, because a connection to a database must exist before some utility operations can be performed, users who are granted SYSCTRL authority for a particular instance also receive the privileges needed to connect to each database under that instance's control.

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

• Update a database, node, or distributed connection services (DCS) directory (by cataloging/ uncataloging databases, nodes, or DCS databases).

• Modify the parameter values of one or more DB2 database configuration files. (A database configuration file is used to control the amount of system resources allocated to a single database during normal operation.)

• Force users off the system.

• Create or destroy (drop) a database.

• Create, alter, or drop a tablespace.

• Make a backup image of a database or a tablespace.

• Restore an existing database using a backup image.

• Restore a tablespace 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 tablespace.

• Update recovery log history files.

• Quiesce (restrict access to) a tablespace.

• Reorganize a table.

• Collect catalog statistics using the RUNSTATS utility.

Like SYSADM authority, SYSCTRL authority can only be assigned to a group. This assignment is made by storing the appropriate group name in the sysctrl_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/instance control authority available with DB2 UDB. Users that have been given this authority are allowed to perform maintenance and utility operations against any database that falls under an instance's control ”but not against the instance itself. Like SYSCTRL authority, SYSMAINT authority is designed to allow special users to maintain a database containing sensitive data that they most likely do not have access to. 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 and utility operations on. However, because a connection to a database must exist before some utility operations can be performed, users who are granted SYSMAINT authority for a particular instance automatically receive the privileges needed to connect to each database under that instance's control.

Users with SYSMAINT 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 tablespace.

• Restore an existing database using a backup image.

• Restore a tablespace 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 tablespace.

• Update recovery log history files.

• Quiesce (restrict access to) a tablespace.

• Reorganize a table.

• Collect catalog statistics using the RUNSTATS utility.

Like SYSADM and SYSCTRL authority, SYSMAINT 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.

Database Administrator authority

Database Administrator (DBADM) authority is the second highest level of administrative authority (below SYSADM) available with DB2 UDB. Users that have been given this authority are allowed to run most DB2 UDB utilities, issue database-specific DB2 commands, perform most SQL operations, and access data stored in any table in a database. However, they can only perform these functions on the database for which DBADM authority is held.

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

• Query the state of a tablespace.

• Update recovery log history files.

• Quiesce (restrict access to) a tablespace.

• Reorganize a table.

• Collect catalog statistics using the RUNSTATS utility.

On the other hand, only users with DBADM authority (or SYSADM authority) are allowed to:

• Read database log files.

• Create, activate, and drop event monitors.

• Give (grant) database privileges to groups and/or individual users.

• Take away (revoke) any privilege from any group and/or individual user, regardless of how it was granted.

Unlike SYSADM, SYSCTRL, and SYSMAINT authority, DBADM 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 DBADM authority for a particular database, they automatically receive CONNECT, CREATETAB, BINDADD, CREATE_NOT_FENCED, and IMPLICIT_SCHEMA database privileges for that database as well.

Any time a user with SYSADM or SYSCTRL authority creates a new database, they automatically receive 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., their SYSADM or SYSCTRL authority is revoked), they retain DBADM authority for that database until it is explicitly removed ( revoked ).

Load authority

Load (LOAD) authority is a special database level of administrative authority that has a much smaller scope that the 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 level provides a way for Database Administrators to allow more users to perform special database 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 tablespace using the LIST TABLESPACES command.

• Quiesce (restrict access to) a tablespace.

• Collect catalog statistics using the RUNSTATS utility.

Like DBADM 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 UDB, two distinct types of privileges exist: database privileges and object privileges.

Database privileges

Database privileges apply to a database as a whole, and for most users, they act as identification that gets verified at the second security checkpoint that must be cleared before access to data is provided. Figure 3-4 shows the different types of database privileges available.

Figure 3-4. Database privileges available with DB2 UDB.

As you can see in Figure 3-4, 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 quiesced (while access to it is restricted).

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 and/or by binding application bind files to the database).

CREATE_EXTERNAL_ROUTINE. Allows a user to create a procedure that can be invoked by applications and other database users and store it in the database.

CREATE_NOT_FENCED. Allows a user to create unfenced user-defined functions (UDFs) and store them in the database. (Unfenced UDFs are UDFs that are considered "safe" enough to be run in the DB2 Database Manager operating environment's process or address space. Unless a function is registered as being unfenced, the DB2 Database Manager insulates its internal resources in such a way that they cannot be utilized by that function.)

IMPLICIT_SCHEMA. Allows a user to implicitly create a new schema in the database by creating an object and assigning that object a schema name that is different from any of the schema names already existing in the database.

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 they can work with any object 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 schemas, tablespaces, tables, indexes, views, packages, routines, sequences, servers, and nicknames. Because the nature of each available database object 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 UDB.

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-5 shows the different types of schema privileges available.

Figure 3-5. Schema privileges available with DB2 UDB.

As you can see in Figure 3-5, 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 these privileges, along with the right to grant any combination of these privileges to other users and groups.

Tablespace privileges

Tablespace privileges control what users can and cannot do with a particular tablespace. (Tablespaces are used to control where data in a database physically resides.) Figure 3-6 shows the different types of tablespace privileges available.

Figure 3-6. Tablespace privileges available with DB2 UDB.

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

CONTROL. Provides a user with every tablespace privilege available, allows the user to remove (drop) the tablespace from the database, and gives the user the ability to grant to or revoke from other users and groups the USE tablespace privilege. (Only users who hold SYSADM or DBADM authority are allowed to grant and revoke CONTROL privileges for an object.)

USE. Allows a user to create tables within the tablespace. (This privilege is used to control which tablespaces a particular user is allowed to create tables in.)

The owner of a tablespace (usually the individual who created the tablespace) automatically receives CONTROL privilege and USE privilege for that tablespace. By default, whenever a new database is created, the USE privilege for tablespace USERSPACE1 is given to the group PUBLIC; however, this privilege can be revoked.

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

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-7 shows the different types of table privileges available.

Figure 3-7. Table privileges available with DB2 UDB.

As you can see in Figure 3-7, 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 to or revoke from other users and groups any available table privileges (except the CONTROL privilege).

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 and/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, 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, 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, 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 CONTROL privilege, along with all other available table privileges, for that table. 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.

Index privileges

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

Figure 3-8. Index privilege available with DB2 UDB.

As you can see in Figure 3-8, only one index privilege exists. That privilege is the CONTROL privilege, which 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 provide a user with the ability to grant to or revoke from other users and groups any available index privilege. That's because only users who hold SYSADM or DBADM 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.

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.) Figure 3-9 shows the different types of view privileges available.

Figure 3-9. View privileges available with DB2 UDB.

As you can see in Figure 3-9, 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 to or revoke from other users and groups any available view privileges (except the CONTROL privilege).

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 add data to the view.

UPDATE. 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 remove rows of data from the view.

In order to create a view, a user must hold appropriate privileges on each base table the view references. Once a view is created, the owner of that view (usually the individual who created the view) automatically receives all available view privileges ”with the exception of the CONTROL privilege ”for that view. A view owner will only receive CONTROL privilege for the view if they also hold CONTROL privilege for every base table the view references.

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.

Figure 3-10. Package privileges available with DB2 UDB.

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 to or revoke from other users and groups any available package privileges (except the CONTROL privilege).

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 they do not have the privileges that are needed to execute the SQL statements stored in the package. That is because any privileges needed to execute SQL statements in a package 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 CONTROL privilege, along with all other available package privileges, for that package. 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.

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 referenced objects.

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-11 shows the different types of routine privileges available.

Figure 3-11. Routine privileges available with DB2 UDB.

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

CONTROL. Provides a user with every routine privilege available, allows the user to remove (drop) the routine from the database, and gives the user the ability to grant to or revoke from other users and groups any available routine privileges (except the CONTROL privilege).

EXECUTE. 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 a DDL statement or when creating a constraint.

The owner of a routine (usually the individual who created the routine) automatically receives CONTROL and EXECUTE privileges for that routine. If the CONTROL privilege is later revoked from the owner, the EXECUTE privilege will be retained and must be explicitly revoked in a separate operation.

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. Applications can use sequences 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-12 shows the different types of sequence privileges available.

Figure 3-12. Sequence privileges available with DB2 UDB.

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

CONTROL. Provides a user with every sequence privilege available, allows the user to remove (drop) the sequence from the database, and gives the user the ability to grant to or revoke from other users and groups any available sequence privileges (except the CONTROL privilege).

USAGE. Allows a user to use the PREVVAL and NEXTVAL expressions that are associated with the sequence. (The PREVVAL expression returns the most recently generated value for the specified sequence; the NEXTVAL expression returns the next value for the specified sequence.)

The owner of a sequence (usually the individual who created the sequence) automatically receives CONTROL and USAGE privilege for that sequence. If the CONTROL privilege is later revoked from the owner, the USAGE privilege will be retained and must be explicitly revoked in a separate operation.

Server privileges

The server privilege controls 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-13 shows the only type of server privilege available.

Figure 3-13. Server privilege available with DB2 UDB.

As you can see in Figure 3-13, only one server privilege exists. That privilege is the PASSTHRU privilege, which 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 at 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-14 shows the different types of nickname privileges available.

Figure 3-14. Nickname privileges available with DB2 UDB.

As you can see in Figure 3-14, four 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 to or revoke from other users and groups any available nickname privileges (except the CONTROL privilege).

ALTER. 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 particular nickname column.

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 a nickname in a parent relationship. (This privilege can be granted for the entire nickname or limited to one or more columns within the nickname.)

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

Requirements for Granting and Revoking Authorities and Privileges

Not only do authorization levels and privileges control what a user can and cannot do, they also control what authorities and privileges a user can grant to and revoke from other users and groups. A list of the authorities and privileges a user who has been given a specific authority level or privilege is allowed to grant and revoke is shown in Table 3-1.

Table 3-1. Requirements for Granting/Revoking Authorities and Privileges

If a User Holds	They Can Grant	They Can Revoke
System Administrator (SYSADM) authority	System Control (SYSCTRL) authority	System Control (SYSCTRL) authority
	System Maintenance (SYSMAINT) authority	System Maintenance (SYSMAINT) authority
	Database Administrator (DBADM) authority	Database Administrator (DBADM) authority
	Load (LOAD) authority	Load (LOAD) authority
	Any database privilege, including CONTROL privilege	Any database privilege, including CONTROL privilege
	Any object privilege, including CONTROL privilege	Any object privilege, including CONTROL privilege
System Control (SYSCTRL) authority	The USE tablespace privilege	The USE tablespace privilege
System Maintenance (SYSMAINT) authority	No authorities or privileges	No authorities or privileges
Database Administrator (DBADM) authority	Any database privilege, including CONTROL privilege	Any database privilege, including CONTROL privilege
	Any object privilege, including CONTROL privilege	Any object privilege, including CONTROL privilege
Load (LOAD) authority	No authorities or privileges	No authorities or privileges
CONTROL privilege on an object (but no other authority)	All privileges available (with the exception of the CONTROL privilege) for the object the user holds CONTROL privilege on	All privileges available (with the exception of the CONTROL privilege) for the object the user holds CONTROL privilege on
A privilege on an object that was assigned with the WITH GRANT OPTION option specified	The same object privilege that was assigned with the WITH GRANT OPTION option specified	No authorities or privileges