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 if the synonym name 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. When resolving object references, a public synonym is used only when the object does not specify 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 schema. 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 schema. |
| object_name | Specifies the name of the object for the synonym. |
| dblink | Specifies the name of the database link for accessing 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: Accessing data across schemas
Assume there are two users hr and finance, and hr needs to access the employee salary table in the finance schema, 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 the salary data using the synonym
obclient> SELECT * FROM emp_salary;
Example 2: Creating a private synonym
Create a private synonym for a commonly used table to facilitate access by users within 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 now 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 the synonym is created can either exist or not, and you may or may not have access privileges to it.
/*Create a user named 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;