When a database is initially generated, there is only one user defined for it. This user, referred to as SQLDBA, has a special authority called "DBA" authority. Only someone with DBA authority can grant authorities to other users.
Granting any one of these authorities to a user who does not already have the CONNECT authority causes that user to be granted CONNECT authority. For example, if resource authority is granted to a user who currently has no authorities, the user will have both RESOURCE and CONNECT authority; if DBA authority is granted, the user will have DBA, CONNECT, SCHEDULE, and RESOURCE authorities.
The following information applies to the GRANT statement and to DB2 Server for VSE & VM application servers only. For a discussion of authorities for another application server, refer to that product's library.
| Note: | In discussions about granting authorities and privileges in this chapter, the "grantor" is defined as the user who preprocessed the program in which the GRANT statement appears. However, for dynamically defined GRANT statements, the grantor is determined at run time, based on the connected authorization ID. |
The System Authorities form of the GRANT statement allows a user having DBA authority to grant authorities to other users. See the DB2 Server for VSE & VM SQL Reference manual for the syntax.
The IDENTIFIED BY clause is optional when granting any of the authorities. If the clause is included, a password is added or changed for each user specified. If the password is the same as the one that currently exists for the user, the change has no real effect. If no passwords are given, none is assigned and previously assigned passwords are retained.
User IDs and passwords are limited to eight characters. They can be entered in double quotation marks to bypass checking under the rules of SQL identifier naming. Embedded blanks are not permitted, even in double quotation marks. If you specify IDENTIFIED BY, you must include a password for every user ID specified. The passwords and user IDs must correspond as indicated in the statement format above.
You can change your password by issuing the following form of the CONNECT statement which does not require special authority.
CONNECT ... IDENTIFIED BY ...
To do this, you need only have CONNECT authority, and may or may not have already been assigned a password.
Granting CONNECT to ALLUSERS is a special case that establishes implicit connect capability for all users in the system when operating under the CICS/VSE system or VM. ALLUSERS may be specified only once for DB2 Server for VM. (See Using VM Implicit Connect.)
(CICS/VSE connect considerations are discussed in Chapter 5, Preprocessing and Running a DB2 Server for VSE Program for DB2 Server for VSE.)
Granting an authority that a user already possesses has no additional effect, except for changing the password if it is specified.
You should not grant CONNECT authority to SYSTEM or PUBLIC. They are used internally.
| Note: | In discussions about revoking authorities and privileges in this chapter, the "revoker" is defined as the user who preprocessed the program in which the REVOKE statement appears. However, for dynamically defined REVOKE statements, the revoker is determined at run time, based on the connected authorization ID. |
The System Authorities form of the REVOKE statement allows a user having DBA authority to revoke an authority from any other users regardless of who originally granted it. The only exceptions are:
See the DB2 Server for VSE & VM SQL Reference manual for the syntax of the REVOKE statement.
If you enter REVOKE for an authority that a user does not have, the revocation is ignored.
Revoking a user's CONNECT authority causes any other authorities to be revoked as well, and the user is deleted from the catalog table SYSUSERAUTH. Revoking CONNECT authority does not cause objects owned by that user to be dropped; if they should be dropped, this can be done by a user with DBA authority.
Revoking DBA authority automatically causes all other authorities except CONNECT to be revoked. Revoking RESOURCE or SCHEDULE authority implies no other revocations.