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.
Data Dictionary Objects
ARGUMENT$
CDB_OBJECT_SIZE
ERROR$
ALL_ARGUMENTS
CDB_PROCEDURES
SOURCE$
ALL_ERRORS_AE
CDB_SOURCE_AE
USER_ARGUMENTS
ALL_OBJECT_SIZE
DBA_ARGUMENTS
USER_ERRORS_AE
ALL_PROCEDURES
DBA_ERRORS_AE
USER_OBJECT_SIZE
ALL_SOURCE_AE
DBA_OBJECT_SIZE
USER_PROCEDURES
CDB_ARGUMENTS
DBA_PROCEDURES
USER_SOURCE_AE
CDB_ERRORS_AE
DBA_SOURCE_AE
Object Privileges
Privileges to tables and views granted through roles may not be valid within a procedure. See the section on AUTHID.
GRANT execute ON <object_name> TO <user_name>;
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE PROCEDURE test AUTHID DEFINER IS
BEGIN
NULL;
END test;
/
CREATE OR REPLACE PROCEDURE <procedure_name> IS
BEGIN
<code here>
END <procedure_name>;
/
CREATE OR REPLACE PROCEDURE no_param IS
BEGIN
dbms_output.put_line('No Params');
END no_param;
/
set serveroutput on
exec no_param;
Single IN Parameter
CREATE OR REPLACE PROCEDURE <procedure_name> (<parameter_name> IN <data_type>) IS
BEGIN
<code here>
END <procedure_name>;
/
CREATE OR REPLACE PROCEDURE in_param (mesg IN VARCHAR2) IS
BEGIN
dbms_output.put_line(mesg);
END in_param;
/
set serveroutput on
exec in_param('Single IN Parameter');
OUT Parameter
CREATE OR REPLACE PROCEDURE <procedure_name> (<parameter_name> OUT <data_type>) IS
BEGIN
<code here>
END <procedure_name>;
/
CREATE OR REPLACE PROCEDURE out_param(mesg OUT VARCHAR2) IS
BEGIN
mesg := 'Single OUT Parameter';
END out_param;
/
set serveroutput on
DECLARE
s VARCHAR2(50);
BEGIN
out_param(s);
dbms_output.put_line(s);
END;
/
CREATE OR REPLACE PROCEDURE out_param (mesg OUT VARCHAR2) IS
BEGIN
mesg := 'Single OUT Parameter';
END out_param;
/
SQL> var x VARCHAR2(30)
SQL> exec out_param(:x)
SQL> print x
IN OUT Parameter
CREATE OR REPLACE PROCEDURE <procedure_name> (<parameter_name> IN OUT <data_type>) IS
BEGIN
<code here>
END <procedure_name>;
/
CREATE OR REPLACE PROCEDURE inout_param (mesg IN OUT VARCHAR2) IS
BEGIN
mesg := mesg || ' an IN OUT Parameter';
END inout_param;
/
set serveroutput on
DECLARE
s VARCHAR2(50) := 'This procedure uses';
BEGIN
inout_param(s);
dbms_output.put_line(s);
END;
/
Multiple Parameters
CREATE OR REPLACE PROCEDURE <procedure_name> (
<parameter_name> IN <data_type>,
<parameter_name> OUT <data_type>,
<parameter_name> IN OUT <data_type>)
IS
BEGIN
<code here>
END <procedure_name>;
/
CREATE OR REPLACE PROCEDURE many_params (
mesg1 IN VARCHAR2,
mesg2 OUT VARCHAR2,
mesg3 IN OUT VARCHAR2) IS
BEGIN
mesg2 := mesg1 || 'Parameter As The OUT';
mesg3 := mesg3 || 'Returned';
END many_params;
/
set serveroutput on
DECLARE
iparm VARCHAR2(50) := 'This is the IN ';
oparm VARCHAR2(50);
ioparm VARCHAR2(50) := 'And This is the IN OUT ';
BEGIN
many_params(iparm, oparm, ioparm);
dbms_output.put_line(oparm || ' ' || ioparm);
END;
/
ROWTYPE% Parameter
CREATE OR REPLACE PROCEDURE
rtproc(rt_in IN servers%ROWTYPE) AUTHID DEFINER IS
BEGIN
dbms_output.put_line('Server ID: ' || TO_CHAR(rt_in.srvr_id));
dbms_output.put_line('Network ID: ' || TO_CHAR(rt_in.network_id));
END rtproc;
/
DECLARE
srvr_rt servers%ROWTYPE;
BEGIN
srvr_rt.srvr_id := 100;
srvr_rt.network_id := 100;
rtproc(srvr_rt);
END rowtype_proc;
/
User Defined Data Type
desc servers
CREATE OR REPLACE TYPE uw_sel_row AUTHID DEFINER AS OBJECT (
srvr_id NUMBER(10), netaddress VARCHAR2(15));
/
CREATE OR REPLACE PROCEDURE rtproc(rt_in IN uw_sel_row) AUTHID DEFINER IS
BEGIN
dbms_output.put_line('Server ID: ' || TO_CHAR(rt_in.srvr_id));
dbms_output.put_line('Network ID: ' || TO_CHAR(rt_in.netaddress));
END rtproc;
/
DECLARE
l_data uw_sel_row := uw_sel_row(NULL, NULL);
BEGIN
l_data.srvr_id := 100;
l_data.netaddress := '192.168.1.101';
rtproc(l_data);
END rowtype_proc;
/
Parameter DEFAULTs
Procedure Without Default
CREATE OR REPLACE PROCEDURE <procedure_name> (
<parameter_name> <data_type> <default_value>) IS
BEGIN
<code>
END;
/
CREATE OR REPLACE PROCEDURE no_default(num_rows PLS_INTEGER) IS
BEGIN
FOR r IN (SELECT object_name FROM all_objects_ae WHERE rownum < num_rows+1) LOOP
dbms_output.put_line(r.object_name);
END LOOP;
END no_default;
/
set serveroutput on
exec no_default
exec no_default(5);
Procedure With Parameter Defaults
CREATE OR REPLACE PROCEDURE <procedure_name> (
<parameter_name> <data_type> DEFAULT <default_value>) IS
BEGIN
<code>
END;
/
CREATE OR REPLACE PROCEDURE with_default (
num_rows PLS_INTEGER DEFAULT 20) IS
BEGIN
FOR r IN (SELECT object_name FROM all_objects
WHERE rownum < num_rows+1)
LOOP
dbms_output.put_line(r.object_name);
END LOOP;
END with_default;
/
set serveroutput on
exec with_default;
exec with_default(5);
Parameter Notations
Positional Notation
exec <procedure_name> (<parameter>,<parameter>);
CREATE OR REPLACE PROCEDURE positional (
min_nr IN PLS_INTEGER DEFAULT 100,
max_nr IN PLS_INTEGER DEFAULT 1000) IS
BEGIN
FOR r IN (SELECT table_name
FROM user_all_all_tables
WHERE num_rows BETWEEN min_nr AND max_nr) LOOP
dbms_output.put_line(r.table_name);
END LOOP;
END positional;
/
-- Note: Tried this with REF CURSORS returning 40K rows with, again,
-- no measurable difference. But now watch what happens when used in a
-- demo received from Tom Kyte on 2/9/2007.
CREATE OR REPLACE PROCEDURE default_io(x IN OUT dbms_sql.varchar2s) AUTHID DEFINER AS
BEGIN
FOR i IN 1 .. 2000000 LOOP
x(i) := RPAD('*', 255, '*');
END LOOP;
RAISE PROGRAM_ERROR;
END default_io;
/
CREATE OR REPLACE PROCEDURE nocopy_io(x IN OUT NOCOPY dbms_sql.varchar2s) AUTHID DEFINER AS
BEGIN
FOR i IN 1 .. 2000000 LOOP
x(i) := RPAD( '*', 255, '*' );
END LOOP;
RAISE PROGRAM_ERROR;
END nocopy_io;
/
set serveroutput on
set timing on
DECLARE
l_x dbms_sql.varchar2s;
BEGIN
FOR i IN 1 .. 1000000 LOOP
l_x(i) := RPAD('*', 250, '*');
END LOOP;
default_io(l_x);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('count = ' || l_x.count);
END;
/
DECLARE
l_x dbms_sql.varchar2s;
BEGIN
FOR i IN 1 .. 1000000 LOOP
l_x(i) := RPAD( '*', 250, '*' );
END LOOP;
nocopy_io(l_x);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('count = ' || l_x.count);
END;
/
Note: If a subprogram exists with an unhandled exception, the value assigned to its OUT and IN OUT formal parameters are not copied into the corresponding actual parameters,
the changes appear to roll back. However, when you specify NOCOPY, assignments to the formal parameters immediately affect the actual parameters.
So, if the subprogram exits with an unhandled exception, the (possibly unfinished) changes are not "rolled back."
Error Handling With NOCOPY
CREATE OR REPLACE PROCEDURE raise_error (p_Raise BOOLEAN, p_ParameterA OUT NOCOPY NUMBER)
AUTHID DEFINER IS
BEGIN
p_ParameterA := 7;
IF p_Raise THEN
RAISE DUP_VAL_ON_INDEX;
ELSE
RETURN;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN;
END raise_error;
/
set serveroutput on
DECLARE
p_B BOOLEAN := TRUE;
n NUMBER;
BEGIN
raise_error(p_B, n);
dbms_output.put_line(n);
END;
/
CREATE OR REPLACE PROCEDURE raise_error (p_Raise BOOLEAN, p_ParameterA IN OUT NOCOPY NUMBER)
AUTHID DEFINER AS
BEGIN
IF p_Raise THEN
RAISE DUP_VAL_ON_INDEX;
ELSE
p_ParameterA := 999;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END raise_error;
/
DECLARE
p_B BOOLEAN := FALSE;
n NUMBER := 100;
BEGIN
raise_error(p_B, n);
dbms_output.put_line(n);
END;
/
DECLARE
p_B BOOLEAN := TRUE;
n NUMBER := 100;
BEGIN
raise_error(p_B, n);
dbms_output.put_line(n);
END;
/
Note: For DEFINER RIGHTS objects execute privileges must be granted directly to the user; not to a role.
With CURRENT USER rights can be granted to a role. AUTHID DEFINER (default) stored procedures are executed in environment equivalent to the one you get after SET ROLE NONE.
In other words, roles are disabled for PL/SQL and any privileges granted via roles do not apply unless you created the procedure with AUTHID CURRENT_USER,
in which case role privileges do apply (but executing such procedures is a bit more expensive because Oracle has to evaluate the privileges on every call).
AUTHID Demo
-- run the procedure with the rights of the procedure's owner. AUTHID DEFINER (the default)
-- run the procedure with the rights of the executing schema. AUTHID CURRENT_USER
conn dbadmin/dbadmin@pdbdev
CREATE USER abc
IDENTIFIED BY abc
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 10M ON uwdata;
GRANT create session, create table, create procedure to abc;
conn abc/abc
CREATE TABLE t1 (
mycol VARCHAR2(20));
CREATE TABLE t2 (
yourcol NUMBER(10,2));
CREATE TABLE t3 (
ourcol DATE);
conn uwclass/uwclass
CREATE OR REPLACE PROCEDURE definer_test AUTHID DEFINER IS
BEGIN
FOR rec IN (SELECT table_name FROM user_all_tables) LOOP
dbms_output.put_line(rec.table_name);
END LOOP;
END definer_test;
/
CREATE OR REPLACE PROCEDURE cu_test AUTHID CURRENT_USER IS
BEGIN
FOR rec IN (SELECT table_name FROM user_all_tables) LOOP
dbms_output.put_line(rec.table_name);
END LOOP;
END cu_test;
/
set serveroutput on
exec definer_test;
exec cu_test;
GRANT execute on definer_test TO abc;
GRANT execute on cu_test TO abc;
conn abc/abc
set serveroutput on
exec uwclass.definer_test;
exec uwclass.cu_test;
conn abc/abc
CREATE OR REPLACE PROCEDURE definer_test AUTHID DEFINER IS
BEGIN
FOR rec IN (SELECT srvr_id FROM servers) LOOP
dbms_output.put_line(rec.srvr_id);
END LOOP;
END definer_test;
/
CREATE OR REPLACE PROCEDURE cu_test AUTHID CURRENT_USER IS
BEGIN
FOR rec IN (SELECT srvr_id FROM servers) LOOP
dbms_output.put_line(rec.srvr_id);
END LOOP;
END cu_test;
/
set serveroutput on
exec definer_test;
exec cu_test;
GRANT execute on definer_test TO uwclass;
GRANT execute on cu_test TO uwclass;
conn uwclass/uwclass
set serveroutput on
exec abc.definer_test;
exec abc.cu_test;
AUTHID Affect On Role Granted Object Rights
/// NEED TO WRITE THIS Demo
ALTER Procedure
Compile
ALTER PROCEDURE <procedure_name> COMPILE [REUSE SETTINGS];
ALTER PROCEDURE testproc COMPILE REUSE SETTINGS;
Compile Debug
ALTER PROCEDURE <procedure_name> COMPILE DEBUG;
CREATE OR REPLACE PROCEDURE debugproc AUTHID DEFINER IS
BEGIN
NULL;
END debugproc;
/
col plsql_debug format a30
SELECT plsql_debug
FROM dba_plsql_object_settings
WHERE name = 'DEBUGPROC';
ALTER PROCEDURE debugproc COMPILE DEBUG;
SELECT plsql_debug
FROM dba_plsql_object_settings
WHERE name = 'DEBUGPROC';
Editionable
ALTER PROCEDURE <procedure_name> <EDITIONABLE | NONEDITIONABLE>;
CREATE OR REPLACE NONEDITIONABLE PROCEDURE morgan AUTHID DEFINER IS
BEGIN
NULL;
END morgan;
/
CREATE OR REPLACE EDITIONABLE PROCEDURE morgan AUTHID DEFINER IS
BEGIN
NULL;
END morgan;
/
CREATE OR REPLACE PROCEDURE morgan AUTHID DEFINER IS
*
ERROR at line 1:
ORA_038824: A CREATE OR REPLACE comand may not change the EDITIONABLE property of an existing object.
