A data engineer must let a BI service principal read only the Gold-layer schema sales.gold, while a separate data science group needs read access to sales.gold and sales.silver but must never see the raw sales.bronze schema. The catalog sales also contains other schemas that neither principal should access. Following least privilege, which grant strategy is correct?
Show answer & explanation
Correct answer: D
WHY D: Least privilege here means granting traversal (USE CATALOG on sales, USE SCHEMA on the needed schemas) plus SELECT scoped to exactly the schemas each principal requires, and never granting SELECT at the catalog level (which would be inherited by bronze and every other schema). This gives the BI principal gold alone and the data science group gold plus silver, with bronze and the other schemas unreachable.
WHY NOT A: This assumes data privileges alone are enough. Without USE CATALOG/USE SCHEMA traversal privileges the SELECT cannot be exercised, so this fails to grant working access; traversal privileges are part of least privilege, not an over-grant.
WHY NOT B: A catalog-wide SELECT inherits down to all schemas, including bronze and the other schemas; relying on targeted revokes to claw back access is error-prone and violates least privilege, and a revoke at the schema level does not remove a privilege inherited from the catalog grant.
WHY NOT C: Ownership confers full control and management rights over the object, not just read access, which is far more than least-privilege read. It also leaves the data science group without access to sales.gold.
WHY NOT E: The BI principal must not read silver; putting both principals in one group with silver and gold over-grants silver to the BI principal, violating least privilege.