Database privileges

• 1: Ownership and implicit privileges
• 2: Inherited privileges
• 2.1: Enabling database inheritance
• 2.2: Enabling schema inheritance
• 2.3: Setting privilege inheritance on tables and views
• 2.4: Example usage: implementing inherited privileges
• 3: Default user privileges
• 4: Effective privileges
• 5: Privileges required for common database operations
• 6: Database object privileges
• 7: Granting and revoking privileges
• 7.1: Superuser privileges
• 7.2: Schema owner privileges
• 7.3: Object owner privileges
• 7.4: Granting privileges
• 7.5: Revoking privileges
• 7.6: Privilege ownership chains
• 8: Modifying privileges
• 9: Viewing privileges granted on objects

1 - Ownership and implicit privileges

All users have implicit privileges on the objects that they own. On creating an object, its owner automatically is granted all privileges associated with the object's type (see Database object privileges). Regardless of object type, the following privileges are inseparable from ownership and cannot be revoked, not even by the owner:

• Authority to grant all object privileges to other users, and revoke them

• ALTER (where applicable) and DROP

• Extension of privilege granting authority on their objects to other users, and revoking that authority

Object owners can revoke all non-implicit, or ordinary, privileges from themselves. For example, on creating a table, its owner is automatically granted all implicit and ordinary privileges:

If user Joan creates table t1, she can revoke ordinary privileges UPDATE and INSERT from herself, which effectively makes this table read-only:

=> \c - Joan
You are now connected as user "Joan".
=> CREATE TABLE t1 (a int);
CREATE TABLE
=> INSERT INTO t1 VALUES (1);
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT
=> REVOKE UPDATE, INSERT ON TABLE t1 FROM Joan;
REVOKE PRIVILEGE
=> INSERT INTO t1 VALUES (3);
ERROR 4367:  Permission denied for relation t1
=> SELECT * FROM t1;
 a
---
 1
(1 row)

Joan can subsequently restore UPDATE and INSERT privileges to herself:

=> GRANT UPDATE, INSERT on TABLE t1 TO Joan;
GRANT PRIVILEGE
dbadmin=> INSERT INTO t1 VALUES (3);
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT
dbadmin=> SELECT * FROM t1;
 a
---
 1
 3
(2 rows)

2 - Inherited privileges

You can manage inheritance of privileges at three levels:

• Database

• Schema

• Tables and views

By default, inherited privileges are enabled at the database level and disabled at the schema level. If privilege inheritance is enabled at both levels, new tables and views automatically inherit those privileges when they are created. You can also exclude inheritance from specific tables and views.

2.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.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

2.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;

2.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)
   

3 - Default user privileges

To set the minimum level of privilege for all users, Vertica has the special PUBLIC, which it grants to each user automatically. This role is automatically enabled, but the database administrator or a superuser can also grant higher privileges to users separately using GRANT statements.

Default privileges for MC users

Privileges on Management Console (MC) are managed through roles, which determine a user's access to MC and to MC-managed Vertica databases through the MC interface. MC privileges do not alter or override Vertica privileges or roles. See Users, roles, and privileges for details.

4 - Effective privileges

A user's effective privileges on an object encompass privileges of all types, including:

• Implicit privileges through object ownership

• Explicit privileges through GRANT statements on objects

• Inherited privileges through privileges on objects with inheritance enabled

You can view your effective privileges on an object with the GET_PRIVILEGES_DESCRIPTION meta-function.

5 - Privileges required for common database operations

This topic lists the required privileges for database objects in Vertica.

Unless otherwise noted, superusers can perform all operations shown in the following tables. Object owners always can perform operations on their own objects.

Schemas

The PUBLIC schema is present in any newly-created Vertica database. Newly-created users must be granted access to this schema:

=> GRANT USAGE ON SCHEMA public TO user;

A database superuser must also explicitly grant new users CREATE privileges, as well as grant them individual object privileges so the new users can create or look up objects in the PUBLIC schema.

Tables

Views

Projections

External procedures

Stored procedures

Libraries

User-defined functions

Sequences

Resource pools

Users/profiles/roles

Object visibility

You can use one or a combination of vsql \d meta commands and SQL system tables to view objects on which you have privileges to view.

• Use \dn to view schema names and owners

• Use \dt to view all tables in the database, as well as the system table V_CATALOG.TABLES

• Use \dj to view projections showing the schema, projection name, owner, and node, as well as the system table V_CATALOG.PROJECTIONS

I/O operations

Comments

Transactions

Sessions

Tuning operations

TLS configuration

