Controlling data access requires an understanding of direct and indirect privileges, administrative authorities, and packages. This section explains these topics and provides some examples.
Directly granted privileges are stored in the system catalog. Methods for auditing the implementation of the database access control plan are discussed in "Using the System Catalog".
Authorization is controlled in three ways:
The following topics are discussed:
The GRANT statement allows an authorized user to grant privileges. A privilege can be granted to one or more authorization names in one statement; or to PUBLIC, which makes the privileges available to all users. Note that an authorization name can be either an individual user or a group.
On operating systems where users and groups exist with the same name, you should specify whether you are granting the privilege to the user or group. Both the GRANT and REVOKE statements support the keywords USER and GROUP. If these optional keywords are not used, the database manager checks the operating system security facility to determine whether the authorization name identifies a user or a group. If the authorization name could be both a user and a group, an error is returned.
The following example grants SELECT privileges on the EMPLOYEE table to the user HERON:
GRANT SELECT
ON EMPLOYEE TO USER HERON
The following example grants SELECT privileges on the EMPLOYEE table to the group HERON:
GRANT SELECT
ON EMPLOYEE TO GROUP HERON
To grant privileges on most database objects, the user must have SYSADM authority, DBADM authority, or CONTROL privilege on that object; or, the user must hold the privilege WITH GRANT OPTION. Privileges can be granted only on existing objects. To grant CONTROL privilege to someone else, the user must have SYSADM or DBADM authority. To grant DBADM authority, the user must have SYSADM authority.
See the SQL Reference for more information about the GRANT statement.
The REVOKE statement allows authorized users to revoke privileges previously granted to other users. To revoke privileges on database objects, you must have DBADM authority, SYSADM authority, or CONTROL privilege on that object. Note that holding a privilege WITH GRANT OPTION is not sufficient to revoke that privilege. To revoke CONTROL privilege from another user, you must have SYSADM or DBADM authority. To revoke DBADM authority, you must have SYSADM authority. Privileges can only be revoked on existing objects.
| Note: | A user without DBADM authority or CONTROL privilege on a table or view is not able to revoke a privilege that they granted through their use of the WITH GRANT OPTION. Also, there is no cascade on the revoke to those who have received privileges granted by the person being revoked. For more information on the authority required to revoke privileges, see the SQL Reference manual. |
If a privilege has been granted to both a user and a group with the same name, you must specify the GROUP or USER keyword when revoking the privilege. The following example revokes the SELECT privilege on the EMPLOYEE table from the user HERON:
REVOKE SELECT
ON EMPLOYEE FROM USER HERON
The following example revokes the SELECT privilege on the EMPLOYEE table from the group HERON:
REVOKE SELECT
ON EMPLOYEE FROM GROUP HERON
Note that revoking a privilege from a group may not revoke it from all members of that group. If an individual name has been directly granted a privilege, it will keep it until that privilege is directly revoked.
If a table privilege is revoked from a user, privileges are also revoked on any view created by that user which depends on the revoked table privilege. However, only the privileges implicitly granted by the system are revoked. If a privilege on the view was granted directly by another user, the privilege is still held.
If an explicitly-granted table (or view) privilege is revoked from a user with DBADM authority, privileges will not be revoked from other views defined on that table. This is because the view privileges are available through the DBADM authority and are not dependent on explicit privileges on the underlying tables.
If you have defined a view based on one or more underlying tables or views and you lose the SELECT privilege to one or more of those tables or views, then the view cannot be used.
| Note: | When CONTROL privilege is revoked from a user on a table or a view, the user continues to have the ability to grant privileges to others. When given CONTROL privilege, the user also receives all other privileges WITH GRANT OPTION. Once CONTROL is revoked, all of the other privileges remain WITH GRANT OPTION until they are explicitly revoked. |
All packages that are dependent on revoked privileges are marked invalid, but can be validated if rebound by a user with appropriate authority. Packages can also be rebuilt if the privileges are subsequently granted again to the binder of the application; running the application will trigger a successful implicit rebind. If privileges are revoked from PUBLIC, all packages bound by users having only been able to bind based on PUBLIC privileges are invalidated. If DBADM authority is revoked from a user, all packages bound by that user are invalidated including those associated with database utilities. Attempting to use a package that has been marked invalid causes the system to attempt to rebind the package. If this rebind attempt fails, an error occurs (SQLCODE -727). In this case, the packages must be explicitly rebound by a user with:
These packages should be rebound at the time the privileges are revoked. See the SQL Reference for more information about the REVOKE and REBIND PACKAGE statements.
If you have defined a trigger based on one or more privileges and you lose one or more of those privileges, then the trigger cannot be used.
The database manager implicitly grants certain privileges to a user who issues a CREATE SCHEMA, CREATE TABLE, CREATE VIEW, or CREATE INDEX statement, or who creates a new package using a PREP or BIND command. Privileges are also granted when objects are created by users with SYSADM or DBADM authority. Similarly, privileges are removed when an object is dropped.
When the created object is a table, index, or package, the user receives CONTROL privilege on the object. When the object is a view, the CONTROL privilege for the view is granted implicitly only if the user has CONTROL privilege for all tables and views referenced in the view definition.
When the object explicitly created is a schema, the schema owner is given ALTERIN, CREATEIN, and DROPIN privileges WITH GRANT OPTION. An implicitly created schema has CREATEIN granted to PUBLIC.
For information about how view privileges are determined, see the CREATE VIEW statement in the SQL Reference manual.
Access to data within a database can be requested by application programs, as well as by persons engaged in an interactive workstation session. A package contains statements that allow users to perform a variety of actions on many database objects. Each of these actions requires one or more privileges.
Privileges granted to individuals binding the package and to PUBLIC are used for authorization checking when static SQL is bound. Privileges granted through groups are not used for authorization checking when static SQL is bound. The user who binds a package must either have been explicitly granted all the privileges required to execute the static SQL statements in the package or have been implicitly granted the necessary privileges through PUBLIC. PUBLIC, group, and user privileges are all used when checking to ensure the user has the appropriate authorization (BIND or BINDADD privilege) to bind the package.
Packages may include both static and dynamic SQL. To process a package with static SQL, a user need only have EXECUTE privilege on the package. This user can then indirectly obtain the privileges of the package binder for any static SQL in the package but only within the restrictions imposed by the package.
To process a package with any dynamic SQL statements, the user must have EXECUTE privilege on the package. The user needs EXECUTE privilege on the package plus any privileges required to execute the dynamic SQL statements in the package. The binder's authorities and privileges are used for any static SQL in the package.
A view provides a means of controlling access or extending privileges to a table by allowing:
For users and application programs that require access only to specific columns of a table, an authorized user can create a view to limit the columns addressed only to those required.
By specifying a WHERE clause in the subquery of a view definition, an authorized user can limit the rows addressed through a view.
To create a view, a user must have SYSADM authority, DBADM authority, or CONTROL or SELECT privilege for each table or view referenced in the view definition. The user must also be able to create an object in the schema specified for the view. That is, CREATEIN privilege for an existing schema or IMPLICIT_SCHEMA authority on the database if the schema does not already exist. See "Creating a View" for more information.
The following scenario illustrates how views can restrict access to information.
Many people may require access to information in the STAFF table, for different reasons. For example:
This requirement can be easily met by granting SELECT and UPDATE privileges on the STAFF table to the group PERSONNL:
GRANT SELECT,UPDATE ON TABLE STAFF TO GROUP PERSONNL
This requirement can be met by creating a view for each department manager. For example, the following view can be created for the manager of department number 51:
CREATE VIEW EMP051 AS
SELECT NAME,SALARY,JOB FROM STAFF
WHERE DEPT=51
GRANT SELECT ON TABLE EMP051 TO JANE
The manager with the authorization name JANE would query the EMP051 view
just like the STAFF table. When accessing the EMP051 view of the STAFF
table, this manager views the following information:
| NAME | SALARY | JOB |
|---|---|---|
| Fraye | 45150.0 | Mgr |
| Williams | 37156.5 | Sales |
| Smith | 35654.5 | Sales |
| Lundquist | 26369.8 | Clerk |
| Wheeler | 22460.0 | Clerk |
CREATE VIEW EMPLOCS AS
SELECT NAME, LOCATION FROM STAFF, ORG
WHERE STAFF.DEPT=ORG.DEPTNUMB
GRANT SELECT ON TABLE EMPLOCS TO PUBLIC
Users who access the employee location view will see the following
information:
| NAME | LOCATION |
|---|---|
| Molinare | New York |
| Lu | New York |
| Daniels | New York |
| Jones | New York |
| Hanes | Boston |
| Rothman | Boston |
| Ngan | Boston |
| Kermisch | Boston |
| Sanders | Washington |
| Pernal | Washington |
| James | Washington |
| Sneider | Washington |
| Marenghi | Atlanta |
| O'Brien | Atlanta |
| Quigley | Atlanta |
| Naughton | Atlanta |
| Abrahams | Atlanta |
| Koonitz | Chicago |
| Plotz | Chicago |
| Yamaguchi | Chicago |
| Scoutten | Chicago |
| Fraye | Dallas |
| Williams | Dallas |
| Smith | Dallas |
| Lundquist | Dallas |
| Wheeler | Dallas |
| Lea | San Francisco |
| Wilson | San Francisco |
| Graham | San Francisco |
| Gonzales | San Francisco |
| Burke | San Francisco |
| Quill | Denver |
| Davis | Denver |
| Edwards | Denver |
| Gafney | Denver |
The DB2 audit facility generates, and allows you to maintain, an audit trail for a series of predefined database events. While not a facility that prevents access to data, the audit facility can monitor and keep a record of attempts to access or modify data objects.
SYSADM authority is required to use the audit facility administrator tool, db2audit.
See Chapter 5. "Auditing DB2 Activities" for a detailed description of the DB2 audit facility.