[Oracle] synonyms (synonym)

(i) the concept of synonyms

Synonyms are aliases for tables, views, indexes, or other schema objects in a database, similar to views, where synonyms do not occupy the actual storage space, and only the definition of synonyms in the data dictionary.

When you develop a database, you should avoid referencing tables, views, or other database objects directly, otherwise, when the structure of the table changes, it will affect the use of the application, this time need to recompile the program, if you create a synonym for the database object, you can use synonyms in the program, so that the structure of the table changes, It does not affect the application. In addition, synonyms can also be used to hide database object names and object owner information, and to simplify access to database objects.

(ii) Classification of synonyms

Synonyms are classified as private synonyms and public synonyms, and private synonyms can only be owned by the user who created them, and the user may control the rights of other users to their synonyms. Public synonyms are owned by the user group, and all users of the database can use common synonyms.

(iii) Synonyms-related permissions

(1) System privileges:

SYSTEM PRIVILEGE	MARK
CREATE synonym	Creates a private synonym in the current schema. You can also modify and delete synonyms
CREATE public synonym	Create a public synonym in the current schema
CREATE any synonym	Create a private synonym in any schema
DROP any synonym	Delete a private synonym from any schema
DROP public synonym	Delete a public synonym in the current schema

(2) Object permissions

None (not sure)

(iv) Use of synonyms

(1) Creating synonyms

CREATE [OR REPLACE] [public] synonym synonym_name for [Schema.] Object

(2) using synonyms

Users can use synonyms in their schema, use public synonyms, and use synonyms in other schemas, but users must also have permission to manipulate database objects that correspond to synonyms.

(3) Delete synonyms

DROP [public] synonym Synonym_name

* Users can delete the private synonym under their schema, to delete the public synonym, you need to have the drop synonym permissions, to delete the synonyms in other schemas, you need to have drop any synonym permissions.

(5) Dictionary of data related to synonyms

VIEW	MARK
Dba_synonyms	View all synonyms for a database
All_synonyms	View all synonyms that the current user can see
User_synonyms	View synonyms owned by the current user

[Oracle] synonyms (synonym)