Synonyms

A synonym is an alias for a database object (such as a table, view, materialized view, sequence, stored procedure, function, package, type, or user-defined type). You can also create synonyms for other synonyms. A synonym is just an alias and does not occupy any storage space. This topic describes the types, characteristics, and privileges of synonyms.

Synonym types

A synonym can be a public synonym or a private synonym.

A public synonym belongs to the PUBLIC group and is accessible to all users. A private synonym belongs to the owner of the object and is accessible only to other users if they are explicitly authorized.

Public synonyms are usually created by DBAs. If you want to create a public synonym, you need the CREATE PUBLIC SYNONYM system privilege.

Features

Synonyms extend the use of the database and allow seamless interaction between different database users. They are often used to simplify object access and enhance object access security.

Create a synonym

The CREATE SYNONYM statement is used to create a synonym. The syntax is as follows:

CREATE [ OR REPLACE ] [ PUBLIC ]
SYNONYM [ schema. ]synonym
FOR [ schema. ]object;

Parameter description

After a synonym is created, you can view the synonyms that you created in the USER_SYNONYMS, ALL_SYNONYMS, or DBA_SYNONYMS view.

Examples

• Create a private synonym.

  obclient> CREATE TABLE t1(c1 INT);
  obclient> CREATE SYNONYM s1 FOR t1;
  obclient> INSERT INTO s1 VALUES(1);
  obclient> SELECT * FROM s1;
  +------+
  | c1   |
  +------+
  |    1 |
  +------+
  1 row in set
  

• Create a public synonym.

  obclient> CREATE PUBLIC SYNONYM syn_pub FOR t1;
  obclient> SELECT * FROM syn_pub;
  +------+
  | c1   |
  +------+
  |    1 |
  +------+
  1 row in set
  

Drop a synonym

You can execute the DROP SYNONYM statement to drop a synonym. The syntax is as follows:

DROP [PUBLIC] SYNONYM [ schema. ]synonym;

Parameter description

Examples

• Drop a private synonym.

  obclient> DROP SYNONYM test.s1;
  Query OK, 0 rows affected
  

• Drop a public synonym.

  obclient> DROP PUBLIC SYNONYM syn_pub;
  Query OK, 0 rows affected
  

Considerations

To create a synonym, you must meet the following privilege requirements:

• To create a private synonym under the current user, you must have the CREATE SYNONYM privilege.

• To create a private synonym under a user other than the current user, you must have the CREATE ANY SYNONYM privilege.

• To create a PUBLIC synonym, you must have the CREATE PUBLIC SYNONYM privilege.

• You do not need the privilege to access the object for which the synonym is created.

/* Connect to the sys user. */
obclient> CREATE USER syn_user IDENTIFIED BY syn_user;
obclient> GRANT CREATE ON syn_user.* TO syn_user;
obclient> GRANT SELECT ON syn_user.* TO syn_user;

/* Connect to the syn_user account and create a synonym. */
obclient> CREATE SYNONYM syn_1 FOR t1;
ERROR-00600: internal error code, arguments: -5036, Access denied; you need (at least one of) the CREATE SYNONYM privilege(s) for this operation

/* Connect to the sys user and grant the CREATE SYNONYM privilege. */
obclient> GRANT CREATE SYNONYM ON *.* TO syn_user;

/* Connect to the syn_user account. */
obclient> CREATE SYNONYM syn_1 FOR t1;