Section 10.4. Administrative Authorities

10.4. Administrative Authorities

Once the user is successfully authenticated, DB2 checks to see if the user has the proper authority for the requested operations, such as performing database manager maintenance operations and managing database objects. Figure 10.12 shows the authorities supported in DB2 and Table 10.7 describes each of them.

Table 10.7. Descriptions of DB2 Administrative Authority Levels

DB2 Administrative Authority	Description
SYSADM	These users have the highest authority level and full privileges for managing the instance. They also have access to all data in the underlying databases.
SYSCTRL	These users have certain privileges in managing the instance, its databases, and database objects. They can create new databases, but do not have access to the data. For example, they cannot issue statements such as DELETE FROM employee or SELECT * FROM employee.
SYSMAINT	Similar to SYSCTRL, SYSMAINT users have certain privileges in managing the instance, its databases, and database objects. However, they cannot create new databases and do not have access to the data. For example, these users cannot issue statements such as DELETE FROM employee or SELECT * FROM employee.
SYSMON	These users can turn snapshot monitor switches on, collect snapshot data, and access other database system monitor data. No other task can be performed unless the required authority or privileges are granted to the same user by other means.
DBADM	Database-level authority that allows users to perform administrative tasks on the specified database. Note that they also have full data access to the database
LOAD	These users can only run the load utility against the specified database. Before the user can load data into a table, he or she must also have the privilege to INSERT and/or DELETE on the target table. (Database object privileges are discussed in more detail in the next section.)
CONNECT	Grants users access the database. Without the CONNECT authority, a user cannot connect to the database even though he or she is successfully authenticated by the security facility.
BINDADD	Allows users to create new packages in the database.
CREATETAB	Allows users to create new tables in the database.
CREATE_NOT_FENCED_ROUTINE	Allows users to create nonfenced routines such as user-defined functions and stored procedures. When a nonfenced routine is invoked, it executes in the database manager's process rather than in its own address space.
IMPLICIT_SCHEMA	Allows users to create a schema implicitly via database object creation. For example, if bob wants to create a table jeff.sales and the schema jeff does not already exist, bob needs to hold the IMPLICIT_SCHEMA authority for this database.
QUIESCE_CONNECT	Allows users to access the database while it is quiesced. When a database is quiesced, only users with SYSADM, DBADM, and QUIESCE_CONNECT authorities can connect to the database and perform administrative tasks.
CREATE_EXTERNAL_ROUTINE	Allows users to create routines written in external languages such as C, Java, and OLE.

Figure 10.12. DB2 administrative authority levels

To give you a better idea of what the system and DBADM authorities can and cannot do, Table 10.8 summarizes some common functions and the authorities required to perform them. For functions that are not listed here, refer to the DB2 manuals. The manuals clearly list the authorities and privileges needed to execute commands and SQL statements.

Table 10.8. Summary of DB2 Administrative Authorities

Function	SYSADM	SYSCTRL	SYSMAINT	SYSMON	DBADM
Update Database Manager Configuration parameters	YES	NO	NO	NO	NO
Grant/revoke DBADM authority	YES	NO	NO	NO	NO
Establish/change SYSCTRL authority	YES	NO	NO	NO	NO
Establish/change SYSMAINT authority	YES	NO	NO	NO	NO
Force users off the database	YES	YES	NO	NO	NO
Create/drop databases	YES	YES	NO	NO	NO
Restore to new database	YES	YES	NO	NO	NO
Update database configuration parameters	YES	YES	YES	NO	NO
Back up databases/table spaces	YES	YES	YES	NO	NO
Restore to existing database	YES	YES	YES	NO	NO
Perform roll forward recovery	YES	YES	YES	NO	NO
Start/stop instances	YES	YES	YES	NO	NO
Restore table spaces	YES	YES	YES	NO	NO
Run traces	YES	YES	YES	NO	NO
Obtain monitor snapshots	YES	YES	YES	YES	NO
Query table space states	YES	YES	YES	NO	YES
Prune log history files	YES	YES	YES	NO	YES
Quiesce table spaces	YES	YES	YES	NO	YES
Quiesce databases	YES	NO	NO	NO	YES
Quiesce instances	YES	YES	NO	NO	NO
Load tables	YES	NO	NO	NO	YES
Set/unset check pending status	YES	NO	NO	NO	YES
Create/drop event monitors	YES	NO	NO	NO	YES

10.4.1. Managing Administrative Authorities

Now that you understand the roles of different authorities in DB2, it's time to show you how to "give" a user or a group of users an authority. The verb give is used because a user receives the system and database authorities through different commands and statements.

