Question

Write syntax for creating synonyms with example

Answer 1

Of course. Here is a detailed explanation of the syntax for creating synonyms in SQL, including their purpose, types, and practical examples.

---

What is a Synonym?

A Synonym is an alternative name or an alias given to a database object, such as a table, view, sequence, or stored procedure. It acts as a permanent nickname or shortcut.

The primary purpose of a synonym is to simplify access to objects by providing a shorter, more memorable name, and to hide the underlying object's actual name and owner (schema).

Why Use a Synonym?

1. Simplicity and Abstraction: It simplifies queries by hiding the complexity of an object's full name, especially when it's in another user's schema (e.g., you can query employees instead of human_resources_schema.employee_master_table).
2. Location Transparency: A user can access an object without knowing which schema owns it. If the object is moved to a different schema, only the synonym needs to be updated, not every application that references it.
3. Backward Compatibility: If you need to rename a table, you can create a synonym with the old table name that points to the new one. This allows old code and reports to continue working without modification.

---

Types of Synonyms

There are two types of synonyms:

1. Private Synonym:
   This is the default type.
   It belongs to the specific schema in which it was created.
   * Only the owner of the synonym (or users granted specific privileges by the owner) can use it.

2. Public Synonym:
   This is accessible to all users in the database.
   It is owned by a special user group called PUBLIC.
   * Typically, only a Database Administrator (DBA) has the privilege to create public synonyms.

Important Note: A synonym is just a pointer; it does not grant permissions. The user trying to access the object through a synonym must still have the necessary privileges (e.g., SELECT, INSERT) on the underlying object itself.

---

Syntax for Creating Synonyms

The general syntax is straightforward:

`sql
CREATE [OR REPLACE] [PUBLIC] SYNONYM synonym_name
FOR object_name;
`

Breakdown of the Syntax:

• CREATE SYNONYM: The command to create a new synonym.
• [OR REPLACE]: An optional clause. If a synonym with the same name already exists, this will overwrite it. This is useful in development scripts.
• [PUBLIC]: An optional keyword. If specified, a public synonym is created. If omitted, a private synonym is created.
• synonym_name: The new, shorter name (the alias) you want to create.
• FOR object_name: The full, qualified name of the original database object you are creating the alias for. This can be in the format schema_name.table_name.

---

Example: Step-by-Step

Let's walk through a common scenario where a user in one schema needs to frequently access a table in another schema.

Scenario:
We have a central HR schema that owns an important employees table.
We have a user named REPORTING_USER who needs to run queries against this employees table.

Step 1: Create the Users and the Original Table (as a DBA or system user)

`sql
-- Create the user who will own the table
CREATE USER hr IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO hr;

-- Create the user who needs to access the table
CREATE USER reporting_user IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO reporting_user;
`

Step 2: Create the Table in the HR Schema

Connect as the hr user and create the table.

`sql
-- Connect as hr user
CONNECT hr/password;

CREATE TABLE employees (

employee_id   NUMBER PRIMARY KEY,
first_name    VARCHAR2(50),
last_name     VARCHAR2(50),
salary        NUMBER(10, 2)

);

INSERT INTO employees VALUES (101, 'John', 'Doe', 60000);
INSERT INTO employees VALUES (102, 'Jane', 'Smith', 75000);
COMMIT;
`

Step 3: Grant Permission to the Other User

The hr user must grant permission to reporting_user to access the employees table. Without this step, the synonym will not work.

`sql
-- Still connected as hr user
GRANT SELECT ON employees TO reporting_user;
`

Step 4: Create the Synonym

Now, reporting_user can connect and create a private synonym to simplify access.

`sql
-- Connect as the user who needs the shortcut
CONNECT reporting_user/password;

-- Without a synonym, the user must type this long name:
SELECT first_name, salary FROM hr.employees WHERE employee_id = 101;

-- Now, let's create a synonym to make it easier
CREATE SYNONYM emp FOR hr.employees;
`

Step 5: Use the Synonym

After the synonym is created, reporting_user can use the short name emp in all their queries.

`sql
-- This query now works perfectly for reporting_user
SELECT first_name, salary FROM emp WHERE employee_id = 102;
`

Result:
The query using emp is much cleaner and easier to write than SELECT ... FROM hr.employees .... The reporting_user doesn't even need to know that the employees table is owned by the hr schema.

---

How to Drop a Synonym

If a synonym is no longer needed, you can remove it using the DROP SYNONYM command.

Syntax:
`sql
DROP [PUBLIC] SYNONYM synonym_name;
`

Example:
The reporting_user can drop their private synonym:
`sql
DROP SYNONYM emp;
`

A DBA would be needed to drop a public synonym:
`sql
DROP PUBLIC SYNONYM all_employees;
`