Access to DB2 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 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.

NOTE

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.

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, any user who 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. 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.

NOTE

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.

Explicit Privileges

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

  • Plan

  • Package

  • Collection

  • Database

  • Subsystem

  • Use

  • Schema

  • Distinct type

  • Routine

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

Privilege

Function

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

ALL

SELECT, UPDATE, DELETE, and UPDATE

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

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 table spaces 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 table spaces 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: 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.

BSDS

The RECOVER BSDS command: to recover the bootstrap data set

CREATEALIAS

The CREATE ALIAS statement: to create an alias for a table or a 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

CREATESG

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 table space

Schema

CREATEIN

Create distinct types, user-defined functions, triggers, and stored procedures in the designated schemas

ALTERIN

Alter user-defined functions or stored procedures; 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

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 

NOTE

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.

Authorities

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

Authorization

Capabilities

Privileges

Installation SYSADM

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

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

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, 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

Package privileges:

  • BIND

  • COPY

  • FREE

Routine privileges: EXECUTE

Distinct type privileges: USAGE

SYSOPR

  • 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, 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)

PACKADM

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:

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

  • ALTER

  • INSERT

  • DELETE

  • SELECT

  • INDEX

  • UPDATE

  • REFERENCES

  • TRIGGER


[*] 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 

NOTE

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.


Ownership

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.

NOTE

The privileges inherent in the ownership of an object cannot be revoked.


Unqualified Objects

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.

NOTE

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.

Qualified Objects

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.

NOTE

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.


Schema Name

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

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) used to create it

Table space

  • ALTER or DROP the table space

  • 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 of 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, 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

Sequence

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:

  • BIND: primary ID becomes the owner

  • REBIND: previous owner retains ownership

Unqualified Names

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

Information

SYSIBM.SYSCOLAUTH

Updating column authority

SYSIBM.SYSDBAUTH

Database privileges

SYSIBM.SYSPLANAUTH

Plan privileges

SYSIBM.SYSPACKAUTH

Package privileges

SYSIBM.SYSRESAUTH

Buffer pool, storage group, collection, table space, and distinct type usage privileges

SYSIBM.SYSROUTINEAUTH

User-defined functions and stored procedure privileges

SYSIBM.SYSSCHEMAAUTH

Schema privileges

SYSIBM.SYSTABAUTH

Tables and views privileges

SYSIBM.SYSUSERAUTH

System authorities


Views

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 



DB2 for z. OS Version 8 DBA Certification Guide
DB2 for z/OS Version 8 DBA Certification Guide
ISBN: 0131491202
EAN: 2147483647
Year: 2003
Pages: 175
Authors: Susan Lawson

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