Help Center/ GaussDB/ Developer Guide(Centralized_V2.0-8.x)/ SQL Reference/ Functions and Operators/ System Information Functions/ Access Permission Query Functions

Updated on 2025-05-29 GMT+08:00

View PDF

Access Permission Query Functions

The DDL permissions, including ALTER, DROP, COMMENT, INDEX, and VACUUM, are inherent permissions implicitly owned by the owner.

The following access permission query function only queries whether a user has a certain permission on an object. That is, the permission on the object recorded in the acl column of the system catalog is returned.

has_any_column_privilege(user, table, privilege)

Description: Queries whether a specified user has access permissions on any column of a table.

**Table 1** Parameter type description
Parameter	Valid Input Parameter Type
user	name, oid
table	text, oid
privilege	text

Return type: Boolean

has_any_column_privilege(table, privilege)

Description: Queries whether the current user has access permissions on any column of a table. For details about the valid parameter types, see Table 1.

Return type: Boolean

Note: has_any_column_privilege checks whether a user can access any column of a table in a particular way. Its parameter possibilities are analogous to those of has_table_privilege, except that the desired access permission type must be some combination of SELECT, INSERT, UPDATE, COMMENT or REFERENCES.

Note that having any of these permissions at the table level indicates that the permission is implicitly granted for each column of the table. Therefore, has_any_column_privilege always returns true if has_table_privilege has the same parameters. A success message is also returned if a column-level permission is granted for at least one column.

has_column_privilege(user, table, column, privilege)

Description: Queries whether a specified user has access permissions on a column.

**Table 2** Parameter type description
Parameter	Valid Input Parameter Type
user	name, oid
table	text, oid
column	text, smallint
privilege	text

Return type: Boolean

has_column_privilege(table, column, privilege)

Description: Queries whether the current user has access permissions on a column. For details about the valid parameter types, see Table 2.

Return type: Boolean

has_column_privilege checks whether a user can access a column in a particular way. Its parameter possibilities are analogous to those of has_table_privilege. Columns can be added either by name or by attribute number. The desired access permission type must be some combination of SELECT, INSERT, UPDATE, COMMENT or REFERENCES.

Note that having any of these permissions at the table level indicates that the permission is implicitly granted for each column of the table.

has_cek_privilege(user, cek, privilege)

Description: Queries whether a specified user has access permissions on a CEK. The following table describes the parameters.

**Table 3** Parameter type description
Parameter	Valid Input Parameter Type	Description	Value Range
user	name, oid	Users	Username or ID
cek	text, oid	CEK	Name or ID of a CEK
privilege	text	Permission	USAGE: allows users to use the specified CEK. DROP: allows users to delete the specified CEK.

Return type: Boolean

has_cmk_privilege(user, cmk, privilege)

Description: Queries whether a specified user has access permissions on a CMK. The following table describes the parameters.

**Table 4** Parameter type description
Parameter	Valid Input Parameter Type	Description	Value Range
user	name, oid	Users	Username or ID
cmk	text, oid	CMK	Name or ID of the CMK
privilege	text	Permission	USAGE: allows users to use the specified CMK. DROP: allows users to delete the specified CMK.

Return type: Boolean

has_database_privilege(user, database, privilege)

Description: Queries whether a specified user has access permissions on a database. The following table describes the parameters. PDB information cannot be queried in a non-PDB. This function cannot be used in a PDB.

**Table 5** Parameter type description
Parameter	Valid Input Parameter Type
user	name, oid
database	text, oid
privilege	text

Return type: Boolean

has_database_privilege(database, privilege)

Description: Queries whether the current user has access permissions on a database. For details about the valid parameter types, see Table 5. PDB information cannot be queried in a non-PDB. This function cannot be used in a PDB.

Return type: Boolean

Note: has_database_privilege checks whether a user can access a database in a particular way. Its parameter possibilities are analogous to those of has_table_privilege. The desired access permission type must be some combination of CREATE, CONNECT, TEMPORARY, ALTER, DROP, COMMENT or TEMP (which is equivalent to TEMPORARY).

