Three surprises brought about by the creation of Oracle Synonyms

Oracle Database provides the synonym management function. Oracle synonyms are aliases of database solution objects. They are often used to simplify object access and improve object access security. In Oracle databases, most database objects, such as tables, views, synonyms, sequences, stored procedures, functions, JAVA classes, packages, and so on, can be defined by the database administrator as needed. Using Oracle Database synonym management can surprise database administrators and application developers.

Surprise 1: Application Development can ignore the specific object name of the database.

In applications, you must constantly call Oracle database objects, such as tables, views, and objects. Therefore, when managing software development, if the application has completed the development of some functions. In this case, the database administrator must change the name of a database object. Then, the application needs to be adjusted. This is inconvenient in actual work. In particular, if some applications provide a function-defined platform, it will be very troublesome. For example, a custom report function is provided in an ERP software. In the system, there is a detailed list of supplier products. However, the user thinks that the report information is incomplete. The user wants to display the finished product corresponding to a part. In this case, you can modify the original database object to complete the customization. However, this is often not recommended. If you accidentally modify the error, it is difficult to modify it back. Therefore, if you need to make major changes to the original report on the custom platform, we recommend that you create another view to collect the information you need. In this case, the user not only needs to define the database object, but also needs to re-adjust the report format of the foreground application. Obviously, the workload is huge.

Now, with the Oracle synonym feature, all of this will become convenient. Because the foreground application does not need to be adjusted, you only need to redefine the database object synonym. This not only guarantees the recoverability of foreground applications, but also reduces the workload. This is the first surprise of Oracle Database synonyms.

Surprise 2: preventing applications from directly accessing database objects and improving database security.

When developing database applications, a common rule that should be followed is to avoid Directly Drinking database tables, views, functions, or other objects. This will greatly damage the security of the database.

For example, if a database object is directly called in a foreground application, attackers can easily understand the basic logical structure of the background database by analyzing the objects referenced by the application. This will obviously provide great convenience for attackers. Therefore, in order to ensure database security, it is best for foreground applications to access the background database through synonyms. In this case, it is difficult for an attacker to analyze the objects in the background database and the relationships between objects through calling the foreground application.

Surprise 3: simplified access to database objects.

Sometimes, the name of a database object may be very long, such as AD_USER_ROLE_NAME_TRL. If you enter such a long object name every time you call this database object, it will certainly cause a headache for the database administrator. However, if the name definition is too short, the readability will be poor. In other databases, the names of database objects are minimized at the cost of readability.

But in Oracle databases, you don't need to worry about it. Because we can set an Oracle synonym for this database object, just like an alias. In this case, you only need to access the object through synonyms without entering such a long object name.

In addition to the preceding three applications, in a distributed database system, a synonym is created for objects stored in a remote database, allowing you to operate remote objects like local objects, you do not need to specify the network connection name. Obviously, this will also bring great convenience to some Distributed Database administrators.

There are two types of Oracle Synonyms: public synonyms and solution synonyms.

1) Public Oracle synonym: it is owned by a special user group Public. As the name suggests, all users in the database can use public synonyms. Common synonyms are often used to mark common database objects, which must be referenced by everyone. When referencing these objects, you do not need to add a Public owner name before them as a limit. Otherwise, if a Public word is added before a Public synonym, the system will give an error message. However, in practice, we do not recommend that you use public synonyms. Because there are already many Common synonyms in the system. If you still define public synonyms for the database, it cannot simplify access to database objects.

2) solution Oracle synonym: it corresponds to a public synonym. It is owned by the user who created it or the solution. It is also known as a private synonym. Of course, the creator of this synonym can control whether other Users have the right to use their own solution synonyms. Solution synonyms are often used in application development to provide named solutions for application development. For example, if a database object, such as a table, is renamed or copied to a new table, and the new name and old name both need to be used, the database administrator can use the solution synonym, that is, create a special synonym for both the old and new names, but both point to the same database object.

In fact, it is quite simple to create a solution. However, it must have a preliminary condition, that is, it must have certain permissions, such as create synonym permissions. If you want to CREATE synonyms in others' solutions, you must also have the create any synonym permission. These are the first conditions that must be followed. Otherwise, synonyms cannot be created.

In addition, even if the database object does not exist, you can set synonyms for the expected database object. This feature is easy to use and can help database development teams or application development teams to collaborate more efficiently. For example, as long as the database administrator and application do a good job of defining object names and synonyms in advance, even if the database administrator has not developed a database object, foreground application developers can also reference the created database objects by referencing synonyms. In this case, it will not have any negative impact on other people's work because a certain step of work is not completed.

1. Detailed explanation of the entire process of creating user permissions in Oracle
2. Create user authorization and Change Password in Oracle
3. Example of code used to create a temporary tablespace in Oracle
4. How to Create a tablespace in Oracle
5. Example code for creating a tablespace in Oracle