Setting Privileges 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.
Set Privileges Inheritance on Tables and Views
CREATE TABLE
/ALTER TABLE
and CREATE VIEW
/ALTER VIEW
can enable tables and views, respectively, to inherit schema privileges. 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
Both CREATE statements omit the clause INCLUDE SCHEMA PRIVILEGES
, so they return a warning message that the new objects will inherit schema privileges. CREATE statements that include this clause do not return a warning message.
You can enable inheritance on existing schemas with ALTER SCHEMA. For example:
=> 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;