Enabling schema inheritance
Caution
Enabling inherited privileges with ALTER SCHEMA ... DEFAULT INCLUDE PRIVILEGES only affects newly created tables, views, and models.
This setting does not affect existing tables, views, and models.
By default, inherited privileges are disabled at the schema level. If inherited privileges are enabled at the database level, you can enable inheritance at the schema level with CREATE SCHEMA and ALTER SCHEMA. For example, the following statement creates the schema my_schema with schema inheritance enabled:
To create a schema with schema inheritance enabled:
=> CREATE SCHEMA my_schema DEFAULT INCLUDE PRIVILEGES;
To enable schema inheritance for an existing schema:
=> ALTER SCHEMA my_schema DEFAULT INCLUDE SCHEMA PRIVILEGES;
After schema-level privilege inheritance is enabled, privileges granted on the schema are automatically inherited by all newly created tables, views, and models in that schema. You can explicitly exclude a table, view, or model from privilege inheritance with the following statements:
For example, to prevent my_table from inheriting the privileges of my_schema:
=> ALTER TABLE my_schema.my_table EXCLUDE SCHEMA PRIVILEGES;
For information about which objects inherit privileges from which schemas, see INHERITING_OBJECTS.
For information about which privileges each object inherits, see INHERITED_PRIVILEGES.
Note
If inherited privileges are disabled for the database, enabling inheritance on its schemas has no effect. Attempts to do so return the following message:
Inherited privileges are globally disabled; schema parameter is set but has no effect.
Schema inheritance for existing objects
Enabling schema inheritance on an existing schema only affects newly created tables, views, and models in that schema. To allow an existing objects to inherit the privileges from their parent schema, you must explicitly set schema inheritance on each object with ALTER TABLE, ALTER VIEW, or ALTER MODEL.
For example, my_schema contains my_table, my_view, and my_model. Enabling schema inheritance on my_schema does not affect the privileges of my_table and my_view. The following statements explicitly set schema inheritance on these objects:
=> ALTER VIEW my_schema.my_view INCLUDE SCHEMA PRIVILEGES;
=> ALTER TABLE my_schema.my_table INCLUDE SCHEMA PRIVILEGES;
=> ALTER MODEL my_schema.my_model INCLUDE SCHEMA PRIVILEGES;
After enabling inherited privileges on a schema, you can grant privileges on it to users and roles with GRANT (schema). The specified user or role then implicitly has these same privileges on the objects in the schema:
=> GRANT USAGE, CREATE, SELECT, INSERT ON SCHEMA my_schema TO PUBLIC;
GRANT PRIVILEGE