6 - Database object privileges

Privileges can be granted explicitly on most user-visible objects in a Vertica database, such as tables and models. For some objects such as projections, privileges are implicitly derived from other objects.

Explicitly granted privileges

The following table provides an overview of privileges that can be explicitly granted on Vertica database objects:

Implicitly granted privileges

Metadata privileges

Superusers have unrestricted access to all database metadata. For non-superusers, access to the metadata of specific objects depends on their privileges on those objects:

Projection privileges

Projections, which store table data, do not have an owner or privileges directly associated with them. Instead, the privileges to create, access, or alter a projection are derived from the privileges that are set on its anchor tables and respective schemas.

7 - Granting and revoking privileges

Vertica supports GRANT and REVOKE statements to control user access to database objects—for example, GRANT (schema) and REVOKE (schema), GRANT (table) and REVOKE (table), and so on. Typically, a superuser creates users and roles shortly after creating the database, and then uses GRANT statements to assign them privileges.

Where applicable, GRANT statements require USAGE privileges on the object schema. The following users can grant and revoke privileges:

• Superusers: all privileges on all database objects, including the database itself

• Non-superusers: all privileges on objects that they own

• Grantees of privileges that include WITH GRANT OPTION: the same privileges on that object

In the following example, a dbadmin (with superuser privileges) creates user Carol. Subsequent GRANT statements grant Carol schema and table privileges:

• CREATE and USAGE privileges on schema PUBLIC

• SELECT, INSERT, and UPDATE privileges on table public.applog. This GRANT statement also includes WITH GRANT OPTION. This enables Carol to grant the same privileges on this table to other users —in this case, SELECT privileges to user Tom:

=> CREATE USER Carol;
CREATE USER
=> GRANT CREATE, USAGE ON SCHEMA PUBLIC to Carol;
GRANT PRIVILEGE
=> GRANT SELECT, INSERT, UPDATE ON TABLE public.applog TO Carol WITH GRANT OPTION;
GRANT PRIVILEGE
=> GRANT SELECT ON TABLE public.applog TO Tom;
GRANT PRIVILEGE

7.1 - Superuser privileges

A Vertica superuser is a database user—by default, named dbadmin—that is automatically created on installation. Vertica superusers have complete and irrevocable authority over database users, privileges, and roles.

Superusers can change the privileges of any user and role, as well as override any privileges that are granted by users with the PSEUDOSUPERUSER role. They can also grant and revoke privileges on any user-owned object, and reassign object ownership.

See also

7.2 - Schema owner privileges

The schema owner is typically the user who creates the schema. By default, the schema owner has privileges to create objects within a schema. The owner can also alter the schema: reassign ownership, rename it, and enable or disable inheritance of schema privileges.

Schema ownership does not necessarily grant the owner access to objects in that schema. Access to objects depends on the privileges that are granted on them.

All other users and roles must be explicitly granted access to a schema by its owner or a superuser.

7.3 - Object owner privileges

The database, along with every object in it, has an owner. The object owner is usually the person who created the object, although a superuser can alter ownership of objects, such as table and sequence.

Object owners must have appropriate schema privilege to access, alter, rename, move or drop any object it owns without any additional privileges.

An object owner can also:

• Grant privileges on their own object to other users

  The WITH GRANT OPTION clause specifies that a user can grant the permission to other users. For example, if user Bob creates a table, Bob can grant privileges on that table to users Ted, Alice, and so on.

• Grant privileges to roles

  Users who are granted the role gain the privilege.

7.4 - Granting privileges

As described in Granting and revoking privileges, specific users grant privileges using the GRANT statement with or without the optional WITH GRANT OPTION, which allows the user to grant the same privileges to other users.

• A superuser can grant privileges on all object types to other users.

• A superuser or object owner can grant privileges to roles. Users who have been granted the role then gain the privilege.

• An object owner can grant privileges on the object to other users using the optional WITH GRANT OPTION clause.

• The user needs to have USAGE privilege on schema and appropriate privileges on the object.

When a user grants an explicit list of privileges, such as GRANT INSERT, DELETE, REFERENCES ON applog TO Bob:

• The GRANT statement succeeds only if all the roles are granted successfully. If any grant operation fails, the entire statement rolls back.

• Vertica will return ERROR if the user does not have grant options for the privileges listed.

When a user grants ALL privileges, such as GRANT ALL ON applog TO Bob, the statement always succeeds. Vertica grants all the privileges on which the grantor has the WITH GRANT OPTION and skips those privileges without the optional WITH GRANT OPTION.

