Synonyms

A synonym is an alias for a schema object of an Oracle tenant. You can create a synonym for a table, view, materialized view, sequence, stored procedure, function, package, object type, user-defined object type, or another synonym. As an alias, a synonym requires no storage other than its definition in the data dictionary. This topic describes the types, basic features, and required privileges of synonyms.

Synonym types

You can create private and public synonyms.

A public synonym can be used by every database user and is owned by a group named PUBLIC. A private synonym is contained in the schema of a specific user and is available only to its owner and users who are granted access to the underlying object.

Public synonyms can be created by database administrators and regular users who are granted the CREATE PUBLIC SYNONYM privilege.

Basic features

Synonyms extend the availability of database objects and allow them to be accessed by different database users with the required privileges. This design simplifies and secures access to database objects.

Create a synonym

You can execute the CREATE SYNONYM statement to create a synonym. The following syntax can be used:

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

Parameters

You can check the synonyms that you have created in the USER_SYNONYMS, ALL_SYNONYMS, and DBA_SYNONYMS views.

Sample code

• 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 synonyms. The following syntax can be used:

DROP [PUBLIC] SYNONYM [ schema. ]synonym;

Parameters

Sample code

• 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

The following part describes the privileges required for creating different types of synonyms:

• To create a private synonym in your own schema, you must have the CREATE SYNONYM privilege.

• To create a private synonym in a schema of another 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 to have the access privilege on the underlying object of the synonym that you want to create.

/*Log on to the database as 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;

/*Log on to the database as the SYS user. The synonym is not created.*/
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

/*Log on to the database as the SYS user and grant the user the CREATE SYNONYM privilege.*/
obclient> GRANT CREATE SYNONYM ON *.* TO syn_user;

/*Log on to the database as the SYS user.*/
obclient> CREATE SYNONYM syn_1 FOR t1;