/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 14 : ENTERPRISE HR SECURITY ARCHITECTURE
Topics Covered
---------------
1 Enterprise security architecture overview
2 HR data schema
3 Application schema
4 User access schema
5 Role-based access design
6 Secure API access model
7 Synonym abstraction layer
8 Privilege management
9 HR reporting access model
10 Enterprise HR security workflow
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Enterprise HR Security Architecture?
*******************************************************************************************/
-- Enterprise HR security architecture protects sensitive HR data
-- using layered security controls.
-- The architecture includes:
-- Data schema
-- Application schema
-- User schema
-- Roles
-- APIs
-- Auditing
/*******************************************************************************************
QUESTION 2
Why HR systems require strong security?
*******************************************************************************************/
-- HR databases contain sensitive information such as:
-- Employee salaries
-- Personal details
-- Performance reviews
-- Benefits information
/*******************************************************************************************
QUESTION 3
What is the Data Schema in HR architecture?
*******************************************************************************************/
-- The data schema owns the tables.
-- Example HR tables include:
-- HR.EMPLOYEES
-- HR.DEPARTMENTS
-- HR.JOBS
/*******************************************************************************************
QUESTION 4
Query HR tables
*******************************************************************************************/
SELECT table_name
FROM dba_tables
WHERE owner = 'HR';
/*******************************************************************************************
QUESTION 5
What is the Application Schema?
*******************************************************************************************/
-- Application schema contains:
-- PL/SQL APIs
-- Views
-- Business logic
/*******************************************************************************************
QUESTION 6
Example application schema procedure
*******************************************************************************************/
CREATE OR REPLACE PROCEDURE training_user.get_employee_basic
(
p_emp_id NUMBER
)
AUTHID DEFINER
AS
v_name VARCHAR2(100);
BEGIN
SELECT first_name
INTO v_name
FROM hr.employees
WHERE employee_id = p_emp_id;
DBMS_OUTPUT.PUT_LINE(v_name);
END;
/
/*******************************************************************************************
QUESTION 7
Grant access to procedure
*******************************************************************************************/
GRANT EXECUTE
ON training_user.get_employee_basic
TO training_user;
/*******************************************************************************************
QUESTION 8
What is the User Access Schema?
*******************************************************************************************/
-- User schemas represent application users.
-- They receive roles and access APIs.
/*******************************************************************************************
QUESTION 9
Create application role
*******************************************************************************************/
CREATE ROLE hr_app_role;
/*******************************************************************************************
QUESTION 10
Grant privileges to role
*******************************************************************************************/
GRANT EXECUTE
ON training_user.get_employee_basic
TO hr_app_role;
/*******************************************************************************************
QUESTION 11
Assign role to application user
*******************************************************************************************/
GRANT hr_app_role
TO training_user;
/*******************************************************************************************
QUESTION 12
Why roles are used in enterprise security?
*******************************************************************************************/
-- Roles simplify privilege management.
-- Instead of granting privileges to each user individually.
/*******************************************************************************************
QUESTION 13
What is a reporting role?
*******************************************************************************************/
-- Reporting users require read-only access
-- to HR data.
/*******************************************************************************************
QUESTION 14
Create HR reporting role
*******************************************************************************************/
CREATE ROLE hr_report_role;
/*******************************************************************************************
QUESTION 15
Grant SELECT privilege to reporting role
*******************************************************************************************/
GRANT SELECT
ON hr.employees
TO hr_report_role;
GRANT SELECT
ON hr.departments
TO hr_report_role;
/*******************************************************************************************
QUESTION 16
Assign reporting role to analyst
*******************************************************************************************/
GRANT hr_report_role
TO training_user;
/*******************************************************************************************
QUESTION 17
Why synonyms are used in enterprise architecture?
*******************************************************************************************/
-- Synonyms hide schema ownership
-- and simplify application queries.
/*******************************************************************************************
QUESTION 18
Create synonym for HR table
*******************************************************************************************/
CREATE SYNONYM emp
FOR hr.employees;
/*******************************************************************************************
QUESTION 19
Query using synonym
*******************************************************************************************/
SELECT employee_id,
first_name
FROM emp;
/*******************************************************************************************
QUESTION 20
Enterprise HR reporting view
*******************************************************************************************/
CREATE VIEW training_user.hr_report_view
AS
SELECT employee_id,
first_name,
department_id
FROM hr.employees;
/*******************************************************************************************
QUESTION 21
Grant access to reporting view
*******************************************************************************************/
GRANT SELECT
ON training_user.hr_report_view
TO hr_report_role;
/*******************************************************************************************
QUESTION 22
Why views improve security?
*******************************************************************************************/
-- Views restrict access to specific columns.
-- Sensitive columns like salary can be hidden.
/*******************************************************************************************
QUESTION 23
Enterprise HR API package
*******************************************************************************************/
CREATE OR REPLACE PACKAGE training_user.hr_api
AS
PROCEDURE get_employee_name(p_emp_id NUMBER);
END;
/
/*******************************************************************************************
QUESTION 24
Package body implementation
*******************************************************************************************/
CREATE OR REPLACE PACKAGE BODY training_user.hr_api
AS
PROCEDURE get_employee_name(p_emp_id NUMBER)
IS
v_name VARCHAR2(100);
BEGIN
SELECT first_name
INTO v_name
FROM hr.employees
WHERE employee_id = p_emp_id;
DBMS_OUTPUT.PUT_LINE(v_name);
END;
END;
/
/*******************************************************************************************
QUESTION 25
Grant package access
*******************************************************************************************/
GRANT EXECUTE
ON training_user.hr_api
TO hr_app_role;
/*******************************************************************************************
QUESTION 26
Enterprise access workflow
*******************************************************************************************/
-- USER
-- |
-- v
-- APPLICATION ROLE
-- |
-- v
-- API PACKAGE
-- |
-- v
-- DATA TABLE
/*******************************************************************************************
QUESTION 27
Benefits of layered security
*******************************************************************************************/
-- Protects sensitive HR data
-- Centralizes access control
-- Simplifies auditing
/*******************************************************************************************
QUESTION 28
HR system auditing example
*******************************************************************************************/
AUDIT SELECT
ON hr.employees
BY ACCESS;
/*******************************************************************************************
QUESTION 29
Security monitoring query
*******************************************************************************************/
SELECT *
FROM unified_audit_trail
WHERE object_name='EMPLOYEES';
/*******************************************************************************************
QUESTION 30
Enterprise HR security architecture summary
*******************************************************************************************/
-- Data schema owns tables
-- Application schema contains APIs
-- Users access data via roles
-- Views restrict sensitive columns
-- Auditing monitors activity
/*******************************************************************************************
END OF PART 14
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.
*******************************************************************************************/