Recall that SYSADM, SYSCTRL, SYSMAINT, and SYSMON are system authorities for an instance. You set these with the Database Manager Configuration parameters by assigning a user group defined in the operating system or security facility to the associated parameters. The following are the entries for the configuration parameters:

 SYSADM group name          (SYSADM_GROUP) = SYSCTRL group name         (SYSCTRL_GROUP) = SYSMAINT group name        (SYSMAINT_GROUP) = SYSMON group name          (SYSMON_GROUP) = 

On Windows, the parameters are set to NULL, which implies that members of the Windows Administrators group own all the system authorities. On Linux and UNIX systems, the primary group of the instance owner is the default value for all the SYS*_GROUP.

To set any of the system groups, you use the update dbm command. For example, if admgrp and maintgrp are valid groups, the following command configures the SYSADM_GROUP and SYSMAINT_GROUP:

 update dbm cfg using sysadm_group admgrp sysmaint_group maintgrp 

This command does not validate the existence of the group. It is your responsibility to enter a valid group name. To reset them to the default value of NULL, specify:

 update dbm cfg using sysadm_group NULL 

NOTE

Resetting DBM and DB configuration parameters to the default value, you must use NULL in uppercase. DB2 treats the lowercase null as an input value.

Since the SYS*_GROUP parameters are not configurable online, you need to stop and restart the instance for the changes to take effect.

You grant and revoke database authorities with the GRANT and REVOKE statements to a user or group of users. Figures 10.13 and 10.14 show the syntax of these statements, and Figure 10.15 illustrates how to use them.

Figure 10.13. GRANT statement for database authorities

           .-,-----------------------------.           V                               | >>-GRANT----+-BINDADD-------------------+-+--ON DATABASE-------->             +-CONNECT-------------------+             +-CREATETAB-----------------+             +-CREATE_EXTERNAL_ROUTINE---+             +-CREATE_NOT_FENCED_ROUTINE-+             +-IMPLICIT_SCHEMA-----------+             +-DBADM---------------------+             +-LOAD----------------------+             '-QUIESCE_CONNECT-----------'        .-,---------------------------------.        V                                   | >--TO----+-+-------+--authorization-name-+-+-------------------><          | +-USER--+                     |          | '-GROUP-'                     |          '-PUBLIC------------------------' 

Figure 10.14. REVOKE statement for database authorities

            .-,-----------------------------.            V                               | >>-REVOKE----+-BINDADD-------------------+-+--ON DATABASE------->              +-CONNECT-------------------+              +-CREATETAB-----------------+              +-CREATE_EXTERNAL_ROUTINE---+              +-CREATE_NOT_FENCED_ROUTINE-+              +-IMPLICIT_SCHEMA-----------+              +-DBADM---------------------+              +-LOAD----------------------+              '-QUIESCE_CONNECT-----------'          .-,---------------------------------.          V                                   |  .-BY ALL-. >--FROM----+-+-------+--authorization-name-+-+--+--------+-----><            | +-USER--+                     |            | '-GROUP-'                     |            '-PUBLIC------------------------' 

Figure 10.15. Examples of granting and revoking database authorities

 CONNECT TO sample; GRANT IMPLICIT_SCHEMA, CREATETAB ON DATABASE TO USER john; GRANT LOAD ON DATABASE TO GROUP loadgrp, USER john; GRANT BINDADD ON DATABASE TO PUBLIC; REVOKE LOAD ON DATABASE FROM GROUP loadgrp; REVOKE CREATETAB ON DATABASE FROM PUBLIC; 

You must first connect to the target database before you specify one or more authorities you want to grant or revoke. The keywords USER and GROUP are optional for both GRANT and REVOKE statements. However, on Linux and UNIX, if you have a user ID and group name defined with the same name, you must specify USER or GROUP explicitly; otherwise you will receive an error message.

Notice that the last example of Figure 10.15 uses the keyword PUBLIC is used. PUBLIC is not the name of a group defined in the operating system or in the external security facility; it is a special group to which everyone belongs. PUBLIC by default receives a few database authorities and/or database object privileges depending on the type of operations performed. Refer to the next section for more information about implicit privileges.

The REVOKE statement uses the BY ALL option as its default. This means that this command revokes each named authority (as well as privilegesthat will be discussed later in this chapter) from all named users and/or groups who were explicitly granted those authorities (and privileges). However, there is no cascade effect to also revoke authorities (and privileges) that were implicitly granted. Stay tuned for the implicit privileges discussion in the following section.