Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Advantages To Using Packages
All related code in a single object
All related code loaded into memory simultaneously
Session global variables and types
Single object compilation
Variables persist for term of session
Initialization section
Overloading
Fewer objects to manage and grant/revoke privileges
Executed as the package owner rather than the caller reducing the dictionary cache load
Data Dictionary Objects
ALL_ARGUMENTS
CDB_PROCEDURES
ERROR$
ALL_ERRORS_AE
CDB_SOURCE_AE
SOURCE$
ALL_OBJECT_SIZE
CDB_WARNING_SETTINGS
USER_ARGUMENTS
ALL_PLSQL_OBJECT_SETTINGS
DBA_ARGUMENTS
USER_ERRORS_AE
ALL_PROCEDURES
DBA_ERRORS_AE
USER_OBJECT_SIZE
ALL_SOURCE_AE
DBA_OBJECT_SIZE
USER_PLSQL_OBJECT_SETTINGS
ALL_WARNING_SETTINGS
DBA_PLSQL_OBJECT_SETTINGS
USER_PROCEDURES
CDB_ARGUMENTS
DBA_PROCEDURES
USER_SOURCE_AE
CDB_ERRORS_AE
DBA_SOURCE_AE
USER_WARNING_SETTINGS
CDB_OBJECT_SIZE
DBA_WARNING_SETTINGS
WARNING_SETTINGS$
CDB_PLSQL_OBJECT_SETTINGS
Object Privileges
GRANT execute ON <function_name>;
Privileges to tables and views granted through roles may not be valid within a procedure. See the section on AUTHID.
System Privileges
ALTER ANY PROCEDURE
CREATE PROCEDURE
DROP ANY PROCEDURE
CREATE ANY PROCEDURE
DEBUG ANY PROCEDURE
EXECUTE ANY PROCEDURE
Orphan Package Headers
Package Header Defining Variables and Constants
CREATE OR REPLACE PACKAGE <package_name> AUTHID <authid_value> IS
<variable name> CONSTANT <data_type> := <value>;
END <package_name>;
/
CREATE OR REPLACE PACKAGE uw_constants AUTHID DEFINER IS
cStartDate CONSTANT DATE := TO_DATE('15-MAR-2018');
cInstructor CONSTANT VARCHAR2(30) := 'A. Morgan';
cPi CONSTANT NUMBER(8,7) := 3.1415926;
END uw_constants;
/
set serveroutput on
DECLARE
x VARCHAR2(20);
BEGIN
x := 'Daniel ' || uw_constants.cInstructor;
dbms_output.put_line(x);
END;
/
DECLARE
x NUMBER(10,8);
BEGIN
x := uw_constants.cPi * 2;
dbms_output.put_line(TO_CHAR(x));
END;
/
Package Header Defining REF CURSORS And User Defined Data Types
CREATE OR REPLACE PACKAGE uw_type AUTHID DEFINER IS
TYPE t_ref_cursor IS REF CURSOR;
TYPE tab_t IS TABLE OF all_tables.table_name%TYPE
INDEX BY BINARY_INTEGER;
ptab_t tab_t;
END uw_type;
/
set serveroutput on
DECLARE
testvar uw_type.tab_t;
BEGIN
testvar(1) := 'This is a test';
dbms_output.put_line(testvar(1));
END;
/
-- See Bulk Collection and Native Dynamic SQL link below for demos with these TYPE definitions.
Simple Packages
Package With One Procedure
CREATE OR REPLACE PACKAGE <package_name> AUTHID <authid_value> AS
PROCEDURE <procedure_name> (<parameters>);
END <package_name>;
/
CREATE OR REPLACE PACKAGE BODY <package_name> AS
--========================================
PROCEDURE <procedure_name> (<parameters>) IS
<define local variables, constants, and exceptions>
BEGIN
<procedure_code>;
END <procedure_name>;
--========================================
END <package_name>;
/
CREATE OR REPLACE PACKAGE one_proc AUTHID DEFINER AS
PROCEDURE get_table(number_in IN PLS_INTEGER);
END one_proc;
/
CREATE OR REPLACE PACKAGE BODY one_proc AS
--========================================
PROCEDURE get_table(number_in IN PLS_INTEGER) IS
tabname user_tables.table_name%TYPE;
BEGIN
SELECT table_name
INTO tabname
FROM user_tables
WHERE rownum < number_in;
dbms_output.put_line(tabname);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Too Many Rows');
END get_table;
--========================================
END one_proc;
/
set serveroutput on
exec one_proc.get_table(2);
Package With One Function
CREATE OR REPLACE PACKAGE one_function AUTHID DEFINER AS
FUNCTION GetOSUser RETURN VARCHAR2;
END one_function;
/
CREATE OR REPLACE PACKAGE BODY one_function AS
--========================================
FUNCTION getosuser RETURN VARCHAR2 IS
vOSUser user_users.username%TYPE;
BEGIN
SELECT osuser
INTO vOSUser
FROM sys.v_$session
WHERE sid = (
SELECT sid
FROM sys.v$mystat
WHERE rownum = 1);
RETURN vOSUser;
EXCEPTION
WHEN OTHERS THEN
RETURN 'UNK';
END getosuser;
--========================================
END one_function;
/
SELECT one_function.getosuser FROM dual;
Complex Packages
Package With Multiple Procedures & Functions
CREATE OR REPLACE PACKAGE <package_name> AUTHID <authid_value> AS
PROCEDURE <procedure_name> (<parameters>);
PROCEDURE <procedure_name> (<parameters>);
PROCEDURE <procedure_name> (<parameters>);
FUNCTION <function_name> (<parameters>) RETURNING <data_type>;
END <package_name>;
/
CREATE OR REPLACE PACKAGE BODY <package_name> AS
--========================================
PROCEDURE <procedure_name> (<parameters>) IS
<define local variables, constants, and exceptions>
BEGIN
<procedure_code>;
END <procedure_name>;
--========================================
PROCEDURE <procedure_name> (<parameters>) IS
<define local variables, constants, and exceptions>
BEGIN
<procedure_code>;
END <procedure_name>;
--========================================
FUNCTION <function_name> (<parameters>)
RETURNING <data_type> IS
<define local variables, constants, and exceptions>
BEGIN
<function_code>;
END <function_name>;
--========================================
END <package_name>;
/
CREATE OR REPLACE PACKAGE pkg_utility AUTHID DEFINER IS
FUNCTION ConvDate (datestringin VARCHAR2) RETURN DATE;
FUNCTION GetOSUser RETURN VARCHAR2;
PROCEDURE errorlogutil(pProcName IN VARCHAR2,
pBlockID IN VARCHAR2,
pSysMode IN VARCHAR2,
pErrCode IN VARCHAR2,
pErrMesg IN VARCHAR2);
END pkg_utility;
/
--========================================
CREATE OR REPLACE PACKAGE BODY pkg_utility IS
FUNCTION ConvDate (datestringin VARCHAR2) RETURN DATE IS
/**************************
6 = M/D/YY
7 = M/DD/YY or MM/D/YY
8 = M/D/YYYY or MM/DD/YY
9 = MM/D/YYYY or M/DD/YYYY
10 = MM/DD/YYYY
**************************/
strlen PLS_INTEGER;
slash1 PLS_INTEGER;
slash2 PLS_INTEGER;
x VARCHAR2(10);
baddate EXCEPTION;
IF strlen = 6 THEN
x := '0' || SUBSTR(datestringin,1,1) || '/0'
|| SUBSTR(datestringin, 3,1) || '/'
|| SUBSTR(datestringin, 5);
RETURN TO_DATE(x, 'MM/DD/RRRR');
ELSIF strlen = 7 THEN
IF slash1 = 2 THEN
RETURN TO_DATE('0' || datestringin, 'MM/DD/RRRR');
ELSIF slash1 = 3 THEN
x := SUBSTR(datestringin,1,3) || '0' ||
SUBSTR(datestringin,4);
RETURN TO_DATE(datestringin, 'MM/DD/RRRR');
ELSE
RAISE baddate;
END IF;
ELSIF strlen = 8 THEN
IF slash1 = 2 THEN
x := '0' || SUBSTR(datestringin,1,2) || '0'
|| SUBSTR(datestringin, 3);
RETURN TO_DATE(datestringin, 'MM/DD/RRRR');
ELSIF slash1 = 3 THEN
RETURN TO_DATE(datestringin, 'MM/DD/RR');
ELSE
RAISE baddate;
END IF;
ELSIF strlen = 9 THEN
IF slash1 = 2 THEN
RETURN TO_DATE('0' || datestringin, 'MM/DD/RRRR');
ELSIF slash1 = 3 THEN
RETURN TO_DATE(datestringin, 'MM/DD/RRRR');
ELSE
RAISE baddate;
END IF;
ELSIF strlen = 10 THEN
RETURN TO_DATE(datestringin, 'MM/DD/RRRR');
ELSE
RAISE baddate;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN TO_DATE('01-JAN-1900');
END ConvDate;
--===========================================
FUNCTION getosuser RETURN VARCHAR2 IS
vOSUser user_users.username%TYPE;
BEGIN
SELECT osuser
INTO vOSUser
FROM sys.v_$session
WHERE sid = (
SELECT sid
FROM sys.v$mystat
WHERE rownum = 1);
RETURN vOSUser;
EXCEPTION
WHEN OTHERS THEN
RETURN 'UNK';
END getosuser;
--===========================================
PROCEDURE errorlogutil (
pProcName VARCHAR2,
pBlockID VARCHAR2,
pSysMode VARCHAR2,
pErrCode VARCHAR2,
pErrMesg VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_log
(proc_name, block_identifier, system_mode,
error_code, error_message, error_user, error_date)
VALUES
(pProcName, pBlockID, pSysMode,
pErrCode, pErrMesg, getOSUser, SYSDATE);
COMMIT;
-- no exception handler intentionally
END errorlogutil;
--===========================================
END pkg_utility;
/
SELECT pkg_utility.convdate('1/2/03') FROM dual;
SELECT pkg_utility.convdate('02/03/2018') FROM dual;
SELECT pkg_utility.getosuser FROM dual;
Package with Local Function or Procedure
Package With Local Function and Global Procedure
CREATE OR REPLACE PACKAGE <package_name> AUTHID <authid_value> AS
PROCEDURE <procedure_name> (<parameters>);
-- note the function is not defined in the header
END <package_name>;
/
CREATE OR REPLACE PACKAGE private_function AUTHID DEFINER AS
PROCEDURE encrypt_name(namein VARCHAR2);
END private_function;
/
--======================================
CREATE OR REPLACE PACKAGE BODY private_function AS
--======================================
-- must appear before it is called
FUNCTION encrypt_name(namein VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN TRANSLATE(namein, 'aeiou', 'uiaeo');
END encrypt_name;
--======================================
PROCEDURE encrypt_name(namein VARCHAR2) IS
val VARCHAR2(50);
BEGIN
val := encrypt_name(namein);
dbms_output.put_line(val);
END encrypt_name;
--======================================
END private_function;
/
set pagesize 25
set linesize 121
col overload format a8
SELECT overload, position, argument_name, in_out, data_type
FROM all_arguments
WHERE object_name = 'CREATE_TUNING_TASK'
ORDER BY overload, position;
Create An Overloaded Package
CREATE TABLE persons (
seqno INTEGER,
name VARCHAR2(30));
INSERT INTO persons VALUES (1, 'Dan Morgan');
INSERT INTO persons VALUES (2, 'Debra Lilley');
COMMIT;
CREATE OR REPLACE PACKAGE overloaded AUTHID DEFINER IS
PROCEDURE insby(namein VARCHAR2);
PROCEDURE insby(numbin PLS_INTEGER);
END overloaded;
/
CREATE OR REPLACE PACKAGE BODY overloaded IS
--==========================================
PROCEDURE local(namein VARCHAR2, nameout OUT VARCHAR2) IS
BEGIN
nameout := TRANSLATE(namein,'AEIOUaeiou','EIOUAeioua');
END local;
--==========================================
PROCEDURE insby (namein VARCHAR2) IS
x PLS_INTEGER;
BEGIN
SELECT MAX(seqno)+1
INTO x
FROM persons;
INSERT INTO persons
(seqno, name)
VALUES
(x, namein);
COMMIT;
END insby;
--==========================================
PROCEDURE insby (numbin PLS_INTEGER) IS
x VARCHAR2(30);
BEGIN
SELECT MAX(name)
INTO x
FROM persons;
local(x, x);
INSERT INTO persons
(seqno, name)
VALUES
(numbin, x);
COMMIT;
END insby;
--==========================================
END overloaded;
/
CREATE OR REPLACE PACKAGE init_sect AUTHID DEFINER IS
global_var VARCHAR2(50);
FUNCTION dummy RETURN VARCHAR2;
END init_sect;
/
CREATE OR REPLACE PACKAGE BODY init_sect IS
FUNCTION dummy RETURN VARCHAR2 IS
BEGIN
RETURN global_var;
END dummy;
--===============================
BEGIN
SELECT 'Dan Morgan'
INTO global_var
FROM dual;
END init_sect;
/
SELECT init_sect.dummy FROM dual;
Reusable Initialization Section Demo
CREATE OR REPLACE PACKAGE init_refresh AUTHID DEFINER IS
global_year VARCHAR2(4);
FUNCTION dummy RETURN VARCHAR2;
PROCEDURE refresh;
END init_refresh;
/
CREATE OR REPLACE PACKAGE BODY init_refresh IS
FUNCTION dummy RETURN VARCHAR2 IS
BEGIN
RETURN global_year;
END dummy;
--===============================
PROCEDURE refresh IS
BEGIN
SELECT TO_CHAR(SYSDATE, 'YYYY')
INTO global_year
FROM dual;
END refresh;
--===============================
BEGIN
refresh;
END init_refresh;
/
-- regular package - uses user global memory (UGA)
CREATE OR REPLACE PACKAGE reg_pkg AUTHID DEFINER IS
x NUMBER(5);
PROCEDURE setval(val NUMBER);
PROCEDURE getval;
END reg_pkg;
/
CREATE OR REPLACE PACKAGE BODY reg_pkg IS
PROCEDURE setval (val NUMBER) IS
BEGIN
x := val;
END setval;
---------------------------------
PROCEDURE getval IS
BEGIN
IF x IS NOT NULL THEN
dbms_output.put_line(x);
ELSE
dbms_output.put_line('x is NULL');
END IF;
END getval;
END reg_pkg;
/
-- view variable persistance of standard package
exec reg_pkg.setval(5);
exec reg_pkg.getval;
--===============================
-- serially reusable package - uses shared pool memory
CREATE OR REPLACE PACKAGE sr_pkg AUTHID DEFINER IS
PRAGMA SERIALLY_REUSABLE;
x NUMBER(5);
PROCEDURE setval(val NUMBER);
PROCEDURE getval;
END sr_pkg;
/
CREATE OR REPLACE PACKAGE BODY sr_pkg IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE setval (val NUMBER) IS
BEGIN
x := val;
END setval;
---------------------------------
PROCEDURE getval IS
BEGIN
IF x IS NOT NULL THEN
dbms_output.put_line(x);
ELSE
dbms_output.put_line('x is NULL');
END IF;
END getval;
END sr_pkg;
/
SELECT object_name, last_ddl_time
FROM user_objects_ae
WHERE object_type = 'PACKAGE';
ALTER PACKAGE init_refresh COMPILE;
SELECT object_name, last_ddl_time
FROM user_objects_ae
WHERE object_type = 'PACKAGE';
Drop Package
Drop Package Header and Body
DROP PACKAGE <package_name>;
SELECT object_name
FROM user_objects_ae
WHERE object_type = 'PACKAGE';
DROP PACKAGE init_refresh;
desc init_refresh
Drop Package Body Only
DROP PACKAGE BODY <package_name>;
SELECT object_name
FROM user_objects_ae
WHERE object_type = 'PACKAGE';
DROP PACKAGE BODY init_sect;
desc init_sect
Undocumented Package
I've no explanation for the following but wanted to document it here.
If anyone can provide clarity please email me.
CREATE OR REPLACE PACKAGE test AUTHID DEFINER AS
FUNCTION testf(args ...) RETURN VARCHAR2;
END test;
/
desc test
SELECT argument_name, position, data_type, in_out
FROM all_arguments
WHERE package_name = 'TEST';
CREATE OR REPLACE PACKAGE BODY test AS
FUNCTION testf(args ...) RETURN VARCHAR2 IS
BEGIN
RETURN 'Z';
END testf;
END test;
/
SQL> sho err
Errors for PACKAGE BODY TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/16 PLS-00999: implementation restriction (may be temporary) ellipsis
not allowed in this context
Package Related Queries
Retrieve Package Metadata
SELECT object_name, edition_name
FROM user_objects_ae
WHERE object_type = 'PACKAGE';
col object_name format a30
col data_type format a15
SELECT object_name, argument_name, position, data_type, data_length
FROM user_arguments
WHERE package_name = 'SR_PKG'
ORDER BY object_name, position;
Retrieve Package Source Code
SELECT text
FROM user_source
WHERE name = 'SR_PKG';
or
SELECT dbms_metadata.get_ddl('PACKAGE', 'SR_PKG')
FROM dual;
Overloaded Packages
conn sys@pdbdev as sysdba
SELECT overload, COUNT(*)
FROM all_arguments
WHERE overload IS NOT NULL
GROUP BY overload
HAVING COUNT(*) > 1
ORDER BY TO_NUMBER(overload);
Is a Package Currently In Use
conn sys@pdbdev as sysdba
desc gv$db_object_cache
SELECT name, loads, executions, pins
FROM v$db_object_cache
WHERE type = 'PACKAGE'
AND pins > 0;