Oracle Basics-Synonyms

First, what is synonymous

　　Synonyms are aliases of objects that are preceded by an object that simplifies SQL statements, hides the names and owners of objects, and provides public access to objects. There are two types of synonyms, the public synonym and the private synonym, respectively, the public synonym can be accessed by all database users, the private synonym can only be accessed in the current user mode, and cannot have the same name as the object or synonym in the current user. Oracle users who want to create synonyms must have permission to create synonyms.

Ii. Creating SQL syntax for public and private synonyms

　　(1) Creating a private synonym: Create OR REPLACE synonym syn_name (synonym name) for Obj_name (object name)

(2) Creating a public synonym: Create OR REPLACE publec syn_name (synonym name) for Obj_name (object name)

(3) Delete private synonym: drop synonym syn_name (synonym name)

(4) Delete public synonym: drop synonym publicly syn_name (synonym name)

(5) grant the private synonym permission to the user: Grant CREATE synonym to User (Oracle user)

(6) Grant the public synonym permission to the user: Grant CREATE publicly synonym to user (Oracle user)

Three, the effect demonstration

Note: All the SQL statements for the test are running in the ORACLE11GR2 environment.

The Scott user creates a synonym for the EMP table, prompting for no permission to create a synonym, 1-1, and then gives Scott permission to create a public synonym with a private synonym, as shown in 1-2

　　

Figure 1-1

　　

Figure 1-2

Next, create a private synonym for the EMP table, but only the Scott user has access to it, 1-3, the system and other users cannot access it, as shown in 1-4

　　

　　　　　　　　　　　　　　　　　　　　　　　　　Figure 1-3

　　

Figure 1-4

Next, create a public synonym for the EMP table, the Scott user can access, 1-5, the system and other users can also access, 1-6 shows

　　

Figure 1-5

　　

Figure 1-6

A little bit every day, there will be improved, ha ha ha haha ...

Oracle Basics-Synonyms