SOL-C01 Question #34: Real Exam Question with Answer & Explanation

Question

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 (UDFs) within a specific schema 'ANALYTICS. However, you want to restrict the ability of `ANALYST' to grant UDF usage privileges to other roles. You also want to ensure that if a new user is granted the 'ANALYST' role in the future, they automatically have the necessary privileges to create UDFs. Which sequence of actions would best fulfill these requirements?

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

Explanation

Option E is the most appropriate. It grants 'CREATE FUNCTION' on 'FUTURE FUNCTIONS' in the 'ANALYTICS' schema, meaning any new UDFs created will automatically grant the privilege. This ensures future users with the 'ANALYST role can create UDFs. Granting USAGE' on the schema is necessary for the 'ANALYST' role to be able to access and interact with the schema, including creating UDFs. The use of 'FUTURE FUNCTIONS' grants is crucial for fulfilling the requirement of automatically granting privileges to new users. The USAGE on SCHEMA is necessary for analyst to use the database and to interact with the schema, so they can create UDFs. Option A is incorrect as it gives GRANT CREATE FUNCTION on schema, but not FUTURE functions inside that schema. Similarly Options B and D are also incorrect for this reason. Options B and D also unnecessarily grant USAGE on the schema to the DATA SCIENTIST. The data scientist already inherits privileges as part of hierarchy.

No community discussion yet for this question.