/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 1 : FOUNDATIONS OF DCL
COMPATIBLE WITH : Oracle 19c / 21c / 23ai
Topics Covered
---------------
1 What is DCL
2 Oracle Security Architecture
3 Users vs Schemas
4 System Privileges
5 Object Privileges
6 GRANT command
7 REVOKE command
8 WITH GRANT OPTION
9 WITH ADMIN OPTION
10 Viewing privileges using Data Dictionary
HR Schema (Oracle Database Sample Schema) is used for reference.
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Data Control Language (DCL) in Oracle?
*******************************************************************************************/
-- DCL controls security and permissions in Oracle Database.
--
-- DCL commands define WHO can access WHAT objects and WHAT operations
-- they are allowed to perform.
--
-- Main DCL commands:
--
-- GRANT
-- REVOKE
--
-- Although roles and profiles are security concepts, they are managed
-- using SQL commands and belong to the broader Oracle security model.
/*******************************************************************************************
QUESTION 2
What is Oracle Database Security Architecture?
*******************************************************************************************/
-- Oracle security is based on several layers.
--
-- USER
-- |
-- |----> Privileges
-- |
-- |----> Roles
-- |
-- |----> Profiles
--
-- Objects owned by schema:
--
-- Tables
-- Views
-- Procedures
-- Packages
-- Sequences
--
-- Access is controlled using privileges.
/*******************************************************************************************
QUESTION 3
What is the difference between a USER and a SCHEMA?
*******************************************************************************************/
-- In Oracle:
--
-- USER = account used to log in
-- SCHEMA = collection of objects owned by that user
--
-- Example:
--
-- User HR owns schema HR
-- HR schema contains:
--
-- HR.EMPLOYEES
-- HR.DEPARTMENTS
-- HR.JOBS
-- HR.LOCATIONS
/*******************************************************************************************
QUESTION 4
How do we create a new database user?
*******************************************************************************************/
-- Only DBA or privileged users can create users.
CREATE USER training_user
IDENTIFIED BY train123;
-- The user currently cannot log in.
/*******************************************************************************************
QUESTION 5
Why can't a newly created user log in to the database?
*******************************************************************************************/
-- Because the user lacks the CREATE SESSION privilege.
--
-- CREATE SESSION allows login.
GRANT CREATE SESSION TO training_user;
/*******************************************************************************************
QUESTION 6
How do we verify privileges granted to a user?
*******************************************************************************************/
-- Oracle stores privilege information in data dictionary views.
SELECT *
FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'TRAINING_USER';
/*******************************************************************************************
QUESTION 7
What are System Privileges?
*******************************************************************************************/
-- System privileges allow users to perform database-level operations.
-- Examples:
-- CREATE TABLE
-- CREATE VIEW
-- CREATE PROCEDURE
-- CREATE SEQUENCE
-- CREATE USER
-- CREATE SESSION
-- ALTER SYSTEM
-- DROP USER
/*******************************************************************************************
QUESTION 8
How do we grant system privileges to a user?
*******************************************************************************************/
GRANT CREATE TABLE TO training_user;
GRANT CREATE VIEW TO training_user;
GRANT CREATE SEQUENCE TO training_user;
/*******************************************************************************************
QUESTION 9
How can multiple system privileges be granted together?
*******************************************************************************************/
GRANT
CREATE TABLE,
CREATE VIEW,
CREATE PROCEDURE
TO training_user;
/*******************************************************************************************
QUESTION 10
How do we check system privileges granted to a user?
*******************************************************************************************/
SELECT *
FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'TRAINING_USER';
/*******************************************************************************************
QUESTION 11
What are Object Privileges in Oracle?
*******************************************************************************************/
-- Object privileges allow operations on specific database objects.
-- Examples:
-- SELECT
-- INSERT
-- UPDATE
-- DELETE
-- EXECUTE
-- REFERENCES
-- INDEX
/*******************************************************************************************
QUESTION 12
How do we grant SELECT access to HR.EMPLOYEES?
*******************************************************************************************/
-- Assume we are connected as HR user.
GRANT SELECT
ON hr.employees
TO training_user;
/*******************************************************************************************
QUESTION 13
How can we allow INSERT privilege on a table?
*******************************************************************************************/
GRANT INSERT
ON hr.employees
TO training_user;
/*******************************************************************************************
QUESTION 14
How can we grant UPDATE privilege only on specific columns?
*******************************************************************************************/
GRANT UPDATE (salary, commission_pct)
ON hr.employees
TO training_user;
/*******************************************************************************************
QUESTION 15
How do we check object privileges granted to a user?
*******************************************************************************************/
SELECT *
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'TRAINING_USER';
/*******************************************************************************************
QUESTION 16
What happens when privileges are granted on a table?
*******************************************************************************************/
-- User training_user can now run:
SELECT employee_id, first_name
FROM hr.employees;
/*******************************************************************************************
QUESTION 17
What is the WITH GRANT OPTION clause?
*******************************************************************************************/
-- WITH GRANT OPTION allows the grantee to grant the same privilege
-- to other users.
-- Example:
GRANT SELECT
ON hr.employees
TO training_user
WITH GRANT OPTION;
/*******************************************************************************************
QUESTION 18
What happens when WITH GRANT OPTION is used?
*******************************************************************************************/
-- training_user can now grant SELECT to other users.
-- Example:
GRANT SELECT
ON hr.employees
TO another_user;
/*******************************************************************************************
QUESTION 19
What happens when the privilege is revoked from training_user?
*******************************************************************************************/
-- Oracle automatically performs CASCADE REVOKE.
-- Meaning:
--
-- Privileges granted by training_user to others
-- are automatically revoked.
/*******************************************************************************************
QUESTION 20
How do we revoke privileges?
*******************************************************************************************/
REVOKE SELECT
ON hr.employees
FROM training_user;
/*******************************************************************************************
QUESTION 21
What is WITH ADMIN OPTION?
*******************************************************************************************/
-- WITH ADMIN OPTION applies to SYSTEM PRIVILEGES.
-- It allows a user to grant system privileges to others.
GRANT CREATE TABLE
TO training_user
WITH ADMIN OPTION;
/*******************************************************************************************
QUESTION 22
How do we check privileges granted using ADMIN OPTION?
*******************************************************************************************/
SELECT *
FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'TRAINING_USER';
/*******************************************************************************************
QUESTION 23
How do we list all users in the database?
*******************************************************************************************/
SELECT username
FROM dba_users;
/*******************************************************************************************
QUESTION 24
How do we see all object privileges in the database?
*******************************************************************************************/
SELECT *
FROM dba_tab_privs;
/*******************************************************************************************
QUESTION 25
How do we see privileges granted to the current user?
*******************************************************************************************/
SELECT *
FROM USER_SYS_PRIVS;
SELECT *
FROM USER_TAB_PRIVS;
/*******************************************************************************************
QUESTION 26
What are the key dictionary views used for security analysis?
*******************************************************************************************/
-- DBA_USERS
-- DBA_SYS_PRIVS
-- DBA_TAB_PRIVS
-- DBA_ROLE_PRIVS
-- DBA_ROLES
-- DBA_PROFILES
/*******************************************************************************************
END OF PART 1
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.
*******************************************************************************************/