has_directory_privilege(user, directory, privilege)

Description: Queries whether a specified user has access permissions on a directory.

**Table 6** Parameter type description
Parameter	Valid Input Parameter Type
user	name, oid
directory	text, oid
privilege	text

Return type: Boolean

has_directory_privilege(directory, privilege)

Description: Queries whether the current user has access permissions on a directory. For details about the valid parameter types, see Table 6.

Return type: Boolean

has_foreign_data_wrapper_privilege(user, fdw, privilege)

Description: Queries whether a specified user has access permissions on a foreign data wrapper.

**Table 7** Parameter type description
Parameter	Valid Input Parameter Type
user	name, oid
fdw	text, oid
privilege	text

Return type: Boolean

has_foreign_data_wrapper_privilege(fdw, privilege)

Description: Queries whether the current user has access permissions on a foreign data wrapper. For details about the valid parameter types, see Table 7.

Return type: Boolean

Note: has_foreign_data_wrapper_privilege checks whether a user can access a foreign data wrapper in a particular way. Its parameter possibilities are analogous to those of has_table_privilege. The desired access permission type must evaluate to USAGE.

has_function_privilege(user, function, privilege)

Description: Queries whether a specified user has access permissions on a function.

**Table 8** Parameter type description
Parameter	Valid Input Parameter Type
user	name, oid
function	text, oid
privilege	text

Return type: Boolean

has_function_privilege(function, privilege)

Description: Queries whether the current user has access permissions on a function. For details about the valid parameter types, see Table 8.

Return type: Boolean

Note: has_function_privilege checks whether a user can access a function in a particular way. Its parameter possibilities are analogous to those of has_table_privilege. When a function is specified by a text string rather than by an OID, the allowed input is the same as that for the regprocedure data type (see Object Identifier Types). The access permission type must be EXECUTE, ALTER, DROP, or COMMENT.

has_language_privilege(user, language, privilege)

Description: Queries whether a specified user has access permissions on languages.

**Table 9** Parameter type description
Parameter	Valid Input Parameter Type
user	name, oid
language	text, oid
privilege	text

Return type: Boolean

has_language_privilege(language, privilege)

Description: Queries whether the current user has access permissions on a language. For details about the valid parameter types, see Table 9.

Return type: Boolean

Note: has_language_privilege checks whether a user can access a procedural language in a particular way. Its parameter possibilities are analogous to those of has_table_privilege. The desired access permission type must evaluate to USAGE.

has_nodegroup_privilege(user, nodegroup, privilege)

Description: Queries whether a user has access permissions on a database node.

Return type: Boolean

**Table 10** Parameter type description
Parameter	Valid Input Parameter Type
user	name, oid
nodegroup	text, oid
privilege	text

has_nodegroup_privilege(nodegroup, privilege)

Description: Queries whether a user has access permissions on a database node. Its parameter possibilities are analogous to those of has_table_privilege. The access permission type must be USAGE, CREATE, COMPUTE, ALTER, or DROP.

Return type: Boolean

has_schema_privilege(user, schema, privilege)

Description: Queries whether a specified user has access permissions on a schema.

Return type: Boolean

has_schema_privilege(schema, privilege)

Description: Queries whether the current user has access permissions on a schema.

Return type: Boolean

Note: has_schema_privilege checks whether a user can access a schema in a particular way. Its parameter possibilities are analogous to those of has_table_privilege. The desired access permission type must be CREATE, USAGE, ALTER, DROP, or COMMENT. If the check type contains the CREATE permission and the checked schema is a schema with the same name as the user, the function returns TRUE only when the user has the OWNER permission for the schema due to the special constraint of the schema with the same name.

has_server_privilege(user, server, privilege)

Description: Queries whether a specified user has access permissions on a foreign server.

Return type: Boolean

