/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 18 : BANKING / FINANCE SECURITY ARCHITECTURE
Topics Covered
---------------
1 Banking security requirements
2 Banking schema architecture
3 Secure role hierarchy
4 Customer data protection
5 Secure API transaction model
6 Auditing financial operations
7 Fraud monitoring mechanisms
8 VPD in banking systems
9 Secure reporting access
10 Enterprise banking security workflow
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
Why do banking systems require strict database security?
*******************************************************************************************/
-- Banking databases store highly sensitive data including:
-- Customer account numbers
-- Transaction records
-- Credit information
-- Personal financial details
-- Unauthorized access can lead to financial fraud.
/*******************************************************************************************
QUESTION 2
Typical banking database architecture
*******************************************************************************************/
-- Banking systems typically use layered security:
-- CUSTOMER_SCHEMA
-- APPLICATION_SCHEMA
-- REPORTING_SCHEMA
-- SECURITY_SCHEMA
/*******************************************************************************************
QUESTION 3
Example banking customer table
*******************************************************************************************/
CREATE TABLE banking_customer
(
customer_id NUMBER PRIMARY KEY,
customer_name VARCHAR2(100),
account_balance NUMBER,
branch_id NUMBER
);
/*******************************************************************************************
QUESTION 4
Insert demonstration data
*******************************************************************************************/
INSERT INTO banking_customer
VALUES (1,'John Smith',15000,101);
INSERT INTO banking_customer
VALUES (2,'Alice Brown',20000,102);
COMMIT;
/*******************************************************************************************
QUESTION 5
Create banking application role
*******************************************************************************************/
CREATE ROLE banking_app_role;
/*******************************************************************************************
QUESTION 6
Grant minimal privileges to application role
*******************************************************************************************/
GRANT SELECT
ON banking_customer
TO banking_app_role;
/*******************************************************************************************
QUESTION 7
Assign role to application user
*******************************************************************************************/
GRANT banking_app_role
TO training_user;
/*******************************************************************************************
QUESTION 8
Why banking systems avoid direct table access?
*******************************************************************************************/
-- Direct access to banking tables
-- increases risk of data leakage.
-- Instead APIs are used.
/*******************************************************************************************
QUESTION 9
Create secure transaction API
*******************************************************************************************/
CREATE OR REPLACE PROCEDURE process_transaction
(
p_customer_id NUMBER,
p_amount NUMBER
)
AUTHID DEFINER
AS
BEGIN
UPDATE banking_customer
SET account_balance = account_balance + p_amount
WHERE customer_id = p_customer_id;
END;
/
/*******************************************************************************************
QUESTION 10
Grant API access
*******************************************************************************************/
GRANT EXECUTE
ON process_transaction
TO banking_app_role;
/*******************************************************************************************
QUESTION 11
Example banking transaction
*******************************************************************************************/
EXEC process_transaction(1,500);
/*******************************************************************************************
QUESTION 12
Why API-based transactions improve security?
*******************************************************************************************/
-- Business logic validation
-- Centralized control
-- Auditable operations
/*******************************************************************************************
QUESTION 13
Auditing financial transactions
*******************************************************************************************/
AUDIT UPDATE
ON banking_customer
BY ACCESS;
/*******************************************************************************************
QUESTION 14
View audit trail
*******************************************************************************************/
SELECT dbusername,
action_name,
object_name
FROM unified_audit_trail
WHERE object_name='BANKING_CUSTOMER';
/*******************************************************************************************
QUESTION 15
Fine-grained auditing example
*******************************************************************************************/
BEGIN
DBMS_FGA.ADD_POLICY
(
object_schema => USER,
object_name => 'BANKING_CUSTOMER',
policy_name => 'BALANCE_ACCESS_AUDIT',
audit_column => 'ACCOUNT_BALANCE'
);
END;
/
/*******************************************************************************************
QUESTION 16
Fraud monitoring query
*******************************************************************************************/
SELECT *
FROM dba_fga_audit_trail
ORDER BY timestamp DESC;
/*******************************************************************************************
QUESTION 17
Row-level security example using VPD
*******************************************************************************************/
CREATE OR REPLACE FUNCTION branch_policy
(
schema_name VARCHAR2,
table_name VARCHAR2
)
RETURN VARCHAR2
AS
BEGIN
RETURN 'branch_id = SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER'')';
END;
/
/*******************************************************************************************
QUESTION 18
Add VPD policy
*******************************************************************************************/
BEGIN
DBMS_RLS.ADD_POLICY
(
object_schema => USER,
object_name => 'BANKING_CUSTOMER',
policy_name => 'BRANCH_POLICY',
function_schema => USER,
policy_function => 'BRANCH_POLICY'
);
END;
/
/*******************************************************************************************
QUESTION 19
Set branch context
*******************************************************************************************/
BEGIN
DBMS_SESSION.SET_IDENTIFIER('101');
END;
/
/*******************************************************************************************
QUESTION 20
Query banking table
*******************************************************************************************/
SELECT *
FROM banking_customer;
/*******************************************************************************************
QUESTION 21
Expected result
*******************************************************************************************/
-- User sees only customers belonging
-- to their branch.
/*******************************************************************************************
QUESTION 22
Role hierarchy example
*******************************************************************************************/
CREATE ROLE banking_manager_role;
GRANT banking_app_role
TO banking_manager_role;
/*******************************************************************************************
QUESTION 23
Grant manager role
*******************************************************************************************/
GRANT banking_manager_role
TO training_user;
/*******************************************************************************************
QUESTION 24
Reporting access model
*******************************************************************************************/
CREATE ROLE banking_report_role;
GRANT SELECT
ON banking_customer
TO banking_report_role;
/*******************************************************************************************
QUESTION 25
Enterprise monitoring
*******************************************************************************************/
SELECT *
FROM unified_audit_trail
ORDER BY event_timestamp DESC;
/*******************************************************************************************
QUESTION 26
Security monitoring indicators
*******************************************************************************************/
-- Unusual login attempts
-- Unauthorized data access
-- Large transaction volumes
/*******************************************************************************************
QUESTION 27
Fraud detection strategy
*******************************************************************************************/
-- Monitor transaction patterns
-- Audit sensitive data access
-- Restrict privileges
/*******************************************************************************************
QUESTION 28
Enterprise banking security architecture
*******************************************************************************************/
-- USER
-- |
-- v
-- APPLICATION ROLE
-- |
-- v
-- SECURE API
-- |
-- v
-- BANKING TABLES
/*******************************************************************************************
QUESTION 29
Banking security best practices
*******************************************************************************************/
-- Use least privilege
-- Implement row-level security
-- Enable auditing
-- Monitor access patterns
/*******************************************************************************************
QUESTION 30
Banking security architecture summary
*******************************************************************************************/
-- API-based access
-- Role-based privileges
-- VPD row-level security
-- FGA monitoring
-- Unified auditing
/*******************************************************************************************
END OF PART 18
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.
*******************************************************************************************/