IBM DB2 11.1 Advanced DBA for Linux, UNIX, and Windows — Question 13

The following statements are executed:
GRANT CREATETAB, DATAACCESS, ON DATABASE TO ROLE role A;
GRANT CREATETAB, BINDADD ON DATABASE TO ROLE role B;
GRANT ROLE role A to USER db2inst3;
GRANT ROLE role B to USER db2inst3;
Which of the following is TRUE if this statement is executed later?
REVOKE ROLE roleB FROM USER db2inst3;

Answer options

• A. This statement will succeed and DB2 INST3 will still be able to create in the database
• B. This statement will succeed and DB2 INST3 only retains DATAACCESS privileges on the database
• C. This statement will fail as CREATETAB is granted to two different roles and must be revoked at the same time
• D. This statement will succeed with a warning since CREATETAB cannot be totally revoked. DB2INST3 will retain both roles

Correct answer: D

Explanation

The correct answer is D because revoking role B from db2inst3 does not completely remove the privileges associated with CREATETAB granted through role A. Options A and B are incorrect as they misinterpret the effect of the revoke operation, while option C is wrong because the revocation process does not require simultaneous revocation of privileges from multiple roles.