The database manager controls security with authorities and privileges granted to users (identified by their user IDs). Authorities limit people's use of DB2 resources (for example, whether they can create tables in PUBLIC dbspaces or acquire PRIVATE dbspaces), while privileges provide security for existing objects in the database (tables, views, indexes, and packages).
All privileges and authorities held within an application server are recorded in the catalog tables.
To access and perform SQL requests for an application server, users (ISQL users, DBS Utility users, and application programs) must be allowed to CONNECT to the application server implicitly
(without a user ID or password), or explicitly (with a user ID and its password).
With either type of connecting, the user can work with utilities, programs, and the data in the database based on pre-established authorities. Connecting is much the same as logging on to the VM or VSE system.
This chapter discusses the following topics:
This section discusses the four types of authorities and how they can be given (granted) to or taken away (revoked) from users.
This section describes how privileges can be used to share or restrict access to the data in tables or views.
This section discusses how a user can connect to an application server. Users must connect to an application server before they can use it.
This section discusses the use of views to restrict access to tables.
This section describes how you can change the password of your DB2 Server for VSE & VM users.
This section discusses how you can limit access to the catalog tables.
This section describes the two ways that you can audit security: by querying the catalog tables or by having the database manager do a security audit trace.
When a database is initially generated, there is only one user ID defined for it: SQLDBA. This user ID belongs to the database administrator (DBA). Only a DBA can grant or revoke authorities to other users.
There are four types of authority: CONNECT, RESOURCE, SCHEDULE, and DBA.
Authorities are hierarchical, with DBA the highest, RESOURCE and SCHEDULE the next, and finally CONNECT. If you have a higher authority, then you also have the authority below it. For example, if you are given DBA authority, you have RESOURCE, SCHEDULE, and CONNECT authority as well. If you are given RESOURCE authority, you also have CONNECT authority but not SCHEDULE or DBA authority.
This authority enables a user to access a particular application server, and to exercise all privileges that have been granted to PUBLIC. These privileges are discussed in detail in Privileges.
A user with CONNECT authority can access data in one of two ways:
Users with this authority can acquire PRIVATE dbspaces for themselves, and create tables both there and in PUBLIC dbspaces.
A DBA automatically possesses RESOURCE authority and the ability to grant it to users. You can give it to just a few users to exercise tight control, or you can extend it to any number. If you want to allow someone to create tables and you must also control how much resources are used, acquire a PRIVATE dbspace for that user rather than granting him or her RESOURCE authority. Because you acquire this dbspace yourself, you control its size and the amount of resources used. This technique is sometimes called "CREATE TABLE authority", but this term is misleading because there is no GRANT CREATE TABLE statement.
The function associated with SCHEDULE authority is not available in the SQL statement set. Therefore, DB2 Server for VSE & VM users cannot use it and SCHEDULE authority is of no direct benefit to DB2 Server for VSE & VM users.
SCHEDULE authority is useful only to online resource managers that manage subsystems of multiple second-level users. The only current example is the DB2 Server for VSE online resource adapter that manages secondary users through the CICS subsystem. The CICS subsystem is a first-level user of the database manager. The use of SCHEDULE authority in a CICS subsystem is discussed here.
The online resource adapter resides in each CICS partition. It initializes the communication links between the CICS partition and the local DB2 Server for VSE database manager, or the DB2 Server for VM database manager accessed through guest sharing, when the operator executes the CICS CIRB transaction or the CICS CIRA transaction. It also does a CONNECT on each link, specifying DBDCCICS as the user ID and SQLDBAPW as the password. This user ID and password can be overridden. Refer to CICS Transaction Environment for details.
The online resource adapter in each CICS partition can connect to many application servers. The DBNAME parameter of the CIRB or CIRA transaction specifies the application server to which you want to connect. If DBNAME is not specified on the CIRB transaction, the default application server is used. Refer to Establishing a Default Application Server for information on DBNAME default rules. All online applications in a CICS partition can access the application servers connected with the online resource adapter.
The schedule function comes into play when a CICS transaction uses SQL statements without preceding them with a CONNECT statement1 on a local application server or on a VM application server accessed through guest sharing. When this occurs, the resource adapter sends a schedule request to the database manager. This request travels on the link being used by the transaction. A schedule request is similar to a CONNECT, but it has no password. The resource adapter determines the user ID as described in CICS Transaction Environment.
The schedule function allows dynamic changing of the current user ID on a link to the database manager without requiring a password. For this to occur, the initial user of the link must have SCHEDULE authority. For a CICS session, the initial user of the link is the unique application name (APPLID) assigned to the CICS partition in the DFHSIT table. The default APPLID name is DBDCCICS. This user ID represents the entire CICS subsystem. The database administrator must grant each APPLID SCHEDULE authority on the application server so that the links to the database manager can be shared implicitly by multiple transactions. If a CICS partition is to connect to more than one local application server, the APPLID for the partition must be granted SCHEDULE authority on each application server.
Transactions that do not issue CONNECT statements1 receive their connection to the database manager implicitly through the CICS subsystem. The assumption is made that the CICS subsystem checked the user's identification and password when the user began the CICS session, so the database manager does not need to do further checking. On the other hand, each transaction is subject to all the other security controls. The user ID received by the database manager with the schedule request is the basis for this transaction user's authorization.
Because CONNECT authority is not needed for CICS transactions, the user IDs that they use need not appear in the SYSTEM.SYSUSERAUTH catalog table. This catalog table does not necessarily have an entry for every user. Second-level users can access all PUBLIC data and may be granted access to PRIVATE data as well. Although a user may not be given CONNECT authority explicitly, that user can be granted RESOURCE authority or SCHEDULE authority and will receive CONNECT authority as a result.
| Note: | This discussion applies only to transactions that do not issue a CONNECT statement. When a transaction does issue a CONNECT statement1, it appears as an ordinary user, and the schedule function is not used. |
A user possessing DBA authority possesses SCHEDULE authority and the ability to grant SCHEDULE authority to other users.
To grant SCHEDULE authority, use a statement such as:
GRANT SCHEDULE TO dbdccics IDENTIFIED BY password
If the user's password has been entered previously and is not to be changed, you can omit the "IDENTIFIED BY password" portion of the GRANT statement. Refer to CICS Transaction Environment for details.
Authorization mechanisms do not apply to users with this authority. They can perform all operations on all tables, can run all programs, and are the only ones who have the following privileges:
No user, including those with DBA authority, can drop a catalog table.
As DBA, you may perform certain operations that are otherwise unauthorized, but may not grant or revoke these operations. For example, you may update a particular table that you do not own explicitly, but you may not grant or revoke this privilege to others.
The functions enabled by DBA authority are potentially quite dangerous to the integrity of the database if applied by an untrained user. Therefore, you should carefully control who receives this authority, as well as being very cautious in the use of this special authority yourself.
To grant any authority (SCHEDULE, CONNECT, RESOURCE, or DBA) to other users of an application server, issue the GRANT statement. You must have DBA authority on that application server. For information on the syntax of this statement, see the DB2 Server for VSE & VM SQL Reference manual.
Granting someone a higher authority automatically gives them the lower authority as well, regardless of whether these are specified on the GRANT statement. Thus, a user who is granted RESOURCE authority will also have CONNECT authority; one who has DBA authority also has CONNECT, RESOURCE, and SCHEDULE authority.
If you are granting authority to a user at a remote system, the authorization-name specified in the GRANT statement must be the authorized user ID of the user on the system where the authority is being granted, not that on the system where the request originates.
To give the user ID MIKE CONNECT authority to the application server, enter:
GRANT CONNECT TO MIKE IDENTIFIED BY mikespwd
If the user MIKE intends to connect to the application server implicitly, you can omit his password:
GRANT CONNECT TO MIKE
To give the user IDs MIKE and JOHN RESOURCE authority to the application server, enter:
GRANT RESOURCE TO MIKE,JOHN IDENTIFIED BY mikespwd,johnspwd
If MIKE intends to connect to the application server implicitly, you may omit his password and just enter:
GRANT RESOURCE TO JOHN IDENTIFIED BY johnspwd GRANT RESOURCE TO MIKE
The following statement enables all users to connect to the application server implicitly:
GRANT CONNECT TO ALLUSERS
Users who wish to connect explicitly to the application server must be given CONNECT authority with a password. In VM, the ability to communicate with a DB2 Server for VM database manager depends on VM directory statements and is discussed in the DB2 Server for VM System Administration manual.
When VSE/AF runs as a guest operating system under the VM/ESA operating systems, VSE users and programs can optionally access a DB2 Server for VM application server. A VSE guest who wishes to do this must obtain authorization. On the GRANT statement, specify a VM user ID that is authorized to run the VSE subsystem.
The subsystemid follows the same general rules for naming data objects as the user ID, and cannot contain lowercase characters, special characters, or DBCS characters.
To give the CICS subsystem MYCICS SCHEDULE authority, enter:
GRANT SCHEDULE TO MYCICS IDENTIFIED BY cicspw
where cicspw is the current password set for the subsystem.
To revoke authorities previously granted to users, issue the REVOKE statement. You must have DBA authority. For information on the syntax of this statement, see the DB2 Server for VSE & VM SQL Reference manual.
Revoking a user's CONNECT authority does not automatically cause any objects owned by that user to be dropped, nor does it revoke any privileges the user has on those objects. For information on how to drop objects, see Removing Users from an Application Server.
If a user's CONNECT authority is revoked, all other authorities are lost. For example, if you are a DBA and another DBA revokes your CONNECT authority, then you will lose your RESOURCE, SCHEDULE, and DBA authorities as well.
A user who loses RESOURCE authority will still have CONNECT authority. You cannot revoke RESOURCE authority from a user with DBA authority.
A user who loses SCHEDULE authority will still have CONNECT authority. You cannot revoke SCHEDULE authority from a user with DBA authority.
A user who loses DBA authority will also lose RESOURCE and SCHEDULE authority, but will retain CONNECT authority.
When revoking remote users, the authorization-name specified in the REVOKE statement must be the authorized user ID of the user on the remote system where the authority is being revoked, not that on the system where the request originates.
To revoke JOHN's CONNECT authority, enter:
REVOKE CONNECT FROM JOHN
To revoke JOHN and ALICE's DBA authority, enter:
REVOKE DBA FROM JOHN,ALICE
To revoke JOHN and ALICE's SCHEDULE authority, enter:
REVOKE SCHEDULE FROM JOHN,ALICE
Use the REVOKE SCHEDULE statement to revoke remote access by a VSE subsystem.
To revoke the SCHEDULE authority of the CICS subsystem called MYCICS, enter:
REVOKE SCHEDULE FROM MYCICS
The DBA grants authorities to the users of the application server. Within the framework set up by the DBA, individual users can grant to each other the privileges they need to access specific data. To grant or revoke privileges on an object, a user must hold GRANT authority on those privileges, and be connected to the application server where the object resides.
The following are the privileges that can be held on a table (or view) in the database:
The first four privileges in this list apply to views as well as to tables.
The holder of a privilege may exercise it directly through a user mechanism such as ISQL, or by compiling and running programs that entail using it.
When an object is created, its ownership is established. If the object name is not qualified (for example, EMPLOYEE), the owner is the connected user. If the object is qualified (for example, JESSICA.EMPLOYEE), the owner is the individual whose user ID is specified. The owner of an object automatically has full privileges on it.
Once the ownership of a table or view is established, there is no way to change it or to revoke the privileges that accompany ownership. If either of these is necessary, you must drop the object, which deletes all privileges on it, and then re-create it with a new owner.
The owner of an object possesses the GRANT option on each privilege, meaning the ability to grant individual privileges, or any combination of them, to other users. When a privilege is granted, the GRANT option (the ability for the recipient to in turn make further grants) may or may not be included.
Privileges can be granted to other users using the GRANT statement described in the DB2 Server for VSE & VM SQL Reference manual.
Withholding these privileges restricts the ability of the recipient to change the state of referential constraints. If the owner of a parent table grants the REFERENCES privilege on it to another user, and the recipient then creates a foreign key relationship with the parent's primary key but does not grant ALTER privilege on the dependent table back to the owner of the parent table, the owner cannot drop the primary key. (He or she may, of course, drop the entire table.)
GRANT UPDATE (ADDR,PHONE) ON EMPDATA TO CINDY
If you are granting a user privileges at a remote system, the authorization-name specified in the GRANT statement must be the same as the name that the grantee uses to access the database manager system on the remote system.
A user who grants another user a privilege may later revoke it, by issuing the REVOKE statement described in the DB2 Server for VSE & VM SQL Reference manual. If a user loses a privilege, all other users to whom that user granted it automatically lose it too by the cascading effect, unless they have another independent source for it. Issuing REVOKE ALL or REVOKE ALL PRIVILEGES takes away all privileges that were granted.
If you are revoking a user's privileges at a remote system, the authorization-name specified in the REVOKE statement must be the name that the user specifies to access the database manager system on the remote system.
All the privileges held by users on tables and views are listed in the catalog tables SYSTEM.SYSTABAUTH and SYSTEM.SYSCOLAUTH. Users can check which privileges they hold and which they have granted to others, by querying these tables.
To determine the privileges that you hold, enter:
SELECT * FROM SYSTEM.SYSTABAUTH
WHERE GRANTEE = user
To determine the privileges that you have granted to other users, enter:
SELECT * FROM SYSTEM.SYSTABAUTH
WHERE GRANTOR = user
AND GRANTEE <> user
AND GRANTEETYPE = ' '
For descriptions of the catalog tables, see the DB2 Server for VSE & VM SQL Reference manual.
DB2 Server for VSE & VM application programs must be preprocessed before they are compiled or assembled. In VM and VSE batch environments, successful preprocessing of an application program results in the creation or replacement of a package in the database. In VSE, successful preprocessing and/or CBNDing of an application program results in the creation or replacement of a package in the database. The contents of the package are instructions used to satisfy database requests at run time.
When a package is created, a level of EXECUTE privilege is granted to its creator. This level is dependent on several factors, such as the preprocessed SQL statements, the existence and ownership of the referenced objects (tables, indexes, and dbspaces), and the creator's authorization level (DBA, RESOURCE, or CONNECT). The creator's EXECUTE privilege follows rules and conditions that are discussed in the DB2 Server for VSE & VM Application Programming manual.
A VM user must have CONNECT authority and be connected to an application server in order to perform SQL requests on it.
All VM users must connect to an application server explicitly or implicitly regardless of whether they are accessing it in multiple user mode or single user mode. If a user does not have a DB2 Server for VM authorization ID and password, the user must connect implicitly. A user with a DB2 Server for VM authorization ID and password can connect either implicitly or explicitly.
In order to run a preprocessor, the DBS Utility, any application program, or ISQL, VM users must establish a default application server. This is done by invoking the SQLINIT EXEC, and needs to be done only once.
To establish the SQLDBA application server as the default, enter:
SQLINIT DBNAME(SQLDBA)
Information about the default application server chosen is stored on the VM user's minidisk (A-disk) in the ARISRMBT module and the LASTING GLOBALV file. If the VM user wants to establish another application server as the default or to change any of the options, he or she would have to re-run the SQLINIT EXEC. For more information see SQLINIT EXEC.
Connecting to the application server implicitly means to connect to it without providing an authorization ID and password explicitly. If a VM user does not provide a CONNECT statement, then the first time that he or she tries to run an SQL statement, the VM application requester connects to the application server implicitly. The database manager checks its catalog tables to see whether that user's ID, the VM logon ID (established in the CP LOGON procedure), has been granted CONNECT authority. (It does not compare the user's CP LOGON password with the DB2 Server for VM application server password, as it can be assumed that a password that has been verified by the CP LOGON procedure is valid.)
Most VM users will want to connect to the application server implicitly, so when you grant them CONNECT authority, use their CP LOGON user IDs.
The implicit connect support works the same for VM application programs, for ISQL, for the DBS Utility, and for remote application servers; however, each has its own considerations, as discussed below.
| Note: | When working in an environment that includes several application servers that can be accessed from several different application requesters, there is the need for unique authorization IDs. The database manager does not recognize the same authorization ID from two (or more) different application requesters as being different. It is the administrator's responsibility to ensure that the authorization IDs in this situation are unique. |
For application programs that contain SQL statements, a distinction is made between the creator and the runner of the program.
When not coded explicitly, the authorization ID is derived from the CP LOGON.
When coded explicitly, the authorization ID and password for the CONNECT statement are derived from host variables in the program. The values for these variables should be acquired at run time from control cards by the executing program. If they are constants fixed in the program, anyone can run the program.
When not coded explicitly, the authorization ID is derived from the CP LOGON.
Refer to the DB2 Server for VSE & VM Application Programming manual for more information about how implicit CONNECT applies to application programs.
To start ISQL, a VM user invokes the ISQL EXEC. The database manager always initially does an implicit connect for ISQL users, so this EXEC does not accept an authorization ID. The authorization ID is derived from the ID of the user's virtual machine, as described on page ***.
The user can issue explicit CONNECT statements to override any previous explicit or implicit connection established for the ISQL session.
Refer to the DB2 Server for VSE & VM Interactive SQL Guide and Reference manual for more information.
When the DBS Utility begins processing an input control file, it expects a CONNECT statement before any other DBS Utility or SQL statements. If none is supplied, the database manager will use the ID of the user's virtual machine.
If the utility is invoked from an application that has already issued a CONNECT statement (implicitly or explicitly), then another one is not expected. Here, the authorization ID that was in effect when the program first invoked the utility is used.
The user can issue explicit CONNECT statements to override any previous explicit or implicit connection.
Refer to the DB2 Server for VSE & VM Database Services Utility manual for more information.
When a VM user implicitly connects to a remote application server, the authorization ID passed by the requester or received by the server may be different than the VM logon user ID. It will depend on how the CMS Communication Directory has been set up for the requester, and whether the server performs user ID translation. Refer to the Distributed Relational Database Connectivity Guide manual for more information about security levels specified in the CMS Communication Directory when implicitly connecting to a remote application server.
When a VM user implicitly connects to an application server using TCP/IP as the communications protocol, an explicit connect is performed by the resource adapter using the authorization ID and password found in the CMS Communications Directory. There is no implicit connect when TCP/IP is being used.
VM users may want to connect to an application server other than the default one, switch to another application server, or connect to an application server as a different authorization ID. These situations entail making an explicit connection.
After connecting to an application server, a VM user may want to switch to a different one. The user issues an SQL CONNECT statement to switch to this second application server.
To switch to the DB01 application server, enter:
CONNECT TO DB01
Since the authorization ID and password are not specified on the CONNECT statement, they will be taken from the VM communications directory file if it is used and if it contains an entry for the DB01 application server. If the file is not used, if it does not exist, or if it does not contain an entry for the DB01 application server, the VM logon user ID will be used in an implicit connect.
If this statement fails, the VM user will not remain connected to the original application server and no other SQL statements will be accepted. The VM user will have to issue a new CONNECT statement.
When the VM user issues the first SQL statement to be processed on the second application server, the database manager will try to implicitly connect him or her to that application server, using the VM logon user ID as the authorization ID. VM users can avoid the implicit connect by connecting as another user (discussed next) while switching application servers.
To switch to the DB01 application server under an authorization ID JOHN with a password of johnpw, enter:
CONNECT JOHN IDENTIFIED BY johnpw TO DB01
| Note: | CONNECT userid IDENTIFIED BY password is not supported for the Distributed Relational Database Architecture (DRDA) protocol. |
If this statement fails, the VM user will not remain connected to the original application server and no other SQL statements will be accepted. The user will have to issue a new CONNECT statement.
A VM user connects under another authorization ID to the currently established application server by issuing an SQL CONNECT statement. If the user is not currently connected to an application server, if the previous connection has been released, or if the user switched to a new CMS Work Unit in VM/ESA, then the default application server, established by the SQLINIT EXEC, will be used.
To connect to the currently established application server under the authorization ID JOHN with a password of johnpw, enter:
CONNECT JOHN IDENTIFIED BY johnpw
| Note: | CONNECT userid IDENTIFIED BY password is not supported for the DRDA protocol. |
If this statement fails, the VM user will remain connected to the application server as the original authorization ID.
A previous connection could be released for the following reasons:
If a user issues an SQL CONNECT statement without any parameters, the database manager will return the following information:
Refer to the DB2 Server for VSE & VM SQL Reference manual for more information about the CONNECT statement.
To control access to the data managed by the database manager, it is necessary to:
The authority to use a DB2 Server for VSE database is established by granting a user CONNECT authority. The CONNECT authority carries with it a DB2 Server for VSE password, which is that user's key to the application server. After a user has received CONNECT authority (been assigned an authorization ID and password), the user can begin to use an application server through the CONNECT function. After users have received their authorization IDs and passwords, they can change their own passwords at any time.
All VSE users must connect to an application server explicitly or implicitly:
The CONNECT function can also be used either directly (through the CONNECT statement with the "userid IDENTIFIED BY password" clause) or indirectly (through a subsystem logon procedure). The procedure for connecting to an application server is slightly different for each user environment. The following sections describe these situations.
You may identify the desired application server by specifying the DBNAME parameter at system startup, on the CICS CIRB or CICS CIRC transaction, on the CONNECT statement, when preprocessing, or when CBNDing. If you do not specify a server name, these DBNAME default rules apply:
| Note: | SQLDS must still be identified in the DBNAME Directory. |
For further information on the DBNAME Directory, refer to the DB2 Server for VSE System Administration manual.
DB2 Server for VSE users can connect to the application server in the following environments:
In a CICS/VSE online environment, online users can connect to the application server implicitly and explicitly. If online users do not explicitly issue a CONNECT statement specifying the authorization ID and the password, then the first time they try to process an SQL statement, the CICS/VSE user is connected to the application server implicitly.
A CONNECT...TO statement is supported in this environment and can be used to switch to a different application server between logical units of work. For further information on switching, refer to Switching to Another Application Server.
If the first SQL statement in a CICS/VSE application is not a CONNECT statement with the TO clause, the default application server is connected. On subsequent CONNECTs performed by that application, if the TO parameter is not specified, then the connection to the previously connected server will be maintained. For further information on default application servers, refer to Establishing a Default Application Server.
In a VSE batch or VSE/ICCF environment, an explicit CONNECT must be the first statement entered by the batch user to access the application server. This statement is described in the DB2 Server for VSE & VM Application Programming manual. Explicit connection is required for all user programs. This connection identifies the authorization ID, and optionally the name of the application server on which the program will run.
A CONNECT..TO statement is supported in this environment and can be used to switch to a different application server between logical units of work. For further information on switching, refer to Switching to Another Application Server.
If the first SQL statement in an application is a CONNECT statement in which the TO server_name clause is not specified, or if this clause is not specified as part of the CONNECT statement following a COMMIT RELEASE or ROLLBACK RELEASE statement, the default application server is connected. If the TO server_name clause is not specified as part of the CONNECT statement following a COMMIT or ROLLBACK statement, the connection to the previously connected server will be maintained. For further information on default application servers, refer to Establishing a Default Application Server.
In this environment, there is a distinction between the user who preprocesses a program that contains SQL statements, and the user who later runs that program.
The user ID and password for the CONNECT statement are derived from host variables in the program. Their values should be acquired at run time from control cards by the executing program. If they are constants fixed in the program, anyone can run the program.
The runner of a program gets the privilege of accessing the application server from the creator of the program.
When CICS users start ISQL, they are prompted for a user ID, password, and target database. If the user enters the user ID and password only, ISQL does an explicit CONNECT to the default target database for the user. If the user does not enter a user ID, password or target database, ISQL does a CONNECT to the default target database as a default user ID for the user; this defaulting is called an implicit CONNECT. If the ISQL user enters a target database only, a CONNECT would be made to that target database using a default user ID. If the user enters the user ID, password and target database, ISQL does an explicit CONNECT to the target database.
In the ISQL environment, you can access any of the application servers connected with the online resource adapter. If the online resource adapter is not connected to an application server, you cannot access the ISQL environment.
| Note: | The ISQL environment is a specific case of the CICS transaction environment, which is discussed in the next section. An ISQL user can enter explicit CONNECT statements to change the connection and override any previous explicit or implicit connection established for the ISQL terminal session. |
Refer to the DB2 Server for VSE & VM Interactive SQL Guide and Reference or the DB2 Server for VSE & VM SQL Reference manual for additional details.
Online transactions need not enter a CONNECT command to establish the user ID within the database manager. If a CONNECT command2 is not entered, the online support establishes the authorization ID for the transaction. The implicit CONNECT is carried out by a SCHEDULE call in the case where the online transaction is connecting to a local application server.
This implicit CONNECT capability is useful if your installation requires terminal users to sign on CICS.
For many transactions, your installation might consider the sign-on verification sufficient. It may also be useful if your installation has just installed the database manager, and finds it convenient to have all users identified by one name (for example, TESTUSER).
The online support establishes a user ID for CICS transactions connecting to a local DB2 Server for VSE application server as follows:
After the user ID is determined as described above for cases b, c, and d, one more requirement must be met to successfully complete the connection to the application server: CONNECT authority must be granted to either the specific authorization ID or "ALLUSERS". ALLUSERS
is a special authorization ID that permits any user ID to be implicitly connected without having been specifically granted CONNECT authority, and can be used by the database administrator to turn on or turn off the implicit CONNECT capability. During database generation, ALLUSERS is granted CONNECT authority by default.
At many installations, the CICS user need not be aware of DB2 Server for VSE authorization ID or authorization capabilities. Here, the CICS implicit connect support can be very useful.
Suppose you code a transaction called STAT that displays the inventory status of a given part. Banes and Smith are to be the users of the application.
You define Banes and Smith to the CICS signon process.
You must then authorize BANES and SMITH to run your program. Of course, you must have the RUN privilege with the GRANT option on your program. For this example, assume that the program was preprocessed with the name INVSTAT:
GRANT RUN ON INVSTAT TO BANES, SMITH
| Note: | BANES and SMITH do not need CONNECT authority. It is connected through internal mechanisms of the DB2 Server for VSE online support. |
You must also establish the security key when you define the inventory program to CICS.
To use the STAT transaction, Banes and Smith merely sign on to the CICS subsystem by entering, for example:
CESN BANES, XXXX
After signed on, they need only enter the transaction identifier STAT, which causes the INVSTAT program to be loaded and invoked. Since there is no CONNECT statement in the program, the user ID established is the signed-on user ID (BANES). Because BANES was granted RUN authority on INVSTAT, the database manager allows the program to process.
Online applications can access any of the application servers connected with the online resource adapter. The online resource adapter can connect to many application servers using the CIRA or CIRB transactions.
Refer to the DB2 Server for VSE & VM Application Programming manual for additional information on this environment.
For online DB2 Server for VSE transactions which are accessing a remote server and which issued an SQL CONNECT statement with the "userid IDENTIFIED BY password" clause to establish the user ID within the database manager, the user ID is established explicitly for the transaction.
For online DB2 Server for VSE transactions which are accessing a remote server and which did not issue an SQL CONNECT statement with the "userid IDENTIFIED BY password" clause to establish the user ID within the database manager, the Online Resource Adapter will attempt to establish the user ID for the transaction implicitly as follows:
VSE users can connect to an application server other than the default one, or connect to an application server as a different authorization ID. VSE batch users can switch from an application server to another. These situations require making an explicit connection. VSE online users can also switch from an application server to another, by issuing an SQL CONNECT statement with the TO parameter, provided that the online resource adapter has established connections to the application server.
After connecting to an application server, a VSE user can switch to another one by issuing an SQL CONNECT statement. The switch occurs between logical units of work.
To switch to the DB01 application server, enter:
CONNECT TO DB01
Because the user ID and password are not specified on the CONNECT statement, the user ID and password used is determined according to the rules described in CICS Transaction Environment and User IDs for Remote CICS/VSE Transactions. For VSE batch users, the user ID and password used in the previous LUW are used if the LUW ends with a COMMIT WORK or ROLLBACK WORK statement. However, if the LUW ends with a COMMIT RELEASE or ROLLBACK RELEASE statement, the next SQL statement after the CONNECT statement is unsuccessful.
If the CONNECT statement is not successful, the VSE batch user does not remain connected to the original application server, and no other SQL statements are accepted. The batch user has to enter a new CONNECT statement.
When the VSE user enters the first SQL statement to be processed on the second application server, the batch resource adapter or the online resource adapter connects the user to that application server using user ID and password previously established. A VSE user can switch to another application server as different ID by connecting as another user, as discussed in the next section.
To switch to the DB01 application server under an authorization ID JOHN with a password of johnpw, enter:
CONNECT JOHN IDENTIFIED BY johnpw TO DB01
If this statement is not successful, the VSE batch user does not remain connected to the original application server and no other SQL statements are accepted. The batch user will have to enter a new CONNECT statement.
A VSE user connects under another authorization ID to the established application server by issuing an SQL CONNECT statement. If the user is not connected to an application server, the default application server is accessed. For batch users, if the previous connection has been released, the default application server is accessed.
To connect to the currently established application server under the authorization ID JOHN with a password of johnpw, enter:
CONNECT JOHN IDENTIFIED BY johnpw
If this statement fails, the VSE user will remain connected to the application server as the original authorization ID.
A previous connection could be released for the following reasons:
If a user enters an SQL CONNECT statement without any parameters, or after the successful execution of a CONNECT statement, the database manager returns the following information in the SQLCA:
Refer to the DB2 Server for VSE & VM SQL Reference manual for more information about the CONNECT statement.
|If the specified application server is remote and the Communication |Protocol specified by the connected user is not TCP/IP, the AR will issue a GDS ALLOCATE command to acquire a session to the remote system where the server runs. The SYSID used in this ALLOCATE command will be the SYSID value from the matching DBNAME Directory entry (and must match a CEDA DEF CONNECTION definition). The AR will then issue a GDS CONNECT PROCESS command to initiate an APPC basic conversation with the remote server. The PROCNAME used in this CONNECT PROCESS command will be the REMTPN value from the matching DBNAME Directory entry.
|If the specified application server is remote and the Communication |Protocol specified by the user is TCP/IP, the AR will acquire a TCP/IP |socket. Then the AR will use this socket to originate a connection |request to initiate a TCP/IP communication with the remote server. In |this case, the TCPPORT and the TCPHOST or the IPADDR from the matching DBNAME |Directory entry are required for issuing the connect request.
If the default |application server is remote and the Communication Protocol specifed |by the connected user is not TCP/IP, the AR will issue a GDS ALLOCATE command to acquire a session to the remote system where the default application server runs. The SYSID used in this ALLOCATE command will be the SYSID value of the default server (and must match a CEDA DEF CONNECTION definition). The AR will then issue a GDS CONNECT PROCESS command to initiate an APPC basic conversation with the remote server. The PROCNAME used in this CONNECT PROCESS command will be the REMTPN value of the default server.
If the default |application server is a remote server and the Communication Protocol |specified by the user is TCP/IP, the AR will acquire a TCP/IP socket. |Then the AR will use this socket to originate a connection request to initiate |a TCP/IP communication with the remote server. In this case, the |TCPPORT and the TCPHOST or the IPADDR from the default |server's DBNAME Directory entry are required for issuing the connect |request.
The default application server is determined when the CIRB transaction was invoked and can be changed subsequently by a CIRC transaction. For more information on establishing a default application server, see Establishing a Default Application Server.
|If the Batch application issues an SQL CONNECT statement with the "TO |server name" clause, the server name is established explicitly for the |transaction and the Batch Resource Adapter uses the DBNAME Directory to |resolve the server name to the target database.
|If the specified application server is a local or host VM (Guest Sharing) |server, communications is done using XPCC as it is currently done. |If the application server is remote and TCP/IP information is present in the |matching DBNAME Directory entry, communications is done using TCP/IP. |If TCP/IP information is not present, an error is returned in the SQLCA: |SQLCODE -841, SQLSTATE 57040, with a reason code in SQLERRD2.
|If the Batch Application issues an SQL CONNECT statement without the "TO |server name" clause, the actions taken by the Batch Resource Adapter depend on |the previous connection state. If the previous state was established |with a COMMIT or ROLLBACK, then the Batch Resource Adapter connects back to |the previous Server name. If the previous state was established with a |COMMIT or ROLLBACK with the RELEASE option, then the Batch Resource Adapter |attempts to connect to the default application server.
|If the default application server is a local or host VM (Guest |Sharing) |server, communications is done using XPCC as it is currently done. |If the application server is remote and TCP/IP information is present in the |matching DBNAME Directory entry, communications is done using TCP/IP. |If TCP/IP information is not present, an error is returned in the SQLCA: |SQLCODE -841, SQLSTATE 57040, with a reason code in SQLERRD2.
|The default application server is determined from the DBNAME |Directory as is currently done. For more information on establishing a |default application server, see "Establishing a Default Application Server". Note that Batch applications cannot access a Remote |server via SNA, only via TCP/IP.
Views control who has access to what data. They can be set up to allow access to a subset of the columns or the rows of a table.
To show how a view can be used to restrict access to information,
consider the information presented in Table 18.
Table 18. Employee Information (EMP_INFO) Table
| NAME | DEPT | SALARY | PHONENO |
|---|---|---|---|
| SMITH | 100 | 25750 | 3978 |
| BANES | 200 | 15051 | 3476 |
| ADAMSON | 105 | 33075 | 4738 |
| PARKER | 200 | 26250 | 6789 |
| KWAN | 100 | 22260 | 7831 |
| WALKER | 105 | 23840 | 5498 |
Many different people may require access to information in this table for different reasons.
This requirement is met by granting users in the personnel department SELECT and UPDATE privileges on this table, as follows:
GRANT SELECT,UPDATE ON EMP_INFO TO PERSONNL
This requirement is met by creating a view for each manager. For example, the following view (called EMP100) can be created for JANE, the manager of department 100:
CREATE VIEW EMP100
AS SELECT NAME,SALARY,PHONENO
FROM EMP_INFO
WHERE DEPT=100
GRANT SELECT ON EMP100 TO JANE
JANE (and any others who have SELECT privilege on this view) would query
it as they would an ordinary table. It would appear as the
following:
| NAME | SALARY | PHONENO |
|---|---|---|
| SMITH | 25750 | 3978 |
| KWAN | 22260 | 7831 |
This requirement is met by creating a view (called PHONE) on the NAME and PHONENO columns:
CREATE VIEW PHONE
AS SELECT NAME,PHONENO
FROM EMP_INFO
GRANT SELECT ON PHONE TO PUBLIC
The keyword PUBLIC grants the privileges on the PHONE view to all
users. Users who access it will see the following table:
| NAME | PHONENO |
|---|---|
| SMITH | 3978 |
| BANES | 3476 |
| ADAMSON | 4738 |
| PARKER | 6789 |
| KWAN | 7831 |
| WALKER | 5498 |
All users' passwords are recorded in the SYSTEM.SYSUSERAUTH catalog table. As a DBA, you can change any user's password at any time. To do this, use a GRANT CONNECT statement.
GRANT CONNECT TO JOHN IDENTIFIED BY xyzabc
Users can also change their own passwords at any time, by issuing a GRANT CONNECT statement to themselves. To change a user's password verified by the CICS subsystem, or some other subsystem, follow the procedure for that subsystem.
You should change all passwords on a periodic basis; for example, every four months.
During database generation, the SELECT privilege is granted to PUBLIC on the catalog tables. In most cases this presents no security problem, but for very sensitive data it may be undesirable. These tables describe every object in the database, thus, while users would not know what specific items of data are stored, they would be able to tell what kind of data existed. Conceivably, a malicious individual could make destructive use of this knowledge.
Before revoking general access to the tables, however, you must weigh the advantages of securing the information in them against the disadvantages of users being unable to retrieve the information they require. The catalog tables are an active dictionary facility, and help to maintain definitions, control information, and general information on data. For example, users can query them to find out what tables they have created, the names and data types of the columns in each of those tables, and any synonyms they have defined.
You might consider revoking PUBLIC access to only the SYSCOLSTATS table, which records the first- and second-most frequent values in the first column used by every index on every table in the database.
If you do decide to secure all the catalog tables, the easiest way to do this is to revoke the SELECT privilege from PUBLIC on them. You must be connected as user ID SQLDBA and have DBA authority. You can then grant authority on specific tables to specific users.
To revoke the SELECT privilege from PUBLIC on SYSTEM.SYSCATALOG, enter:
REVOKE SELECT ON SYSTEM.SYSCATALOG FROM PUBLIC
Before you revoke SELECT privileges from PUBLIC, you should also consider what impact there might be on existing applications. In particular, some applications may need to read a catalog table, so will fail if this authority is revoked. Naturally, in these cases you must grant the SELECT privilege to the creator of the program.
Note also that if the creator (the person who preprocessed the program) is not its sole runner, you must also specify the WITH GRANT OPTION clause for this person, in order to enable him or her to grant authority to other users to run the program.
User JULIE has created a program that accesses SYSTEM.SYSCATALOG, and she grants RUN authority to KATHY and BILL. If you revoke the SELECT privilege from PUBLIC, you can preserve KATHY's and BILL's authority to run JULIE's program by issuing:
GRANT SELECT ON SYSTEM.SYSCATALOG TO JULIE WITH GRANT OPTION
If you revoke the SELECT privilege from PUBLIC on a catalog table, and later wish to completely restore it, you should also specify the WITH GRANT OPTION clause.
To restore authority to PUBLIC on SYSTEM.SYSACCESS, enter:
GRANT SELECT ON SYSTEM.SYSACCESS TO PUBLIC WITH GRANT OPTION
Refer to the DB2 Server for VSE & VM SQL Reference manual for a description of the catalog tables.
There are two ways to audit security: by querying the catalog tables, or by having the database manager do a security audit trace.
If you simply want to know what security structures exist, the first method is sufficient. The catalog tables maintain a record of authorization privileges: who has what authority and from whom they received it. But they do not record information about the use of these privileges: for example, the number of unsuccessful attempts to access a resource, the number of accesses based strictly on DBA authority, or similar authorization use information. For this type of information, you must use a security audit trace.
Both ways of auditing security are discussed below.
The following are examples of queries you might enter against the catalog tables in security auditing:
SELECT NAME FROM SYSTEM.SYSUSERAUTH
WHERE AUTHOR=' '
The WHERE clause serves to eliminate any entries in SYSTEM.SYSUSERAUTH for program dependencies from the query result.
SELECT COUNT(*) FROM SYSTEM.SYSPROGAUTH
WHERE CREATOR = 'WALTERS'
AND PROGNAME = 'PAYROLL'
AND GRANTOR = 'BENNETT'
This query only counts user BENNETT's first-level grantees (those who received their authority directly from user BENNETT).
SELECT COUNT(*) FROM SYSTEM.SYSPROGAUTH
WHERE CREATOR = 'WALTERS'
AND PROGNAME = 'PAYROLL'
AND GRANTOR <> 'WALTERS'
SELECT COUNT(*) FROM SQLDBA.SYSUSERLIST
WHERE RESOURCEAUTH = 'Y'
AND DBAAUTH <> 'Y'
AND AUTHOR = ' '
SELECT COUNT(*) FROM SYSTEM.SYSTABAUTH
WHERE TCREATOR = 'JOHNSON'
AND TTNAME = 'EMPLOYEE'
AND GRANTOR <> 'JONES'
AND GRANTEETYPE = ' '
Here, the GRANTEETYPE = ' ' portion of the WHERE clause eliminates entries for programs.
SELECT * FROM SYSTEM.SYSTABAUTH
WHERE TCREATOR = 'PERSONNL'
AND TTNAME = 'EMPLOYEE'
AND SELECTAUTH = 'Y'
AND GRANTEETYPE = ' '
AND GRANTOR = 'LAPIS'
ORDER BY TIMESTAMP
Security audit tracing is one of the functions that can be performed using the trace facility. A security audit trace is unique in that it is not necessarily done for problem determination. Start a trace of the security audit function of the RDS component by using the TRACRDS initialization parameter. Alternatively, you can start it by issuing the TRACE command from the operator's console after the application server has been started.
For descriptions of the TRACRDS parameter, the TRACE operator command, the trace output records, and the utility that formats these records into readable output, see the DB2 Server for VSE & VM Operation manual.
In VM, you can direct the trace output to tape, to a CMS file, or to a memory area known as a trace buffer. However, if your installation uses the security audit trace frequently, you may want to direct the output to a CMS file. To do this, you must enter a CMS FILEDEF command before starting the application server, and supply particular responses to the prompts that come up when tracing is started. For descriptions of the FILEDEF command and the appropriate message responses, see the DB2 Server for VSE & VM Operation manual.
As with other traces, you can get two levels of information. Level 1 traces and records the following information:
Level 2 keeps track of all DB2 Server for VSE & VM authorization checks.
Table 21 shows each type of authorization verification that the
database manager does, and which results are traced.
Table 21. Information Recorded by a Security Audit Trace
| Type of Authorization Check | Result Traced at Level 2 | Result Traced at Level 1 |
|---|---|---|
| CONNECT | Y,I,N | Y,I,N |
| RUN | G,Y,D,N,P | D,N |
|
SELECT, INSERT, UPDATE, DELETE, ALTER, and INDEX | G,Y,D,N,P | D,N |
| RESOURCE | Y,N | N |
| REFERENCES | Y,N | D,N |
| DBA | D,N | D,N |
|
Grants of Special Privileges (DBA, CONNECT, RESOURCE, and SCHEDULE) | D,N | G,N |
| Grants of RUN Authority | G,N | |
| ||
For each result of an authorization check that is traced, the database manager creates a trace record in the same format as other kinds of trace records. These records are identical in format for all levels and types of authorization, and are written to the same (VSE) trace output file, or (VM) trace tape (or CMS file).
If a value does not apply for a specific occurrence, the database manager sets it to blanks. For example, a trace record for CONNECT does not contain the name of a resource (that is, a table name).
Each trace record contains (where applicable):
The Resource 2 field shows the column (where applicable) on checks of UPDATE authority. It can also contain a description of the reason that the database manager is checking a certain authority. For example, it might contain "ALTER PUB DBSPACE" on a check for the DBA authority needed to alter a PUBLIC dbspace. In this case, DBA would be the type of authorization being checked, while the Resource 2 field provides more information about why this authority is required.
When analyzing trace records, remember that many operations on views are restricted. These restrictions are reflected in the trace records generated during CREATE VIEW processing. When the database manager creates a view, it checks the user's authority on the base tables to determine what authority to give that user on the view. It also checks the view itself to see what operations cannot be performed on it. For example, because deletions are not allowed in views that involve a join, the authorization check for DELETE would return an N. The N shows that deletions are not allowed against the view; it does not necessarily imply that the creator is not authorized to delete from the base table.
Authorization checks during CREATE VIEW processing are traced, but only at level 2. The result field of the trace record indicates whether an authorization check is a result of CREATE VIEW processing. The CREATE VIEW indicator is the letter V following the usual result indicator. For example, a successful verification of SELECT authority on a base table produces a result value of YV -- yes during view creation. You can use this indicator to distinguish between normal authorization checks and those done during view creation.
| Note: | Tracing occurs during preprocessing and execution of programs, and during the
dynamic execution of statements in ISQL or DBS Utility.
Authorization traces for data manipulation operations in programs occur during preprocessing, not during execution. |
You can use the DBS Utility to load security audit trace records into a table. When the trace information is in a table, you can use SQL statements to answer questions such as:
Figure 21 shows a DB2 Server for VSE example DBS Utility job to create a security table and load trace records into it. In the example, the trace output file is on tape.
Figure 21. Loading Security Audit Records into a Table - DB2 Server for VSE
// JOB DATALOAD SECURITY AUDIT TRACE
// EXEC=PROC=DBNAME01
// EXEC=PROC=ARIS71PL
// TLBL ARITRAC
// EXEC ARISQLDS,SIZE=AUTO,PARM='SYSMODE=S,LOGMODE=Y,PROGNAME=ARIDBS'
COMMENT ' '
COMMENT ' ********************************* '
COMMENT ' * DATALOAD SECURITY AUDIT TRACE * '
COMMENT ' ********************************* '
COMMENT ' '
COMMENT ' ACQUIRE A DBSPACE(PRIVATE) '
COMMENT ' NAMED SECURITY '
COMMENT ' '
ACQUIRE PRIVATE DBSPACE NAMED SECURITY;
COMMENT ' '
COMMENT ' CREATE A TABLE IN THE PRIVATE DBSPACE '
COMMENT ' '
CREATE TABLE AUDIT_TAB(TRPOINT SMALLINT,
YEAR CHAR(2),
MONTH CHAR(2),
DAY CHAR(2),
TIME CHAR(8),
USERID CHAR(8),
GRANTEE CHAR(8),
RESOURCE1 CHAR(18),
RESOURCE2 CHAR(18),
OWNER CHAR(8),
AUTHTYPE CHAR(8),
RESULT CHAR(2),
EXTLUWID CHAR(35))
IN SECURITY;
COMMENT ' '
COMMENT ' LOAD DATA - (NOTE _ YOU MAY '
COMMENT ' WISH TO INTERCHANGE DAY/MONTH) '
COMMENT ' '
DATALOAD TABLE (AUDIT_TAB) IF POS (11-14)=-220659706
TRPOINT 7-8 FIXED
EXTLUWID 41-75 CHAR
YEAR 124-125 CHAR
MONTH 118-119 CHAR
DAY 121-122 CHAR
TIME 143-150 CHAR
USERID 168-175 CHAR
GRANTEE 193-200 CHAR
RESOURCE1 218-235 CHAR
RESOURCE2 253-270 CHAR
OWNER 288-295 CHAR
AUTHTYPE 313-320 CHAR
RESULT 338-339 CHAR
INFILE(ARITRAC PDEV(TAPE) BLKSZ(4096) RECFM(VB) RECSZ(384))
|
Figure 22 shows a DB2 Server for VM example of running the DBS Utility. The utility reads a CMS file (SECTAB DATA A), which contains statements to create a security audit table and load trace records into it. Before invoking the utility, ensure that the appropriate trace tape is mounted on virtual device 182.
Figure 22. Loading Security Audit Records into a Table - DB2 Server for VM
Command to Invoke the DBS Utility: FILEDEF TRACE1 TAP2 SL (RECFM VB BLOCK 4096 LRECL 384 EXEC SQLDBSU ID(SQLDBA) IN(SECTAB DATA A) PR(TERMINAL) SECTAB DATA A Contains: CONNECT user IDENTIFIED BY password;
COMMENT ' '
COMMENT ' ********************************* '
COMMENT ' * DATALOAD SECURITY AUDIT TRACE * '
COMMENT ' ********************************* '
COMMENT ' '
COMMENT ' ACQUIRE A DBSPACE(PRIVATE) '
COMMENT ' NAMED SECURITY '
COMMENT ' '
ACQUIRE PRIVATE DBSPACE NAMED SECURITY;
COMMENT ' '
COMMENT ' CREATE A TABLE IN THE PRIVATE DBSPACE '
COMMENT ' '
CREATE TABLE AUDIT_TAB(TRPOINT SMALLINT,
YEAR CHAR(2),
MONTH CHAR(2),
DAY CHAR(2),
TIME CHAR(8),
USERID CHAR(8),
GRANTEE CHAR(8),
RESOURCE1 CHAR(18),
RESOURCE2 CHAR(18),
OWNER CHAR(8),
AUTHTYPE CHAR(8),
RESULT CHAR(2),
EXTLUWID CHAR(35))
IN SECURITY;
COMMENT ' '
COMMENT ' LOAD DATA - (NOTE _ YOU MAY '
COMMENT ' WISH TO INTERCHANGE DAY/MONTH) '
COMMENT ' '
DATALOAD TABLE (AUDIT_TAB) IF POS (11-14) = -220659706
TRPOINT 7-8 FIXED
EXTLUWID 41-75 CHAR
YEAR 124-125 CHAR
MONTH 118-119 CHAR
DAY 121-122 CHAR
TIME 143-150 CHAR
USERID 168-175 CHAR
GRANTEE 193-200 CHAR
RESOURCE1 218-235 CHAR
RESOURCE2 253-270 CHAR
OWNER 288-295 CHAR
AUTHTYPE 313-320 CHAR
RESULT 338-339 CHAR
INFILE(TRACE1)
|
| Note: | The external logical unit of work identifier (EXTLUWID) is only used for conversations that use the DRDA protocol. |
If you have other trace functions active while you are tracing a security audit, include an input-record-id clause (IF POS (11-14) = -220659706) on the DATALOAD command to identify that only security audit trace records are to be loaded. This is necessary because the trace records from other functions are interspersed with those of the security audit trace.
When doing a security audit trace, it is usually to your advantage to trace the parser component at the same time. When you trace this component at level 1, the resultant trace records describe the SQL statement entered into the database manager. By using the timestamp in the trace records, you can correlate the input to the security audit trace records produced.
If you plan to load the security audit trace records into a table, you may want to print the parser trace records by using the trace formatter. If you are printing the security audit records, you may want to also print the parser records by specifying both the parser and security audit components for the trace formatter. An example producing such a listing is shown in Table 22 and Table 23.
In VM, if you directed the trace output to a CMS file (by issuing a CMS FILEDEF command), you can still use the DBS Utility to load the trace data into tables. To do this, enter the following CMS FILEDEF command before invoking the SQLDBSU EXEC:
FILEDEF ddname DISK filename filetype filemode (RECFM VB LRECL 384 BLOCK 4096
Notes:
In VSE, if, when starting the application server, you directed the trace output to disk, you must change the INFILE statement to:
INFILE(ARITRAC PDEV(DASD) BLKSZ(4088) RECFM(VB) RECSZ(384))
In addition, you must change the job control to identify the DASD SAM trace output file. For example:
// DLBL ARITRAC,'TRACE.FILE1' // EXTENT ,VSER01,1,0,301,120 // ASSGN SYS006,195
// DLBL ARITRAC,'TRACE.FILE1',0,VSAM,DISP=(,DELETE)
When DISP=(,DELETE), the VSAM file is deleted after it is read. If you do not want the file to be deleted, specify DISP=(,KEEP) or omit the DISP parameter.
The above examples would replace the TLBL statement in Figure 21.
Once you have loaded the security audit trace records into a table, you can enter SQL statements against them. This method may make viewing the records easier, but has a disadvantage in that any user who has DBA authority can change the table, and any tampering may make the data incorrect. You should always print the trace records and protect the trace tape to ensure that there is always a valid copy.
Figure 23 shows examples of typical security audit queries. Some of the records traced appear only at level 2; level 2 can generate a significant number of trace records. These queries are shown as they might appear as input to the DBS Utility.
Figure 23. Example Security Audit Queries
COMMENT '******************************************'
COMMENT ' SELECT ALL RECORDS FROM AUDIT TABLE '
COMMENT ' WHERE AUTHORIZATION WAS DENIED '
COMMENT '******************************************'
SELECT * FROM AUDIT_TAB WHERE RESULT='N';
COMMENT '******************************************'
COMMENT ' SELECT RECORDS FROM AUDIT TABLE '
COMMENT ' RECORDED BETWEEN 8 A.M. AND 12:30 P.M. '
COMMENT ' ON JUNE 29 '
COMMENT '******************************************'
SELECT * FROM AUDIT_TAB WHERE MONTH = '06'
AND DAY = '29' AND TIME BETWEEN '08:00:00' AND '12:30:00';
COMMENT '******************************************'
COMMENT ' SELECT RECORDS FROM AUDIT TABLE '
COMMENT ' RECORDED BETWEEN 12:30 P.M. AND 4:00 P.M.'
COMMENT ' ON JUNE 29 AND AUTHORIZED DUE TO DBAAUTH.'
COMMENT '******************************************'
SELECT * FROM AUDIT_TAB WHERE MONTH = '06'
AND DAY = '29' AND TIME BETWEEN '12:30:00'
AND '16:00:00' AND RESULT = 'D';
COMMENT '******************************************'
COMMENT ' SELECT CHECKS OF UPDATE AUTHORITY '
COMMENT ' AGAINST TABLE USER1.TAB1 '
COMMENT ' RECORDED BETWEEN 08:00 P.M. AND 4:00 P.M.'
COMMENT ' ON JUNE 29 NOT DUE TO VIEW CREATION. '
COMMENT ' (update checks traced at level 2) '
COMMENT '******************************************'
SELECT * FROM AUDIT_TAB WHERE MONTH = '06'
AND DAY = '29' AND TIME BETWEEN '08:00:00'
AND '16:00:00' AND OWNER = 'USER1'
AND RESOURCE1 = 'TAB1' AND AUTHTYPE = 'UPDATE'
AND RESULT NOT LIKE '%V';
COMMENT '******************************************'
COMMENT ' SELECT CHECKS OF UPDATE AUTHORITY '
COMMENT ' AGAINST TABLE USER1.TAB1 '
COMMENT ' RECORDED BETWEEN 08:00 P.M. AND 4:00 P.M.'
COMMENT ' ON JUNE 29 DUE TO DBAAUTH '
COMMENT ' (DBA activity traced at level 1 or 2) '
COMMENT '******************************************'
SELECT * FROM AUDIT_TAB WHERE MONTH = '06' AND
DAY = '29' AND TIME BETWEEN '08:00:00'
AND '16:00:00' AND OWNER = 'USER1'
AND RESOURCE1 = 'TAB1' AND AUTHTYPE = 'UPDATE'
AND RESULT = 'D';
COMMENT '******************************************'
COMMENT ' SELECT ALL GRANTS OF RUN AUTH ON '
COMMENT ' PROGRAMS USER1.DBD1 AND USER1.DBD3. '
COMMENT '******************************************'
SELECT * FROM AUDIT_TAB WHERE AUTHTYPE = 'RUN' AND
OWNER = 'USER1' AND RESOURCE1 = 'DBD1' OR
RESOURCE1 = 'DBD3';
|
A security audit trace, especially a level 2 one, can generate a large amount of information, and even more information is generated if you are tracing other components or functions at the same time. All of these records are placed in a single trace file. To print them selectively, you need to use the trace formatting utility.
This utility accepts control statements, which in VM, it reads from a CMS file. As it does not access the database manager, the latter does not have to be running for the trace formatter to work.
Table 22 shows an example of invoking the DB2 Server for VSE trace
formatter. The control statements print out all security audit trace
records and all parser trace records. The example also restricts the
output by date and time and is only for USER1.
Table 22. Printing Security Audit Records from the Trace File
// JOB RUN TRACE FORMATTER // TLBL ARITRAC,file-id <-- File-id of trace tape (optional ) // ASSGN SYS004,cuu <-- Address of tape unit // EXEC ARIMTRA,SIZE=AUTO SUBCOMP AU PA USERID USER1 DATE 06/29/85 TIME 12:00:00 23:00:00 /* /& |
Notes:
Table 23 shows an example of invoking the DB2 Server for VM trace formatter. The interactive SQLTRFMT EXEC supplied by IBM resides on the production minidisk (Q-disk) and invokes XEDIT to edit a CMS file called SQLTRFMT TRACE A. Use this exec to type in the control statements. When you file SQLTRFMT SQLTRACE A, the SQLTRFMT EXEC then asks where you want its output directed.
The control statements shown in Table 23 print all security audit (AU) trace records and all parser
(PA) trace records. They also restrict the output to those records
generated for a specific date (06/29/85), time (12:00:00 to
23:00:00), and user (USER1).
Table 23. Printing Security Audit Records from the Trace File
Invoking the Trace Formatter: SQLTRFMT |
Example Control Statements SUBCOMP AU PA USERID USER1 DATE 06/29/85 TIME 12:00:00 23:00:00 |
If you are directing your trace output to tape, then before invoking the trace formatter, ensure that the appropriate tape is mounted on virtual device 182. If you are directing it to a CMS file, you must enter a CMS FILEDEF command for the file before invoking SQLTRFMT. Use the same FILEDEF that you issued before you invoked SQLSTART (and initiated the trace). See the DB2 Server for VSE & VM Operation manual for the command format.
Complete instructions for using the utility are in the DB2 Server for VSE & VM Operation manual.