Enabling schema inheritance
Caution
Enabling inherited privileges with ALTER SCHEMA ... DEFAULT INCLUDE PRIVILEGES only affects newly created tables and views.
This setting does not affect already-existing tables and views.
By default, inherited privileges are disabled at the schema level. If inherited privileges are enabled for the database, you can enable inheritance of schema privileges by its tables and views, with CREATE SCHEMA and ALTER SCHEMA. Unless explicitly excluded, privileges granted on the schema are automatically inherited by all new tables and views in it.
For information about which tables and views inherit privileges from which schemas, see INHERITING_OBJECTS.
For information about which privileges each table or view inherits, see the 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.
Enabling inheritance of schema privileges has no effect on existing tables and views. You must explicitly set schema inheritance on them with ALTER TABLE and ALTER VIEW. You can also explicitly exclude tables and views from inheriting schema privileges with CREATE TABLE/ALTER TABLE, and CREATE VIEW/ALTER VIEW, respectively.
You can enable schema privilege inheritance during schema creation with the following statement:
=> CREATE SCHEMA s1 DEFAULT INCLUDE PRIVILEGES;
If the schema already exists, you can use ALTER SCHEMA to have all newly created tables and views inherit the privileges of the schema. Tables and views created on the schema before this statement are not affected:
=> ALTER SCHEMA s1 DEFAULT INCLUDE PRIVILEGES;
After enabling inherited privileges on a schema, you can grant privileges on it to users and roles with GRANT (schema):
=> GRANT USAGE, CREATE, SELECT, INSERT ON SCHEMA S1 TO PUBLIC;
GRANT PRIVILEGE