Grant Inherited Privileges

Grant inherited privileges at the schema level. When inherited privileges is enabled, all privileges granted to the schema are automatically granted to all newly created tables or views in the schema. Existing tables or views remain unchanged when you alter the schema to include or exclude inherited privileges.

By default, inherited privileges are enabled at the database level and disabled at the schema level (unless you indicate otherwise while running CREATE SCHEMA). See Enable or Disable Inherited Privileges at the Database Level for more information. To apply inherited privileges, you must meet one of the following conditions:

• Be the owner of the object
• Be a superuser

Inherit Privileges on a Schema

Use the CREATE SCHEMA or ALTER SCHEMA SQL statements to apply inherited privileges to a schema. The tables and views in that schema then inherit any privileges granted to the schema by default.

This example shows how to create a new schema with inherited privileges. The DEFAULT parameter sets the default behavior so that all new tables and views created in this schema automatically inherit the schema's privileges:

This example shows how to modify an existing schema to enable inherited privileges:

The following message appears when you specify INCLUDE PRIVILEGES while Inherited privileges is disabled at the database level:

See Enable or Disable Inherited Privileges at the Database Level to enable Inherited Privileges at the database level.

Inherit Privileges on a Table or Flex Table

You can specify an individual table or flex table to inherit privileges from the schema. Use CREATE TABLE or ALTER TABLE SQL statements to enable inherited privileges for a table. The table-level flag takes priority over the schema flag, while the database knob takes priority over both.

This example shows creating a new table with inherited privileges:

This example shows how to modify an existing table to enable inherited privileges:

If you run CREATE TABLE, CREATE TABLE LIKE, or CREATE TABLE AS SELECT in a schema with inherited privileges set, the following informational warning appears:

Note that this message does not appear when you add the INCLUDE SCHEMA PRIVILEGES statement.

Exclude Privileges on a Table

You can exclude a table in a schema with inherited privileges so that table does not inherit the schema's privileges. Use CREATE TABLE or ALTER TABLE SQL statements to exclude inherited privileges for a table.

This example shows creating a new table and excluding schema privileges:

This example shows how to modify an existing table to exclude inherited privileges:

Include Privileges on a View

You can specify a View to inherit privileges from the schema. Use CREATE VIEW or ALTER VIEW SQL statements to enable inherited privileges for a view.

This example shows creating a view with inherited privileges enabled:

This example shows how to modify an existing view to enable inherited privileges:

Exclude Privileges on a View

You can exclude a view in a schema with inherited privileges so that view does not inherit the schema's privileges. Use CREATE VIEW or ALTER VIEW SQL statements to exclude inherited privileges for a view.

This example shows creating a new view and excluding schema privileges:

This example shows how to modify an existing view to exclude inherited privileges: