RFD Cloud Technologies

Robert Diekmann's Dev Demo Platform

PL/SQL Is My Super Power

4 of 17 · 3 Least Privilege

3 Least Privilege

What I have always found frustrating is when I wish to use a feature of Oracle, I find that it has been turned off. Oracle has a “least privilege” philosophy for its databases and because PL/SQL is part of the database this philosophy has spilled over to how it is managed in most organizations. So many times, I have found that everything has been turned off. I’ll get into more about the least privilege philosophy later in this book but for now see example 12 for what needs to be enabled.

Use	Required privilege
Basic PL/SQL Usage in Anonymous Blocks	CREATE SESSION
Using Collections in Stored Procedures, Functions, Packages	CREATE PROCEDURE — to create standalone procedures. CREATE FUNCTION — to create standalone functions. CREATE PACKAGE — to define reusable collection-based APIs. CREATE TYPE — if you define SQL-level collection types (nested tables or varrays in SQL scope). EXECUTE — on any user-defined collection types or packages you reference from another schema.
Using SQL-Level Collections (VARRAY, Nested Table)	CREATE TYPE — to define collection types at SQL level. CREATE TABLE — to use collection types in table columns. INSERT, UPDATE, SELECT, DELETE — to work with those tables.

Example 12 – Privileges for using a collection

Table of contents