HAS_TABLE_PRIVILEGE

Indicates whether a user can access a table in a particular way. The function returns a true (t) or false (f) value.

Behavior Type

Stable

Syntax

HAS_TABLE_PRIVILEGE ( [ user, ] [schema-name.]table , privilege )

Parameters

user

Specifies the name or OID of a database user. The default is the CURRENT_USER.

schema

Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example:

myschema.thisDbObject
table

Specifies the name or OID of a table in the logical schema. If necessary, specify the database and schema, as noted above.

privilege
  • SELECT Allows the user to SELECT from any column of the specified table.
  • INSERT Allows the user to INSERT records into the specified table and to use the COPY command to load the table.
  • UPDATE Allows the user to UPDATE records in the specified table.
  • DELETE Allows the user to delete a row from the specified table.
  • REFERENCES Allows the user to create a foreign key constraint (privileges required on both the referencing and referenced tables).

Privileges

A superuser can check all other user's table privileges.

Users without superuser privileges can use HAS_TABLE_PRIVILEGE to check:

Examples

SELECT HAS_TABLE_PRIVILEGE('store.store_dimension', 'SELECT');
 HAS_TABLE_PRIVILEGE 
---------------------
 t
(1 row)
SELECT HAS_TABLE_PRIVILEGE('release', 'store.store_dimension', 'INSERT');
 HAS_TABLE_PRIVILEGE 
---------------------
 t
(1 row)
SELECT HAS_TABLE_PRIVILEGE('store.store_dimension', 'UPDATE');
 HAS_TABLE_PRIVILEGE 
---------------------
 t
(1 row)
SELECT HAS_TABLE_PRIVILEGE('store.store_dimension', 'REFERENCES');
 HAS_TABLE_PRIVILEGE 
---------------------
 t
(1 row)
SELECT HAS_TABLE_PRIVILEGE(45035996273711159, 45035996273711160, 'select');

 HAS_TABLE_PRIVILEGE 
--------------------- 
t 
(1 row)