Security

22.	Which of the following is NOT a valid method of authentication that can be used by DB2 9? A. SERVER B. SERVER_ENCRYPT C. CLIENT D. DCS
23.	In a client-server environment, which two of the following can be used to verify passwords? A. System Catalog B. User ID/password file C. Client Operating System D. Communications layer E. Application Server
24.	A table named DEPARTMENT has the following columns: DEPT_ID DEPT_NAME MANAGER AVG_SALARY Which of the following is the best way to prevent most users from viewing AVG_SALARY data? A. Encrypt the table's data B. Create a view that does not contain the AVG_SALARY column C. Revoke SELECT access for the AVG_SALARY column from users who should not see AVG_SALARY data D. Store AVG_SALARY data in a separate table and grant SELECT privilege for that table to the appropriate users
25.	Which authority or privilege is granted by the DB2 Database Manager configuration file? A. CONNECT B. CONTROL C. SYSMAINT D. EXECUTE
26.	Which two of the following authorities allow a user to create a new database? A. SYSADMN B. SYSCTRL C. SYSMAINT D. DBADM E. CREATEDB
27.	Assuming USER1 has no authorities or privileges, which of the following will allow USER1 to create a view named VIEW1 that references two tables named TAB1 and TAB2? A. CREATEIN privilege on the database B. REFERENCES privilege on TAB1 and TAB2 C. CREATE_TAB privilege on the database D. SELECT privilege on TAB1 and TAB2
28.	Which of the following will allow user USER1 to change the comment associated with a table named TABLE1? A. GRANT UPDATE ON TABLE table1 TO user1 B. GRANT CONTROL ON TABLE table1 TO user1 C. GRANT ALTER ON TABLE table1 TO user1 D. GRANT REFERENCES ON TABLE table1 TO user1
29.	A table called DEPARTMENT has the following columns: DEPT_ID DEPT_NAME MANAGER Which of the following statements will ONLY allow user USER1 to modify the DEPT_NAME column? A. GRANT ALTER ON TABLE department TO user1 B. GRANT ALTER (dept_name) ON TABLE department TO user1 C. GRANT UPDATE ON TABLE department TO user1 D. GRANT UPDATE (dept_name) ON TABLE department TO user1
30.	An index named EMPID_X exists for a table named EMPLOYEE. Which of the following will allow user USER1 to drop the EMPID_X index? A. GRANT DROP ON INDEX empid_x TO user1 B. GRANT DELETE ON INDEX empid_x TO user1 C. GRANT INDEX ON TABLE employee TO user1 D. GRANT CONTROL ON INDEX empid_x TO user1
31.	On which two of the following database objects may the SELECT privilege be controlled? A. Sequence B. Nickname C. Schema D. View E. Index
32.	User USER1 wants to utilize an alias to remove rows from a table. Assuming USER1 has no authorities or privileges, which of the following privileges are needed? A. DELETE privilege on the table B. DELETE privilege on the alias C. DELETE privilege on the alias; REFERENCES privilege on the table D. REFERENCES privilege on the alias; DELETE privilege on the table
33.	User USER1 holds CONTROL privilege on table TABLE1. Which two of the following statements is user USER1 allowed to execute? A. GRANT CONTROL ON table1 TO user2 B. GRANT LOAD ON table1 TO user2 C. GRANT INSERT, UPDATE ON table1 TO user2 WITH GRANT OPTION D. GRANT BINDADD ON table1 TO PUBLIC E. GRANT ALL PRIVILEGES ON table1 TO PUBLIC
34.	A user wishing to invoke an SQL stored procedure that queries a table must have which of the following privileges? A. CALL privilege on the procedure; SELECT privilege on the table B. CALL privilege on the procedure; REFERENCES privilege on the table C. EXECUTE privilege on the procedure; SELECT privilege on the table D. EXECUTE privilege on the procedure; REFERENCES privilege on the table
35.	After the following SQL statement is executed: GRANT ALL PRIVILEGES ON TABLE employee TO USER user1 Assuming user USER1 has no other authorities or privileges, which of the following actions is user USER1 allowed to perform? A. Drop an index on the EMPLOYEE table B. Grant all privileges on the EMPLOYEE table to other users C. Alter the table definition D. Drop the EMPLOYEE table
36.	Which two of the following privileges is required in order to use a package? A. BINDADD B. BIND C. CONNECT D. EXECUTE E. USE
37.	Which of the following statements allows user USER1 to take the ability to create packages in a database named SAMPLE away from user USER2? A. REVOKE CONNECT ON DATABASE FROM user2 B. REVOKE CREATETAB ON DATABASE FROM user2 C. REVOKE BIND ON DATABASE FROM user2 D. REVOKE BINDADD ON DATABASE FROM user2
38.	Which of the following will provide user USER1 and all members of the group GROUP1 with the ability to perform DML, but no other operations on table TABLE1? A. GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE table1 TO user1 AND group1 B. GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE table1 TO USER user1, GROUP group1 C. GRANT ALL PRIVILEGES EXCEPT ALTER, INDEX, REFERENCES ON TABLE table1 TO USER user1, GROUP group1 D. GRANT CONTROL ON TABLE table1 TO user1 AND group1
39.	What does the following statement do? GRANT REFERENCES (col1, col2) ON TABLE table1 TO user1 WITH GRANT OPTION A. Gives user USER1 the ability to refer to COL1 and COL2 of table TABLE1 in queries, along with the ability to give this authority to other users and groups. B. Gives user USER1 the ability to refer to COL1 and COL2 of table TABLE1 in views, along with the ability to give this authority to other users and groups. C. Gives user USER1 the ability to define a referential constraint on table TABLE1 using columns COL1 and COL2 as the parent key of the constraint. D. Gives user USER1 the ability to define a referential constraint on table TABLE1 using columns COL1 and COL2 as the foreign key of the constraint.
40.	User USER1 is the owner of TABLE1. Assuming user USER1 only holds privileges for TABLE1, which of the following is the best way to remove all privileges user USER1 holds? A. REVOKE CONTROL ON table1 FROM user1 B. REVOKE ALL PRIVILEGES ON table1 FROM user1 C. REVOKE CONTROL ON table1 FROM user1; REVOKE ALL PRIVILEGES ON table1 FROM user1; D. REVOKE CONTROL, ALL PRIVILEGES ON table1 FROM user1
41.	User USER1 has the privileges needed to invoke a stored procedure named GEN_RESUME. User USER2 needs to be able to call the procedure - user USER1 and all members of the group PUBLIC should no longer be allowed to call the procedure. Which of the following statement(s) can be used to accomplish this? A. GRANT EXECUTE ON ROUTINE gen_resume TO user2 EXCLUDE user1, PUBLIC B. GRANT EXECUTE ON PROCEDURE gen_resume TO user2; REVOKE EXECUTE ON PROCEDURE gen_resume FROM user1, PUBLIC; C. GRANT CALL ON ROUTINE gen_resume TO user2 EXCLUDE user1 PUBLIC D. GRANT CALL ON PROCEDURE gen_resume TO user2; REVOKE CALL ON PROCEDURE gen_resume FROM user1, PUBLIC;
42.	A view named V.VIEW1 is based on a table named T.TABLE1. A user with DBADM authority issues the following statement: GRANT INSERT ON v.view1 TO user1 WITH GRANT OPTION Which of the following statements is USER1 authorized to execute? A. GRANT INSERT ON t.table1 TO user2 B. GRANT CONTROL ON v.view1 TO user2 C. GRANT ALL PRIVILEGES ON v.view1 TO user2 D. GRANT INSERT ON v.view1 TO user2
43.	What does the following statement do? GRANT ALTER ON SEQUENCE gen_empid TO user1 WITH GRANT OPTION A. Gives USER1 the ability to change the comment associated with a sequence named GEN_EMPID, along with the ability to give this CONTROL authority for the sequence to other users and groups. B. Gives USER1 the ability to change the values returned by the PREVIOUS_VALUE and NEXT_VALUE expressions associated with a sequence named GEN_EMPID, along with the ability to give CONTROL authority for the sequence to other users and groups. C. Gives USER1 the ability to change the comment associated with a sequence named GEN_EMPID, along with the ability to give this authority to other users and groups. D. Gives USER1 the ability to change the values returned by the PREVIOUS_VALUE and NEXT_VALUE expressions associated with a sequence named GEN_EMPID, along with the ability to give this authority to other users and groups.

