Purpose
This statement is used to create a synonym.
Syntax
CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM [ synonym_schema. ]synonym_name
FOR [ object_schema. ]object_name [ @dblink ]
Parameters
| Parameter | Description |
|---|---|
| OR REPLACE | Indicates whether to recreate the synonym with the new definition if the synonym already exists. |
| PUBLIC | Specifies PUBLIC to create a public synonym, which can be used by all users. Users must have the appropriate privileges on the base object to use the synonym. Public synonyms are used only when the object is not specified with a schema. If PUBLIC is not specified, the synonym is private and can only be accessed within the current schema, and the synonym name must be unique within the current schema. |
| synonym_schema | Specifies the schema where the synonym is located. If schema. is omitted, the default schema is your own. If PUBLIC is specified, the schema is not required. |
| synonym_name | Specifies the name of the synonym. |
| object_schema | Specifies the schema where the object for the synonym is located. If schema. is omitted, the default schema is your own. |
| object_name | Specifies the name of the object corresponding to the synonym. |
| dblink | Specifies the name of the database link used to access objects in a remote database. If dblink is specified, object_name should be the name of an object in the remote database. |
Examples
Example 1: Cross-schema data access
Assume two users hr and finance, and hr needs to access the employee salary table in finance, but direct table access privileges cannot be granted for security reasons.
-- Create the salary table as the finance user
obclient> CREATE TABLE salary (
emp_id NUMBER,
emp_name VARCHAR2(50),
base_salary NUMBER,
bonus NUMBER
);
-- Create the synonym as the hr user
obclient> CREATE SYNONYM emp_salary FOR finance.salary;
-- Now, the hr user can query salary data through the synonym
obclient> SELECT * FROM emp_salary;
Example 2: Create a private synonym
Create private synonyms for commonly used tables to facilitate access by users in the current schema.
-- Execute as the system administrator
obclient> CREATE PUBLIC SYNONYM departments FOR hr.departments;
obclient> CREATE PUBLIC SYNONYM employees FOR hr.employees;
-- Any user can directly query the tables
obclient> SELECT * FROM employees;
Considerations
When creating a synonym, the following privilege requirements must be met:
To create a private synonym in the current schema, you need the
CREATE SYNONYMprivilege.To create a private synonym in a different schema, you need the
CREATE ANY SYNONYMprivilege.To create a public synonym, you need the
CREATE PUBLIC SYNONYMprivilege.The object for which you are creating a synonym may or may not exist, and you may or may not have access privileges to it.
/* Create the user synonym_user */
obclient> CREATE USER synonym_user IDENTIFIED BY synonym_user;
obclient> GRANT CREATE ON *.* TO synonym_user;
obclient> GRANT SELECT ON *.* TO synonym_user;
/* Connect as the synonym_user user */
obclient> CREATE OR REPLACE SYNONYM syn1 FOR tbl1;
ERROR-00600: internal error code, arguments: -5036, Access denied; you need (at least one of) the CREATE SYNONYM privilege(s) for this operation
/* Grant the CREATE SYNONYM privilege to the synonym_user user */
obclient> GRANT CREATE SYNONYM TO synonym_user;
obclient> CREATE OR REPLACE SYNONYM syn1 FOR tbl1;