Oracle synonym surprises

Guide: SynonymIt is an alias for database solution objects and is often used to simplify object access and improve object access security. When using synonyms,Oracle DatabaseTranslate it into the name of the corresponding solution object. Similar to a view, synonyms do not occupy the actual storage space. Only the definition of synonyms is saved in the data dictionary. 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 synonym function, 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 a 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 Database Synonyms: public synonyms and solution synonyms.

A Public synonym is owned by a special user group named 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.

The solution synonym corresponds to a public synonym. It is owned by the user who created the solution 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.

Pay attention to the following issues when using solution Synonyms.

First, pay attention to the differences between using your own solution and the synonym of others' solutions. After a user creates a synonym in his/her own solution, the user has all the permissions of the object. You can use this object synonym as if you were using the original database object. Such as querying data, inserting, modifying, and deleting records. However, unlike public synonyms, scheme synonyms cannot be used no matter whether other users are granted the object permissions for the objects corresponding to the schema synonyms, because synonyms are private. That is to say, if there is a USER table. Although User A has the create any synonym permission, user A can CREATE synonyms for this database object. However, this table does not have the Select permission. User A still cannot use this synonym to access this database object. Otherwise, the database prompts "the table or view does not exist ". If User A wants to access the User table through A synonym, the User A must have the Select and other permissions of the table to perform corresponding operations on the table using the synonym. That is to say, if you create a scheme synonym pointing to objects in other schemes in your own scheme, you can only access objects with object permissions after being granted the object access permission. In addition, because the solution synonym is private, other users cannot access an object even if they have the relevant permissions to use the solution synonym. This is the private nature of solution synonyms.

Second, pay attention to the order of name resolution in the Oracle database. For example, when we access a table using the FROM user clause, how does the database query whether this object exists in the database? It has a certain resolution order. If an unlimited' database object, such as tables, views, and stored procedures, is referenced in the program code we write, the database will query whether referenced objects exist in a certain order. Generally, verification is performed in the following order. First, check whether the current user owns the object. Second, check whether the object name is a synonym owned by the current user. Therefore, in practical applications, our database administrators should try to use solution synonyms and use less public synonyms. This is very helpful for improving the database operation efficiency.

Third, the data dictionary view is a good example of public synonyms. Sometimes, we can use its configuration to manage our public synonyms and solution synonyms. In practice, we can also design all database object names in advance and match them with all synonyms. The script is then used for one-time generation. If synonyms are generated one by one, the workload is still quite large.

It can be seen that Oracle synonyms have many benefits. I hope you can better master Oracle synonyms through the study in this article. I believe this will be useful in your future work.