Answers

22. 

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

23. 

The correct answers are C and E. Authentication is usually performed by an external security facility that is not part of DB2. This security facility may be part of the operating system (as is the case with AIX, Solaris, Linux, HP-UX, Windows 2000/NT, and many others), may be a separate add-on product (for example, Distributed Computing Environment (DCE) Security Services), or may not exist at all (which is the case with Windows 95, Windows 98, and Windows Millennium Edition). The combination of authentication types specified at both the client and the server determine which authentication method is actually used.

24. 

The correct answer is B. A view is a virtual table residing in memory that provides an alternative way of working with data that resides in one or more base tables. For this reason, views can be used to prevent access to select columns in a table. While it is possible to encrypt the data stored in the DEPARTMENT table or move the AVG_SALARY data to a separate table (you cannot revoke SELECT privilege for a column), the best solution is to create a view for the DEPARTMENT table that does not contain the AVG_SALARY column, revoke SELECT privilege on the DEPARTMENT table from users who are not allowed to see AVG_SALARY data, and grant SELECT privilege on the new view to users who need to access the rest of the data stored in the DEPARTMENT table.

25. 

The correct answer is C. The instance-level authorities available (SYSADM, SYSCTRL, SYSMAINT, and SYSMON) can only be given to a group of users and the names of the groups that have been given these authorities are recorded in the DB2 Database Manager configuration files that are associated with each instance.

