A data engineer needs to grant SELECT privileges on all tables within a newly created schema named 'SALES DATA' to a role called 'ANALYST ROLE'. However, new tables may be added to this schema in the

Question

Options

• AGrant SELECT privilege to ANALYST ROLE directly on each table using GRANT SELECT ON
• BGrant the USAGE privilege on the database containing the schema and SELECT privilege on
• CCreate a custom role that inherits from 'ANALYST ROLE and grant 'SELECT privilege on all tables
• DGrant 'SELECT privilege to the PUBLIC role on all tables within the 'SALES_DATX schema.
• EUse a stored procedure to automatically grant the SELECT privilege to 'ANALYST ROLE

• A
  4% (2)
• B
  58% (32)
• C
  11% (6)
• D
  24% (13)
• E
  4% (2)

Explanation

Option B is correct because it follows the principle of least privilege while leveraging future grants - granting USAGE on the database and schema gives ANALYST_ROLE the ability to see and navigate those objects, and pairing it with GRANT SELECT ON ALL TABLES IN SCHEMA 'SALES_DATA' plus GRANT SELECT ON FUTURE TABLES IN SCHEMA 'SALES_DATA' ensures both existing and newly added tables are covered automatically without manual intervention.

Why the distractors fail:

• A requires manually re-granting privileges every time a new table is added - not scalable and misses future tables entirely.
• C misunderstands role inheritance; a child role inherits from its parent, not the other way around, and this still doesn't solve the future-table problem.
• D grants access to the PUBLIC role, which every user belongs to by default - a serious security violation that over-exposes data.
• E relies on a stored procedure that must be manually triggered or scheduled; it's neither automatic nor the platform's intended mechanism for this use case.

Memory tip: Think "USAGE + FUTURE" - you always need USAGE to unlock navigation through the hierarchy (database → schema), and FUTURE TABLES is the keyword that makes permissions self-maintaining. If a question mentions "automatically inherit," your answer almost certainly involves a future grant.

No community discussion yet for this question.