13.3 Synonyms

13.3 Synonyms

In enterprise database environments, schema architectures often span multiple user schemas, remote database servers, and evolving object names. Referencing database objects using full schema-qualified names (such as hr.employees or finance.monthly_ledger@ny_sales_link) introduces tight coupling, security exposure of schema topologies, and extensive code refactoring when objects move.

A Synonym is an alias or alternative pointer for any database schema object. Synonyms store no physical data of their own; they store a metadata definition in the Oracle data dictionary (USER_SYNONYMS, ALL_SYNONYMS, DBA_SYNONYMS) that transparently redirects SQL statements to the underlying target object.

---

Supported Objects and Purposes of Synonyms

Synonyms can be created for virtually any named database object:

• Tables and Object Tables
• Views and Materialized Views
• Sequences
• Stored PL/SQL Procedures, Functions, and Packages
• Java Stored Assets and User-Defined Types
• Other Synonyms (Synonym Chaining)

Core Architectural Benefits

1. Location Transparency: Insulates client applications from schema renames, table migrations, and remote database link (@dblink) locations. If a table moves from schema HR to GLOBAL_HR, updating the synonym redirects all applications without modifying a single line of application SQL.
2. Simplifying SQL Syntax: Eliminates the need for users to prefix object names with schema qualifiers (hr.employees becomes simply emp).
3. Security Abstraction: Obscures the actual schema owner and object names from end users and external client tools.

---

Private Synonyms vs. Public Synonyms

Oracle Database provides two distinct categories of synonyms: Private Synonyms and Public Synonyms.

+-------------------------------------------------------------------------+
|                    PRIVATE VS. PUBLIC SYNONYMS                          |
+-------------------------------------------------------------------------+
|                                                                         |
|  USER SCHEMA (e.g. 'SCOTT'):       DATABASE-WIDE ('PUBLIC' SCHEMA):     |
|  CREATE SYNONYM emp FOR hr.emp;    CREATE PUBLIC SYNONYM dept FOR hr.dept;|
|  - Resides in SCOTT schema         - Belongs to PUBLIC schema           |
|  - Owned by SCOTT                  - Owned by PUBLIC                    |
|  - Visible to SCOTT only           - Visible to ALL database users      |
|  - Requires CREATE SYNONYM         - Requires CREATE PUBLIC SYNONYM     |
|                                                                         |
+-------------------------------------------------------------------------+

Exhaustive Comparison Matrix

---

Synonym Creation Syntax & Clauses

Creating a synonym utilizes the CREATE SYNONYM DDL statement:

CREATE [OR REPLACE] [PUBLIC] SYNONYM synonym_name
FOR [schema.]object_name[@dblink_name];

Syntax Elements Breakdown

• OR REPLACE: Re-creates the synonym if it already exists, allowing the target object definition to change without dropping the synonym or invalidating dependent object privileges.
• PUBLIC: Creates a public synonym accessible to all users. Omitting PUBLIC creates a private synonym in the current schema.
• synonym_name: The identifier assigned to the synonym.
• FOR [schema.]object_name: The target object being aliased. If schema is omitted, Oracle assumes the object resides in the current user's schema.
• @dblink_name: Specifies a database link to an object residing on a remote Oracle database server.

Creation Examples

-- 1. Create a private synonym in the current schema pointing to HR.EMPLOYEES
CREATE SYNONYM emp_table FOR hr.employees;

-- 2. Create or replace a public synonym pointing to HR.DEPARTMENTS
CREATE OR REPLACE PUBLIC SYNONYM departments FOR hr.departments;

-- 3. Create a private synonym pointing to a remote table via database link
CREATE SYNONYM remote_sales FOR sales.orders@ny_headquarters_db;

---

Oracle 3-Tier Object Name Resolution Hierarchy

When an unqualified identifier (e.g., SELECT * FROM employees;) appears in a SQL statement, the Oracle SQL parser resolves the object name using a strict 3-Tier Name Resolution Hierarchy.

+-------------------------------------------------------------------------+
|                 ORACLE OBJECT NAME RESOLUTION ENGINE                    |
+-------------------------------------------------------------------------+
|                                                                         |
|  User executes: SELECT * FROM INVOICES;                                 |
|                                                                         |
|                          +-------------------+                          |
|                          |  TIER 1: LOCAL    |                          |
|                          |  SCHEMA OBJECT    |                          |
|                          +---------+---------+                          |
|                                    |                                    |
|                   +----------------+----------------+                   |
|                   | (Found)                         | (Not Found)       |
|                   v                                 v                   |
|          [ Access Local Table,             +-------------------+        |
|            View, or Private                |  TIER 2: PRIVATE  |        |
|            Synonym 'INVOICES' ]            |  SYNONYM (LOCAL)  |        |
|                                            +---------+---------+        |
|                                                      |                  |
|                                     +----------------+----------------+ |
|                                     | (Found)                         | |
|                                     v                                 v |
|                            [ Resolve Private                 +--------+-|
|                              Synonym Target ]                | TIER 3:  |
|                                                              | PUBLIC   |
|                                                              | SYNONYM  |
|                                                              +----+-----+|
|                                                                   |     |
|                                                  +----------------+     |
|                                                  | (Found)        | (No)|
|                                                  v                v     |
|                                         [ Resolve Public   [ ORA-00942: |
|                                           Synonym Target ]   Table or   |
|                                                              View does  |
|                                                              not exist] |
|                                                                         |
+-------------------------------------------------------------------------+