26. 

The correct answers are A and B. Only users with System Administrator (SYSADM) authority or System Control (SYSCTRL) authority are allowed to create new databases.

27. 

The correct answer is D. In order to create a view, a user must hold appropriate privileges (at a minimum, SELECT privilege) on each base table the view references. CREATEIN is a schema privilege-not a database privilege; REFERENCES privilege allows a user to create and drop foreign key constraints that reference the table in a parent relationship; and CREATETAB privilege allows a user to create new tables in the database (there is no CREATE_TAB privilege).

28. 

The correct answer is C. The ALTER table privilege allows a user to add columns to the table, add or change comments associated with the table and/or any of its columns, create a primary key for the table, create a unique constraint for the table, create or drop a check constraint for the table, and create triggers for the table (provided the user holds the appropriate privileges for every object referenced by the trigger). The UPDATE table privilege allows a user to modify data in a table; the CONTROL table privilege allows a user to remove (drop) a table from a database and gives the user the ability to grant and revoke one or more table privileges (except the CONTROL privilege) to/from other users and groups; the REFERENCES table privilege allows a user to create and drop foreign key constraints that reference the table in a parent relationship.

29. 

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 user USER1 with the ability to change the data stored in any column of the UPDATE table.

30. 

The correct answer is D. The first GRANT statement (Answer A) is not valid because there is no DROP privilege; 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 user 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.

31. 

The correct answers are B and D. SELECT privilege is available for tables, views, and nicknames. The SELECT table privilege allows a user to retrieve data from a table, create a view that references the table, and run the EXPORT utility against the table; the SELECT view privilege allows a user to retrieve data from a view, create a second view that references the view, and run the EXPORT utility against the view; and the SELECT privilege for a nickname allows a user to retrieve data from the table or view within a federated data source that the nickname refers to.

32. 

The correct answer is A. The DELETE table privilege allows a user to remove rows of data from a table. Aliases are publicly referenced names, so no special authority or privilege is required to use them. However, tables or views referred to by an alias have still have the authorization requirements that are associated with these types of objects.

33. 

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.

34. 

The correct answer is C. The EXECUTE privilege, when granted, allows a user to invoke a routine (a routine can be a user-defined function, a stored procedure, or a method that can be invoked by several different users), create a function that is sourced from the routine (provided the routine is a function), and reference the routine in a Data Definition Language SQL statement (for example, CREATE VIEW and CREATE TRIGGER) statement. When the EXECUTE privilege is granted for a routine, any privileges needed by the routine must also be granted-in this case, the SELECT privilege is needed for the table the procedure will query.

35. 

The correct answer is C. The GRANT ALL PRIVILEGES statement gives USER1 the following privileges for the EMPLOYEE table: ALTER, SELECT, INSERT, UPDATE, DELETE, INDEX, and REFERENCES. To drop an index, USER1 would need CONTROL privilege on the index - not the table the index is based on; USER1 cannot grant privileges to other users because the WITH GRANT OPTION clause was not specified with the GRANT ALL PRIVILEGES statement used to give USER1 table privileges; and in order to drop the EMPLOYEE table, USER1 would have to have CONTROL privilege on the table-CONTROL privilege is not granted with the GRANT ALL PRIVILEGES statement.

36. 

The correct answers are C and D. Users must be able to connect to a database before they can use a package and they need to be able to execute the package once they are connected; therefore both CONNECT and EXECUTE privileges are required if the user does not have SYSADM authority

