DATABASE EXPERT

🗄️
DBA-DEV TOOLKIT
👨‍💻 Vinayak Vishweshwara Dabgar
⚙️ Oracle Apps DBA - Consultant
💻 DBA - Developer
🏗️ Database Architect
🚀 Performance Tuning Expert
🗃️ Database Expertise
🟢 Oracle Database
🔵 Microsoft SQL Server
🟠 MySQL
🐘 PostgreSQL
☁️ Amazon RDS
📊 Amazon Redshift
🔗 Professional Links
🌐 Professional Website 🎓 Corporate Training LMS 📧 vdabgar.in@gmail.com 💼 LinkedIn Profile
📍 Office Address
🏠 No.20, SAI SADAN
🏢 Near LVS Apartments
🛣️ TC Palya Main Road
📌 Krishnarajapura
🌆 Bangalore - 560036
🌆 Karnataka, India
⬅ Back
Synonyms and Cross-schema Access


/*******************************************************************************************

Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK

PART 4 : SYNONYMS AND CROSS-SCHEMA ACCESS

Compatible with:
Oracle 12c / 19c / 21c / 23ai

Topics Covered
---------------
1  What is a Synonym
2  Why Synonyms are required
3  Private Synonyms
4  Public Synonyms
5  Synonyms for Tables
6  Synonyms for Sequences
7  Synonyms for Procedures
8  Cross Schema Access
9  Synonyms with Roles
10 Dropping Synonyms
11 Synonym Security Considerations

*******************************************************************************************/



/*******************************************************************************************
QUESTION 1
What is a Synonym in Oracle Database?
*******************************************************************************************/

-- A SYNONYM is an alternate name (alias) for a database object.

-- Synonyms can reference:
-- Tables
-- Views
-- Sequences
-- Procedures
-- Functions
-- Packages
-- Materialized views

-- Synonyms help simplify SQL statements and hide schema ownership.



/*******************************************************************************************
QUESTION 2
Why are synonyms useful in enterprise applications?
*******************************************************************************************/

-- Example without synonym

SELECT *
FROM hr.employees;

-- Example with synonym

SELECT *
FROM employees;

-- Advantages

-- Cleaner SQL
-- Easier application portability
-- Hides schema ownership
-- Simplifies maintenance



/*******************************************************************************************
QUESTION 3
What are the types of synonyms?
*******************************************************************************************/

-- Oracle supports two types of synonyms.

-- 1 PRIVATE SYNONYM
--    Accessible only within the schema where it is created.

-- 2 PUBLIC SYNONYM
--    Accessible to all users in the database.



/*******************************************************************************************
QUESTION 4
How do we create a demonstration table for synonym examples?
*******************************************************************************************/

-- We will create a demo table in training_user schema.

CREATE TABLE training_user.emp_training
(
training_id   NUMBER PRIMARY KEY,
employee_id   NUMBER,
course_name   VARCHAR2(100)
);



/*******************************************************************************************
QUESTION 5
Insert demonstration data into the table
*******************************************************************************************/

INSERT INTO training_user.emp_training
VALUES (1,101,'Oracle SQL');

INSERT INTO training_user.emp_training
VALUES (2,102,'PLSQL Programming');

COMMIT;



/*******************************************************************************************
QUESTION 6
How do we create a PRIVATE SYNONYM for the table?
*******************************************************************************************/

CREATE SYNONYM emp_training
FOR training_user.emp_training;



/*******************************************************************************************
QUESTION 7
How does the private synonym simplify queries?
*******************************************************************************************/

-- Instead of writing

SELECT *
FROM training_user.emp_training;

-- We can write

SELECT *
FROM emp_training;



/*******************************************************************************************
QUESTION 8
How do we verify synonyms in the database?
*******************************************************************************************/

SELECT *
FROM USER_SYNONYMS;



/*******************************************************************************************
QUESTION 9
How do we create a sequence for training records?
*******************************************************************************************/

CREATE SEQUENCE training_seq
START WITH 1
INCREMENT BY 1;



/*******************************************************************************************
QUESTION 10
How can synonyms simplify sequence usage?
*******************************************************************************************/

CREATE SYNONYM training_sequence
FOR training_seq;



/*******************************************************************************************
QUESTION 11
Use synonym to generate sequence numbers
*******************************************************************************************/

INSERT INTO emp_training
VALUES
(
training_sequence.NEXTVAL,
103,
'Oracle Performance Tuning'
);

COMMIT;



/*******************************************************************************************
QUESTION 12
Why is synonym abstraction useful in enterprise systems?
*******************************************************************************************/

-- Suppose sequence name changes later.

-- Instead of modifying application code everywhere,
-- we only modify the synonym.



/*******************************************************************************************
QUESTION 13
Example enterprise maintenance change
*******************************************************************************************/

-- Old sequence

DROP SYNONYM training_sequence;

CREATE SYNONYM training_sequence
FOR training_seq;



