Hologres:Account and permission management functions

USER_DISPLAY_NAME

Converts the ID of an Alibaba Cloud account or RAM user into the corresponding display name.

Syntax

SELECT USER_DISPLAY_NAME(user_name);

Parameters

Return value

Returns the display name as a string. For an Alibaba Cloud account, the display name is an email address, for example, xx@aliyun.com. For a RAM user, the display name follows the format RAM$mainaccount:subuser or RAM$public.

Example

-- Get the display name for an Alibaba Cloud account ID
SELECT USER_DISPLAY_NAME('13532241323xxx');

HG_DISPLAY_NAME_TO_ID

Converts the display name of an Alibaba Cloud account or RAM user into the corresponding account ID.

Syntax

SELECT HG_DISPLAY_NAME_TO_ID(aliyun_id);

Parameters

Return value

Returns the account ID as a string. For an Alibaba Cloud account, the ID is a numeric string such as 13532241323xxx. For a RAM user, the ID is a string such as p4_23402030200xxx.

Example

-- Get the account ID for a RAM user
SELECT HG_DISPLAY_NAME_TO_ID('RAM$main:subuser');

APPLY_PRIVILEGES

Copies the Access Control List (ACL)-based authorization information and ownership from a source table to a destination table in the standard PostgreSQL authorization model. Use this function after creating a table with CREATE TABLE LIKE, INSERT OVERWRITE, or automatic partitioning, instead of manually re-granting privileges.

Syntax

-- Copy privileges without revoking existing privileges on the destination table
CALL APPLY_PRIVILEGES('<old_table>', '<new_table>', false);

-- Copy privileges and revoke all existing privileges on the destination table
CALL APPLY_PRIVILEGES('<old_table>', '<new_table>');

Parameters

Limits

• Requires Hologres V1.1.48 or later. To upgrade, see the "Manual upgrade" section in Upgrade instances, or join a DingTalk group for assisted upgrade by visiting Obtain online support for Hologres.

• Requires the standard PostgreSQL authorization model. If the simple permission model (SPM) or the schema-level permission model (SLPM) is enabled, switch to the standard PostgreSQL authorization model first. See Switch between permission models.

• Requires GRANT and REVOKE privileges on the destination table.

• For partitioned tables, privileges are applied to the parent table only, not to child tables.

Example

-- Copy privileges from test_table1 to test_table2, keeping existing privileges on test_table2
CALL APPLY_PRIVILEGES('test_table1', 'test_table2', false);

-- Copy privileges from test_table1 to test_table2, revoking existing privileges on test_table2
CALL APPLY_PRIVILEGES('test_table1', 'test_table2');

Sample data

The examples in this section use a table named test, created as follows:

CREATE TABLE test (a INT);
INSERT INTO test (a) VALUES (1);
INSERT INTO test (a) VALUES (2);
INSERT INTO test (a) VALUES (3);

HAS_ANY_COLUMN_PRIVILEGE

Checks whether the current user or a specified user holds specific privileges on all columns of a specified table.

Valid privileges: SELECT, INSERT, UPDATE, REFERENCES.

Syntax

HAS_ANY_COLUMN_PRIVILEGE([user, ] table, privilege)

Parameters

Example

SELECT HAS_ANY_COLUMN_PRIVILEGE('139699392458****', 'test', 'SELECT,INSERT,UPDATE,REFERENCES');

Result:

 has_any_column_privilege
--------------------------
 t

HAS_COLUMN_PRIVILEGE

Checks whether the current user holds specific privileges on a specified column.

Valid privileges: SELECT, INSERT, UPDATE, REFERENCES.

Syntax

HAS_COLUMN_PRIVILEGE(table, column, privilege)

Parameters

Example

SELECT HAS_COLUMN_PRIVILEGE('test', 'a', 'SELECT,INSERT,UPDATE,REFERENCES');

Result:

 has_column_privilege
