Is there any hierarchy within schema permissions?

[Kenny92]

I have recently completed the Data Engineering with Databricks v3 course on the Partner Academy. Some of the quiz questions have me mixed up.

Specifically, I am wondering about this question from the "Manage Data Access with Unity Catalog" module.

I have gathered from submitting with different answers that the answer it is marking as correct is "GRANT CREATE ON SCHEMA customers TO `data.engineer@company.com`;" This has left me confused about a few things:

1. Does the CREATE schema permission implicitly include the ability to read (SELECT) the tables within? If so, I cannot find any note of this in the Databricks documentation.
2. Even if CREATE does also offer table read access, wouldn't it be useless in any case without also having USAGE? This would make the correct answer the first one ("GRANT USAGE CREATE ON SCHEMA customers TO `data.engineer@company.com`;")
3. And if CREATE doesn't also offer read access, then wouldn't the correct answer be the last one ("GRANT ALL PRIVILEGES ON SCHEMA customers TO `data.engineer@company.com`"), since granting all privileges would include SELECT? (Though this would be an unsatisfying answer, since the question states the team member merely needs "permission to view the tables" and this grants privileges well beyond that.

Perhaps the better way to ask this is to ignore all of the options provided...how would you accomplish the task? I would simply grant the team member USAGE and SELECT on the schema, which would apply to all current and future tables within. no?

[daniel_sahal]

@Kenny Shaevel​ 

I'm really confused with this question tbh. It might refer to the public preview of Unity Catalog.

With Privilege Model Version 1.0 IMO it should look like this:

GRANT USE_SCHEMA ON SCHEMA customers TO `data.engineer@company.com`;
GRANT SELECT ON SCHEMA customers TO `data.engineer@company.com`;

This privileges will allow `data.engineer@company.com` to use customers schema and read tables from it.

https://docs.databricks.com/data-governance/unity-catalog/manage-privileges/privileges.html

[Anonymous · Accepted solution]

@Kenny Shaevel​ :

Yes, there is a hierarchy of permissions within schema permissions in Databricks. Here is the order of permissions, from least to most permissive:

1. USAGE: Allows the user to see the schema and its contents, but not interact with them in any way.
2. CREATE: Allows the user to create objects (e.g. tables) within the schema, but not interact with existing objects.
3. ALTER: Allows the user to modify existing objects within the schema.
4. DROP: Allows the user to delete objects within the schema.
5. ALL PRIVILEGES: Grants all of the above permissions.

Regarding your specific questions:

1. No, the CREATE permission does not implicitly include the ability to read (SELECT) tables within the schema. However, a user with the CREATE permission can create tables with read permissions for specific users or groups.
2. You are correct that the USAGE permission is required for a user to interact with objects within the schema, so the correct answer would be "GRANT USAGE CREATE ON SCHEMA customers TO data.engineer@company.com".
3. Again, you are correct that granting ALL PRIVILEGES would include the SELECT permission, but it would grant more permissions than necessary for the task at hand.

In summary, the best approach would be to grant the team member USAGE and SELECT on the schema, which would allow them to view the tables within the schema without granting unnecessary permissions.

[Kenny92]

Thanks, Suteja! This is very clear.

Please also pass on to the appropriate team at Databricks that the quiz has the wrong answer marked.