Oracle Database Objects _ synonyms

A synonym is a database object that is an alias defined for a database object , and the primary purpose of using synonyms is to simplify the writing of SQL statements .

The concept and type of synonyms

Synonyms can be used to define an alias for an object of a user, or an object of another user , to simplify the writing of a command or program , and to use synonyms directly in a command or program instead of the original object .

You can create synonyms for tables, views, stored programs, sequences , and so on, or you can create synonyms for a synonym , or even a synonym for a nonexistent object , and the system only The synonym is used to verify that the object it represents is stored.
The synonym itself does not contain the data or code in the original object, it acts only as a pointer .

When using synonyms, the system finds the object it points to by the definition of the synonym, translating access to the synonym into the access to the original object .
Synonyms are only useful for user-friendly manipulation of database objects.

Oracle supports two types of synonyms, private synonyms and public synonyms .

Private synonyms are created by ordinary users and are only used by the user by default.
Public synonyms are typically created by DBAs and can be used by all users.

The meaning of public synonyms is that it represents an object that everyone can access.

Note: If you want to access a public synonym, you need to have access to the original object that the synonym points to.

In general, when you have permission to a related database object, you can do the related operations, but if you define a synonym, you simplify the writing of the SQL statement.

For example, when accessing database objects in other user modes, the schema name before the object can be omitted.

A user can arbitrarily create a synonym for another user's object, but this does not mean that the user has access to other users ' objects.

Because as mentioned earlier, only when synonyms are used does the system verify that the user has the appropriate access rights.

For example:

Create synonym emp for scott.emp;

SELECT * from EMP;

Note: Create a synonym EMP for the database object EMP in Scott user mode as a normal user.

Prompt for error:ORA-00942: The table or view does not exist without access rights.

In another case, you create a synonym for an object that does not exist:

Create synonym em for scott.em;

select * from EM;

Error:ORA-00980: Synonym conversion is no longer valid when using synonym EM to access source object.

Creation and deletion of synonyms

Users can create synonyms in their own mode, and they need to have the Create synonym system permission.
If you want to create synonyms in another user's schema, you need to have the create any synonym this system right.

Normal user If you want to create a public synonym, you need to have the synonym system permission.

The command to create a private synonym is create synonym, which has the following syntax rules:

CREATE synonym synonym for user name. Object name;

The syntax format for creating a public synonym is:

CREATE synonym synonym for user name. Object name;

Note: The user name here. The object name refers to other user patterns, because the synonym is generally created for easy access to objects in other user modes.

Of course, you can create synonyms in your own mode, and it makes no sense for ordinary users to create private synonyms.

For public synonyms, both ordinary users and DBA authority users can create public synonyms in their own user mode, which makes it easier for other users to access the database object.

For example, the DBA authority user creates a public synonym for the dynamic performance view at the beginning of v$.

Users can delete a synonym if they do not use it.

The command to delete the synonym is drop synonym, which has the syntax format:
DROP synonym synonym name;

Note: If you want to delete a public synonym, add the keyword synonym before the keyword.

A user can delete a synonym that he or she creates, and if you want to delete a synonym created by another user, you have the drop any synonym system permission.
DBAs can delete all common synonyms, and normal users need to have drop public synonym system permissions to remove common synonyms .
When a synonym is deleted, its related information is also removed from the data dictionary.

Note: For a normal user, even a public synonym that you create, you still need to have the drop synonym system permission to delete the synonym that you created.

Query for synonym information

A synonym is a database object whose related information is stored in a data dictionary.

There are three data dictionaries related to synonyms: User_synonyms, all_synonyms, dba_synonyms.

In the data dictionary user_synonyms, the synonyms that are owned by the current user are recorded. The columns of this table are defined and their meanings are as follows:

Synonym_name synonym Name

The object that the Table_owner points to belongs to the master

The name of the object that table_name points to

Db_link Database Links

Note: The Db_link column refers to the table_name obtained by Dblink when creating a synonym synonym.

If you want to query which synonyms the current user has created, and which objects each represents which user, you can execute the following SELECT statement to query:
SELECT Synonym_name, TABLE_OWNER, table_name from user_synonyms;

All synonyms that the current user can use are recorded in the data dictionary all_synonyms, including private synonyms and public synonyms.
All synonyms in the database are recorded in the data dictionary dba_synonyms, including private synonyms created by each user and common synonyms created by the DBA.
This view is accessible only to DBAs, whose structure includes all columns of the data dictionary user synonyms, and a column owner represents the creator of the synonym .
You can query the data dictionary dba_synonyms if you want to query the entire database scope for information about a synonym.
For example, to query all synonyms created by the user Scott, you can execute the following SELECT statement:

SELECT Synonym_name, TABLE_OWNER, table_name from dba_synonyms WHERE owner= ' SCOTT ';

If you want to query what synonyms the user Scott's Table dept has, you can execute the following SELECT statement:

SELECT Synonym_name, table_owner from dba_synonyms WHERE owner= ' SCOTT ' and table_name= ' EMP ';

If you want to query all common synonyms in the system, you can execute the following SELECT statement:

SELECT Synonym_name, table_owner from dba_synonyms WHERE owner= ' public ';

Note: The difference between Table_owner and owner:

Table_owner refers to the owner of the database object to which the synonym refers, generally the creator of the object, and owner is the creator of the synonym.

Oracle Database Objects _ synonyms