For example, if the user Bob has delete privileges with the optional grant option on the applog table, only DELETE privileges are granted to Bob, and the statement succeeds:

=> GRANT DELETE ON applog TO Bob WITH GRANT OPTION;GRANT PRIVILEGE

For details, see the GRANT statements.

7.5 - Revoking privileges

The following non-superusers can revoke privileges on an object:

• Object owner

• Grantor of the object privileges

The user also must have USAGE privilege on the object's schema.

For example, the following query on system table V_CATALOG.GRANTS shows that users u1, u2, and u3 have the following privileges on schema s1 and table s1.t1:

 => SELECT object_type, object_name, grantee, grantor, privileges_description FROM v_catalog.grants
     WHERE object_name IN ('s1', 't1') AND grantee IN ('u1', 'u2', 'u3');
object_type | object_name | grantee | grantor |  privileges_description
-------------+-------------+---------+---------+---------------------------
 SCHEMA      | s1          | u1      | dbadmin | USAGE, CREATE
 SCHEMA      | s1          | u2      | dbadmin | USAGE, CREATE
 SCHEMA      | s1          | u3      | dbadmin | USAGE
 TABLE       | t1          | u1      | dbadmin | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u2      | u1      | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u3      | u2      | SELECT*
(6 rows)

In the following statements, u2 revokes the SELECT privileges that it granted on s1.t1 to u3 . Subsequent attempts by u3 to query this table return an error:

=> \c - u2
You are now connected as user "u2".
=> REVOKE SELECT ON s1.t1 FROM u3;
REVOKE PRIVILEGE
=> \c - u3
You are now connected as user "u2".
=> SELECT * FROM s1.t1;
ERROR 4367:  Permission denied for relation t1

Revoking grant option

If you revoke privileges on an object from a user, that user can no longer act as grantor of those same privileges to other users. If that user previously granted the revoked privileges to other users, the REVOKE statement must include the CASCADE option to revoke the privilege from those users too; otherwise, it returns with an error.

For example, user u2 can grant SELECT, INSERT, and UPDATE privileges, and grants those privileges to user u4:

=> \c - u2
You are now connected as user "u2".
=> GRANT SELECT, INSERT, UPDATE on TABLE s1.t1 to u4;
GRANT PRIVILEGE

If you query V_CATALOG.GRANTS for privileges on table s1.t1, it returns the following result set:

=> \ c
You are now connected as user "dbadmin".
=> SELECT object_type, object_name, grantee, grantor, privileges_description FROM v_catalog.grants
     WHERE object_name IN ('t1') ORDER BY grantee;
 object_type | object_name | grantee | grantor |                   privileges_description
-------------+-------------+---------+---------+------------------------------------------------------------
 TABLE       | t1          | dbadmin | dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, TRUNCATE*
 TABLE       | t1          | u1      | dbadmin | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u2      | u1      | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u4      | u2      | INSERT, SELECT, UPDATE
(3 rows)

Now, if user u1 wants to revoke UPDATE privileges from user u2, the revoke operation must cascade to user u4, who also has UPDATE privileges that were granted by u2; otherwise, the REVOKE statement returns with an error:

=> \c - u1
=> REVOKE update ON TABLE s1.t1 FROM u2;
ROLLBACK 3052:  Dependent privileges exist
HINT:  Use CASCADE to revoke them too
=> REVOKE update ON TABLE s1.t1 FROM u2 CASCADE;
REVOKE PRIVILEGE
=> \c
You are now connected as user "dbadmin".
=>  SELECT object_type, object_name, grantee, grantor, privileges_description FROM v_catalog.grants
     WHERE object_name IN ('t1') ORDER BY grantee;
 object_type | object_name | grantee | grantor |                   privileges_description
-------------+-------------+---------+---------+------------------------------------------------------------
 TABLE       | t1          | dbadmin | dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, TRUNCATE*
 TABLE       | t1          | u1      | dbadmin | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u2      | u1      | INSERT*, SELECT*
 TABLE       | t1          | u4      | u2      | INSERT, SELECT
(4 rows)

You can also revoke grantor privileges from a user without revoking those privileges. For example, user u1 can prevent user u2 from granting INSERT privileges to other users, but allow user u2 to retain that privilege:

=> \c - u1
You are now connected as user "u1".
=> REVOKE GRANT OPTION FOR INSERT ON TABLE s1.t1 FROM U2 CASCADE;
REVOKE PRIVILEGE

You can confirm results of the revoke operation by querying V_CATALOG.GRANTS for privileges on table s1.t1:

