Privileges and the reader and contributor pattern

Grant to the schema, through two groups

You now have a namespace and identities. This lesson connects them. The pattern we use on every platform is short: for every schema, one reader group and one contributor group, and a group as owner. People get access by joining a group, and nobody gets a grant of their own.

We cover the privileges you need, how inheritance works, and why the schema is the right level. We do not cover metastore-level privileges in depth.

The privileges that matter

The Unity Catalog documentation defines each privilege. These are the ones you use daily.

Two points trip up most teams. First, usage privileges are required in addition to the data privilege. A group with SELECT on a table but no USE SCHEMA sees nothing. Second, MODIFY requires SELECT, so a contributor must also be a reader.

Inheritance: why the schema is the level

Privileges granted on a catalog or schema apply to its current and future child objects. The documentation gives the example that granting SELECT on a schema covers every existing and new table and view in it. The same applies to MODIFY, READ VOLUME, WRITE VOLUME and EXECUTE. MANAGE on a container also gives MANAGE on its children. The documentation also notes that inheritance requires privilege model version 1.0, so older metastores may need an upgrade first.

That behaviour makes the schema the natural level for grants:

• A grant on a table needs repeating for every new table, and people forget.
• A grant on a catalog is wide: it reaches every schema, including the ones you add next year.
• A grant on a schema matches a layer or a subject area. A new table in gold is covered the moment it exists.

Grant on a table or volume only when one object has a different audience from the rest of its schema, such as a table with salary data. Our standard adds a table-level group for those cases.

The reader and contributor pattern

For every schema, create two groups.

Pipelines that write silver are members of the silver contributor group and the bronze reader group. Analysts are members of the gold reader group. Nobody except the ingestion principal is a member of any bronze contributor group. The layers of lesson 1 and the groups line up one to one, so the access model explains itself.

Here is the SQL for one schema. Group names follow the convention from lesson 2.

-- Reader: may see and query everything in prd_finance.gold, present and future.
GRANT USE CATALOG ON CATALOG prd_finance              TO `grp-uc-prd-finance-gold-reader`;
GRANT USE SCHEMA  ON SCHEMA  prd_finance.gold         TO `grp-uc-prd-finance-gold-reader`;
GRANT SELECT      ON SCHEMA  prd_finance.gold         TO `grp-uc-prd-finance-gold-reader`;
GRANT READ VOLUME ON SCHEMA  prd_finance.gold         TO `grp-uc-prd-finance-gold-reader`;
 
-- Contributor: reads like the reader, and may also create and change tables and files.
GRANT USE CATALOG  ON CATALOG prd_finance             TO `grp-uc-prd-finance-gold-contributor`;
GRANT USE SCHEMA   ON SCHEMA  prd_finance.gold        TO `grp-uc-prd-finance-gold-contributor`;
GRANT SELECT       ON SCHEMA  prd_finance.gold        TO `grp-uc-prd-finance-gold-contributor`;
GRANT MODIFY       ON SCHEMA  prd_finance.gold        TO `grp-uc-prd-finance-gold-contributor`;
GRANT CREATE TABLE ON SCHEMA  prd_finance.gold        TO `grp-uc-prd-finance-gold-contributor`;
GRANT WRITE VOLUME ON SCHEMA  prd_finance.gold        TO `grp-uc-prd-finance-gold-contributor`;
 
-- Ownership belongs to a group.
ALTER SCHEMA prd_finance.gold OWNER TO `grp-uc-prd-finance-gold-owner`;

Run grants like this through your pipeline, from code. In lesson 7 the same pattern becomes a Terraform module, so nobody types a GRANT by hand in production.

Inspect and revoke

SHOW GRANTS ON SCHEMA prd_finance.gold;                    -- everything granted on this schema
SHOW GRANTS `grp-uc-prd-finance-gold-reader` ON SCHEMA prd_finance.gold;
 
REVOKE CREATE TABLE ON SCHEMA prd_finance.gold FROM `grp-uc-prd-finance-gold-contributor`;

The documentation notes that REVOKE succeeds even if the privilege was never granted, so it is safe to run in cleanup scripts, and equally safe to mistype. Read the output of SHOW GRANTS afterwards.

Sensitive tables need their own audience

Suppose gold.payroll must be visible to HR only. Keep the schema reader grant for general tables, and either place payroll in its own schema, such as gold_hr, or give it a table-level group. A separate schema is cleaner: the grants stay at schema level, and the inheritance works for you. If you must keep payroll in gold, the reader grant on the schema would expose it. That is why we design the schema boundaries around audiences, not only around layers.

Review access every quarter

Remove permissions nobody uses. Once a quarter, list the members of each group and the grants of each schema, and ask the owner to confirm both. Because access flows through groups, the review is a list of group members, not thousands of individual grants. System tables help (lesson 5), since they show what a group actually used.

Common mistakes

• Missing USE SCHEMA. The classic "I cannot see my table".
• Granting to a person "just for today". It stays for years.
• Grants on the catalog when a schema was meant.
• ALL PRIVILEGES as a shortcut. It grants more than the job needs and hides intent.
• Owner is the engineer who created the object. Change ownership to the group.

Summary

Grant on the schema, to two groups per schema, and let inheritance cover future tables. Remember the usage privileges. Give ownership to a group. Handle exceptions with separate schemas or table-level groups, and review quarterly.

Next lesson: finer control inside a table with row filters and column masks.