has_server_privilege(server, privilege)

Description: Queries whether the current user has access permissions on a foreign server.

Return type: Boolean

Note: has_server_privilege checks whether a user can access a foreign server in a particular way. Its parameter possibilities are analogous to those of has_table_privilege. The access permission type must be USAGE, ALTER, DROP, or COMMENT.

has_table_privilege(user, table, privilege)

Description: Queries whether a specified user has access permissions on a table.

Return type: Boolean

has_table_privilege(table, privilege)

Description: Queries whether the current user has access permissions on a table.

Return type: Boolean

Note: has_table_privilege checks whether a user can access a table in a particular way. The user can be specified by name or by OID (pg_authid.oid), or be set to public which indicates the PUBLIC role. If this parameter is omitted, current_user is used. The table can be specified by name or by OID. When it is specified by name, the name can be schema-qualified if necessary. The desired access permission type is specified by a text string, which must be one of the values SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, ALTER, DROP, COMMENT, INDEX or VACUUM. Optionally, WITH GRANT OPTION can be added to a permission type to test whether the permission is held with the grant option. Also, multiple permission types can be separated by commas (,), in which case the result will be true if any of the listed permissions is held.

Example:

    
         gaussdb=# SELECT has_table_privilege('tpcds.web_site', 'select');
 has_table_privilege  
--------------------- 
 t 
(1 row)

gaussdb=# SELECT has_table_privilege('omm', 'tpcds.web_site', 'select,INSERT WITH GRANT OPTION ');
 has_table_privilege  
---------------------
 t 
(1 row)

has_tablespace_privilege(user, tablespace, privilege)

Description: Queries whether a specified user has access permissions on a tablespace.

Return type: Boolean

has_tablespace_privilege(tablespace, privilege)

Description: Queries whether the current user has access permissions on a tablespace.

Return type: Boolean

Note: has_tablespace_privilege checks whether a user can access a tablespace in a particular way. Its parameter possibilities are analogous to those of has_table_privilege. The access permission type must be CREATE, ALTER, DROP, or COMMENT.

pg_has_role(user, role, privilege)

Description: Queries whether a specified user has access permissions on a role.

Return type: Boolean

pg_has_role(role, privilege)

Description: Queries whether the current user has access permissions on a role.

Return type: Boolean

Note: pg_has_role checks whether a user can access a role in a particular way. Its parameter possibilities are analogous to those of has_table_privilege, except that public cannot be used as a username. The desired access permission type must be some combination of MEMBER or USAGE. MEMBER denotes direct or indirect membership in the role (that is, permission SET ROLE), while USAGE denotes the usage permission on the role that is available without the SET ROLE permission.

has_any_privilege(user, privilege)

Description: Queries whether a specified user has certain ANY permission. If multiple permissions are queried at the same time, true is returned as long as one permission is obtained.

Return type: Boolean

**Table 11** Parameter type description
Parameter	Valid Input Parameter Type	Description	Value Range
user	name	Users	Existing username
privilege	text	ANY permission	Available values: CREATE ANY TABLE [WITH ADMIN OPTION] ALTER ANY TABLE [WITH ADMIN OPTION] DROP ANY TABLE [WITH ADMIN OPTION] SELECT ANY TABLE [WITH ADMIN OPTION] INSERT ANY TABLE [WITH ADMIN OPTION] UPDATE ANY TABLE [WITH ADMIN OPTION] DELETE ANY TABLE [WITH ADMIN OPTION] CREATE ANY SEQUENCE [WITH ADMIN OPTION] CREATE ANY INDEX [WITH ADMIN OPTION] CREATE ANY FUNCTION [WITH ADMIN OPTION] EXECUTE ANY FUNCTION [WITH ADMIN OPTION] CREATE ANY PACKAGE [WITH ADMIN OPTION] EXECUTE ANY PACKAGE [WITH ADMIN OPTION] CREATE ANY TYPE [WITH ADMIN OPTION]