A data warehouse has a role hierarchy where 'ANALYST' reports to 'DATA SCIENTIST' and 'DATA SCIENTIST' reports to 'DATA ENGINEER'. The 'ANALYST' role needs to create and use user-defined functions (UD

Question

Options

• AGRANT CREATE FUNCTION ON SCHEMAANALYTICS TO ROLE ANALYST, GRANT USAGE
• BGRANT CREATE FUNCTION ON SCHEMAANALYTICS TO ROLE ANALYST, GRANT USAGE
• CGRANT CREATE FUNCTION ON FUTURE FUNCTIONS IN SCHEMAANALYTICS TO ROLE
• DGRANT CREATE FUNCTION ON FUTURE FUNCTIONS IN SCHEMAANALYTICS TO ROLE
• EGRANT CREATE FUNCTION ON FUTURE FUNCTIONS IN SCHEMAANALYTICS TO ROLE

• A
  11% (4)
• B
  22% (8)
• C
  3% (1)
• D
  8% (3)
• E
  57% (21)

Explanation

Option E correctly combines two critical elements: using FUTURE FUNCTIONS grants and omitting WITH GRANT OPTION. The FUTURE FUNCTIONS clause ensures that when new UDFs are created in the ANALYTICS schema, the ANALYST role automatically receives usage privileges - satisfying the requirement that future users granted ANALYST inherit necessary access without manual re-granting. By omitting WITH GRANT OPTION, the ANALYST role can use UDFs but cannot propagate that privilege to other roles, which directly enforces the restriction requirement.

Options A and B likely include WITH GRANT OPTION, which violates the restriction on ANALYST granting UDF usage to others. Options C and D likely use FUTURE FUNCTIONS correctly but either include WITH GRANT OPTION or are missing the schema USAGE grant needed to access the schema itself - both of which break one of the stated requirements.

Memory tip: Think "FUTURE = forward-looking inheritance, no GRANT OPTION = end of the privilege chain." If a question mentions automatic privileges for future objects or future role members, FUTURE GRANTS is almost always part of the answer. If it says restrict re-granting, that means no WITH GRANT OPTION.

No community discussion yet for this question.