/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 5 : AUTHID AND PL/SQL SECURITY
Compatible with:
Oracle 12c / 19c / 21c / 23ai
Topics Covered
---------------
1 AUTHID concept
2 Definer Rights
3 Invoker Rights
4 Security context
5 Cross schema access
6 Role behavior in PL/SQL
7 Dynamic SQL privilege behavior
8 Privilege escalation risks
9 Secure API design
10 Enterprise PL/SQL security best practices
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is AUTHID in PL/SQL?
*******************************************************************************************/
-- AUTHID determines whose privileges are used when
-- a stored procedure or function executes.
-- AUTHID has two possible values.
-- AUTHID DEFINER
-- AUTHID CURRENT_USER
/*******************************************************************************************
QUESTION 2
What is the default AUTHID behavior?
*******************************************************************************************/
-- If AUTHID is not specified,
-- Oracle automatically uses
-- AUTHID DEFINER
-- Example
CREATE OR REPLACE PROCEDURE training_user.sample_proc
AS
BEGIN
NULL;
END;
/
/*******************************************************************************************
QUESTION 3
What is AUTHID DEFINER?
*******************************************************************************************/
-- AUTHID DEFINER means the procedure executes
-- with the privileges of the procedure OWNER.
/*******************************************************************************************
QUESTION 4
Example of AUTHID DEFINER procedure
*******************************************************************************************/
CREATE OR REPLACE PROCEDURE training_user.show_emp_count
AUTHID DEFINER
AS
v_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM hr.employees;
DBMS_OUTPUT.PUT_LINE('Employee Count = ' || v_cnt);
END;
/
/*******************************************************************************************
QUESTION 5
Allow another user to execute the procedure
*******************************************************************************************/
GRANT EXECUTE
ON training_user.show_emp_count
TO public;
/*******************************************************************************************
QUESTION 6
Why does the procedure work even if caller lacks table privileges?
*******************************************************************************************/
-- Because the procedure executes using
-- privileges of the OWNER (training_user).
/*******************************************************************************************
QUESTION 7
What is AUTHID CURRENT_USER?
*******************************************************************************************/
-- AUTHID CURRENT_USER means procedure executes
-- using privileges of the CALLER.
/*******************************************************************************************
QUESTION 8
Example of Invoker Rights procedure
*******************************************************************************************/
CREATE OR REPLACE PROCEDURE training_user.show_emp_count_inv
AUTHID CURRENT_USER
AS
v_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM hr.employees;
DBMS_OUTPUT.PUT_LINE(v_cnt);
END;
/
/*******************************************************************************************
QUESTION 9
What happens if caller lacks privileges?
*******************************************************************************************/
-- The procedure fails with
-- ORA-00942
-- table or view does not exist
/*******************************************************************************************
QUESTION 10
Grant required privilege to caller
*******************************************************************************************/
GRANT SELECT
ON hr.employees
TO training_user;
/*******************************************************************************************
QUESTION 11
Why are roles ignored in stored procedures?
*******************************************************************************************/
-- Oracle disables role privileges inside PL/SQL
-- for security reasons.
-- Only directly granted privileges apply.
/*******************************************************************************************
QUESTION 12
Demonstration of role privilege limitation
*******************************************************************************************/
CREATE ROLE hr_view_role;
GRANT SELECT
ON hr.employees
TO hr_view_role;
GRANT hr_view_role
TO training_user;
-- Attempt procedure creation
CREATE OR REPLACE PROCEDURE training_user.test_role_access
AS
v_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM hr.employees;
END;
/
-- This may fail due to role privilege restriction.
/*******************************************************************************************
QUESTION 13
Correct approach for PL/SQL access
*******************************************************************************************/
GRANT SELECT
ON hr.employees
TO training_user;
/*******************************************************************************************
QUESTION 14
What is dynamic SQL behavior in PL/SQL?
*******************************************************************************************/
-- Dynamic SQL always checks runtime privileges.
/*******************************************************************************************
QUESTION 15
Example of dynamic SQL
*******************************************************************************************/
CREATE OR REPLACE PROCEDURE training_user.dynamic_test
AUTHID DEFINER
AS
v_cnt NUMBER;
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM hr.employees'
INTO v_cnt;
END;
/
/*******************************************************************************************
QUESTION 16
Why is dynamic SQL important for security?
*******************************************************************************************/
-- Dynamic SQL requires privileges at runtime
-- even under DEFINER rights.
/*******************************************************************************************
QUESTION 17
What is privilege escalation risk in DEFINER procedures?
*******************************************************************************************/
-- Poorly written DEFINER procedures can allow
-- unauthorized access to sensitive data.
/*******************************************************************************************
QUESTION 18
Example of potential security risk
*******************************************************************************************/
CREATE OR REPLACE PROCEDURE training_user.get_salary
AUTHID DEFINER
AS
v_salary NUMBER;
BEGIN
SELECT salary
INTO v_salary
FROM hr.employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE(v_salary);
END;
/
-- Any user with EXECUTE privilege could see salary.
/*******************************************************************************************
QUESTION 19
How to mitigate privilege escalation?
*******************************************************************************************/
-- Use parameter validation
-- Restrict access via roles
-- Use secure API packages
/*******************************************************************************************
QUESTION 20
What is secure API design in Oracle?
*******************************************************************************************/
-- Enterprise applications expose procedures
-- instead of tables.
-- Users interact only with APIs.
/*******************************************************************************************
QUESTION 21
Example secure API
*******************************************************************************************/
CREATE OR REPLACE PROCEDURE training_user.get_employee_basic
(
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 22
Grant API access
*******************************************************************************************/
GRANT EXECUTE
ON training_user.get_employee_basic
TO public;
/*******************************************************************************************
QUESTION 23
Why is API access safer?
*******************************************************************************************/
-- Users cannot access underlying tables directly.
/*******************************************************************************************
QUESTION 24
How do packages interact with AUTHID?
*******************************************************************************************/
-- AUTHID applies at package level.
/*******************************************************************************************
QUESTION 25
Example package specification
*******************************************************************************************/
CREATE OR REPLACE PACKAGE training_user.emp_pkg
AUTHID DEFINER
AS
PROCEDURE list_emp;
END;
/
/*******************************************************************************************
QUESTION 26
Package body implementation
*******************************************************************************************/
CREATE OR REPLACE PACKAGE BODY training_user.emp_pkg
AS
PROCEDURE list_emp
IS
BEGIN
FOR r IN
(
SELECT employee_id, first_name
FROM hr.employees
)
LOOP
DBMS_OUTPUT.PUT_LINE(r.employee_id || ' ' || r.first_name);
END LOOP;
END;
END;
/
/*******************************************************************************************
QUESTION 27
Enterprise security pattern
*******************************************************************************************/
-- Users
-- |
-- v
-- Application Roles
-- |
-- v
-- PL/SQL API Packages
-- |
-- v
-- Tables
/*******************************************************************************************
QUESTION 28
Benefits of API-based security
*******************************************************************************************/
-- Prevents direct table access
-- Centralized business logic
-- Easier auditing
-- Better access control
/*******************************************************************************************
QUESTION 29
Best practices for PL/SQL security
*******************************************************************************************/
-- Avoid granting table privileges to application users
-- Use API procedures
-- Validate all parameters
-- Avoid dynamic SQL when possible
-- Use least privilege model
/*******************************************************************************************
QUESTION 30
Interview summary
*******************************************************************************************/
-- AUTHID DEFINER
-- Uses owner privileges
-- AUTHID CURRENT_USER
-- Uses caller privileges
-- Roles ignored inside PL/SQL
-- Secure applications expose procedures
-- not tables.
/*******************************************************************************************
END OF PART 5
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.
*******************************************************************************************/