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, through configuration parameter disableinheritedprivileges. 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 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.

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

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)