=> \c
You are now connected as user "dbadmin".
=> SELECT object_type, object_name, grantee, grantor, privileges_description FROM v_catalog.grants
      WHERE object_name IN ('t1') ORDER BY grantee;
 object_type | object_name | grantee | grantor |                   privileges_description
-------------+-------------+---------+---------+------------------------------------------------------------
 TABLE       | t1          | dbadmin | dbadmin | INSERT*, SELECT*, UPDATE*, DELETE*, REFERENCES*, TRUNCATE*
 TABLE       | t1          | u1      | dbadmin | INSERT*, SELECT*, UPDATE*
 TABLE       | t1          | u2      | u1      | INSERT, SELECT*
 TABLE       | t1          | u4      | u2      | SELECT
(4 rows)

The query results show:

• User u2 retains INSERT privileges on the table but can no longer grant INSERT privileges to other users (as indicated by absence of an asterisk).

• The revoke operation cascaded down to grantee u4, who now lacks INSERT privileges.

See also

7.6 - Privilege ownership chains

The ability to revoke privileges on objects can cascade throughout an organization. If the grant option was revoked from a user, the privilege that this user granted to other users will also be revoked.

If a privilege was granted to a user or role by multiple grantors, to completely revoke this privilege from the grantee the privilege has to be revoked by each original grantor. The only exception is a superuser may revoke privileges granted by an object owner, with the reverse being true, as well.

In the following example, the SELECT privilege on table t1 is granted through a chain of users, from a superuser through User3.

• A superuser grants User1 CREATE privileges on the schema s1:

  => \c - dbadmin
  You are now connected as user "dbadmin".
  => CREATE USER User1;
  CREATE USER
  => CREATE USER User2;
  CREATE USER
  => CREATE USER User3;
  CREATE USER
  => CREATE SCHEMA s1;
  CREATE SCHEMA
  => GRANT USAGE on SCHEMA s1 TO User1, User2, User3;
  GRANT PRIVILEGE
  => CREATE ROLE reviewer;
  CREATE ROLE
  => GRANT CREATE ON SCHEMA s1 TO User1;
  GRANT PRIVILEGE
  

• User1 creates new table t1 within schema s1 and then grants SELECT WITH GRANT OPTION privilege on s1.t1 to User2:

  => \c - User1
  You are now connected as user "User1".
  => CREATE TABLE s1.t1(id int, sourceID VARCHAR(8));
  CREATE TABLE
  => GRANT SELECT on s1.t1 to User2 WITH GRANT OPTION;
  GRANT PRIVILEGE
  

• User2 grants SELECT WITH GRANT OPTION privilege on s1.t1 to User3:

  => \c - User2
  You are now connected as user "User2".
  => GRANT SELECT on s1.t1 to User3 WITH GRANT OPTION;
  GRANT PRIVILEGE
  

• User3 grants SELECT privilege on s1.t1 to the reviewer role:

  => \c - User3
  You are now connected as user "User3".
  => GRANT SELECT on s1.t1 to reviewer;
  GRANT PRIVILEGE
  

Users cannot revoke privileges upstream in the chain. For example, User2 did not grant privileges on User1, so when User1 runs the following REVOKE command, Vertica rolls back the command:

=> \c - User2
You are now connected as user "User2".
=> REVOKE CREATE ON SCHEMA s1 FROM User1;
ROLLBACK 0:  "CREATE" privilege(s) for schema "s1" could not be revoked from "User1"

Users can revoke privileges indirectly from users who received privileges through a cascading chain, like the one shown in the example above. Here, users can use the CASCADE option to revoke privileges from all users "downstream" in the chain. A superuser or User1 can use the CASCADE option to revoke the SELECT privilege on table s1.t1 from all users. For example, a superuser or User1 can execute the following statement to revoke the SELECT privilege from all users and roles within the chain:

=> \c - User1
You are now connected as user "User1".
=> REVOKE SELECT ON s1.t1 FROM User2 CASCADE;
REVOKE PRIVILEGE

When a superuser or User1 executes the above statement, the SELECT privilege on table s1.t1 is revoked from User2, User3, and the reviewer role. The GRANT privilege is also revoked from User2 and User3, which a superuser can verify by querying the V_CATALOG.GRANTS system table.

=> SELECT * FROM grants WHERE object_name = 's1' AND grantee ILIKE 'User%';
 grantor | privileges_description | object_schema | object_name | grantee
---------+------------------------+---------------+-------------+---------
 dbadmin | USAGE                  |               | s1          | User1
 dbadmin | USAGE                  |               | s1          | User2
 dbadmin | USAGE                  |               | s1          | User3