----------------------
 t

HAS_DATABASE_PRIVILEGE

Checks whether a user holds specific privileges on a database.

Valid privileges: CREATE, TEMPORARY, TEMP.

Syntax

HAS_DATABASE_PRIVILEGE([user, ] database, privilege)

Parameters

Example

-- Replace glz with the name of your database
SELECT HAS_DATABASE_PRIVILEGE('glz', 'CREATE,TEMPORARY,TEMP');

Result:

 has_database_privilege
------------------------
 t

HAS_FOREIGN_DATA_WRAPPER_PRIVILEGE

Checks whether a user holds specific privileges on a foreign data wrapper (FDW).

Valid privileges: USAGE.

Syntax

HAS_FOREIGN_DATA_WRAPPER_PRIVILEGE([user, ] fdw, privilege)

Parameters

Example

-- Replace dlf_fdw with the name of your foreign data wrapper
SELECT HAS_FOREIGN_DATA_WRAPPER_PRIVILEGE('dlf_fdw', 'USAGE');

Result:

 has_foreign_data_wrapper_privilege
------------------------------------
 t

HAS_FUNCTION_PRIVILEGE

Checks whether a user holds specific privileges on a function.

Valid privileges: EXECUTE.

Syntax

has_function_privilege([user, ] function, privilege)

Parameters

Example

SELECT has_function_privilege('now()', 'EXECUTE');

Result:

 has_function_privilege
------------------------
 t

HAS_LANGUAGE_PRIVILEGE

Checks whether a user holds specific privileges on a language.

Valid privileges: USAGE.

Syntax

HAS_LANGUAGE_PRIVILEGE([user, ] language, privilege)

Parameters

Example

SELECT HAS_LANGUAGE_PRIVILEGE('plpgsql', 'USAGE');

Result:

 has_language_privilege
------------------------
 t

HAS_SCHEMA_PRIVILEGE

Checks whether a user holds specific privileges on a schema.

Valid privileges: CREATE, USAGE.

Syntax

HAS_SCHEMA_PRIVILEGE([user, ] schema, privilege)

Parameters

Example

SELECT HAS_SCHEMA_PRIVILEGE('public', 'CREATE,USAGE');

Result:

 has_schema_privilege
----------------------
 t

HAS_SERVER_PRIVILEGE

Checks whether a user holds specific privileges on a foreign server.

Valid privileges: USAGE.

Syntax

HAS_SERVER_PRIVILEGE([user, ] server, privilege)

Parameters

Example

SELECT HAS_SERVER_PRIVILEGE('meta_warehouse_server', 'USAGE');

Result:

 has_server_privilege
----------------------
 t

HAS_TABLE_PRIVILEGE

Checks whether a user holds specific privileges on a table.

Valid privileges: SELECT, INSERT, UPDATE, DELETE, TRUNCATE.

Syntax

HAS_TABLE_PRIVILEGE([user, ] table, privilege)

Parameters

Example

SELECT HAS_TABLE_PRIVILEGE('test', 'SELECT,INSERT');

Result:

 has_table_privilege
---------------------
 t

HAS_TABLESPACE_PRIVILEGE

Checks whether a user holds specific privileges on a tablespace.

Valid privileges: CREATE.

Syntax

HAS_TABLESPACE_PRIVILEGE([user, ] tablespace, privilege)

Parameters

Example

SELECT HAS_TABLESPACE_PRIVILEGE('pg_default', 'CREATE');

Result:

 has_tablespace_privilege
--------------------------
 t

HAS_TYPE_PRIVILEGE

Checks whether the current user or a specified user holds specific privileges on a specified type of object, such as tables, views, or sequences.

Syntax

HAS_TYPE_PRIVILEGE([user, ] type, privilege)

Parameters

Example

SELECT HAS_TYPE_PRIVILEGE('test', 'USAGE');

Result:

 has_type_privilege
--------------------
 t