Help Center/ GaussDB(DWS)/ Troubleshooting/ Account/Permission/Password/ An Error Message Is Displayed When a Common User Executes the Statement for Creating or Deleting a GDS or OBS Foreign Table, Indicating that the User Does Not Have the Permission or the Permission Is Insufficient
Updated on 2024-01-25 GMT+08:00

An Error Message Is Displayed When a Common User Executes the Statement for Creating or Deleting a GDS or OBS Foreign Table, Indicating that the User Does Not Have the Permission or the Permission Is Insufficient

Symptom

An administrator can execute the statement for creating a GDS or OBS foreign table, but an error "ERROR: permission denied to create foreign table in security mode" is reported when a common user executes the statement.

Possible Causes

The error message indicates that the common user does not have the permission for creating a foreign table.

Handling Procedure

You can use the ALTER USER or ALTER ROLE syntax to specify the USEFT parameter, granting a role or user the permission to use foreign tables.

USEFT | NOUSEFT determines whether a new role or user can perform operations on foreign tables, such as creating, deleting, modifying, and reading/witting foreign tables.

  • If USEFT is specified, the role or user can perform operations on foreign tables.
  • The default value is NOUSEFT, indicating that the new role or user does not have permissions to perform operations on foreign tables.
To grant the permission to use foreign tables to a common user or role, run the following command as a database administrator:
1
ALTER USER user_name USEFT;

For details about how to modify user or role permissions, see ALTER USER or ALTER ROLE.For details about how to modify user or role permissions, see "ALTER USER" or "ALTER ROLE" in Data Warehouse Service SQL Syntax Reference.

Common users or roles can create foreign tables after being granted the permission to use foreign tables.