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

Question

You have a requirement to implement a role hierarchy in Snowflake. The `DATA ADMIN' role should be able to manage all data-related objects within a specific database, while the `REPORTING ROLE should only be able to query data within specific schemas. Which of the following SQL statements correctly demonstrates how to achieve this, considering that the `DATA ADMIN' role already exists? A. B. C. D. E.

Options

• ACREATE ROLE REPORTING_ROLE; GRANT ROLE REPORTING_ROLE TO ROLE DATA_ADMIN; GRANT USAGE ON DATABASE my_database TO ROLE DATA_ADMIN; GRANT ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE my_database TO ROLE DATA_ADMIN; GRANT SELECT ON ALL TABLES IN SCHEMA my_database.my_schema TO ROLE REPORTING_ROLE;
• BCREATE ROLE REPORTING_ROLE; GRANT ROLE DATA_ADMIN TO ROLE REPORTING_ROLE; GRANT USAGE ON DATABASE my_database TO ROLE DATA_ADMIN; GRANT ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE my_database TO ROLE DATA_ADMIN; GRANT SELECT ON ALL TABLES IN SCHEMA my_database.my_schema TO ROLE REPORTING_ROLE;
• CCREATE ROLE REPORTING_ROLE; GRANT USAGE ON DATABASE my_database TO ROLE DATA_ADMIN; GRANT ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE my_database TO ROLE DATA_ADMIN; GRANT USAGE ON SCHEMA my_database.my_schema TO ROLE REPORTING_ROLE; GRANT SELECT ON ALL TABLES IN SCHEMA my_database.my_schema TO ROLE REPORTING_ROLE;
• DCREATE ROLE REPORTING_ROLE; GRANT USAGE ON DATABASE my_database TO ROLE DATA_ADMIN; GRANT CREATE SCHEMA ON DATABASE my_database TO ROLE DATA_ADMIN; GRANT SELECT ON ALL TABLES IN SCHEMA my_database.my_schema TO ROLE REPORTING_ROLE;
• ECREATE ROLE REPORTING_ROLE; GRANT OWNERSHIP ON DATABASE my_database TO ROLE DATA_ADMIN; GRANT USAGE ON SCHEMA my_database.my_schema TO ROLE REPORTING_ROLE; GRANT SELECT ON ALL TABLES IN SCHEMA my_database.my_schema TO ROLE REPORTING_ROLE;

Explanation

Option B correctly implements the role hierarchy and grants the necessary privileges. By granting REPORTING ROLE' to 'DATA_ADMIW, 'DATA_ADMIN' inherits the privileges of 'REPORTING_ROLE. Granting 'USAGE on the database to 'DATA_ADMIN' allows the role to access the database. Granting SELECT on all tables in the reporting schema to `REPORTING ROLE' allows the role to query the data. Options C and D use FUTURE GRANTS which are applied to new objects, not existing ones. Option A grants ownership, which is too much for a reporting role. Option E gives all privileges which are again excessive.

No community discussion yet for this question.