Help Center/ GaussDB/ Developer Guide(Distributed_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	Description	Value Range
user	name, oid	Users	Username or ID
table	text, oid	Tables	Table name or ID
privilege	text	Permission	SELECT: allows the SELECT statement to be executed on any column of a specified table. INSERT: allows the INSERT statement to be executed on any column of a specified table. UPDATE: allows the UPDATE statement to be executed on any column of a specified table. REFERENCES: allows a FOREIGN KEY constraint (not supported in distributed mode). COMMENT: allows the COMMENT statement to be executed on any column of a specified table.

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 Parameter type description.

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, 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	Description	Value Range
user	name, oid	Users	Username or ID
table	text, oid	Table name	Table name or ID
column	text, smallint	Column name	Name or attribute number of a column
privilege	text	Permission	SELECT: allows the SELECT statement to be executed on specified columns of a table. INSERT: allows the INSERT statement to be executed on specified columns of a table. UPDATE: allows the UPDATE statement to be executed on specified columns of a table. REFERENCES: allows a FOREIGN KEY constraint (not supported in distributed mode). COMMENT: allows the COMMENT statement to be executed on specified columns of a table.

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 Parameter type description.

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

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

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

**Table 5** Parameter type description
Parameter	Valid Input Parameter Type	Description	Value Range
user	name, oid	Users	Username or ID
database	text, oid	Database	Database name or ID
privilege	text	Permission	CREATE: For databases, allows new schemas to be created within the database. TEMPORARY: allows users to create temporary tables when the database is used. TEMP: allows users to create temporary tables when the database is used. CONNECT: allows users to access specified databases. ALTER: allows users to modify the attributes of a specified object. DROP: allows users to delete a specified object. COMMENT: allows users to define or modify comments of a specified object.

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 Parameter type description.

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, or TEMP (which is equivalent to TEMPORARY).

has_directory_privilege(user, directory, privilege)

**Table 6** Parameter type description
Parameter	Valid Input Parameter Type	Description	Value Range
user	name, oid	Users	Username or ID
directory	text, oid	Directory	Directory name or OID
privilege	text	Permission	READ: allows read operations on the directory. WRITE: allows write operations on the directory.

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

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 Parameter type description.

Return type: Boolean

has_foreign_data_wrapper_privilege(user, fdw, privilege)

**Table 7** Parameter type description
Parameter	Valid Input Parameter Type	Description	Value Range
user	name, oid	Users	Username or ID
fdw	text, oid	Foreign data wrapper	Name or ID of the foreign data wrapper
privilege	text	Permission	USAGE: allows access to the foreign data wrapper.

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

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 Parameter type description.

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)

**Table 8** Parameter type description
Parameter	Valid Input Parameter Type	Description	Value Range
user	name, oid	Users	Username or ID
function	text, oid	Function	Function name or ID
privilege	text	Permission	EXECUTE: allows users to use specified functions and the operators that are realized by the functions. ALTER: allows users to modify the attributes of a specified object. DROP: allows users to delete a specified object. COMMENT: allows users to define or modify comments of a specified object.

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

Return type: Boolean

has_function_privilege(function, privilege)

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

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 desired access permission type must be EXECUTE.

has_language_privilege(user, language, privilege)

**Table 9** Parameter type description
Parameter	Valid Input Parameter Type	Description	Value Range
user	name, oid	Users	Username or ID
language	text, oid	Language	Language name or ID
privilege	text	Permission	USAGE: allows users to specify a procedural language when creating a function.

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

Return type: Boolean

has_language_privilege(language, privilege)

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

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: Checks whether a user has permission to access a cluster node.

Return type: Boolean

**Table 10** Parameter type description
Parameter	Valid Input Parameter Type	Description	Value Range
user	name, oid	Users	Existing username or ID
nodegroup	text, oid	Cluster node	Existing cluster node
privilege	text	Permission	USAGE: For sub-clusters, allows users who can access objects contained in the schema to access tables in the sub-cluster. CREATE: For sub-clusters, allows users to create tables within the sub-cluster. COMPUTE: allows elastic computing in the sub-cluster. ALTER: allows users to modify the attributes of a specified object. DROP: allows users to delete a specified object.

has_nodegroup_privilege(nodegroup, privilege)

Description: Checks whether a user has permission to access a cluster node.

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_sequence_privilege(user, sequence, privilege)

Description: Queries whether a specified user has permissions on sequences.

Return type: Boolean

**Table 11** Parameter type description
Parameter	Valid Input Parameter Type	Description	Value Range
user	name, oid	Users	Existing username or ID
sequence	text, oid	Sequence	Existing sequence name or ID
privilege	text	Permission	USAGE: For sequences, allows users to use the nextval function. SELECT: allows users to create a sequence. UPDATE: allows users to execute the UPDATE statement. ALTER: allows users to modify the attributes of a specified object. DROP: allows users to delete a specified object. COMMENT: allows users to define or modify comments of a specified object.

has_sequence_privilege(sequence, privilege)

Description: Queries whether the current user has permissions on sequences.

Return type: Boolean

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. If the desired access permission type is specified by a text string, the text string must be one of the values SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCESTRIGGER, 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 12** 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]