/*******************************************************************************************
QUESTION 14
How do we create synonym for HR table?
*******************************************************************************************/

-- HR schema must first grant access.

GRANT SELECT
ON hr.employees
TO training_user;



/*******************************************************************************************
QUESTION 15
Create synonym for HR table
*******************************************************************************************/

CREATE SYNONYM emp
FOR hr.employees;



/*******************************************************************************************
QUESTION 16
Query HR table using synonym
*******************************************************************************************/

SELECT employee_id,
first_name,
salary
FROM emp
WHERE department_id = 50;



/*******************************************************************************************
QUESTION 17
How do synonyms help hide schema ownership?
*******************************************************************************************/

-- Application developers do not need to know the table owner.

-- Instead of

SELECT *
FROM hr.employees;

-- They use

SELECT *
FROM emp;



/*******************************************************************************************
QUESTION 18
What is a PUBLIC SYNONYM?
*******************************************************************************************/

-- Public synonyms are accessible to all users.

-- Only DBA users typically create public synonyms.



/*******************************************************************************************
QUESTION 19
Example of creating a PUBLIC SYNONYM
*******************************************************************************************/

CREATE PUBLIC SYNONYM emp_public
FOR hr.employees;



/*******************************************************************************************
QUESTION 20
Using public synonym
*******************************************************************************************/

SELECT employee_id, first_name
FROM emp_public;



/*******************************************************************************************
QUESTION 21
Why public synonyms are risky in production systems?
*******************************************************************************************/

-- Potential name conflicts
-- Security exposure
-- Harder maintenance

-- Enterprise systems prefer PRIVATE SYNONYMS.



/*******************************************************************************************
QUESTION 22
How do we create synonyms for procedures?
*******************************************************************************************/

-- Example demonstration procedure.

CREATE OR REPLACE PROCEDURE training_user.training_info
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Training module active');
END;
/



/*******************************************************************************************
QUESTION 23
Create synonym for procedure
*******************************************************************************************/

CREATE SYNONYM training_proc
FOR training_user.training_info;



/*******************************************************************************************
QUESTION 24
Execute procedure using synonym
*******************************************************************************************/

EXEC training_proc;



/*******************************************************************************************
QUESTION 25
How do synonyms support cross schema access?
*******************************************************************************************/

-- Step 1
-- Owner grants privilege

GRANT SELECT
ON hr.departments
TO training_user;

-- Step 2
-- Create synonym

CREATE SYNONYM dept
FOR hr.departments;



/*******************************************************************************************
QUESTION 26
Query using synonym
*******************************************************************************************/

SELECT department_id, department_name
FROM dept;



/*******************************************************************************************
QUESTION 27
How do we see public synonyms?
*******************************************************************************************/

SELECT *
FROM DBA_SYNONYMS
WHERE OWNER='PUBLIC';



/*******************************************************************************************
QUESTION 28
How do we drop private synonyms?
*******************************************************************************************/

DROP SYNONYM emp;



/*******************************************************************************************
QUESTION 29
How do we drop public synonyms?
*******************************************************************************************/

DROP PUBLIC SYNONYM emp_public;



/*******************************************************************************************
QUESTION 30
What are best practices for synonyms in enterprise databases?
*******************************************************************************************/

-- Use private synonyms instead of public synonyms
-- Avoid excessive public synonyms
-- Use synonyms for application abstraction
-- Use synonyms when schema ownership might change
-- Avoid synonym name conflicts



/*******************************************************************************************
QUESTION 31
Sequence vs Synonym comparison (Interview question)
*******************************************************************************************/

-- SEQUENCE
-- Generates unique numeric values

-- SYNONYM
-- Provides alternate name for object

-- Synonyms do NOT store data.



/*******************************************************************************************
QUESTION 32
Enterprise HR application design example
*******************************************************************************************/

-- Application code

INSERT INTO emp_training
VALUES
(
training_sequence.NEXTVAL,
:employee_id,
:course_name
);



-- If sequence changes

CREATE SYNONYM training_sequence
FOR new_training_seq;



-- Application code remains unchanged.



/*******************************************************************************************
END OF PART 4

Disclaimer and Limitation of Liability

The author provides all scripts, queries, and related materials “as is”, without any express or implied warranties, including but not limited to warranties of accuracy, completeness, reliability, merchantability, or fitness for a particular purpose.

By using, executing, or implementing any part of this material, the user acknowledges and agrees that they do so at their own risk. The author shall not be held liable for any direct, indirect, incidental, consequential, special, or exemplary damages, including but not limited to loss of data, loss of profits, system failures, security breaches, or any other damages or losses arising from the use or misuse of these materials.

Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and configurations in a controlled, non-production (test) environment prior to deploying them in any live or production systems.

It is the sole responsibility of the user to ensure that the use of these materials complies with all applicable local, national, and international laws, regulations, and organizational policies.

By proceeding to use these materials, the user agrees to indemnify and hold harmless the author from any claims, damages, liabilities, or expenses arising from their use.

*******************************************************************************************/