Precedence and Shadowing / Masking Rules

1. Local Objects Always Prevail: If user SCOTT owns a local table named DEPARTMENTS, any query executed by SCOTT referencing DEPARTMENTS will always access SCOTT.DEPARTMENTS, completely ignoring any public synonym named DEPARTMENTS.
2. Private Synonyms Shadow Public Synonyms: If user SCOTT creates a private synonym named DEPARTMENTS pointing to HR.DEPARTMENTS, SCOTT's query will access HR.DEPARTMENTS, masking the public synonym.
3. Public Synonyms Serve as Fallbacks: A public synonym is resolved only if no local object and no private synonym with that name exists in the querying user's schema.

---

Privilege Decoupling: Synonyms Do NOT Confer Security Grants

A critical conceptual trap on the 1Z0-071 examination is the relationship between synonyms and object privileges.

[!CRITICAL] Synonyms are Pointers, Not Permissions: Creating a synonym (private or public) does NOT grant any privileges on the underlying object. A synonym is purely an alias.

Privilege Verification Scenario

1. User ADMIN creates a public synonym: CREATE PUBLIC SYNONYM emp FOR hr.employees;
2. User SCOTT connects and runs: SELECT * FROM emp;
3. Result: If SCOTT has not been granted SELECT on hr.employees (either directly or via a role), the query fails with: ORA-00942: table or view does not exist (or ORA-01031: insufficient privileges).
4. For SCOTT to query emp, HR or a DBA must execute: GRANT SELECT ON hr.employees TO scott; (or TO PUBLIC).

---

Dangling Synonyms and Object Lifecycle Analysis

Oracle does not enforce referential integrity checks between a synonym and its underlying target object at creation time.

1. Creating Synonyms for Non-Existent Objects

You can successfully create a synonym for an object that does not currently exist. Oracle compiles the synonym metadata into the data dictionary without error.

-- Table future_inventory does not exist yet!
CREATE SYNONYM inv_syn FOR future_inventory;
-- Synonym created.

-- Attempting to query the synonym fails at runtime:
SELECT * FROM inv_syn;
-- ORA-00942: table or view does not exist

2. The Dangling Synonym Phenomenon

If a base table is dropped, any synonyms pointing to that table are not automatically dropped or updated. They become dangling synonyms.

+-------------------------------------------------------------------------+
|                     DANGLING SYNONYM LIFECYCLE                          |
+-------------------------------------------------------------------------+
|                                                                         |
|  1. CREATE SYNONYM emp_syn FOR employees;                               |
|     - Synonym points to valid EMPLOYEES table                           |
|     - Query SELECT * FROM emp_syn succeeds                              |
|                                                                         |
|  2. DROP TABLE employees;                                               |
|     - EMPLOYEES table is dropped from database                          |
|     - EMP_SYN remains in USER_SYNONYMS (Dangling Synonym)                |
|                                                                         |
|  3. SELECT * FROM emp_syn;                                              |
|     - Fails with ORA-00942: table or view does not exist                |
|                                                                         |
|  4. CREATE TABLE employees (...);                                       |
|     - Table recreated with matching name                                |
|     - EMP_SYN immediately works again without re-creation!              |
|                                                                         |
+-------------------------------------------------------------------------+

---

Dropping Synonyms

Synonyms are dropped using the DROP SYNONYM or DROP PUBLIC SYNONYM statement:

-- Drop a private synonym in the current schema
DROP SYNONYM emp_table;

-- Drop a public synonym (requires DROP PUBLIC SYNONYM privilege)
DROP PUBLIC SYNONYM departments;

Rules for Dropping Synonyms

1. Base Object Unaffected: Dropping a synonym deletes only the alias definition from USER_SYNONYMS or ALL_SYNONYMS. It has zero effect on the underlying base table, view, sequence, or stored procedure.
2. Public Synonym Syntax Requirement: To drop a public synonym, the PUBLIC keyword must be included (DROP PUBLIC SYNONYM name;). Executing DROP SYNONYM name; only attempts to drop a private synonym of that name in the user's schema, and fails with ORA-01434: private synonym to be dropped does not exist when no such private synonym exists. ORA-01432: public synonym to be dropped does not exist is the matching error for DROP PUBLIC SYNONYM.

---

Querying Synonym Metadata in Data Dictionary

SELECT synonym_name, table_owner, table_name, db_link
FROM user_synonyms
WHERE synonym_name = 'EMP_TABLE';