Create a synonym

ODC supports visualized synonym creation. This topic describes how to create a synonym with ODC.

Overview

A synonym is an alias that you create for a database object of another user. You can perform operations on the object by querying and operating the synonym.

Synonyms are similar to views. You can use synonyms to add, delete, modify, and query table data. You can define synonyms for most database objects in OceanBase Database, such as tables, views, materialized views, sequences, functions, stored procedures, packages, and synonyms.

Synonyms extend the availability of database objects and allow them to be accessed by different database users with the required privileges. This design simplifies and secures access to database objects.

Synonym types

OceanBase Database supports synonyms and public synonyms.

• Synonyms: In general, a synonym created by a regular user is a private synonym. A private synonym can be accessed by the user who created it or other users who are granted the required privileges. To create a private synonym under the current user, you must have the CREATE SYNONYM privilege.

• Public synonyms: A public synonym is created by a user who is granted the administrator role, such as the system user account, and can be accessed by all users.

For more information, see Synonyms.

As shown in the preceding figure, you can create a synonym in the following eight steps:

1. Specify the synonym name.
2. Specify the object owner.
3. Specify the object type.
4. Specify the object name.
5. Specify the synonym type.
6. Verify the information about the synonym.
7. Edit the synonym.
8. Complete the synonym creation.

Procedure

In this example, a synonym named syn_employee is created in the OceanBase Developer Center (ODC) console for an employee table in the SYS database. Procedure:

Step 1: Specify the name of the synonym.

Log in to the ODC console and click the name of the desired connection to go to the corresponding connection management page. You can click Synonym in the left-side navigation pane to get a list of synonyms and a list of public synonyms. To create a synonym, click + in the upper-right corner of the synonym list or choose Create > Synonym in the top navigation bar.

In the Create Synonym dialog box, specify the name of the synonym.

Step 2: Specify the object owner.

Specify the schema where the object is located.

Step 3: Specify the object type.

Specify the object type. Valid values: Table and View.

Step 4: Specify the object name.

Specify the name of the object for which the synonym is created.

Step 5: Specify the synonym type.

Specify the synonym type. Valid values: Synonym and Public Synonym.

Step 6: Verify the information about the synonym.

Click Next: Verify SQL Statement to go to the Create Synonym page.

Step 7: Edit the synonym.

Edit the statement on the Create Synonym page.

On the statement editing page, a synonym definition statement is generated based on the information specified in the Create Synonym dialog box. You can modify this statement. After you confirm the statement, click the Create button in the upper-right corner of the page to complete the synonym creation. A synonym cannot be modified after it is created.

Before a synonym is created, you can edit the SQL statements of the synonym on the Create Synonym page. Syntax:

CREATE OR REPLACE SYNONYM syn_name FOR schema_name.object_name;

Parameters

Step 8: Complete the synonym creation.

Click Create. After you create a synonym, you can use it in the same way as a built-in synonym.

Syntax:

SELECT * FROM syn_name ([syn_parameter[,...]])

Example:

SELECT * FROM syn_employee;

Related topics

• Create a table
• Create a view
• Create a function
• Create a stored procedure
• Create a sequence
• Create a program package
• Create a trigger
• Create a type