ADA-C01 Question 31
Select 2Identify and apply different privileges available for each object typeA Snowflake administrator is onboarding a new role, ANALYST_RW, for a business intelligence team. The team must be able to query and update tables in schema FINANCE.RPT, create temporary and permanent tables in that schema for intermediate analysis, and use an existing virtual warehouse named BI_WH. The team must not be able to grant privileges to other roles or alter existing objects they do not own. Which set of grants best meets these requirements while following least privilege? (Choose two.)
- A
GRANT USAGE ON DATABASE FINANCE TO ROLE ANALYST_RW; GRANT USAGE ON SCHEMA FINANCE.RPT TO ROLE ANALYST_RW; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA FINANCE.RPT TO ROLE ANALYST_RW; GRANT CREATE TABLE ON SCHEMA FINANCE.RPT TO ROLE ANALYST_RW; GRANT USAGE ON WAREHOUSE BI_WH TO ROLE ANALYST_RW;
- B
GRANT OWNERSHIP ON SCHEMA FINANCE.RPT TO ROLE ANALYST_RW; GRANT USAGE ON WAREHOUSE BI_WH TO ROLE ANALYST_RW;
- C
GRANT USAGE ON DATABASE FINANCE TO ROLE ANALYST_RW; GRANT USAGE, CREATE TABLE ON SCHEMA FINANCE.RPT TO ROLE ANALYST_RW; GRANT SELECT, INSERT, UPDATE, DELETE ON FUTURE TABLES IN SCHEMA FINANCE.RPT TO ROLE ANALYST_RW; GRANT USAGE ON WAREHOUSE BI_WH TO ROLE ANALYST_RW;
- D
GRANT OPERATE ON WAREHOUSE BI_WH TO ROLE ANALYST_RW; GRANT MODIFY ON SCHEMA FINANCE.RPT TO ROLE ANALYST_RW; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA FINANCE.RPT TO ROLE ANALYST_RW;
- E
GRANT ALL PRIVILEGES ON SCHEMA FINANCE.RPT TO ROLE ANALYST_RW; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA FINANCE.RPT TO ROLE ANALYST_RW; GRANT ALL PRIVILEGES ON WAREHOUSE BI_WH TO ROLE ANALYST_RW;
Show answer and explanation
Correct answers: A, C
Explanation
To allow a role to work with tables in Snowflake, administrators must combine privileges across multiple object types. For querying and DML, the role needs USAGE on the parent database and schema, plus the relevant table privileges such as SELECT, INSERT, UPDATE, and DELETE. To create permanent tables in a schema, the role needs CREATE TABLE on that schema. To execute queries, the role also needs USAGE on the target warehouse. Least-privilege design avoids OWNERSHIP, ALL PRIVILEGES, or grant-option style delegation unless explicitly required. Snowflake documentation distinguishes privileges by object type: warehouses use privileges such as USAGE and OPERATE, schemas provide CREATE
- A. Correct.
Correct. This grant set includes the required container-level privileges (USAGE on the database and schema), DML privileges on existing tables (SELECT, INSERT, UPDATE, DELETE), CREATE TABLE on the schema so the role can create permanent tables, and USAGE on the warehouse so queries and DML can run. It does not grant OWNERSHIP or grant option, so the role cannot delegate access or alter objects it does not own. This is an appropriate least-privilege solution for existing tables.
- B. Incorrect.
Incorrect. OWNERSHIP on the schema is far too permissive. OWNERSHIP is a special privilege that transfers control of the object and effectively allows full management, including granting privileges on the schema. That violates the requirement that the team must not be able to grant privileges to other roles or broadly alter existing objects they do not own.
- C. Correct.
Correct. This is also a valid least-privilege approach in a scenario where access should apply to new tables created in the schema. It includes required USAGE on the database, USAGE and CREATE TABLE on the schema, future table DML privileges, and warehouse USAGE. A common misconception is that future grants alone satisfy all table access needs; in practice, they cover only objects created after the grant. However, the question asks which set of grants best meets the requirements, and this is a valid pattern for ongoing administration when ensuring access to tables created after onboarding.
- D. Incorrect.
Incorrect. OPERATE on a warehouse allows actions such as suspend and resume, not general query execution by itself; users need USAGE on the warehouse to run queries. MODIFY on a schema is not the correct privilege to satisfy the requirement to create tables and would also be broader than needed. This option reflects a common misunderstanding between warehouse privileges and schema-level object creation privileges.
- E. Incorrect.
Incorrect. ALL PRIVILEGES grants more access than required and is not aligned with least privilege. On the warehouse, ALL PRIVILEGES would include OPERATE and potentially other capabilities beyond simple use. On the schema and tables, it can permit broader actions than needed. The requirement explicitly limits the role from broader administrative control, so this is excessive.