Inherited privileges

• 1: Enabling database inheritance
• 2: Enabling schema inheritance
• 3: Setting privilege inheritance on tables and views
• 4: Example usage: implementing inherited privileges

1 - Enabling database inheritance

By default, inherited privileges are enabled at the database level. You can toggle database-level inherited privileges with the DisableInheritedPrivileges configuration parameter.

To enable inherited privileges:

=> ALTER DATABASE database_name SET DisableInheritedPrivileges = 0;

To disable inherited privileges:

=> ALTER DATABASE database_name SET DisableInheritedPrivileges = 1;

2 - Enabling schema inheritance

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:

• CREATE TABLE/ALTER TABLE

• CREATE VIEW/ALTER VIEW

• ALTER MODEL

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.

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

See also

• Setting privilege inheritance on tables and views
• Granting and revoking privileges

3 - Setting privilege inheritance on tables and views

If inherited privileges are enabled for the database and a schema, privileges granted to the schema are automatically granted to all new tables and views in it. You can also explicitly exclude tables and views from inheriting schema privileges.

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.

Set privileges inheritance on tables and views

CREATE TABLE/ALTER TABLE and CREATE VIEW/ALTER VIEW can allow tables and views to inherit privileges from their parent schemas. For example, the following statements enable inheritance on schema s1, so new table s1.t1 and view s1.myview automatically inherit the privileges set on that schema as applicable:

=> CREATE SCHEMA s1 DEFAULT INCLUDE PRIVILEGES;
CREATE SCHEMA
=> GRANT USAGE, CREATE, SELECT, INSERT ON SCHEMA S1 TO PUBLIC;
GRANT PRIVILEGE
=> CREATE TABLE s1.t1 ( ID int, f_name varchar(16), l_name(24));
WARNING 6978:  Table "t1" will include privileges from schema "s1"
CREATE TABLE
=> CREATE VIEW s1.myview AS SELECT ID, l_name FROM s1.t1
WARNING 6978:  View "myview" will include privileges from schema "s1"
CREATE VIEW

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, however, are not affected:

=> CREATE SCHEMA s2;
CREATE SCHEMA
=> CREATE TABLE s2.t22 ( a int );
CREATE TABLE
...
=> ALTER SCHEMA S2 DEFAULT INCLUDE PRIVILEGES;
ALTER SCHEMA

In this case, inherited privileges were enabled on schema s2 after it already contained table s2.t22. To set inheritance on this table and other existing tables and views, you must explicitly set schema inheritance on them with ALTER TABLE and ALTER VIEW:

=> ALTER TABLE s2.t22 INCLUDE SCHEMA PRIVILEGES;

Exclude privileges inheritance from tables and views

You can use CREATE TABLE/ALTER TABLE and CREATE VIEW/ALTER VIEW to prevent table and views from inheriting schema privileges.

The following example shows how to create a table that does not inherit schema privileges:

=> CREATE TABLE s1.t1 ( x int) EXCLUDE SCHEMA PRIVILEGES;

You can modify an existing table so it does not inherit schema privileges:

=> ALTER TABLE s1.t1 EXCLUDE SCHEMA PRIVILEGES;

4 - Example usage: implementing inherited privileges

The following steps show how user Joe enables inheritance of privileges on a given schema so other users can access tables in that schema.

1. Joe creates schema schema1, and creates table table1 in it:

   
   =>\c - Joe
   You are now connected as user Joe
   => CREATE SCHEMA schema1;
   CRDEATE SCHEMA
   => CREATE TABLE schema1.table1 (id int);
   CREATE TABLE
   

2. Joe grants USAGE and CREATE privileges on schema1 to Myra:

   
   => GRANT USAGE, CREATE ON SCHEMA schema1 to Myra;
   GRANT PRIVILEGE
   

3. Myra queries schema1.table1, but the query fails:

   
   =>\c - Myra
   You are now connected as user Myra
   => SELECT * FROM schema1.table1;
   ERROR 4367: Permission denied for relation table1
   

4. Joe grants Myra SELECT ON SCHEMA privileges on schema1:

   
   =>\c - Joe
   You are now connected as user Joe
   => GRANT SELECT ON SCHEMA schema1 to Myra;
   GRANT PRIVILEGE
   

5. Joe uses ALTER TABLE to include SCHEMA privileges for table1:

   
   => ALTER TABLE schema1.table1 INCLUDE SCHEMA PRIVILEGES;
   ALTER TABLE
   

6. Myra's query now succeeds:

   
   =>\c - Myra
   You are now connected as user Myra
   => SELECT * FROM schema1.table1;
   id
   ---
   (0 rows)
   

7. Joe modifies schema1 to include privileges so all tables created in schema1 inherit schema privileges:

   
   =>\c - Joe
   You are now connected as user Joe
   => ALTER SCHEMA schema1 DEFAULT INCLUDE PRIVILEGES;
   ALTER SCHEMA
   => CREATE TABLE schema1.table2 (id int);
   CREATE TABLE
   

8. With inherited privileges enabled, Myra can query table2 without Joe having to explicitly grant privileges on the table:

   
   =>\c - Myra
   You are now connected as user Myra
   => SELECT * FROM schema1.table2;
   id
   ---
   (0 rows)