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 a synonym for another synonym. Because a synonym is merely an alias, it is defined only in the data dictionary and does not occupy any storage space. This topic describes the types, basic characteristics, and privilege requirements of synonyms.

Types

Synonyms are classified into public synonyms and private synonyms.

Public synonyms are part of the PUBLIC group and can be accessed by all users. Private synonyms are owned by the object owner and can be accessed by other users only after they have been explicitly authorized.

Public synonyms are typically created by DBAs. If a regular user wants to create a public synonym, they must have the CREATE PUBLIC SYNONYM system privilege.

Basic characteristics

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

Creating synonyms

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;

Parameters

After a synonym is created, you can view it 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

The DROP SYNONYM statement is used to drop a synonym. The syntax is as follows:

DROP [PUBLIC] SYNONYM [ schema. ]synonym;

Parameters

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

When you create a synonym, make sure that the following conditions are met:

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

• To create a private synonym in 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` user. The synonym creation fails. */
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` user. */
obclient> CREATE SYNONYM syn_1 FOR t1;