1. Introduction

When accessing objects like tables, views, or procedures in another schema in Oracle, there are certain rules to follow, even if you have the required privileges to interact with the object.

By default, if you want to access an object (for example, a table) in a different schema, you cannot simply reference it by its object name alone. You must qualify the object name with the schema name in which it resides.

For example, if you are logged in as USER_A and want to access the employees table that belongs to USER_B, you need to write:

SELECT * FROM USER_B.employees;

2. Privileges

Regardless of whether you are using a fully qualified name (e.g., USER_B.employees) or a public synonym, you must have the appropriate privileges to access the object. For tables, you need SELECT privileges; for procedures, you need EXECUTE privileges, and so on.

Even if the public synonym exists, lacking the necessary privileges will result in a permission error. Therefore, the creation of a public synonym simplifies access but does not bypass security and privilege checks.

3. Example Scenario

Suppose USER_A wants to run a query on the sales table that exists in USER_B‘s schema.

Without a synonym, USER_A must write:

SELECT * FROM USER_B.sales;