/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 6 : ORACLE SECURITY ARCHITECTURE FUNDAMENTALS
Compatible with:
Oracle 12c / 19c / 21c / 23ai
Topics Covered
---------------
1 Oracle security architecture overview
2 Security layers
3 Authentication vs Authorization
4 Users vs Schemas
5 Oracle object ownership
6 Least privilege model
7 Principle of separation of duties
8 Security administration roles
9 Data dictionary security views
10 Enterprise HR security simulation
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Oracle Database Security Architecture?
*******************************************************************************************/
-- Oracle security architecture is a framework that controls
-- how users authenticate, access objects, and perform actions.
-- It includes several layers:
-- Authentication
-- Authorization
-- Roles
-- Privileges
-- Profiles
-- Auditing
/*******************************************************************************************
QUESTION 2
What are the major security layers in Oracle?
*******************************************************************************************/
-- Oracle database security can be viewed in layers.
-- Layer 1
-- USER AUTHENTICATION
-- Layer 2
-- PRIVILEGES
-- Layer 3
-- ROLES
-- Layer 4
-- PROFILES
-- Layer 5
-- AUDITING
/*******************************************************************************************
QUESTION 3
What is authentication in Oracle?
*******************************************************************************************/
-- Authentication verifies identity of the user.
-- Methods include:
-- Password authentication
-- OS authentication
-- Kerberos authentication
-- SSL authentication
/*******************************************************************************************
QUESTION 4
What is authorization?
*******************************************************************************************/
-- Authorization determines what actions
-- an authenticated user can perform.
-- Authorization uses:
-- System privileges
-- Object privileges
-- Roles
/*******************************************************************************************
QUESTION 5
What is the difference between authentication and authorization?
*******************************************************************************************/
-- Authentication
-- verifies WHO you are.
-- Authorization
-- determines WHAT you can do.
/*******************************************************************************************
QUESTION 6
What is the difference between a USER and a SCHEMA?
*******************************************************************************************/
-- In Oracle database
-- USER
-- is a database account used for login.
-- SCHEMA
-- is the collection of objects owned by that user.
/*******************************************************************************************
QUESTION 7
Example: HR schema ownership
*******************************************************************************************/
-- HR user owns objects such as:
-- HR.EMPLOYEES
-- HR.DEPARTMENTS
-- HR.JOBS
-- HR.LOCATIONS
SELECT table_name
FROM dba_tables
WHERE owner = 'HR';
/*******************************************************************************************
QUESTION 8
What is object ownership in Oracle?
*******************************************************************************************/
-- The creator of a database object
-- automatically becomes its owner.
-- Example
CREATE TABLE training_user.sample_table
(
id NUMBER
);
/*******************************************************************************************
QUESTION 9
Who can access the object after creation?
*******************************************************************************************/
-- Only the owner has full privileges.
-- Other users require GRANT privileges.
/*******************************************************************************************
QUESTION 10
Example granting access to another user
*******************************************************************************************/
GRANT SELECT
ON training_user.sample_table
TO training_user;
/*******************************************************************************************
QUESTION 11
What is the Principle of Least Privilege?
*******************************************************************************************/
-- Users should be granted only the minimum
-- privileges required to perform their tasks.
-- This reduces security risks.
/*******************************************************************************************
QUESTION 12
Example of least privilege violation
*******************************************************************************************/
-- Bad design
GRANT DBA TO training_user;
-- This grants excessive privileges.
/*******************************************************************************************
QUESTION 13
Correct approach using minimal privileges
*******************************************************************************************/
GRANT CREATE SESSION
TO training_user;
/*******************************************************************************************
QUESTION 14
What is Separation of Duties?
*******************************************************************************************/
-- Separation of duties ensures
-- no single user has excessive control.
-- Example roles
-- DBA
-- Application Developer
-- Security Administrator
-- Auditor
/*******************************************************************************************
QUESTION 15
Typical enterprise security roles
*******************************************************************************************/
CREATE ROLE app_user_role;
CREATE ROLE reporting_role;
CREATE ROLE security_admin_role;
/*******************************************************************************************
QUESTION 16
Assign privileges to roles
*******************************************************************************************/
GRANT SELECT
ON hr.employees
TO reporting_role;
/*******************************************************************************************
QUESTION 17
Grant role to user
*******************************************************************************************/
GRANT reporting_role
TO training_user;
/*******************************************************************************************
QUESTION 18
Why roles are better than direct privilege grants?
*******************************************************************************************/
-- Roles simplify privilege management.
-- Example
-- Instead of granting privileges
-- individually to hundreds of users.
/*******************************************************************************************
QUESTION 19
What are administrative roles in Oracle?
*******************************************************************************************/
-- Some built-in roles include
-- DBA
-- CONNECT
-- RESOURCE
SELECT *
FROM dba_roles;
/*******************************************************************************************
QUESTION 20
Why built-in roles should be used carefully?
*******************************************************************************************/
-- Some built-in roles contain
-- excessive privileges.
-- For example
-- DBA role has almost unrestricted access.
/*******************************************************************************************
QUESTION 21
How can we inspect privileges in DBA role?
*******************************************************************************************/
SELECT *
FROM ROLE_SYS_PRIVS
WHERE ROLE = 'DBA';
/*******************************************************************************************
QUESTION 22
What are Oracle data dictionary views?
*******************************************************************************************/
-- Oracle stores metadata
-- about database objects and privileges
-- in data dictionary views.
/*******************************************************************************************
QUESTION 23
Common security dictionary views
*******************************************************************************************/
-- DBA_USERS
-- DBA_ROLES
-- DBA_ROLE_PRIVS
-- DBA_SYS_PRIVS
-- DBA_TAB_PRIVS
-- DBA_PROFILES
/*******************************************************************************************
QUESTION 24
Example query to see users
*******************************************************************************************/
SELECT username,
account_status,
profile
FROM dba_users;
/*******************************************************************************************
QUESTION 25
How to see role assignments
*******************************************************************************************/
SELECT *
FROM dba_role_privs;
/*******************************************************************************************
QUESTION 26
How to see system privileges
*******************************************************************************************/
SELECT *
FROM dba_sys_privs;
/*******************************************************************************************
QUESTION 27
How to see object privileges
*******************************************************************************************/
SELECT *
FROM dba_tab_privs;
/*******************************************************************************************
QUESTION 28
Enterprise HR security scenario
*******************************************************************************************/
-- Example architecture
-- HR schema owns employee data
-- Application users access data
-- via roles and APIs
/*******************************************************************************************
QUESTION 29
Example HR reporting role
*******************************************************************************************/
CREATE ROLE hr_reporting_role;
GRANT SELECT
ON hr.employees
TO hr_reporting_role;
GRANT SELECT
ON hr.departments
TO hr_reporting_role;
/*******************************************************************************************
QUESTION 30
Grant reporting role to application user
*******************************************************************************************/
GRANT hr_reporting_role
TO training_user;
/*******************************************************************************************
QUESTION 31
Testing access via role
*******************************************************************************************/
SELECT employee_id,
first_name,
salary
FROM hr.employees;
/*******************************************************************************************
QUESTION 32
Security best practices summary
*******************************************************************************************/
-- Use least privilege model
-- Use roles for privilege management
-- Avoid granting DBA role unnecessarily
-- Monitor privilege usage
-- Use auditing for sensitive operations
/*******************************************************************************************
END OF PART 6
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.
*******************************************************************************************/