Authorizing users to access data
Authorization is an integral part of controlling DB2. The security and authorization mechanisms that control access to DB2 data are both direct and indirect. DB2 performs direct security checks of user IDs and passwords before users gain access to a DB2 subsystem. DB2 security mechanisms include specific objects, privileges on those objects, and some privileges that provide broader authority. DB2 also controls data access indirectly with authorization checks at bind time and run time for application plans and packages.
You probably noticed references to authorization in previous chapters. For example, you must be authorized to run SQL statements that create and alter DB2 objects. Even when users run a SELECT statement to query table information, their authorization might limit what they see. The user might see data only in a subset of columns that are defined in a view. Views provide a good variety of security controls.
Before you issue DB2 commands, run utilities, run application packages and plans, or use most other DB2 functions, you need the appropriate authorization or privilege. For example, to make changes to a table, you need authorization to access that table. A privilege allows an action on an object. For example, to insert data into a table requires the privilege to insert data. 
GRANT and REVOKE statements provide access control for DB2 objects. This section explains how privileges and authorities can be granted to authorization IDs in many combinations and how they can be revoked.
“DB2 and the z/OS Security Server” on page 72 introduces the RACF component of the z/OS Security Server, an alternative to using DB2 authorization. You can use the RACF component or an equivalent product to control access to DB2 objects. 
Controlling access to DB2 subsystems
DB2 UDB for z/OS performs security checks to authenticate users before they gain access to DB2 data. This section describes the authentication mechanisms that a DB2 requester supports and a DB2 server accepts. 
Authentication occurs when the CONNECT statement is issued to connect the application process to the designated server. The server or the local DB2 subsystem checks the authorization ID and password to verify that the user is authorized to connect to the server.
You can use RACF or the z/OS Security Server to authenticate users that access a DB2 database. “DB2 and the z/OS Security Server” on page 72 describes the Security Server.
Local DB2 access
A local DB2 user is subject to several security checks. For example, when you run DB2 under TSO and use the TSO logon ID as the DB2 primary authorization ID, that ID is verified with a password when the user logs on.
When the server is the local DB2 subsystem, RACF verifies the password and checks whether the authorization ID is allowed to use the DB2 resources that are defined to RACF. If an exit routine is defined, RACF or the z/OS Security Server perform further security checking.
Remote access
When the server is not the local DB2 subsystem, the following security checks occur:
The local security manager at the server verifies the DB2 primary authorization ID and password. A subsequent verification determines whether the authorization ID is allowed to access DB2.
Security options for SNA or TCP/IP protocols are checked in the communications database (CDB).
As “Distributed data facility” on page 76 describes, DDF supports TCP/IP and SNA communication protocols in a distributed environment. As a requester or a server, DB2 chooses how to send or accept authentication mechanisms, based on which network protocol is used. DB2 uses SNA security mechanisms for SNA network connections and DRDA security mechanisms for TCP/IP or Kerberos network connections.
DRDA security options provide the following support for encrypting sensitive data:
DB2 UDB for z/OS servers can provide secure, high-speed data encryption and decryption.
DB2 UDB for z/OS requesters have the option of encrypting user IDs and, optionally, passwords when requesters connect to remote servers. Requesters can also encrypt security-sensitive data when communicating with servers so that the data is secure when traveling over the network.
You can use RACF or a similar security subsystem to perform authentication. RACF can:
Verify a remote authorization ID associated with a connection by checking the ID against its password.
Verify whether the authorization ID is allowed to access DB2 through a remote connection.
Verify whether the authorization ID is allowed to access DB2 from a specific remote site.
Generate PassTickets, an alternative to passwords, on the sending side. A PassTicket lets a user gain access to a host system without sending the RACF password across the network.
Communications database
The DB2 communications database contains a set of DB2 catalog tables that let you control aspects of remote requests. DB2 uses this database to obtain information about connections with remote systems.
Kerberos security
As a server, DB2 supports Kerberos security for authenticating remote users. The authentication mechanisms are encrypted Kerberos tickets rather than user IDs and passwords.
You can establish DB2 UDB for z/OS support for Kerberos authentication through the z/OS Security Server. Kerberos is also a network security option for DB2 Connect clients.
Workstation access
When a workstation client accesses a DB2 UDB for z/OS server, DB2 Connect passes all authentication information from the client to the server. Workstation clients can encrypt user IDs and passwords when they issue a CONNECT statement. Database connection services (DCS) authentication must be set to DCS_ENCRYPT. 
An authentication type for each instance determines user verification. The authentication type is stored in the database manager configuration file at the server. The following authentication types are allowed with DB2 Connect:
The user ID and password are validated at the client.
The user ID and password are validated at the database server.
SERVER_ENCRYPT
The same authentication as SERVER and, in addition, passwords are encrypted at the client.
The client logs on to the server using Kerberos authentication.
Controlling data access: The basics
Access to data includes, but is not limited to, a user who is engaged in an interactive terminal session. For example, access can be from a remote server, from an IMS or a CICS transaction, or from a program that runs in batch mode. The term process is used to represent all forms of access to data.
Figure 9.1 suggests several routes from a process to DB2 data, with controls on every route. This section explains how those controls work.
Figure 9.1. DB2 data access control
How authorization IDs control data access
One of the ways that DB2 controls access to data is through the use of identifiers. A set of one or more DB2 identifiers, called authorization IDs, represents every process that connects to or signs on to DB2. This section describes those IDs.
Authorization IDs come in three types:
As a result of assigning authorization IDs, every process has exactly one ID, called the primary authorization ID. Generally, the primary authorization ID identifies a process. For example, statistics and performance trace records use a primary authorization ID to identify a process.
All other IDs are secondary authorization IDs. A secondary authorization ID, which is optional, can hold additional privileges that are available to the process. For example, you could use a secondary authorization ID for a z/OS Security Server group.
CURRENT SQLID
One ID (either primary or secondary) is designated as the CURRENT SQLID. The CURRENT SQLID holds the privileges that are exercised when certain dynamic SQL statements run. You can set the CURRENT SQLID to the primary ID or to any of the secondary IDs. If an authorization ID of a process has system administration (SYSADM) authority, the process can set its CURRENT SQLID to any authorization ID. You can change the value of the CURRENT SQLID during your session.
Example: If ALPHA is your primary authorization ID or one of your secondary authorization IDs, you can make it the CURRENT SQLID by issuing this SQL statement:
SET CURRENT SQLID = 'ALPHA';
Your security and network systems and the choices that you make for DB2 connections affect the use of IDs. If two different accesses to DB2 are associated with the same set of IDs, DB2 cannot determine whether they involve the same process. You might know that someone else is using your ID, but DB2 does not; nor does DB2 know that you are using someone else's ID. DB2 recognizes only the IDs.
How authorization IDs hold privileges and authorities
DB2 controls access to its objects by a set of privileges. Each privilege allows an action on some object. Figure 9.2 shows the three primary ways within DB2 to give access to data to an ID.
IDs can hold privileges that allow them to take certain actions or be prohibited from doing so. DB2 privileges provide extremely precise control.
Related privileges
DB2 defines sets of related privileges, called administrative authorities. By granting an administrative authority to an ID, you grant all the privileges that are associated with it, in one statement. You can read more about administrative authorities in the next section.
Object privileges
Ownership of an object carries with it a set of related privileges on the object. An ID can own an object that it creates, or it can create an object that another ID will own. Creation and ownership of objects are separately controlled.
Application plan and package privileges
The privilege to execute an application plan or a package deserves special attention. Executing a plan or package implicitly exercises all the privileges that the plan or package owner needed when binding it. Therefore, granting the privilege to execute can provide a detailed set of privileges and can eliminate the need to grant other privileges separately.
Example: Assume that an application plan issues the INSERT and SELECT statement on several tables. You need to grant INSERT and SELECT privileges only to the plan owner. Any authorization ID that is subsequently granted the EXECUTE privilege on the plan can perform those same INSERT and SELECT statements through the plan. You don't need to explicitly grant that ID the privilege to perform those statements.
Controlling access to DB2 objects through explicit privileges and authorities
One way of controlling access within DB2 is by granting, not granting, or revoking explicit privileges and authorities.
An explicit privilege is a named privilege that is granted with the GRANT statement or that is revoked with the REVOKE statement.
An administrative authority is a set of privileges, often covering a related set of objects. Authorities often include privileges that are not explicit, have no name, and cannot be specifically granted, such as the ability to terminate any utility job.
Explicit privileges
Explicit privileges provide very detailed control. For example, assume that a user needs to select, insert, and update data in a table. To take these actions, the user needs the SELECT, INSERT, and UPDATE privilege on the table. 
Explicit privileges are available for these objects:
Buffer pools
Collections
Databases
Distinct types
JARs (A JAR is a Java Archive, which is a file format for aggregating many files into one file.)
Packages
Plans
Routines (functions and procedures)
Schemas
Sequences
Storage groups
Systems
Tables
Table spaces
Views
The authorization hierarchy
Privileges are grouped into administrative authorities. Those authorities form a hierarchy. Each authority includes a specific group of privileges. The administrative authorities fall into the categories of system, database, and collection authorities. The highest-ranking administrative authority is SYSADM. Each level of authority includes the privileges of all lower-ranking authorities.
The system authorities described below are ranked from highest to lowest:
System administration authority includes all DB2 privileges (except for a few that are reserved for installation), which are all grantable to others.
SYSCTRL
System control authority includes most SYSADM privileges; it excludes the privileges to read or change user data.
SYSOPR
System operator authority includes the privileges to issue most DB2 commands and end any utility job.
The database authorities described below are ranked from highest to lowest:
DBADM
Database administration authority includes the privileges to control a specific database. Users with DBADM authority can access tables and alter or drop table spaces, tables, or indexes in that database.
DBCTRL
Database control authority includes the privileges to control a specific database and run utilities that can change data in the database.
DBMAINT
Database maintenance authority includes the privileges to work with certain objects and to issue certain utilities and commands in a specific database.
PACKADM has package administrator authority for designated collections.
Controlling access by using multilevel security
In Version 8, DB2 provides a powerful security scheme called multilevel security. Multilevel security is a security policy that classifies data and users according to a system of hierarchical security levels and nonhierarchical security categories. Multilevel security prevents unauthorized users from accessing information at a higher classification than their authorization and prevents users from declassifying information. 
Using multilevel security, you can define security for DB2 objects and perform other checks, including row-level security checks. Row-level security checks control which users have authorization to view, modify, or perform actions on table rows. With multilevel security, you no longer need to use special views or database variables to control security at the row level.
You can create a security label for a table row by defining a column in the CREATE TABLE or ALTER TABLE statement as the security label. As each row is accessed, DB2 uses RACF to compare the security label of the row and the user to determine whether the user has appropriate authorization to access the row. Row-level security checks occur whenever a user issues a SELECT, INSERT, UPDATE, or DELETE statement to access a table with a security-label column or runs a utility request for data in a row that is protected by a security label.
Controlling access by using views
The table privileges DELETE, INSERT, SELECT, UPDATE, and GRANT ALL can also be granted on a view. By creating a view and granting privileges on it, you can give an ID access to only a specific combination of data. This capability is sometimes called field-level access control or field-level sensitivity.
“Defining a view that combines information from several tables” on page 266 shows a typical view of the EMP and DEPT tables. The view reveals only the employee numbers and names of the managers of a restricted list of departments.
Example: Suppose that you want a particular ID, MATH110, to be able to extract certain data from the EMP table for statistical investigation. To be exact, suppose that you want to allow data access like this:
From columns HIREDATE, JOB, EDL, SALARY, COMM (but not an employee's name or identification number)
Only for employees that were hired after December 15, 1996
Only for employees with an education level of 14 or higher
Only for employees whose job is not MGR or PRS
You can create and name a view that shows exactly that combination of data:
CREATE VIEW SALARIES AS SELECT HIREDATE, JOB, EDL, SALARY, COMM FROM EMP WHERE HIREDATE > '1996-12-15' AND EDLEVEL >= 14 AND JOB <> 'MGR' AND JOB <> 'PRS';
Then, use the GRANT statement to grant the SELECT privilege on the view SALARIES to MATH110:
GRANT SELECT ON SALARIES TO MATH110;
Now, MATH110 can run SELECT statements that query only the restricted set of data.
Granting and revoking privileges
The SQL GRANT statement lets you grant explicit privileges to authorization IDs. The REVOKE statement lets you take them away. Only a privilege that has been explicitly granted can be revoked.
Granting privileges is flexible. For example, consider table privileges. You can grant all the privileges on a table to an ID. Alternatively, you can grant separate, specific privileges that allow that ID to retrieve data from the table, insert rows, delete rows, or update specific columns. By granting or not granting those privileges on views of the table, you can effectively determine exactly what action an ID can or cannot take on the table.
You can use the GRANT statement to assign privileges as follows:
Grant privileges to a single ID or to several IDs in one statement.
Grant a specific privilege on one object in a single statement, grant a list of privileges, or grant privileges over a list of objects.
Grant ALL, for all the privileges of accessing a single table or for all privileges that are associated with a specific package.
Examples of granting privileges
This section includes examples of granting some system privileges, use privileges, and table privileges.
Example: To grant the privileges of system operator authority to user NICHOLLS, the system administrator uses the following statement:
GRANT SYSOPR TO NICHOLLS;
Assume that your business decides to associate job tasks with authorization IDs. In the next group of examples, PKA01 is the ID of a package administrator, and DBA01 is the ID of a database administrator.
Examples: Suppose that the system administrator uses the ADMIN authorization ID, which has SYSADM authority, to issue the following GRANT statements:
GRANT PACKADM ON COLLECTION GOLFS TO PKA01 WITH GRANT OPTION;
This statement grants PACKADM authority to PKA01. PKA01 acquires package privileges on all packages in the collection named GOLFS and the CREATE IN privilege on that collection. In addition, specifying WITH GRANT OPTION gives PKA01 the ability to grant those privileges to others.
GRANT CREATEDBA TO DBA01;
CREATEDBA grants DBA01 the privilege to create databases, and DBA01 acquires DBADM authority over those databases.
GRANT USE OF STOGROUP SG1 TO DBA01 WITH GRANT OPTION;
This statement allows DBA01 to use storage group SG1 and to grant that privilege to others.
GRANT USE OF BUFFERPOOL BP0, BP1 TO DBA01 WITH GRANT OPTION;
This statement allows DBA01 to use buffer pools BP0 and BP1 and to grant that privilege to others.
The next group of examples shows specific table privileges that you can grant to specific users.
Examples:
GRANT SELECT ON DEPT TO PUBLIC;
This statement grants SELECT privileges on the DEPT table. Granting the select privilege to PUBLIC gives the privilege to all users at the current server.
GRANT UPDATE (EMPNO,DEPT) ON TABLE EMP TO NATZ;
This statement grants UPDATE privileges on columns EMPNO and DEPT in the EMP table to user NATZ.
GRANT ALL ON TABLE EMP TO KWAN,ALONZO WITH GRANT OPTION;
This statement grants all privileges on the EMP table to users KWAN and ALONZO. The WITH GRANT OPTION clause allows these two users to grant the table privileges to others.
Examples of revoking privileges
The same ID that grants a privilege can revoke it by issuing the REVOKE statement. If two or more grantors grant the same privilege to an ID, executing a single REVOKE statement does not remove the privilege for that ID. To remove the privilege, each ID that explicitly granted the privilege must explicitly revoke it.
Here are some examples of revoking privileges that were previously granted.
Examples:
REVOKE SYSOPR FROM NICHOLLS;
This statement revokes SYSOPR authority from user NICHOLLS.
REVOKE UPDATE ON EMP FROM NATZ;
This statement revokes the UPDATE privilege on the EMP table from NATZ.
REVOKE ALL ON TABLE EMP FROM KWAN,ALONZO;
This statement revokes all privileges on the EMP table from users KWAN and ALONZO.
An ID with SYSADM or SYSCTRL authority can revoke privileges that are granted by other IDs.
Examples: A user with SYSADM or SYSCTRL authority can issue the following statements:
REVOKE CREATETAB ON DATABASE DB1 FROM PGMR01 BY ALL;
In this statement, the CREATETAB privilege that user PGMR01 holds is revoked regardless of who or how many people explicitly granted this privilege to this user.
REVOKE CREATETAB, CREATETS ON DATABASE DB1 FROM PGMR01 BY DBUTIL1;
This statement revokes privileges that are granted by DBUTIL1 and leaves intact the same privileges if they were granted by any other ID.
Revokes can get more complicated. Privileges can be revoked as the result of a cascade revoke. In this case, revoking a privilege from a user can also cause that privilege to be revoked from other users.