Overview
A synonym is an alias for a database object. You can create a synonym for an object that belongs to another user, which lets you query and operate on the alias to directly access the object.
A synonym is similar to a view. It supports create, read, update, and delete (CRUD) operations on tables. A database administrator (DBA) can create synonyms for OceanBase database objects such as tables, views, functions, stored procedures, sequences, packages, and other synonyms.
Synonyms expand the use of a database and allow seamless interaction between different users. They are often used to simplify object access and enhance security.
Synonym types
The two synonym types are ordinary and public.
Private synonym: A synonym created by a regular user. The user must have the permission to create synonyms. Only the creator can access it, but other users can access it if they are granted permission.
Public synonym: A synonym created by a user with a DBA role, such as `system`. All users can access a public synonym.
For more information, see OceanBase Database.
As shown in the preceding figure, creating a synonym involves the following eight steps:
This topic describes how to create a synonym named `syn_employee` for the `employee` table in the `SYS` database using OceanBase Developer Center (ODC).
The data used in this topic is for demonstration only. Replace it with your own data as needed.
Procedure
Step 1: Specify the synonym name
In OceanBase Developer Center (ODC), click a connection name. In the navigation pane on the left, click the Synonyms tab to view a list of synonyms. The list is divided into private and public synonyms. In the upper-right corner of the list, click + to create a synonym. You can also click New > Synonym in the top navigation bar of ODC.
In the Create Synonym panel, specify the synonym name.
Step 2: Specify the object owner
Select the schema of the object owner.
Step 3: Specify the object type
Select the object type. The options are Table and View.
Step 4: Specify the object name
Enter the name of the object for which you are creating the synonym.
Step 5: Specify the synonym type
Select the synonym type. The options are Private Synonym and Public Synonym.
Step 6: Confirm the new synonym
Click Next: Confirm SQL to go to the SQL confirmation page.
Step 7: Edit the synonym
On the SQL confirmation page, you can edit the synonym statement.
The toolbar on the editing page provides the following features:
Feature |
Description |
Format |
Click this button to format the selected SQL statements or all SQL statements in the current SQL window. This includes operations such as indenting, adding line breaks, and highlighting keywords. |
Find/Replace |
Enter content in the find box to search the script. After finding the content, you can enter new content in the replace box to replace it. |
Undo |
Reverts the script to the state before the last operation. |
Redo |
After you perform an Undo operation, this re-applies the undone operation to the script. |
Case |
Provides three options: UPPERCASE, lowercase, and Capitalize. Converts the selected text to the corresponding case. |
Indent |
Provides two options: Add Indent and Remove Indent. Adds or removes indents for the selected statements. |
Comment |
Provides two options: Add Comment and Remove Comment. Converts the selected statements into comments or back into SQL statements. |
IN Value Conversion |
Converts a format such as A B into ('A','B'). |
A synonym definition statement is generated based on the information that you specified in the Create Synonym window. You can modify this statement directly.
You can edit the synonym creation statement on the SQL confirmation page. The syntax is as follows:
CREATE OR REPLACE SYNONYM syn_name FOR schema_name.object_name;Parameters:
Parameter |
Description |
OR REPLACE |
If the synonym already exists, this clause updates it. |
syn_name |
The name of the synonym. Regular synonym names must be unique within a schema. |
schema_name |
The database schema to which the object belongs. |
object_name |
The name of the object. |
Step 8: Finish creating the synonym
After you confirm the statement, click Create in the upper-right corner of the page to create the synonym. You cannot modify a synonym after it is created.
In the navigation pane on the left, you can right-click an object to open a shortcut menu with management operations. These operations include View, Create, Delete, Download, and Refresh. For more information, see Manage synonyms and Introduction to common features.
Syntax:
SELECT * FROM syn_name ([syn_parameter[,…])Example:
SELECT * FROM syn_employee;