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 it can create an object to be owned by another ID. There are separate controls for creation and ownership. We will take a look at how privileges, authorities, and ownership work together to provide security for access to DB2 objects. Executing a plan or 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. NOTE | You can use resource access control facility (RACF) access control to supplement or replace the DB2 GRANT and REVOKE. | 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. These will be covered in detail in this chapter. NOTE | 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, then any user that gains access can do anything, but no GRANT or REVOKE statements are allowed. | Authorization IDs 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. There will be only one primary authorization ID for a process, and it is the ID normally used to uniquely identify a process. Secondary Authorization ID A secondary authorization ID is optional, and this ID can hold additional privileges. Secondary authorizations are often used for groups, such as RACF groups, and a primary authorization ID can be associated with multiple secondary authorization IDs. Current SQL ID Either the primary ID or the secondary ID can be the current SQL ID at any given time. Furthermore, one ID (either primary or secondary) 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 (covered in this section) 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. Explicit Privileges There are several explicit privileges that can be granted to a primary or secondary authorization ID, allowing that ID the privilege to perform a particular task. When some privileges are granted, they also provide an inherited authority (e.g., if CREATEDBA is granted to an ID, then that ID will become DBADM over the database it creates). The privileges are grouped into several categories. -
Plans -
Packages -
Collections -
Databases -
Systems -
Usage -
Schemas -
Distinct types -
Routines Table 2-9 provides a list of the available privileges that can be granted to a primary or secondary authorization ID and the type of usage associated with the privilege. Table 2-9. Privileges for Primary or Secondary Authorization ID Privilege | Provides the following usage | Table | ALTER | ALTER TABLE, to change the table definition. | DELETE | DELETE, to delete rows. | INDEX | CREATE INDEX, to create an index on the table. | INSERT | INSERT, to insert rows. | REFERENCES | 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 | SELECT, to retrieve data from the table. | TRIGGER | CREATE TRIGGER, to define a trigger on a table. | GRANT ALL | SQL statements of all table privileges. | Plan | BIND | BIND, REBIND, and FREE PLAN, to bind or free the plan. | EXECUTE | RUN, to use the plan when running the application. | Package | BIND | 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. | COPY | The COPY option of BIND PACKAGE, to copy a package. | EXECUTE | Inclusion of the package in the PKLIST option of BIND PLAN. | GRANT ALL | All package privileges. | Collection | CREATE IN | Naming the collection in the BIND PACKAGE subcommand. | Database | CREATETAB | The CREATE TABLE statement, to create tables in the database. | CREATETS | The CREATE TABLESPACE statement, to create tablespaces in the database. | DISPLAYDB | The DISPLAY DATABASE command, to display the database status. | DROP | The DROP and ALTER DATABASE statements, to drop or alter the database. | IMAGCOPY | The QUIESCE, COPY, and MERGECOPY utilities, to prepare for, make, and merge copies of tablespaces in the database; the MODIFY utility, to remove records of copies. | LOAD | The LOAD utility, to load tables in the database. | RECOVERDB | The RECOVER, REBUILD INDEX, and REPORT utilities, to recover objects in the database and report their recovery status. | REORG | The REORG utility, to reorganize objects in the database. | REPAIR | The REPAIR and DIAGNOSE utilities (except REPAIR DBD and DIAGNOSE WAIT), to generate diagnostic information about and repair data in objects in the database. | STARTDB | The START DATABASE command, to start the database. | STATS | The RUNSTATS and CHECK utilities, to gather statistics and check indexes and referential constraints for objects in the database. | STOPDB | The STOP DATABASE command, to stop the database. | Subsystem | ARCHIVE | 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. | BINDADD | The BIND subcommand with the ADD option, to create new plans and packages. | BINDAGENT | The BIND, REBIND, and FREE subcommands, and the DROP PACKAGE statement, to bind, rebind, or free a plan or package, or copy a package on behalf of the grantor. The BINDAGENT privilege is intended for separation of function, not for added security. A bind agent with the EXECUTE privilege might be able to gain all the authority of the grantor of BINDAGENT. | BSDS | The RECOVER BSDS command, to recover the bootstrap dataset. | CREATEALIAS | The CREATE ALIAS statement, to create an alias for a table or view name. | CREATEDBA | The CREATE DATABASE statement, to create a database and have DBADM authority over it. | CREATEDBC | The CREATE DATABASE statement, to create a database and have DBCTRL authority over it. | CREATEESG | The CREATE STOGROUP statement, to create a storage group. | CREATETMTAB | The CREATE GLOBAL TEMPORARY TABLE statement, to define a created temporary table. | DISPLAY | The DISPLAY ARCHIVE, DISPLAY BUFFERPOOL, DISPLAY DATABASE, DISPLAY LOCATION, DISPLAY LOG, DISPLAY THREAD, and DISPLAY TRACE commands, to display system information. | MONITOR1 | Receive trace data that is not potentially sensitive. | MONITOR2 | Receive all trace data. | RECOVER | The RECOVER INDOUBT command, to recover threads. | STOPALL | The STOP DB2 command, to stop DB2. | STOSPACE | The STOSPACE utility, to obtain data about space usage. | TRACE | The START TRACE, STOP TRACE, and MODIFY TRACE commands, to control tracing. | Use | USE OF BUFFERPOOL | A buffer pool. | USE OF STOGROUP | A storage group. | USE OF TABLESPACE | A tablespace. | Schema | CREATEIN | Create distinct types, user-defined functions, triggers, and stored procedures in the designated schemas. | ALTERIN | Alter user-defined functions or stored procedures, or specify a comment for distinct types, user-defined functions, triggers, and stored procedures in the designated schemas. | DROPIN | Drop distinct types, user-defined functions, triggers, and stored procedures in the designated schemas. | Distinct Type | USAGE ON DISTINCT TYPE | A distinct type. | Routine | EXECUTE ON FUNCTION | A user-defined function. | EXECUTE ON PROCEDURE | A stored procedure. | NOTE | There are also additional privileges for statements, commands, and utility jobs. | GRANTing and REVOKEing Privileges The privileges in Table 2-9 must be GRANTed to an authorization ID. The GRANT and REVOKE statements are part of the SQL language known as DCL (Data Control Language). We will take a look at a couple of examples of GRANTing and REVOKEing privileges. 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 everyone the ability to select, update, insert, or delete data from a particular table, we must execute the following SQL statement: NOTE | The keyword PUBLIC allows for any user to have the privilege granted. | To take away everyone's delete privilege 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 revok e. Related and Inherited Privileges DB2 has defined sets of related privileges that are identified by administrative authorities. We will take a look at these authorities in the next section. This grouping makes it easier to administrate authority, because instead of having to grant several individual privileges to an ID, one can simple grant the administrative authoritywhich includes all applicable privileges. There are also privileges that are inherited with object ownership. Authorities An administrative authority is a set of privileges, often covering a related set of objects (see Table 2-10). Authorities often include privileges that are not explicit, have no name, and cannot be specifically granted; for example, the ability to terminate any utility job, which is included in the SYSOPR authority. Table 2-10. Authorities Authorization | Description of Capabilities | Privileges | Installation SYSADM | Assigned during DB2 installation. This ID has all the privileges of SYSADM plus -
Authority is not recorded in the DB2 catalog. The catalog need not be available to check installation SYSADM authority. (The authority outside the catalog is crucial: If the catalog tablespace SYSDBAUT is stopped , for example, DB2 cannot check the authority to start it again. Only an installation SYSADM can start it.) -
No ID can revoke this authority; it can be removed only by changing the module that contains the subsystem initialization parameters (typically 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 those are stopped or in restricted status. -
Run the DIAGNOSE utility with the WAIT statement. -
Start and stop the database containing the application registration table (ART) and object registration table (ORT). | All privileges of all the authorities. | SYSCTRL | 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 SYSOPR (when the catalog is available) or 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 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 that SYSCTRL authority is intended for separation of function, not for added security. | System privileges: -
BINDADD -
BINDAGENT -
CREATEDBC -
CREATESG -
BSDS -
CREATETMTAB -
CREATEALIAS -
CREATEDBA -
MONITOR1 -
MONITOR2 -
STOSPACE Privileges on all tables: -
ALTER INDEX -
REFERENCES -
TRIGGER Privileges on catalog tables: -
SELECT -
UPDATE -
INSERT -
DELETE Privileges on all plans: Privileges on all packages: Privileges on all collections: Privileges on all schemas: Use privileges on -
BUFFERPOOL -
TABLESPACE -
STOGROUP | SYSADM | Includes SYSCTRL plus 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 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 privileges listed above to others. Holders of SYSADM authority can also 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: Package privileges: FREERoutine privileges: Distinct type privileges: | SYSOPR | -
Ability to 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: -
DISPLAY -
STOPALL -
RECOVER -
TRACE Privileges on routines: | Installation SYSOPR | Assigned during DB2 installation. Privileges in addition to SYSOPR: -
Authority is not recorded in the DB2 catalog. The catalog need not be available to check installation SYSOPR authority. -
No ID can revoke the authority; it can be removed only by changing the module that contains the subsystem initialization parameters (typically 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. | All privileges held by SYSOPR. System privileges: | | -
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 logical page list (LPL) entries or group buffer pool recovery-pending status. These IDs cannot change the access mode. | | PACKADM | Has all package privileges on all packages in specific collections or on all collections, plus 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: Privileges on all packages in the collection: | DBMAINT | 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: -
CREATETAB -
STARTDB -
CREATETS -
STATS -
DISPLAYDB -
STOPDB -
IMAGCOPY | DBCTRL | In addition to DBMAINT privileges, it can run utilities that can change the data. | All privileges held by DBMAINT on a database. Privileges on one database: -
DROP LOAD -
RECOVERDB REORG -
REPAIR | DBADM | 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 tablespace, 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: -
ALTER -
INSERT -
DELETE -
SELECT -
INDEX -
UPDATE -
REFERENCES -
TRIGGER | GRANTing and REVOKEing Authorities The authorities in Table 2-10 must be GRANTed to an authorization ID. Just as with privileges, we can do this via the GRANT and REVOKE statements. We will take a look at a couple of examples of GRANTing and REVOKEing authorities. In order to grant DB2EXPT DBADM authority on a the DB2CERT database, the following statement must be issued: GRANT DBADM ON 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 a the DB2CERT database and allow DB2EXPT to give this authority to others, the following statement must be issued: GRANT DBADM ON DB2CERT TO DB2EXPT WITH GRANT OPTION NOTE | If the DBADM authority is ever revoked from DB2EXPT, then any ID that has been granted DBADM from this ID will also be automatically revoked. | Ownership There are implicit privileges included with ownership of an object. When you create DB2 objects (except for plans and packages) by issuing SQL CREATE statements in which you name the object, you establish ownership. The owner implicitly holds certain privileges over it. NOTE | The privileges inherent in the ownership of an object cannot be revoked. | Unqualified Objects 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 than ownership of user-defined functions, stored procedures, distinct types, 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 these ways: -
If the CREATE statement was issued dynamicallyvia SPUFI or the Query Management Facility (QMF)the owner of the created object is the current SQL ID of the issuer. That ID must have the privileges that are 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 QUALIFIER option, the OWNER option, or both. -
- 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. -
- With both the QUALIFIER option and the OWNER option, the QUALIFIER is the owner of the object. -
- If neither option is specified, the binder of the plan or package is implicitly the object owner. NOTE | The plan or package owner must have all required privileges on the objects designated by the qualified names. | Explicitly establish the ownership a user-defined function, stored procedure, distinct type, or trigger 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 that are needed to create the object. -
If the CREATE statement is issued statically, by running a plan or package that contains it, the 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 implicitly the object owner. The following section describes how the implicit qualifier is determined for a user-defined function, stored procedure, distinct type, or trigger if they are unqualified. Qualified Objects If an object name is qualified, how ownership of the object is established 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 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, and if the owner has SYSADM or SYSCTRL authority, it can also use any qualifier for a view or alias. NOTE | If your current SQL ID has at least DBCTRL authority, you can use any qualifier for a table or index, and if it has SYSADM or SYSCTRL authority, you can also use any qualifier for a 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 that are 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. | Schema Name If you create a distinct type, user-defined function, stored procedure, or trigger with a qualified name, the qualifier will be the schema nam e. It 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. Unlike other objects, however, this qualifier does not identify the owner of the object. You establish ownership of a distinct type, user-defined function, stored procedure, or trigger 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 that are needed to create the object. -
If the CREATE statement is issued statically, by running a plan or package that contains it, the 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. Privileges of Ownership by Object Table 2-11 shows the privileges that are inherited with ownership of an object. Table 2-11. Inherited Ownership Privileges Object Type | Implicit Privileges of Ownership | Storage group | | Database | DBCTRL or DBADM authority over the database, depending on the privilege (CREATEDBC or CREATEDBA) that is used to create it | Tablespace | | Table | -
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 the LOAD utility for the table -
Define referential constraints on any table or set of columns -
CREATE a trigger on the table | Index | ALTER or DROP the index | View | -
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) | Synonym | USE or DROP the synonym | Package | BIND, REBIND, FREE, COPY, DROP, EXECUTE, or DROP the package | Plan | BIND, REBIND, FREE, or EXECUTE the plan | Alias | DROP the alias | Distinct type | USE or DROP a distinct type | User-defined functions | EXECUTE, ALTER, DROP, START, STOP, or DISPLAY a user-defined function | Stored procedure | EXECUTE, ALTER, DROP, START, STOP, or DISPLAY a stored procedure | |