Contents
The system keeps track of the privileges that each authorization ID has, and makes sure that each ID performs only authorized operations on the database.
Authorized users can create and drop tables or views, and compile and run programs that operate on these tables or views. Anyone who creates a table or view or compiles a program can selectively share the use of that table, view, or program with other authorization IDs.
The privileges you need vary depending on what operations you want to perform. There are two categories of privileges: privileges on tables and views, and privileges on programs.
You can have any or all of the following privileges on specific tables and views:
When you create a new table or view, you are automatically given full privileges on it. In most situations, you are also given the GRANT option on each privilege which enables you to grant any or all of these individual privileges to other authorization IDs. When you grant a privilege, you may include the GRANT option so that the recipient will be able to grant the privilege to others in turn.
If you grant the privileges on an object to PUBLIC, all authorization IDs (including those that do not yet exist) will have the same privileges that you have.
If you have DBA authority, you have the same privileges on an object and you can grant those privileges (or drop the object) in the same way that the owner of the object can.
Any privilege that you hold on a table or view may be exercised directly through ISQL and the DBS utility as well as application programs.
Privileges on tables and views are listed in the database manager catalog tables. SYSTABAUTH and SYSCOLAUTH. To check what privileges you hold or have granted to other authorization IDs, make the suitable queries on these tables. See the DB2 Server for VSE & VM SQL Reference manual for more information on the catalog tables.
Once you have granted a privilege, you can revoke it by issuing a REVOKE statement. (You can never revoke a privilege from yourself.) If you revoke a privilege from user LEENA, it is automatically revoked from all authorization IDs to whom LEENA granted it, unless the other authorization IDs have another independent source for the same privilege. The most common and most convenient way to to enter a REVOKE statement is through ISQL or the DBS utility. You can code REVOKE statements within a program; however, because the user ID and passwords in the REVOKE statements cannot be host variables, the statements have limited use.
If you attempt to revoke a privilege that is currently in use by a running program, the REVOKE statement is queued until the program ends its current logical unit of work. For example, if you revoke the UPDATE privilege from user MARY, but MARY's program is running and is already making updates, your REVOKE statement does not take effect until MARY's updates are finished.
The database manager can also automatically revoke privileges on views, or drop the view definition. Suppose BILL grants GENE the SELECT privilege with the GRANT option on the EMPLOYEES table. GENE then defines a view called SALARY on this table, and grants the SELECT privilege on that view to other users. After some time, BILL decides to revoke the SELECT privilege on the EMPLOYEES table from GENE. When BILL does so, the system also automatically revokes the SELECT privilege from SALARY also, including all SELECT privileges on SALARY that GENE passed on. If after this process GENE holds no privileges on SALARY, the definition of SALARY is dropped.
Application programs must be preprocessed before they are compiled or assembled. Successfully preprocessing an application program results in the creation or replacement of a package in the database. The contents of the package are then used to satisfy database requests at run time.
When the package is created, the system determines the level of the RUN privilege to be given to the owner (EXECUTE privilege can be used as a synonym for RUN privilege). This depends on such factors as the preprocessed SQL statements, the existence and ownership of the referenced objects (tables, indexes, dbspaces, and so on), and the owner's authorization level (DBA, RESOURCE, or CONNECT) for DB2 Server for VM, and (DBA, RESOURCE, SCHEDULE, or CONNECT) for DB2 Server for VSE.
The owner of a package is assigned the RUN privilege based on the following rules:
There is an exception to this rule: if an SQL statement selects information from a table on which the owner does not have the explicit SELECT privilege, and the owner has DBA authority, then the owner may still be assigned the RUN privilege. This will depend on the result of preprocessing all the other SQL statements in the program.
When a particular SQL statement references objects that do not exist or have different attributes at preprocessing time, the system still creates a package for the program and assigns RUN privilege to the owner. In this case, the required objects must be correctly defined at run time, or execution of the program will fail.
In fact, the determination of whether an owner receives the RUN privilege is based on the aggregate "score" of all preprocessed SQL statements in the program. Each statement is individually assigned an authorization score; at the end of the preprocessing phase, the system picks the lowest score, and assigns that to the owner.
The scores, and the decision tables used to assign them, are discussed in Appendix F, Decision Tables to Grant Privileges on Packages.
The database manager provides a GRANT statement that allows the owner of a package to grant the RUN privilege on the package to other users.
The owner of a package is assigned the GRANT RUN privilege when all preprocessed SQL statements in the program allow the owner to GRANT RUN. If the owner can grant the RUN privilege on a package, a user with DBA authority has the same ability.
Circumstances which enable an owner to gain the GRANT RUN privilege include:
| Note: | The following statements also require DBA authority, but do not affect the
RUN privilege, because they are not checked until run time (when they may be
rejected).
|
There is a difference between static, dynamic, and extended dynamic SQL statements, when determining the privileges of the owner and other users of the package being run.
The REVOKE statement may be used to revoke the RUN privilege on a package in the same way it revokes privileges on tables and views.
In some situations, the system automatically revokes the RUN privilege from a number of users. Suppose user GENE has preprocessed a program that makes use of some privilege, such as SELECT. GENE receives the RUN privilege on the package with the GRANT option, and grants this privilege to other users.
If the SELECT privilege is now revoked from GENE, the package associated with the program is automatically marked invalid. When the program is run (by GENE or any other user), the system attempts to regenerate a valid (fully authorized) package. At the time of this regeneration process, the following outcomes are possible:
The database manager records the current RUN and GRANT RUN privileges held by all authorization IDs in the SYSPROGAUTH catalog table. The entries in the catalog identify:
The entries are added to the catalog tables as an application is preprocessed. The entries may depend, of course, on whether the package satisfies the various conditions described in the preceding sections. The system also makes entries in the SYSPROGAUTH catalog table when someone grants the RUN privilege to another authorization ID.
The system also updates the SYSUSERAUTH, SYSCOLAUTH, and SYSTABAUTH catalog tables. The package's dependency on some authorization is recorded in these catalog tables. For example, when a package requires RESOURCE authority to execute successfully, an entry is made in SYSUSERAUTH to reflect that dependency. The system uses the catalog table entries to keep track of valid and invalid packages.