/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 9 : SECURE APPLICATION SECURITY DESIGN
Topics Covered
---------------
1 Application security model
2 Three-schema architecture
3 Application users vs schema owners
4 API-based data access
5 Preventing direct table access
6 Using views for security
7 Secure package interfaces
8 Security wrappers
9 Enterprise HR application example
10 Secure coding practices
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Secure Application Security Design?
*******************************************************************************************/
-- Secure application security design ensures that
-- end users cannot access database tables directly.
-- Instead, users interact with:
-- PL/SQL APIs
-- Views
-- Controlled roles
-- This model protects sensitive data.
/*******************************************************************************************
QUESTION 2
What is the Three-Schema Architecture?
*******************************************************************************************/
-- Enterprise Oracle applications typically use three schemas.
-- DATA SCHEMA
-- APPLICATION SCHEMA
-- USER SCHEMA
-- Structure
-- USERS
-- |
-- v
-- APPLICATION SCHEMA
-- |
-- v
-- DATA SCHEMA
/*******************************************************************************************
QUESTION 3
What is the Data Schema?
*******************************************************************************************/
-- The data schema owns all tables.
-- Example
-- HR schema owns
-- HR.EMPLOYEES
-- HR.DEPARTMENTS
-- HR.JOBS
/*******************************************************************************************
QUESTION 4
What is the Application Schema?
*******************************************************************************************/
-- The application schema contains
-- PL/SQL packages
-- Views
-- Business logic
/*******************************************************************************************
QUESTION 5
What is the User Schema?
*******************************************************************************************/
-- Users connect using limited privileges.
-- They interact only with APIs.
/*******************************************************************************************
QUESTION 6
Why should users not access tables directly?
*******************************************************************************************/
-- Direct table access can expose sensitive data.
-- Example sensitive columns
-- SALARY
-- SOCIAL_SECURITY_NUMBER
-- BONUS
/*******************************************************************************************
QUESTION 7
Example insecure design
*******************************************************************************************/
GRANT SELECT
ON hr.employees
TO training_user;
-- This exposes full employee data.
/*******************************************************************************************
QUESTION 8
Secure design approach
*******************************************************************************************/
-- Remove table access.
REVOKE SELECT
ON hr.employees
FROM training_user;
/*******************************************************************************************
QUESTION 9
Create secure API procedure
*******************************************************************************************/
CREATE OR REPLACE PROCEDURE training_user.get_employee_name
(
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 10
Grant access to procedure
*******************************************************************************************/
GRANT EXECUTE
ON training_user.get_employee_name
TO training_user;
/*******************************************************************************************
QUESTION 11
Why is API access safer?
*******************************************************************************************/
-- Users can only retrieve approved data.
-- Sensitive columns remain protected.
/*******************************************************************************************
QUESTION 12
Use views to restrict data access
*******************************************************************************************/
CREATE VIEW training_user.emp_public_view
AS
SELECT employee_id,
first_name,
department_id
FROM hr.employees;
/*******************************************************************************************
QUESTION 13
Grant access to view
*******************************************************************************************/
GRANT SELECT
ON training_user.emp_public_view
TO training_user;
/*******************************************************************************************
QUESTION 14
Why views improve security?
*******************************************************************************************/
-- Views hide sensitive columns.
-- Example columns hidden
-- SALARY
-- COMMISSION
/*******************************************************************************************
QUESTION 15
Enterprise HR reporting example
*******************************************************************************************/
CREATE VIEW training_user.hr_report_view
AS
SELECT
employee_id,
first_name,
department_id
FROM hr.employees;
/*******************************************************************************************
QUESTION 16
Grant report access
*******************************************************************************************/
GRANT SELECT
ON training_user.hr_report_view
TO training_user;
/*******************************************************************************************
QUESTION 17
What are security wrappers?
*******************************************************************************************/
-- Security wrappers are PL/SQL procedures
-- that enforce additional validation.
/*******************************************************************************************
QUESTION 18
Example security wrapper
*******************************************************************************************/
CREATE OR REPLACE PROCEDURE training_user.get_employee_department
(
p_emp_id NUMBER
)
AUTHID DEFINER
AS
v_dept NUMBER;
BEGIN
SELECT department_id
INTO v_dept
FROM hr.employees
WHERE employee_id = p_emp_id;
DBMS_OUTPUT.PUT_LINE(v_dept);
END;
/
/*******************************************************************************************
QUESTION 19
Grant access to wrapper procedure
*******************************************************************************************/
GRANT EXECUTE
ON training_user.get_employee_department
TO training_user;
/*******************************************************************************************
QUESTION 20
What is a secure package interface?
*******************************************************************************************/
-- Packages group related procedures.
-- They expose controlled APIs.
/*******************************************************************************************
QUESTION 21
Create secure package specification
*******************************************************************************************/
CREATE OR REPLACE PACKAGE training_user.hr_api
AS
PROCEDURE get_employee_name(p_emp_id NUMBER);
PROCEDURE get_employee_department(p_emp_id NUMBER);
END;
/
/*******************************************************************************************
QUESTION 22
Create package body
*******************************************************************************************/
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;
PROCEDURE get_employee_department(p_emp_id NUMBER)
IS
v_dept NUMBER;
BEGIN
SELECT department_id
INTO v_dept
FROM hr.employees
WHERE employee_id = p_emp_id;
DBMS_OUTPUT.PUT_LINE(v_dept);
END;
END;
/
/*******************************************************************************************
QUESTION 23
Grant package access
*******************************************************************************************/
GRANT EXECUTE
ON training_user.hr_api
TO training_user;
/*******************************************************************************************
QUESTION 24
Enterprise application access flow
*******************************************************************************************/
-- USER
-- |
-- v
-- APPLICATION ROLE
-- |
-- v
-- PL/SQL API
-- |
-- v
-- TABLE
/*******************************************************************************************
QUESTION 25
Benefits of secure application design
*******************************************************************************************/
-- Protects sensitive data
-- Centralizes business logic
-- Prevents SQL injection risks
-- Simplifies auditing
/*******************************************************************************************
QUESTION 26
Secure coding practices
*******************************************************************************************/
-- Validate inputs
-- Avoid dynamic SQL
-- Restrict privileges
-- Use bind variables
/*******************************************************************************************
QUESTION 27
Example input validation
*******************************************************************************************/
IF p_emp_id IS NULL THEN
RAISE_APPLICATION_ERROR(-20001,'Invalid employee id');
END IF;
/*******************************************************************************************
QUESTION 28
Avoid dynamic SQL unless necessary
*******************************************************************************************/
-- Bad
EXECUTE IMMEDIATE 'SELECT * FROM hr.employees';
-- Good
SELECT *
FROM hr.employees;
/*******************************************************************************************
QUESTION 29
Enterprise security model summary
*******************************************************************************************/
-- No direct table access
-- APIs control data access
-- Roles manage privileges
-- Views hide sensitive data
/*******************************************************************************************
QUESTION 30
Interview summary
*******************************************************************************************/
-- Secure applications use
-- API procedures
-- Views
-- Roles
-- Least privilege model
/*******************************************************************************************
END OF PART 9
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.
*******************************************************************************************/