🎯 Practice Oracle SQL: Use Synonyms for Object Access

🎯 Practice: Use Synonyms for Simplified Object Access

Synonyms in Oracle SQL allow users to access database objects (like tables, views, or procedures) without needing to reference their full schema-qualified names. Follow this practice to create and use synonyms effectively in your database environment.

1. Suppose you have a table in another schema

-- In schema HR:
CREATE TABLE EMPLOYEE (
  EMP_ID     NUMBER,
  EMP_NAME   VARCHAR2(50),
  DEPT_ID    NUMBER
);

2. Grant SELECT access to another user

GRANT SELECT ON HR.EMPLOYEE TO APPUSER;

This gives APPUSER permission to use the HR.EMPLOYEE table.

3. Log in as APPUSER and create a synonym

-- In APPUSER schema:
CREATE SYNONYM EMP FOR HR.EMPLOYEE;

Now APPUSER can query EMP instead of using the full HR.EMPLOYEE.

4. Use the synonym in a SELECT query

SELECT * FROM EMP;

This will return rows from HR.EMPLOYEE using the synonym EMP.

5. Drop the synonym

DROP SYNONYM EMP;

Once dropped, you'll have to use the full object name again unless you recreate it.

---

📝 Additional Practice:

• Create a PUBLIC SYNONYM (DBA only) and query from any user.
• Create a synonym for a procedure or view and call it from another schema.
• Use synonyms in joins or report views.

---

🔗 Synonyms make your SQL cleaner and more portable — practice them to simplify cross-schema access and reporting!