(3 rows)

8 - Modifying privileges

A superuser or object owner can use one of the ALTER statements to modify a privilege, such as changing a sequence owner or table owner. Reassignment to the new owner does not transfer grants from the original owner to the new owner; grants made by the original owner are dropped.

9 - Viewing privileges granted on objects

You can view information about privileges, grantors, grantees, and objects by querying these system tables:

• GRANTS
• INHERITED_PRIVILEGES
• INHERITING_OBJECTS

An asterisk (*) appended to a privilege indicates that the user can grant the privilege to other users.

You can also view the effective privileges on a specified database object by using the GET_PRIVILEGES_DESCRIPTION meta-function.

Viewing explicitly granted privileges

To view explicitly granted privileges on objects, query the GRANTS table.

The following query returns the explicit privileges for the schema, myschema.

=> SELECT grantee, privileges_description FROM grants WHERE object_name='myschema';
 grantee | privileges_description
---------+------------------------
 Bob     | USAGE, CREATE
 Alice   | CREATE
 (2 rows)

Viewing inherited privileges

To view which tables and views inherit privileges from which schemas, query the INHERITING_OBJECTS table.

The following query returns the tables and views that inherit their privileges from their parent schema, customers.

=> SELECT * FROM inheriting_objects WHERE object_schema='customers';
     object_id     |     schema_id     | object_schema |  object_name  | object_type
-------------------+-------------------+---------------+---------------+-------------
 45035996273980908 | 45035996273980902 | customers     | cust_info     | table
 45035996273980984 | 45035996273980902 | customers     | shipping_info | table
 45035996273980980 | 45035996273980902 | customers     | cust_set      | view
 (3 rows)

To view the specific privileges inherited by tables and views and information on their associated grant statements, query the INHERITED_PRIVILEGES table.

The following query returns the privileges that the tables and views inherit from their parent schema, customers.

=> SELECT object_schema,object_name,object_type,privileges_description,principal,grantor FROM inherited_privileges WHERE object_schema='customers';
 object_schema |  object_name  | object_type |                          privileges_description                           | principal | grantor
---------------+---------------+-------------+---------------------------------------------------------------------------+-----------+---------
 customers     | cust_info     | Table       | INSERT*, SELECT*, UPDATE*, DELETE*, ALTER*, REFERENCES*, DROP*, TRUNCATE* | dbadmin   | dbadmin
 customers     | shipping_info | Table       | INSERT*, SELECT*, UPDATE*, DELETE*, ALTER*, REFERENCES*, DROP*, TRUNCATE* | dbadmin   | dbadmin
 customers     | cust_set      | View        | SELECT*, ALTER*, DROP*                                                    | dbadmin   | dbadmin
 customers     | cust_info     | Table       | SELECT                                                                    | Val       | dbadmin
 customers     | shipping_info | Table       | SELECT                                                                    | Val       | dbadmin
 customers     | cust_set      | View        | SELECT                                                                    | Val       | dbadmin
 customers     | cust_info     | Table       | INSERT                                                                    | Pooja     | dbadmin
 customers     | shipping_info | Table       | INSERT                                                                    | Pooja     | dbadmin
 (8 rows)

Viewing effective privileges on an object

To view the current user's effective privileges on a specified database object, user the GET_PRIVILEGES_DESCRIPTION meta-function.

In the following example, user Glenn has set the REPORTER role and wants to check his effective privileges on schema s1 and table s1.articles.

• Table s1.articles inherits privileges from its schema (s1).

• The REPORTER role has the following privileges:

  • SELECT on schema s1

  • INSERT WITH GRANT OPTION on table s1.articles

• User Glenn has the following privileges:

  • UPDATE and USAGE on schema s1.

  • DELETE on table s1.articles.

GET_PRIVILEGES_DESCRIPTION returns the following effective privileges for Glenn on schema s1:

=> SELECT GET_PRIVILEGES_DESCRIPTION('schema', 's1');
   GET_PRIVILEGES_DESCRIPTION
--------------------------------
 SELECT, UPDATE, USAGE
(1 row)

GET_PRIVILEGES_DESCRIPTION returns the following effective privileges for Glenn on table s1.articles:

=> SELECT GET_PRIVILEGES_DESCRIPTION('table', 's1.articles');
   GET_PRIVILEGES_DESCRIPTION
--------------------------------
 INSERT*, SELECT, UPDATE, DELETE
(1 row)

See also

• Inherited privileges
• Database users and privileges