37. 

The correct answer is D. The BINDADD database privilege allows a user to create packages in a database by precompiling embedded SQL application source code files against the database and/or by binding application bind files to the database. The CONNECT database privilege allows a user to establish a connection to a database and the CREATETAB database privilege allows a user to create new tables in the database. The BIND privilege is a package privilege-not a database privilege-and it allows a user to rebind a package that has already been bound to a database.

38. 

The correct answer is B. The syntax used to grant table privileges is:

 GRANT [ALL <PRIVILEGES> |         Privilege <(ColumnName, ...)> , ...] ON TABLE [TableName] TO [ Recipient, ...] <WITH GRANT OPTION> 

where:

Privilege	Identifies one or more table privileges that are to be given to one or more users and/or groups. The following values are valid for this parameter: CONTROL, ALTER, SELECT, INSERT, UPDATE, DELETE, INDEX, and REFERENCES. (CONTROL privilege is not recognized by DB2 for iSeries and DB2 for zSeries.)
ColumnName	Identifies by name one or more specific columns that UPDATE or REFERENCES privileges are to be associated with. This option is only used when Privilege contains the value UPDATE or REFERENCES.
TableName	Identifies by name the table that all table privileges specified are to be associated with.
Recipient	Identifies the name of the user(s) and/or group(s) that are to receive the table privileges specified. The value specified for the Recipient parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC.

CONTROL privilege allows a user to remove (drop) a table from a database and gives the user the ability to grant and revoke one or more table privileges (except the CONTROL privilege) to/from other users and groups; granting ALL PRIVILEGES gives a user the right to perform other operations besides DML operations.

39. 

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.)

40. 

The correct answer is C. The owner of a table automatically receives CONTROL privilege, along with all other table privileges available for that table. If the CONTROL privilege is later revoked from the table owner, all other privileges that were automatically granted to the owner when the table was created 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 user USER1 holds on table TABLE1. If an attempt is made to try to combine both operations in a single statement as shown in answer D, an error will be generated.

41. 

The correct answer is B. The syntax used to grant the only stored procedure privilege available is:

 GRANT EXECUTE ON [RoutineName] |[PROCEDURE <SchemaName.> *] TO [Recipient, ...] <WITH GRANT OPTION> 

The syntax used to revoke the only stored procedure privilege available is:

 REVOKE EXECUTE ON [RoutineName | [PROCEDURE <SchemaName.> *] FROM [Forfeiter, ...] <BY ALL> RESTRICT 

where:

RoutineName	Identifies by name the routine (user-defined function, method, or stored procedure) that the EXECUTE privilege is to be associated with.
TypeName	Identifies by name the type in which the specified method is found.
SchemaName	Identifies by name the schema in which all functions, methods, or procedures-including those that may be created in the future-are to have the EXECUTE privilege granted on.
Recipient	Identifies the name of the user(s) and/or group(s) that are to receive the EXECUTE privilege. The value specified for the Recipient parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC.
Forfeiter	Identifies the name of the user(s) and/or group(s) that are to lose the routine privileges specified. The value specified for the Forfeiter parameter can be any combination of the following: <USER> [UserName], <GROUP> [GroupName], and PUBLIC.

Thus, the proper way to grant and revoke stored procedure privileges is by executing the GRANT EXECUTE … and REVOKE EXECUTE … statements.

42. 

The correct answer is D. The first GRANT statement (Answer A), when executed, would attempt to give user USER2 INSERT privilege on table T.TABLE1-since user 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, user USER1 does not have the authority needed to grant this privilege; and the third GRANT statement (Answer C), when executed, would attempt to give user USER2 every view privilege available (except the CONTROL privilege) on view V.VIEW1-since user USER1 does not have the authority needed to grant these privileges, this statement would also fail.

43. 

The correct answer is C. The ALTER sequence privilege allows a user to perform administrative tasks like restarting the sequence, changing the increment value for the sequence, and add or change the comment associated with the sequence. And when the GRANT statement is executed with the WITH GRANT OPTION clause specified, the user/group receiving privileges is given the ability to grant the privileges received to others. There is no CONTROL privilege for a sequence and the USAGE privilege is the sequence privilege that allows a user to use the PREVIOUS VALUE and NEXT VALUE expressions that are associated with the sequence. (The PREVIOUS VALUE expression returns the most recently generated value for the specified sequence; the NEXT VALUE expression returns the next value for the specified sequence.)