Updated on 2024-06-03 GMT+08:00

ALTER SYNONYM

Description

Changes the owner of a SYNONYM object.

Precautions

  • Currently, only the owner of the SYNONYM object can be changed.
  • Only a system administrator has the permission to modify the owner of the SYNONYM object. When separation of duties is enabled, system administrators do not have the permission to change the owner of the SYNONYM object by default.
  • The new owner must have the CREATE permission on the schema where the SYNONYM object resides.
  • PUBLIC synonyms cannot be modified.

Syntax

ALTER SYNONYM synonym_name
    OWNER TO new_owner;

Parameters

  • synonym_name

    Specifies the name of the synonym to be modified, which can contain the schema name.

    Value range: a string. It must comply with the naming convention.

  • new_owner

    Specifies the new owner of the SYNONYM object.

    Value range: a string. It must be a valid username.

Examples

-- Create a system administrator.
gaussdb=# CREATE USER sysadmin WITH SYSADMIN PASSWORD '********';

-- Switch the system administrator.
gaussdb=# \c - sysadmin

-- Create synonym t1.
gaussdb=# CREATE OR REPLACE SYNONYM t1 FOR ot.t1;

-- Create user u1.
gaussdb=# CREATE USER u1 PASSWORD '********';

-- Assign permissions to the new system administrator.
gaussdb=# GRANT ALL ON SCHEMA sysadmin TO u1;

-- Change the owner of synonym t1 to u1.
gaussdb=# ALTER SYNONYM t1 OWNER TO u1;

-- Delete synonym t1.
gaussdb=# DROP SYNONYM t1;

-- Revoke permissions from user u1.
gaussdb=# REVOKE ALL ON SCHEMA sysadmin FROM u1;

-- Delete user u1.
gaussdb=# DROP USER u1;

-- Switch to the initial user init_user. Replace init_user with the actual initial username.
gaussdb=# \c - init_user

-- Delete user sysadmin.
gaussdb=# DROP USER sysadmin;

Helpful Links

CREATE SYNONYM and DROP SYNONYM