DB2 Universal Database V8.1 Certification Exam 700 Study Guide

The correct answer is D. In Version 8.x of DB2 UDB, the following authentication types are available: SERVER, SERVER_ENCRYPT, CLIENT, KERBEROS, and KRB_SERVER_ENCRYPT. (Although DCS was a valid method of authentication in DB2 UDB Version 7.x, it is no longer supported in Version 8.x.)

The correct answer is B. The CONTROL privilege gives USER1 the ability to do everything with the EMPLOYEE table (alter the table definition, retrieve data, insert data, update data, delete data, create indexes, define referential constraints, and grant any combination of table privileges to others); the UPDATE privilege only allows USER1 to modify existing data in the EMPLOYEE table; and the INSERT WITH GRANT OPTION allows USER1 to add data to the EMPLOYEE table and to grant that privilege to other users/groups.

The correct answers are C and E. Authentication is performed by an external security facility that is not part of DB2 UDB, so answers A and D are automatically eliminated. The security facility used to authenticate users is often part of the operating system and the combination of authentication types specified at both the client and the server determine which authentication method is actually used.

The correct answer is D. The first GRANT statement (answer A) provides USER1 with the ability to alter the table definition for the DEPARTMENT table; the second GRANT statement (answer B) is not valid because you can only specify column names with the UPDATE and REFERENCES privilege; and the third GRANT statement (answer C) provides USER1 with the ability to change the data stored in any column of the UPDATE table.

The correct answer is D. The first GRANT statement (answer A) provides USER1 with the ability to delete rows from the EMPLOYEE table; the second GRANT statement (answer B) is not valid because DELETE is not an index privilege (DELETE is a table or view privilege); and the third GRANT statement (answer C) provides USER1 with the ability to create indexes for the EMPLOYEE table. The only thing that a person who has CONTROL privilege for an index can do with that index is delete (drop) it.

The correct answers are C and E. The first GRANT statement (answer A) is not valid because only users with System Administrator (SYSADM) authority or Database Administrator (DBADM) authority are allowed to explicitly grant CONTROL privilege on any object; the second GRANT statement (answer B) is not valid because LOAD is not a table privilege (LOAD is a database privilege); and the fourth GRANT statement (answer C) is not valid because BINDADD is not a table privilege (BINDADD is a database privilege). However, a user with CONTROL privilege on a table can grant any table privilege (except the CONTROL privilege), along with the ability to give that privilege to other users and/or groups to anyone ”including the group PUBLIC.

The correct answer is B. The first GRANT statement (answer A) is not valid because only users with System Administrator (SYSADM) authority or Database Administrator (DBADM) authority are allowed to explicitly grant CONTROL privilege on any object; the third GRANT statement (answer C) is not valid because CREATE_EXTERNAL_ROUTINE is not a table privilege (CREATE_EXTERNAL_ROUTINE is a database privilege); and the last GRANT statement (answer D) is not valid because LOAD is not a table privilege (LOAD is a database privilege).

The correct answers are B and E. The first and third GRANT statements (answers A and C) are not valid because USE is not a routine privilege (USE is a tablespace privilege); and the fourth GRANT statement (answer D) is not valid because U.UDF1 is a user-defined function ”not a package (this GRANT statement is attempting to grant package privileges on a function and will fail).

The correct answer is C. The REFERENCES table privilege allows a user to create and drop foreign key constraints that reference a table in a parent relationship. This privilege can be granted for the entire table or limited to one or more columns within the table, in which case only those columns can participate as a parent key in a referential constraint. (This particular GRANT statement also gives USER1 the ability the ability to give the REFERENCES privilege for columns COL1 and COL2 to other users and groups.)

The correct answer is C. The owner of a table automatically receives CONTROL privilege, along with all other available table-level privileges, for that table. If the CONTROL privilege is later revoked from the table owner, all other privileges that were automatically granted to the owner for that particular table are not automatically revoked. Instead, they must be explicitly revoked in one or more separate operations. Therefore, both REVOKE statements shown in answer C must be executed in order to completely remove all privileges USER1 holds on TABLE1 since they are the table owner.

The correct answer is D. The first GRANT statement (answer A), when executed, would attempt to give USER2 INSERT privilege on table T.TABLE1 ”since USER1 does not have the authority needed to grant this privilege, this statement would fail; the second GRANT statement (answer B) is not valid because only users with System Administrator (SYSADM) authority or Database Administrator (DBADM) authority are allowed to explicitly grant CONTROL privilege on any object ”again, USER1 does not have the authority needed to grant this privilege; and the third GRANT statement (answer C), when executed, would attempt to give USER2 every privilege (except the CONTROL privilege) on view V.VIEW1 ”since USER1 does not have the authority needed to grant these privileges, this statement would also fail.

The correct answer is D. The first and second GRANT statements (answers A and B) are not valid because "ALL USERS" is not a valid clause of the GRANT statement; and the third GRANT statement (answer C) is not valid because it gives the group PUBLIC every table privilege available (except the CONTROL privilege). The last GRANT statement is correct because it only gives the group PUBLIC (all users) the privileges needed to execute Data Definition Language (DDL) statements against the table T.TABLE1.