Which role(s) can alter or drop table XYZ?

The following SQL command was executed:

Use role SECURITYADMIN;

Grant ownership

On future tables

In schema PROD. WORKING

To role PROD_WORKING_OWNER;

Grant role PROD_WORKING_OWNER to role SYSADMIN;

Use role ACCOUNTADMIN;

Create table PROD.WORKING.XYZ (value number);

Which role(s) can alter or drop table XYZ?
A . Because ACCOUNTADMIN created the table, only the ACCOUNTADMIN role can alter or drop table XYZ.
B . SECURITYADMIN, SYSADMIN, and ACCOUNTADMIN can alter or drop table XYZ.
C . PROD_WORKING_OWNER, ACCOUNTADMIN, and SYSADMIN can alter or drop table XYZ.
D . Only the PROD_WORKING_OWNER role can alter or drop table XYZ.

View Answer

Answer: C

Explanation:

According to the GRANT OWNERSHIP documentation, the ownership privilege grants full control over the table and can only be held by one role at a time. However, the current owner can also grant the ownership privilege to another role, which transfers the ownership to the new role. In this case, the SECURITYADMIN role granted the ownership privilege on future tables in the PROD.WORKING schema to the PROD_WORKING_OWNER role. This means that any table created in that schema after the grant statement will be owned by the PROD_WORKING_OWNER role. Therefore, the PROD_WORKING_OWNER role can alter or drop table XYZ, which was created by the ACCOUNTADMIN role in the PROD.WORKING schema. Additionally, the ACCOUNTADMIN role can also alter or drop table XYZ, because it is the top-level role that has all privileges on all objects in the account. Furthermore, the SYSADMIN role can also alter or drop table XYZ, because it was granted the PROD_WORKING_OWNER role by the SECURITYADMIN role. The SYSADMIN role can activate the PROD_WORKING_OWNER role and inherit its privileges, including the ownership privilege on table XYZ. The SECURITYADMIN role cannot alter or drop table XYZ, because it does not have the ownership privilege on the table, nor does it have the PROD_WORKING_OWNER role.