SOL-C01 · Question #46
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 on
The correct answer is C. CREATE 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;. 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. Grant
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;
How the community answered
(26 responses)- A8% (2)
- B4% (1)
- C85% (22)
- D4% (1)
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.
Topics
Community Discussion
No community discussion yet for this question.