/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 19 : ORACLE SECURITY INTERVIEW & EXAM MASTER GUIDE
Topics Covered
---------------
1 Oracle privilege interview questions
2 Roles vs privileges interview traps
3 PL/SQL security interview questions
4 Auditing interview questions
5 VPD interview scenarios
6 Database Vault interview questions
7 Label security interview questions
8 Security architecture questions
9 DBA troubleshooting scenarios
10 Oracle security exam tips
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is the difference between SYSTEM privileges and OBJECT privileges?
*******************************************************************************************/
-- SYSTEM PRIVILEGES
-- Allow actions at database level.
-- Examples:
-- CREATE TABLE
-- CREATE USER
-- ALTER SYSTEM
-- OBJECT PRIVILEGES
-- Allow operations on specific objects.
-- Examples:
-- SELECT
-- INSERT
-- UPDATE
-- DELETE
/*******************************************************************************************
QUESTION 2
What is the difference between WITH GRANT OPTION and WITH ADMIN OPTION?
*******************************************************************************************/
-- WITH GRANT OPTION
-- Used with object privileges.
-- WITH ADMIN OPTION
-- Used with system privileges or roles.
/*******************************************************************************************
QUESTION 3
Why do roles not work inside PL/SQL procedures?
*******************************************************************************************/
-- Oracle disables role privileges inside PL/SQL
-- for security reasons.
-- Only direct privilege grants are considered.
/*******************************************************************************************
QUESTION 4
What is the difference between AUTHID DEFINER and AUTHID CURRENT_USER?
*******************************************************************************************/
-- AUTHID DEFINER
-- Procedure runs using owner's privileges.
-- AUTHID CURRENT_USER
-- Procedure runs using caller's privileges.
/*******************************************************************************************
QUESTION 5
What is Oracle Virtual Private Database (VPD)?
*******************************************************************************************/
-- VPD implements Row-Level Security.
-- It dynamically adds predicates
-- to restrict rows returned to users.
/*******************************************************************************************
QUESTION 6
What is the difference between VPD and Fine-Grained Auditing?
*******************************************************************************************/
-- VPD
-- Controls data access.
-- FGA
-- Audits data access.
/*******************************************************************************************
QUESTION 7
What is Oracle Database Vault?
*******************************************************************************************/
-- Database Vault prevents privileged users
-- from accessing protected data.
-- Even DBAs may be restricted.
/*******************************************************************************************
QUESTION 8
What is Oracle Label Security?
*******************************************************************************************/
-- Label Security implements Mandatory Access Control.
-- Rows are assigned classification labels.
/*******************************************************************************************
QUESTION 9
What is least privilege principle?
*******************************************************************************************/
-- Users should receive only
-- the privileges required for their tasks.
/*******************************************************************************************
QUESTION 10
What is role hierarchy?
*******************************************************************************************/
-- Roles can be granted to other roles.
-- This creates hierarchical privilege inheritance.
/*******************************************************************************************
QUESTION 11
Explain secure application design.
*******************************************************************************************/
-- Secure applications avoid direct table access.
-- Users access data through:
-- APIs
-- Views
-- Controlled roles
/*******************************************************************************************
QUESTION 12
How can DBAs detect excessive privileges?
*******************************************************************************************/
-- Query DBA_SYS_PRIVS
-- Query DBA_TAB_PRIVS
-- Use DBMS_PRIVILEGE_CAPTURE
/*******************************************************************************************
QUESTION 13
What is Unified Auditing?
*******************************************************************************************/
-- Unified auditing centralizes
-- database activity monitoring.
/*******************************************************************************************
QUESTION 14
Where are unified audit records stored?
*******************************************************************************************/
-- UNIFIED_AUDIT_TRAIL view.
/*******************************************************************************************
QUESTION 15
What is Fine-Grained Auditing used for?
*******************************************************************************************/
-- Auditing specific columns
-- Auditing conditional data access.
/*******************************************************************************************
QUESTION 16
Explain privilege escalation risk.
*******************************************************************************************/
-- When users gain additional privileges
-- through grant chains or insecure APIs.
/*******************************************************************************************
QUESTION 17
What is Oracle security hardening?
*******************************************************************************************/
-- Process of securing database by:
-- Removing unnecessary privileges
-- Locking unused accounts
-- Enforcing password policies
/*******************************************************************************************
QUESTION 18
What are ANY privileges?
*******************************************************************************************/
-- Privileges that allow operations
-- on all database objects.
-- Example:
-- SELECT ANY TABLE
/*******************************************************************************************
QUESTION 19
Why are PUBLIC grants dangerous?
*******************************************************************************************/
-- PUBLIC grants provide privileges
-- to every database user.
/*******************************************************************************************
QUESTION 20
What is Oracle security audit?
*******************************************************************************************/
-- Process of reviewing:
-- Privileges
-- Roles
-- Access patterns
-- Audit trails
/*******************************************************************************************
QUESTION 21
DBA troubleshooting scenario
*******************************************************************************************/
-- Problem:
-- User cannot access HR table.
-- Solution:
-- Check object privileges.
SELECT *
FROM dba_tab_privs
WHERE table_name='EMPLOYEES';
/*******************************************************************************************
QUESTION 22
Another troubleshooting scenario
*******************************************************************************************/
-- Problem:
-- PL/SQL procedure fails with ORA-00942.
-- Cause:
-- Role privilege used instead of direct grant.
/*******************************************************************************************
QUESTION 23
Security audit query example
*******************************************************************************************/
SELECT *
FROM dba_role_privs
WHERE granted_role='DBA';
/*******************************************************************************************
QUESTION 24
Detect users with ANY privileges
*******************************************************************************************/
SELECT *
FROM dba_sys_privs
WHERE privilege LIKE '%ANY%';
/*******************************************************************************************
QUESTION 25
Check PUBLIC object access
*******************************************************************************************/
SELECT *
FROM dba_tab_privs
WHERE grantee='PUBLIC';
/*******************************************************************************************
QUESTION 26
Oracle exam trap
*******************************************************************************************/
-- Question:
-- Can role privileges be used inside PL/SQL?
-- Answer:
-- No.
/*******************************************************************************************
QUESTION 27
Oracle exam trap
*******************************************************************************************/
-- Question:
-- Can a procedure with AUTHID DEFINER
-- access tables even if caller lacks privileges?
-- Answer:
-- Yes.
/*******************************************************************************************
QUESTION 28
Oracle exam trap
*******************************************************************************************/
-- Question:
-- Which package implements VPD?
-- Answer:
-- DBMS_RLS
/*******************************************************************************************
QUESTION 29
Oracle exam trap
*******************************************************************************************/
-- Question:
-- Which package implements Fine-Grained Auditing?
-- Answer:
-- DBMS_FGA
/*******************************************************************************************
QUESTION 30
Oracle security interview summary
*******************************************************************************************/
-- Use least privilege model
-- Use roles for privilege management
-- Protect sensitive data using VPD
-- Audit critical operations
-- Monitor privilege usage
/*******************************************************************************************
END OF PART 19
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.
*******************************************************************************************/