Oracle Security

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 ...