Purpose
This statement is used to create a synonym.
Syntax
CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM [ synonym_schema. ]synonym_name
FOR [ object_schema. ]object_name;
Parameters
| Parameter | Description |
|---|---|
| OR REPLACE | Indicates whether to recreate an existing synonym with the new definition. |
| 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, public synonyms are used only when the object schema is not specified. 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 clause is not required for the synonym. |
| 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 corresponding to the synonym. |
Examples
Create a synonym for a table.
obclient> CREATE TABLE tbl1(col1 INT,col2 INT); Query OK, 0 rows affected obclient> CREATE SYNONYM syn1 FOR tbl1; Query OK, 0 rows affected obclient> INSERT INTO syn1 values(1,1); Query OK, 1 row affected obclient> SELECT * FROM syn1; +------+------+ | COL1 | COL2 | +------+------+ | 1 | 1 | +------+------+ 1 row in setCreate a
PUBLICsynonymobclient> CREATE PUBLIC SYNONYM syn2 FOR tbl1; Query OK, 0 rows affected
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 schema other than the current one, you need the
CREATE ANY SYNONYMprivilege.To create a
PUBLICsynonym, you need theCREATE PUBLIC SYNONYMprivilege.The object for which a synonym is being 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;
Query OK, 0 rows affected
obclient> GRANT CREATE ON *.* TO synonym_user;
Query OK, 0 rows affected
obclient> GRANT SELECT ON *.* TO synonym_user;
Query OK, 0 rows affected
/*Connect to 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;
Query OK, 0 rows affected
obclient> CREATE OR REPLACE SYNONYM syn1 FOR tbl1;
Query OK, 0 rows affected