An individual process can be represented by a primary authorization identifier (ID), possibly one or more secondary IDs, and an SQL ID. The use of IDs is affected by the security and network systems and by the DB2 connections made.
DB2 controls access to objects by assigning privileges and authorities to either primary or secondary IDs. Ownership of an object carries with it a set of related privileges over the object. An ID can own an object it creates or can create an object to be owned by another ID. Creation and ownership have separate controls. We look at how privileges, authorities, and ownership work together to provide security for access to DB2 objects.
Executing a plan or a package exercises implicitly all the privileges that the owner needed when binding it. Hence, granting the privilege to execute can provide a finely detailed set of privileges and can eliminate the need to grant other privileges separately.
You can use RACF access control to supplement or replace the DB2 GRANT and REVOKE operations.
Explicit Privileges and Authorities
One way of controlling access within DB2 is by granting, not granting, or revoking explicit privileges and authorities to authorization IDs. A privilege allows a specific operation, sometimes on a specific object. An explicit privilege has a name and is held as the result of an SQL GRANT or REVOKE statement. The authorities group these privileges together. Both privileges and authorities are held by authorization IDs.
When installing or updating DB2, the entire system of control within DB2 can be disabled by setting USE PROTECTION to NO DB2. If protection in DB2 is disabled, any user who gains access can do anything, but no GRANT or REVOKE statements are allowed.
Every process that connects to or signs on to DB2 is represented by a set of one or more DB2 short identifiers called authorization IDs. Authorization IDs can be assigned to a process by default procedures or by user-written exit routines.
Primary Authorization ID
A primary authorization ID is assigned to every process. A process will have only one primary authorization ID, and it is normally used to uniquely identify a process.
Secondary Authorization ID
A secondary authorization ID is optional and can hold additional privileges. Secondary authorizations are often used for groups, such as RACF groups. A primary authorization ID can be associated with multiple secondary authorization IDs.
A new user who is added to an RACF group is visible the next time he or she logs on to TSO.
Current SQL ID
Either the primary ID or the secondary ID can be the current SQL ID at any given time. Either the primary or secondary ID is designated as the current SQL ID. You can change the value of the SQL ID during your session. If DB2EXPT is your primary or one of your secondary authorization IDs, you can make it your current SQL ID by issuing the SQL statement:
SET CURRENT SQLID ='DB2EXPT';
An ID with SYSADM authority can set the current SQL ID to any string of up to 8 bytes, whether or not it is an authorization ID or associated with the process that is running.
Several explicit privileges can be granted to a primary or a secondary authorization ID, allowing that ID the privilege of performing a particular task. When some privileges are granted, they also provide an inherited authority; for example, if CREATEDBA is granted to an ID, that ID will become DBADM (database administrator authority) over the database it creates.
The privileges are grouped into several categories:
Table 3-1 lists the available privileges that can be granted to a primary or secondary authorization ID and the type of use associated with the privilege.
Table 3-1. Privilege Categories and Functions
ALTER TABLE: to change the table definition
DELETE: to delete rows
CREATE INDEX: to create an index on the table
INSERT: to insert rows
ALTER or CREATE TABLE: to add or remove a referential constraint referring to the named table or to a list of columns in the table
SELECT: to retrieve data from the table
CREATE TRIGGER: to define a trigger on a table
SELECT, UPDATE, DELETE, and UPDATE
BIND, REBIND, and FREE PLAN: to bind or free the plan
RUN: to use the plan when running the application
The BIND, REBIND, and FREE PACKAGE subcommands and the DROP PACKAGE statement: to bind or free the package and, depending on the installation option, BIND NEW PACKAGE: to bind a new version of a package
The COPY option of BIND PACKAGE: to copy a package
Inclusion of the package in the PKLIST option of BIND PLAN
All package privileges
Naming the collection in the BIND PACKAGE subcommand
The CREATE TABLE statement: to create tables in the database
The CREATE TABLESPACE statement: to create table spaces in the database
The DISPLAY DATABASE command: to display the database status
The DROP and ALTER DATABASE statements: to drop or alter the database
The QUIESCE, COPY, and MERGECOPY utilities: to prepare for, make, and merge copies of table spaces in the database; the MODIFY utility: to remove records of copies
The LOAD utility: to load tables in the database
The RECOVER, REBUILD INDEX, and REPORT utilities: to recover objects in the database and report their recovery status
The REORG utility: to reorganize objects in the database
The REPAIR and DIAGNOSE utilities, except REPAIR DBD and DIAGNOSE WAIT: to generate diagnostic information about, and repair data in, objects in the database
The START DATABASE command: to start the database
The RUNSTATS and CHECK utilities: to gather statistics and check indexes and referential constraints for objects in the database
The STOP DATABASE command: to stop the database
The ARCHIVE LOG command: to archive the current active log; the DISPLAY ARCHIVE command: to give information about input archive logs; the SET LOG command: to modify the checkpoint frequency specified during installation; and the SET ARCHIVE command: to control allocation and deallocation of tape units for archive processing.
The BIND subcommand with the ADD option: to create new plans and packages
The BIND, REBIND, and FREE subcommands, and the DROP PACKAGE statement: on behalf of the grantor, to bind, rebind, or free a plan or a package or to copy a package. The BINDAGENT provides separation of duties, with relatively weak security. A bind agent with the EXECUTE privilege might be able to gain all the authority of the grantor of BINDAGENT.
The RECOVER BSDS command: to recover the bootstrap data set
The CREATE ALIAS statement: to create an alias for a table or a view name
The CREATE DATABASE statement: to create a database and have DBADM authority over it
The CREATE DATABASE statement: to create a database and have DBCTRL authority over it
The CREATE STOGROUP statement: to create a storage group
The CREATE GLOBAL TEMPORARY TABLE statement: to define a created temporary table
The DISPLAY ARCHIVE, DISPLAY BUFFERPOOL, DISPLAY DATABASE, DISPLAY LOCATION, DISPLAY LOG, DISPLAY THREAD, and DISPLAY TRACE commands: to display system information
Receive trace data that is not potentially sensitive
Receive all trace data
The RECOVER INDOUBT command: to recover threads
The STOP DB2 command: to stop DB2
The STOSPACE utility: to obtain data about space usage
The START TRACE, STOP TRACE, and MODIFY TRACE commands: to control tracing
USE OF BUFFERPOOL
A buffer pool
USE OF STOGROUP
A storage group
USE OF TABLESPACE
A table space
Create distinct types, user-defined functions, triggers, and stored procedures in the designated schemas
Alter user-defined functions or stored procedures; specify a comment for distinct types, user-defined functions, triggers, and stored procedures in the designated schemas
Drop distinct types, user-defined functions, triggers, and stored procedures in the designated schemas
USAGE ON DISTINCT TYPE
A distinct type
EXECUTE ON FUNCTION
A user-defined function
EXECUTE ON PROCEDURE
A stored procedure
Additional privileges are available for statements, commands, and utility jobs.
Granting and Revoking Privileges
The privileges in Table 3-1 must be granted to an authorization ID. The GRANT and REVOKE statements are part of the DCL.
In order to grant DB2EXPT the ability to select data from a particular table, we must execute the following SQL statement:
GRANT SELECT ON DB2CERT.TEST_TAKEN TO DB2EXPT
To grant DB2EXPT the ability to bind packages to the CERTCL collection, we would execute the following statement:
GRANT BIND ON PACKAGE CERTCL.* TO DB2EXPT
To grant DB2EXPT the ability to bind new packages to the CERTCL collection, we would execute the following statements:
GRANT CREATE IN COLLECTION CERTCL TO DB2EXPT GRANT BINDADD TO DB2EXPT
To grant everyone the ability to select, update, insert, or delete data from a particular table, we could execute the following SQL statement:
GRANT ALL ON DB2CERT.TEST_TAKEN TO PUBLIC
The keyword PUBLIC allows any user to have the privilege granted.
To take away everyone's priviledge to delete data from a particular table, we must execute the following SQL statement:
REVOKE DELETE ON DB2CERT.TEST_TAKEN FROM PUBLIC
Revoking a privilege from a user can also cause that privilege to be revoked from other users. This is called a cascade revoke.
Related and Inherited Privileges
DB2 has defined sets of related privileges that are identified by administrative authorities. This grouping makes it easier to administrate authority; instead of having to grant several individual privileges to an ID, one can simple grant the administrative authority, which includes all applicable privileges.
Some privileges are inherited with object ownership.
An administrative authority is a set of privileges that often cover a related set of objects (see Table 3-2). Authorities often include privileges that are not explicit, have no name, and cannot be specifically granted. An example is the ability to terminate any utility job, which is included in the SYSOPR authority.
Table 3-2. Administrative Authorities
Assigned during DB2 installation. This ID has all the privileges of SYSADM, as well as the following.
Authority is not recorded in the DB2 catalog, which need not be available to check installation SYSADM authority. (The authority outside the catalog is crucial: If the catalog table space SYSDBAUT is stopped, for example, DB2 cannot check the authority to start it again. Only an installation SYSADM can start it.)
This can be revoked only by changing the module that contains the subsystem initialization parameterstypically, DSNZPARM.
Those IDs can also run the CATMAINT utility, access DB2 when the subsystem is started with ACCESS(MAINT), start databases DSNDB01 and DSNDB06 when they are stopped or in restricted status, run the DIAGNOSE utility with the WAIT statement, and start and stop the database containing the application registration table (ART) and object registration table (ORT).
All privileges of all the authorities
Almost complete control of the DB2 subsystem but cannot access user data directly unless granted the privilege to do so. Designed for administering a system containing sensitive data, SYSCTRL can
Act as installation SYSOPRwhen the catalog is availableor DBCTRL over any database
Run any allowable utility on any database
Issue a COMMENT ON, LABEL ON, or LOCK TABLE statement for any table
Create a view for itself or others on any catalog table
Create tables and aliases for itself or others
Bind a new plan or package, naming any ID as the owner
Without additional privileges, it cannot
Execute DML (Data Manipulation Language) statements on user tables or views
Run plans or packages
Set the current SQL ID to a value that is not one of its primary or secondary IDs
Start or stop the database containing the ART and ORT
Act fully as SYSADM or as DBADM over any database
Access DB2 when the subsystem is started with ACCESS(MAINT)
Note: SYSCTRL authority provides relatively weak security.
Privileges on all tables:
Privileges on catalog tables:
Privileges on all plans: BIND
Privileges on all packages:
Privileges on all collections:
Privileges on all schemas:
Use privileges on
Includes SYSCTRL, as well as access to all data. SYSADM can
Use all the privileges of DBADM over any database
Use EXECUTE and BIND on any plan or package and COPY on any package
Use privileges over views that are owned by others
Set the current SQL ID to any valid value, whether it is currently a primary or a secondary authorization ID
Create and drop synonyms and views for others on any table
Use any valid value for OWNER in BIND or REBIND
Drop database DSNDB07
Grant any of the preceding privileges to others
Drop or alter any DB2 object, except system databases, issue a COMMENT ON or LABEL ON statement for any table or view, and terminate any utility job, but SYSADM cannot specifically grant those privileges
All privileges held by SYSCTRL and DBADM
Plan privileges: EXECUTE
Routine privileges: EXECUTE
Distinct type privileges: USAGE
Issue most DB2 commands except ARCHIVE LOG, START DATABASE, STOP DATABASE, and RECOVER BSDS
Ability to terminate any utility job
Ability to execute the DSN1SDMP utility
Privileges on routines:
Assigned during DB2 installation. Privileges in addition to SYSOPR:
Authority is not recorded in the DB2 catalog, which need not be available to check installation SYSOPR authority.
The authority can be revoked only by changing the module that contains the subsystem initialization parameterstypically, DSNZPARM.
Access DB2 when the subsystem is started with ACCESS(MAINT).
Run all allowable utilities on the directory and catalog databases (DSNDB01 and DSNDB06).
Run the REPAIR utility with the DBD statement.
Start and stop the database containing the ART and ORT.
Issue dynamic SQL statements that are not controlled by the DB2 governor. Issue a START DATABASE command to recover objects that have LPL (Logical Page List) entries or group buffer pool recovery-pending status. These IDs cannot change the access mode.
All privileges held by SYSOPR
System privileges: ARCHIVE STARTDB (cannot change access mode)
Has all package privileges on all packages in specific collections or on all collections, as well as the CREATE IN privilege on those collections. If the installation option BIND NEW PACKAGE is BIND, PACKADM also has the privilege to add new packages or new versions of existing packages.
Privileges on a collection: CREATE IN
Privileges on all packages in the collection:
Granted for a specific database, in which the ID can create certain objects, run certain utilities, and issue certain commands. Can use the TERM UTILITY command to terminate all utilities except DIAGNOSE, REPORT, and STOSPACE on the database.
Privileges on one database:
In addition to DBMAINT privileges, it can also run utilities that can change the data.
All privileges held by DBMAINT on a database
Privileges on one database:
In addition to the privileges held by DBCTRL over a specific database, DBADM has privileges to access any of its tables through SQL statements. Can also drop and alter any table space, table, or index in the database and issue a COMMENT ON, LABEL ON, or LOCK TABLE statement for any table. If the value of field DBADM CREATE VIEW on installation panel DSNTIPP was set to YES during DB2 installation, a user with DBADM authority can create a view for another user ID on any table or combination of tables and views in a database.
All privileges held by DBCTRL on a database
Privileges on tables and views in one database:
[*] DBCTRL authority is a good way to give a DBA all the necessary privileges needed, without the ability to access the data itself.
Granting and Revoking Authorities
The authorities in Table 3-2 must be granted to an authorization ID. As with privileges, this can be done via the GRANT and REVOKE statements.
In order to grant DB2EXPT DBADM authority on the DB2CERT database, the following statement must be issued:
GRANT DBADM ON DATABASE DB2CERT TO DB2EXPT
In order to remove PACKADM authority from DB2EXPT, the following statement must be issued:
REVOKE PACKADM FROM DB2EXPT
WITH GRANT Option
If an authority is granted with the WITH GRANT option, the holder can grant the privileges contained in that authority to others. In order to grant DB2EXPT DBADM authority on the DB2CERT database and allow DB2EXPT to give this authority to others, the following statement must be issued:
GRANT DBADM ON DATABASE DB2CERT TO DB2EXPT WITH GRANT OPTION
If the DBADM authority is ever revoked from DB2EXPT, any ID that has been granted DBADM from this ID will also be automatically revoked. This is the primary reason why administrative authorities should be given to a role or group, rather than an individual authority.
Implicit privileges are included with ownership of an object. When you create DB2 objects, except for plans and packages, which are bound by issuing SQL CREATE statements in which you name the object, you establish ownership. The owner implicitly holds certain privileges over it.
The privileges inherent in the ownership of an object cannot be revoked.
The qualifier (or schema name) of tables, views, indexes, and aliases is the same as the owner. Synonyms are always unqualified. The schema name of stored procedures, user-defined functions, distinct types, triggers, and sequences may be different from the owner. If an object name is unqualified, how ownership of the object is established depends on the type of object. Ownership of tables, views, indexes, aliases, and synonyms with unqualified names is established differently from ownership of user-defined functions, stored procedures, distinct types, sequences, and triggers with unqualified names. This section describes how ownership is established for each group of objects.
If the name of a table, view, index, alias, or synonym is unqualified, you establish the object's ownership in one of two ways. If the CREATE statement was issued dynamically, via SPUFI or QMF, the owner of the created object is the current SQL ID of the issuer. That ID must have the privileges needed to create the object.
If the CREATE statement was issued statically, via execution of a plan or package that contains it, the ownership of the created object depends on the option used for the bind operation. The plan or package can be bound with the OWNER option.
With the QUALIFIER option only, the qualifier is the owner of the object. The QUALIFIER option allows the binder to name a qualifier to use for all unqualified names of tables, views, indexes, aliases, or synonyms that appear in the plan or package.
With the OWNER option only, the owner is the owner of the object.
If neither option is specified, the binder of the plan or package is implicitly the object owner.
The plan or package owner must have all required privileges on the objects designated by the qualified names.
Explicitly establishing the ownership of a user-defined function, stored procedure, distinct type, sequence, or trigger can be done in the following ways.
If the CREATE statement is issued dynamically, the owner of the created object is the current SQL ID of the issuer. That ID must have the privileges needed to create the object.
If the CREATE statement is issued staticallyby running a plan or package that contains itthe owner of the object is the plan or package owner. The OWNER bind option can be used to explicitly name the object owner. If the OWNER bind option is used, the binder of the package or plan is implicitly the object owner.
If an object name is qualified during creation, establishing ownership of the object depends, again, on the type of object. For tables, views, indexes, aliases, or synonyms created with a qualified name, the qualifier becomes the owner of the object, subject to these restrictions for specifying the qualifier.
If you issue the CREATE statement dynamically and have no administrative authority, the qualifier must be your primary ID or one of your secondary IDs.
If the CREATE statement is issued statically and if the owner of the plan or package that contains the statement has no administrative authority, the qualifier can be only the owner. However, if the owner has at least DBCTRL authority, the plan or package can use any qualifier for a table or an index. If the owner has SYSADM or SYSCTRL authority, the plan or package can also use any qualifier for a view or an alias.
If your current SQL ID has at least DBCTRL authority, you can use any qualifier for a table, index, view, or alias. If the current SQL ID has at least DBCTRL authority, the qualifier ID does not need any privileges. Otherwise, the SQL ID must have any additional privileges needed to create the object. If the current SQL ID does not have at least DBCTRL authority, the qualifier ID must hold all the necessary privileges.
If you create a distinct type, user-defined function, stored procedure, trigger, or sequence with a qualified name, the qualifier will be the schema name, which identifies the schema to which the object belongs. You can think of all objects that are qualified by the same schema name as a group of related objects. But this qualifier does not identify the owner of the object. You establish ownership of a distinct type, user-defined function, stored procedure, trigger, or sequence in these ways.
If the CREATE statement is issued dynamically, the owner of the created object is your current SQL ID. That ID must have the privileges needed to create the object.
If the CREATE statement is issued staticallyby running a plan or package that contains itthe owner of the object is the plan or package owner. The OWNER bind option can be used to explicitly name the object owner. If the OWNER bind option is not used, the binder of the package or plan is the implicit object owner.
If the schema name is omitted during the creation of one of these objects, DB2 will determine the schema name using the following methods.
If the CREATE statement is issued dynamically, the schema name of the created object is your current SQL ID.
If the CREATE statement is issued statically, by running a plan or package that contains it, the schema name of the object is the plan or package qualifier. The QUALIFIER bind option can be used to explicitly name the object qualifier. If the QUALIFIER bind option is not specified, the owner is used.
For more information on schemas, refer to Chapter 15.
Privileges of Ownership by Object
Table 3-3 shows the privileges inherited with ownership of an object.
Table 3-3. Inherited Privileges
Implicit Privileges of Ownership
ALTER or DROP the table or any indexes on it
Use LOCK TABLE, COMMENT ON, or LABEL on it
CREATE an index or view on the table
SELECT or UPDATE any row or column
INSERT or DELETE any row
Use of the LOAD utility for the table
Define referential constraints on any table or set of columns
CREATE a trigger on the table
ALTER or DROP the index
DROP, COMMENT ON, or LABEL the view or SELECT any row or column
UPDATE any row or column
INSERT or DELETE any row, if the view is not read-only
USE or DROP the synonym
BIND, REBIND, FREE, COPY, EXECUTE, or DROP the package
BIND, REBIND, FREE, or EXECUTE the plan
DROP the alias
USE or DROP a distinct type
EXECUTE, ALTER, DROP, START, STOP, or DISPLAY a user-defined function
EXECUTE, ALTER, DROP, START, STOP, or DISPLAY a stored procedure
Use (USAGE), ALTER, COMMENT ON, or DROP
Plan or Package Ownership
An application plan or a package can take many actions, all of them requiring one or more privileges, on many tables. The owner of the plan or package must hold every required privilege. Another ID can execute the plan or package if it has only the EXECUTE privilege. In that way, another ID can exercise all the privileges used in validating the plan or package but only within the restrictions imposed by the SQL statements in the original program.
The executing ID can use some of the owner's privileges, within limits. If the privileges are revoked from the owner, the plan or the package is invalidated. It must be rebound, and the new owner must have the required privileges.
If an attempt to execute the plan or package occurs prior to the rebind (and automatic rebinds are permitted), DB2 will attempt to rebind the plan/package; if the owner does not have the privileges at that time, the plan/package will be marked inoperative.
The BIND and REBIND subcommands create or change an application plan or a package. On either subcommand, use the OWNER option to name the owner of the resulting plan or package.
If the OWNER option is used:
Any user can name the owner using their primary or any of their secondary IDs.
An ID with the BINDAGENT privilege can name the grantor of that privilege.
An ID with SYSCTRL or SYSADM authority can name any authorization ID on a BIND command but not on a REBIND command.
If the OWNER option is omitted:
A plan or a package can contain SQL statements that use unqualified table view and alias names. For static SQL, the default qualifier for those names is the owner of the plan or package. However, you can use the QUALIFIER option of the BIND command to specify a different qualifier.
For plans or packages that contain static SQL, using the BINDAGENT privilege and the OWNER and QUALIFIER options gives you considerable flexibility in performing bind operations. For plans or packages that contain dynamic SQL, the DYNAMICRULES behavior determines how DB2 qualifies unqualified object names.
For DML statements with unqualified distinct types, user-defined functions, stored procedures, and trigger names in dynamic SQL statements, DB2 finds the schema name to use as the qualifier by searching schema names in the CURRENT PATH special register. For static statements, the PATH bind option determines the path that DB2 searches to resolve unqualified distinct types, user-defined functions, stored procedures, sequences, and trigger names.
However, an exception exists for ALTER, CREATE, DROP, COMMENT ON, GRANT, and REVOKE statements. For static SQL, specify the qualifier for these statements in the QUALIFIER bind option. For dynamic SQL, the qualifier for these statements is the authorization ID of the CURRENT SQLID special register.
Plan Execution Authorization
The owner of the plan or package must have authorization to execute all static SQL statements embedded in the plan or package. These authorizations do not need to be in place when the plan or package is bound; nor do the objects that are referred to need to exist at that time.
A bind operation always checks whether a local object exists and whether the owner has the required privileges on it. Any failure results in a message. To choose whether the failure prevents the bind operation from completing, use the VALIDATE option of BIND PLAN and BIND PACKAGE and also the SQLERROR option of BIND PACKAGE. If you let the operation complete, the checks are made again at runtime. The corresponding checks for remote objects are always made at runtime. Authorization to execute dynamic SQL statements is also checked at runtime. The authorization ID used for this check is determined by the DYNAMICRULES bind option. In order to include a package in a plan's PKLIST, the owner will need to be given execute authority on the package.
For more information on plans and packages, refer to Chapter 11.
Catalog Table Information for Object Access
Table 3-4 contains information about privileges and authorities being held on various objects in the DB2 subsystem.
Table 3-4. DB2 Privileges and Authorities
DB2 Catalog Table
Updating column authority
Buffer pool, storage group, collection, table space, and distinct type usage privileges
User-defined functions and stored procedure privileges
Tables and views privileges
Views can be used to control what data a user can see: certain columns, certain rows, or even a combination. Using views is another way to further restrict access to data. A view is created that allows users to see only certain columns or rows; users are allowed access to the view, not to the base table.
The following example allows the user of the view to see test scores for candidates who took the test at test center 001.
CREATE VIEW TEST01 AS SELECT SCORES FROM DB2CERT.TEST_TAKEN WHERE TCID = 001