Access to DB2 Objects

Team-Fly    

 
DB2 Universal Database for OS/390 v7.1 Application Certification Guide
By Susan Lawson
Table of Contents
Chapter 2.  Database Objects


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

graphics/note_icon.jpg

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

graphics/note_icon.jpg

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

graphics/note_icon.jpg

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

graphics/note_icon.jpg

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:

  • BIND

Privileges on all packages:

  • BIND

  • COPY

Privileges on all collections:

  • CREATE IN

Privileges on all schemas:

  • CREATE IN

  • DROPIN

  • ALTERIN

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:

  • EXECUTE

Package privileges:

  • BIND

  • COPY

FREERoutine privileges:

  • EXECUTE

Distinct type privileges:

  • USAGE

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:

  • START DISPLAY

  • STOP

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:

  • ARCHIVE STARTDB (cannot change access mode)

 
  • 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:

  • CREATE IN

Privileges on all packages in the collection:

  • EXECUTE

  • BIND

  • COPY

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

graphics/note_icon.jpg

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

graphics/note_icon.jpg

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

graphics/note_icon.jpg

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

graphics/note_icon.jpg

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

  • ALTER or DROP the storage group

  • Name it in the USING clause of a CREATE INDEX or CREATE TABLESPACE statement

Database

DBCTRL or DBADM authority over the database, depending on the privilege (CREATEDBC or CREATEDBA) that is used to create it

Tablespace

  • ALTER or DROP the tablespace

  • Name it in the IN clause of a CREATE TABLE statement

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


Team-Fly    
Top


DB2 Universal Database for OS. 390 v7. 1 Application Certification Guide
DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)
ISBN: 0131007718
EAN: 2147483647
Year: 2002
Pages: 163
Authors: Susan Lawson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net