| Security Advisory |
This package provides an interface for printing and retrieving the source text of a PL/SQL unit in its post-processed form.
Oracle is far too liberal with tools that can be used by unprivileged users to reverse engineer PL/SQL code.
SELECT dbms_metadata.get_ddl(..., ..., ...) FROM dual;
SELECT * FROM all_source;
SELECT * FROM all_source_ae;
SELECT * FROM user_source;
SELECT * FROM user_source_ae;
On the "good" side of the equation Oracle does provide the wrap executable that can be used to defeat these tools.
On the "bad" side of the equation very few organizations utilize wrap and in a large number of cases Oracle Corp. doesn't use it either.
The issue with DBMS_PREPROCESSOR is that for no justifiable reason EXECUTE is granted to PUBLIC by default. |
| |
| Recommended Security Rules |
NEVER
- Accept Oracle's default grant of EXECUTE to PUBLIC for this package
WITH GREAT CARE
- Verify in a pre-production environment that code/applications that will be hosted do not require EXECUTE
- If any code/application requires EXECUTE grant EXECUTE explicitly and document the change
- Revoke EXECUTE from PUBLIC
CAUTIONS
- When improving security by revoking default grants such as EXECUTE to PUBLIC be sure to add them to your testing when upgrading to newer versions or upgrading the application(s) deployed and utilizing the database.
|
| |
| |
| DBMS_PREPROCESSOR Package Information |
| AUTHID |
CURRENT_USER |
| Data Types |
TYPE source_lines_t IS TABLE OF VARCHAR2(32767)
INDEX BY BINARY_INTEGER; |
| Dependencies |
| ALL_SOURCE |
DBMS_STANDARD |
PLITBLM |
| DBMS_OUTPUT |
DBMS_SYS_ERROR |
|
|
| Documented |
Yes |
| Exceptions |
| Error Code |
Reason |
| ORA-00931 |
Missing identifier. The object_name should not be NULL |
| ORA-06502 |
Numeric or value error |
| ORA-24234 |
Insufficient privileges or object does not exist |
| ORA-24235 |
Invalid object type: Must be one of PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, TYPE, TYPE, BODY or TRIGGER |
| ORA-24236 |
Source text is empty |
| ORA-24241 |
Source text is in wrapped format |
|
| First Available |
10gR2 |
| Security Model |
Owned by SYS with EXECUTE granted to PUBLIC |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmspp.sql |
| Subprograms |
|
| |
| GET_POST_PROCESSED_SOURCE |
Returns post-processed source text
Overload 1 |
dbms_preprocessor.get_post_processed_source (
object_type IN VARCHAR2,
schema_name IN VARCHAR2,
object_name IN VARCHAR2)
RETURN source_lines_t; |
CREATE OR REPLACE FUNCTION test_func RETURN NUMBER AUTHID DEFINER IS
i user_tables.blocks%TYPE := 1;
BEGIN
-- a demo comment in the code
i := i + 1;
RETURN i;
END test_func;
/
set serveroutput on
DECLARE
retval_t dbms_preprocessor.source_lines_t;
BEGIN
retval_t := dbms_preprocessor.get_post_processed_source(
'FUNCTION', 'UWCLASS', 'TEST_FUNC');
FOR i IN 1..retval_t.LAST
LOOP
dbms_output.put_line(retval_t(i));
END LOOP;
END;
/
FUNCTION test_func RETURN NUMBER AUTHID DEFINER IS
i user_tables.blocks%TYPE := 1;
BEGIN
-- a demo comment in the code
i := i + 1;
RETURN i;
END test_func;
PL/SQL procedure successfully completed.
-- after wrapping the test_func source code above the anonymous block was rerun
-- to prove that the package cannot be used to reverse engineer wrapped PL/SQL
-- See Exception ORA-24241 (above)
CREATE OR REPLACE FUNCTION test_func wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
7f be
tb+RDMRsWcBdvT66bdcZOiaX6SUwg2LwLZ7hfy9EO06UZC3TNrrWlUPCs5XHrsKYAlyesyXU
TrXwcK3Q7i5SiPAXr5swls06QlXM+jSe25b0xTnEtheRxVakkx1qjScDqC4aBSHXhpOxn5jT
M1uK4Fr1kb1MHoW05Oi1bMZn8TgaPPIoxj0fHu+oeQ==
/
DECLARE
retval_t dbms_preprocessor.source_lines_t;
BEGIN
retval_t := dbms_preprocessor.get_post_processed_source(
'FUNCTION', 'UWCLASS', 'TEST_FUNC');
FOR i IN 1..retval_t.LAST
LOOP
dbms_output.put_line(retval_t(i));
END LOOP;
END;
/
DECLARE
*
ERROR at line 1:
ORA-24241: source text is in wrapped format
ORA-06512: at "SYS.DBMS_PREPROCESSOR", line 84
ORA-06512: at "SYS.DBMS_PREPROCESSOR", line 197
ORA-06512: at line 4 |
| Overload 2 |
dbms_preprocessor(source IN VARCHAR2) RETURN source_lines_t; |
set serveroutput on
DECLARE
instring VARCHAR2(32767);
retval_t dbms_preprocessor.source_lines_t;
BEGIN
instring := 'BEGIN ' ||
' FOR i IN 1 .. 100 LOOP ' ||
' NULL; ' ||
' END LOOP; ' ||
'END;';
retval_t := dbms_preprocessor.get_post_processed_source(instring);
FOR i IN 1..retval_t.LAST
LOOP
dbms_output.put_line(retval_t(i));
END LOOP;
END;
/
BEGIN FOR i IN 1 .. 100 LOOP NULL; END LOOP; END;
PL/SQL procedure successfully completed. |
| Overload 3 |
dbms_preprocessor(source_lines_t IN VARCHAR2) RETURN source_lines_t; |
set serveroutput on
DECLARE
inval_t dbms_preprocessor.source_lines_t;
retval_t dbms_preprocessor.source_lines_t;
BEGIN
inval_t(1) := 'BEGIN ';
inval_t(2) := ' FOR i IN 1 .. 100 LOOP ';
inval_t(3) := ' NULL; ';
inval_t(4) := ' END LOOP; ';
inval_t(5) := 'END;';
retval_t := dbms_preprocessor.get_post_processed_source(inval_t);
FOR i IN 1..retval_t.LAST
LOOP
dbms_output.put_line(retval_t(i));
END LOOP;
END;
/
BEGIN FOR i IN 1 .. 100 LOOP NULL; END LOOP; END;
PL/SQL procedure successfully completed. |
| |
| PRINT_POST_PROCESSED_SOURCE |
Given a stored PL/SQL unit, print its post-processed source text
Overload 1 |
dbms_preprocessor.print_post_processed_source(
object_type IN VARCHAR2,
schema_name IN VARCHAR2,
object_name IN VARCHAR2); |
set serveroutput on
BEGIN
dbms_preprocessor.print_post_processed_source('FUNCTION', 'UWCLASS', 'TEST_FUNC');
END;
/ |
| Overload 2 |
dbms_preprocessor.print_post_processed_source(source IN VARCHAR2); |
set serveroutput on
DECLARE
str VARCHAR2(32767) := 'BEGIN NULL; END;';
BEGIN
dbms_preprocessor.print_post_processed_source(str);
END;
/ |
| Overload 3 |
dbms_preprocessor.print_post_processed_source(source IN source_lines_t); |
set serveroutput on
DECLARE
inval_t dbms_preprocessor.source_lines_t;
BEGIN
inval_t(1) := 'BEGIN ';
inval_t(2) := 'FOR i IN 1 .. 100 LOOP ';
inval_t(3) := 'NULL; ';
inval_t(4) := 'END LOOP; ';
inval_t(5) := 'END;';
dbms_preprocessor.print_post_processed_source(inval_t);
END;
/ |