6.3 Synonyms This section looks at how and why we use synonyms in a database for security. As we said in Chapter 3, a synonym is really a nickname for something else. In an Oracle database, a synonym can represent one of several kinds of objects a table, view, sequence, snapshot, program, procedure, function, or even another synonym. For our discussion, we will use a table as our reference object. Normally, if you want to access an object, you have to know the owner of the object and the object name . If you want to look at something in the employee table owned by mary , you would say: SELECT * FROM mary.employee; The "mary.employee" reference is called a fully qualified path name . Because you have supplied all the information Oracle needs to locate the object, after verifying the statement's syntax for correctness and checking to see that you have privilege to access the mary.employee table, your query will be processed and the results returned to you. Suppose, though, that you do not know the owner's name and you issue the following query: SELECT * FROM employee; Since you did not include the owner or schema name, the RDBMS will assume that you have a table called employee and will first look in your own object area and your list of private synonyms for the table. If Oracle does not find the table within your own area, it will look at publicly available objects and public synonyms that have been defined to include the location name (i.e., a synonym). If Oracle does not find an employee table reference in any of these areas, you will get an error message such as the following: ORA-00942: table or view does not exist This is probably the most frequently encountered error message. The solution then would seem to be to always code everything with fully qualified path names , right? Not really, since there is a problem with this approach. Such statements are usually embedded into applications, and if the owner of the table changes, all the applications have to be modified and recompiled. So far, we've been talking about object ownership, but it is equally possible that the table name may be changed, and again the applications would have to be changed. It has never been a great idea to hardcode path names into applications. Oracle solved this problem by providing the ability to create a synonym that contains the object's location within its definition. All you have to do is use the synonym in your application and Oracle will resolve the object's true location. 6.3.1 About Public and Private Synonyms There are two types of synonyms: public and private. A private synonym is owned by a specific user and is available for use only by that user , even if he or she issues privileges to other users. A public synonym is owned by a special user named public (which is really a role). Even though a public synonym exists for an object, you may not be able to see that object unless you have the SELECT privilege on that object. Figure 6.1 shows the contrast between private and public synonyms. Figure 6.1. Public vs. private synonyms  Private synonyms are not unusual. They are frequently used by developers as a way to test stored programs, functions, snapshots, etc., without interfering with other developers. However, public synonyms are used more frequently in production systems. Only a user who has been granted the CREATE PUBLIC SYNONYM privilege can create a public synonym. Assuming that mary has the appropriate privileges, the following command could be issued: SQL> CREATE PUBLIC SYNONYM employee 2* FOR mary.employee; The result is that the synonym "employee" will be found as an entry in the public list of objects. When found, it will be used as the target object. If permissions have been granted on it to the user, the system will navigate to the location of mary 's employee table. Tips for Creating Synonyms When you are using synonyms, note the following: -
Creating the public synonym or, for that matter, any synonym, does not convey any default ability to see the underlying object. There are no default privileges associated with a synonym. -
An instance of a public synonym may occur only once. That is, if mary creates the public synonym "admin_emp" for the mary.employee table, no other user may create a synonym of the same name. This makes sense because public covers everybody and there is no need for a subsequent definition. | 6.3.2 Examples Using Public and Private Synonyms In this section, you will find several practical examples of how public and private synonyms work. 6.3.2.1 Hiding the tables and owner This example demonstrates the ability to "hide" the underlying tables and owner from a user. The steps used to demonstrate this follow: -
mary creates a table and a public synonym. -
mary grants the SELECT privilege to public on the public synonym. -
ralph examines the table through the synonym name. -
ralph attempts to see the underlying object and owner but fails. -
ralph can only see the underlying object if he knows the object name and the owner name. A user whose name is mary creates a table with two columns : SQL> CREATE TABLE employee 2 (ssan NUMBER(9), 3 name VARCHAR2(35)); Table created. mary now creates a public synonym. The privilege to do so was previously granted. SQL> CREATE PUBLIC SYNONYM empl 2 FOR mary.employee; Synonym created. SELECT is granted to public , making the table accessible to all database users. SQL> GRANT select ON empl TO public; Grant succeeded. Connect as a different user. Note that logout is not required. SQL> CONNECT ralph/xyz_4u Connected. ralph describes the synonym and gets the structure of the real object: mary.employee. SQL> DESCRIBE empl Name Null? Type ------------------------------- -------- ---- SSAN NUMBER(9) NAME VARCHAR2(35) ralph attempts to describe the underlying object, but does not fully qualify the object. The system attempts to find the employee object, but as owned by ralph , not mary . An error message results since ralph does not own an employee table. SQL> DESCRIBE employee Object does not exist. ralph now describes the object using the fully qualified name that includes the object's owner. SQL> DESCRIBE mary.employee Name Null? Type ------------------------------- -------- ---- SSAN NUMBER(9) NAME VARCHAR2(35) 6.3.2.2 Using private synonyms and path names This example shows that, even though privilege is granted to everyone on a private synonym, no one will be able to see the object unless they use a fully qualified name. In the example, the following steps will be performed. -
The original user, mary , creates a private synonym. -
mary grants the SELECT privilege on the private synonym to public . -
When another user attempts to use the private synonym, an error is returned. The user must use the fully qualified name as for the table in the first example. Connect as mary . SQL> CONNECT mary/abc75!d Connected. Create a private synonym for the employee table. SQL> CREATE SYNONYM people 2 FOR mary.employee; Synonym created. Grant SELECT on the private synonym to public . SQL> GRANT select ON people TO public; Grant succeeded. Connect as another user and attempt to access the public synonym. SQL> CONNECT ralph/xyz_4u Connected. SQL> DESCRIBE people Object does not exist. Now fully qualify the object with owner and name, as in the following. SQL> DESCRIBE mary.people Name Null? Type ------------------------------- -------- ---- SSAN NUMBER(9) NAME VARCHAR2(35) 6.3.2.3 Using public synonyms without user grants This example demonstrates that if access is not given for a public synonym, a user will not be able to see that object. -
mary will REVOKE SELECT on the private synonym ( people ) and then DROP the synonym. -
mary will CREATE a new public synonym called people . -
mary will not give any public access for the synonym. -
ralph will try to "see" the synonym and will not be able to do so. Connect as mary and fix the problem. SQL> CONNECT mary/abc75!d Connected. SQL> REVOKE select ON people FROM public; Revoke succeeded. SQL> DROP SYNONYM people; Synonym dropped. SQL> CREATE PUBLIC SYNONYM people FOR mary.employee; Synonym created. Connect as ralph and attempt to use the public synonym "people." SQL> CONNECT ralph/xyz_4u Connected. Just try "people" first. SQL> DESCRIBE people Object does not exist. Now fully qualify the object. The following illustrates a public synonym without access privileges. SQL> DESCRIBE mary.people Object does not exist. SQL> SELECT owner, object_name, object_type 2 FROM all_objects 3 WHERE object_name like 'PEO%'; OWNER OBJECT_NAME OBJECT_TYPE --------------------- -------------------------- ------------ PUBLIC PEOPLE SYNONYM 6.3.2.4 Using no synonyms/user grants and private synonyms with no grants This final example demonstrates that even though public synonyms exist for objects, the objects cannot be accessed unless privilege to do so is granted to the user. The steps used in this example are as follows : -
mary drops the public synonym. (Remember that no access was granted to either the base table employee or the synonym "people.") -
The SELECT privilege is then given on the employee table to ralph , but no public synonym is created. -
When ralph attempts to access the employee table, he must use the fully qualified path. -
ralph then creates a private synonym for the employee table. -
Finally, a completely new user attempts to use both the mary.employee table, and the ralph.people synonym. Both efforts will be resoundingly unsuccessful . Connect as user mary . SQL> CONNECT mary/abc75!d Connected. Drop the public synonym. SQL> DROP PUBLIC SYNONYM people; Synonym dropped. Grant SELECT on the employee table to ralph only: SQL> GRANT select ON employee TO ralph; Grant succeeded. Connect as ralph and access the employee table. SQL> CONNECT ralph/xyz_4u Connected. SQL> DESCRIBE employee Object does not exist. Try the fully qualified name. SQL> DESCRIBE mary.employee Name Null? Type ------------------------------- -------- ---- SSAN NUMBER(9) NAME VARCHAR2(35) Create a private synonym for mary 's employee table. SQL> CREATE SYNONYM people FOR mary.employee; Synonym created. Describe the "people" table. SQL> DESCRIBE people Name Null? Type ------------------------------- -------- ---- SSAN NUMBER(9) NAME VARCHAR2(35) Connect as user scott . SQL> CONNECT scott/tiger Connected. Try to use the mary employee table. SQL> DESCRIBE employee Object does not exist. SQL> DESCRIBE mary.employee Object does not exist. Try to use the ralph private synonym "people." SQL> DESCRIBE people Object does not exist. SQL> DESCRIBE ralph.people Object does not exist. See if the data dictionary has any information on either object. In the following, access is granted but no synonym exists: SQL> SELECT owner, object_name, object_type 2 FROM all_objects 3 WHERE object_name IN ('EMPLOYEE','PEOPLE'); no rows selected A public synonym can be used to provide access to an object while hiding the owner or schema location of that object. This is referred to as location transparency . However, a synonym does not prevent a user with privileges from accessing the object directly. Also, as illustrated in Chapter 6, a public synonym is always listed in the ALL_OBJECTS data dictionary view, and that view is available to every user in the database. Note that until access was granted, the synonym was as unavailable as the table. | 
