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.
Refer to 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, refer to 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. Refer to 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, refer to the CREATE VIEW statement in the SQL Reference manual.
The BIND and PRECOMPILE commands create or change an application package. On either one, use the OWNER option to name the owner of the resulting package. There are simple rules for naming the owner of a package:
Not all operating systems that can bind a package using DB2 database products support the OWNER option.
Refer to the Command Reference for more information on the BIND and PRECOMPILE commands.
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.
When a package contains references to nicknames, authorization processing for package creators and package users is slightly more complex. When a package creator successfully binds packages that contain nicknames, the package creator does not have to pass authentication checking or privilege checking for the tables and views that the nicknames reference at the data source. However, the package executor must pass authentication and authorization checking at data sources.
For example, assume that a package creator's .SQC file contains several SQL statements. One static statement references a local table. Another dynamic statement references a nickname. When the package is bound, the package creator's authid is used to verify privileges for the local table-but no checking is done for the data source objects that the nickname identifies. When another user executes the package, assuming they have the EXECUTE privilege for that package, that user does not have to pass any additional privilege checking for the statement referencing the table. However, for the statement referencing the nickname, the user executing the package must pass authentication checking and privilege checking at the data source.
When the .SQC file contains all dynamic SQL statements and a mixture of table and nickname references, DB2 authorization checking for local objects and nicknames is similar. Package users must pass privilege checking for any local objects (tables, views) within the statements and also pass privilege checking for nickname objects (package users must pass authentication and privilege checking at the data source containing the objects that the nicknames identify). In both cases, users of the package must have the EXECUTE privilege.
The ID and password of the package executor is used for all data source authentication and privilege processing. This information can be changed by creating a user mapping.
| Note: | Nicknames cannot be specified in static SQL. Do not use the DYNAMICRULES option (set to BIND) with packages containing nicknames. |
It is possible that packages containing nicknames might require additional authorization steps because DB2 uses dynamic SQL when communicating with DB2 Family data sources. The authorization ID running the package at the data source must have the appropriate authority to execute the package dynamically at that data source. See the SQL Reference for more information about how DB2 processes static and dynamic SQL.
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.
If you are creating views that reference nicknames, you do not need additional authority on the data source objects (tables and views) referenced by nicknames in the view; however, your users must have SELECT authority or the equivalent authorization level for the underlying data source objects when they access the view.
If your users do not have the proper authority at the data source for underlying objects (tables and views), you can:
Users can then access the columns by issuing a SELECT statement that references the new nickname.
The following scenario provides a more detailed example of how views can be used to restrict access to information.
Many people might 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 7, Auditing DB2 Activities for a detailed description of the DB2 audit facility.