| Security Advisory |
| SQL Tracing is valuable tool when used for performance tuning and triage. Tracing can also be used to learn about the internals of wrapped code and to reveal methods and dependencies.
This PL/SQL package is still documented and supported and has EXECUTE granted to PUBLIC which makes it a substantial security risk. |
| |
| Recommended Security Rules |
NEVER
- Deploy a database without revoking EXECUTE from PUBLIC.
WITH GREAT CARE
- Identify legitimate requirements for access to this package and grant EXECUTE explicitly to only justified schemas
- Query the data dictionary after EXECUTE has been revoked from PUBLIC to verify the equivalence created is the equivalence approved by IT management and your CISO
CAUTIONS
- Some usage may be in the form of dynamic SQL so carefully verify usage requirements in source code as well as in DBA_DEPENDENCIES
|
| |
| How Oracle Works |
| SQL Tracing with ALTER SYSTEM has been around since the Jurassic. Oracle has been building tools to make tracing easier since almost that long. |
ALTER SYSTEM statements can be manually entered at the SQL*Plus command line and will produce an output.
There are hundreds, if not thousands, of Event Numbers that can be used to trace specific behaviors but the overwhelming majority of tracing is with the 10046 event and that this package was designed to simplify starting and stopping 10046 tracing.
This package, through its constants, greatly extends the capabilities first introduced with the DBMS_SUPPORT package and can provide little-known but valuable insights into SQL and PL/SQL execution.
It is the value that this package can provide, when put into the wrong hands through a grant of EXECUTE to PUBLIC that creates the issue. Use of this package should be tightly restricted to only those that can justify a need and usage should be audited. |
| |
| DBMS_SUPPORT Information |
| AUTHID |
DEFINER |
| Constants |
| Name |
Data Type |
Value |
| SQL Trace Constants |
| trace_all_calls |
INTEGER |
1 |
| trace_enabled_calls |
INTEGER |
2 |
| trace_all_exceptions |
INTEGER |
4 |
| trace_enabled_exceptions |
INTEGER |
8 |
| trace_limit |
INTEGER |
16 |
| trace_all_sql |
INTEGER |
32 |
| trace_enabled_sql |
INTEGER |
64 |
| trace_all_lines |
INTEGER |
128 |
| trace_enabled_lines |
INTEGER |
256 |
| trace_pause |
INTEGER |
4096 |
| trace_resume |
INTEGER |
8192 |
| trace_stop |
INTEGER |
16384 |
| no_trace_administrative |
INTEGER |
32768 |
| no_trace_handled_exceptions |
INTEGER |
65536 |
| Version Constants |
| trace_major_version |
BINARY_INTEGER |
0 |
| trace_minor_version |
BINARY_INTEGER |
1 |
| PL/SQL Trace Constants |
| plsql_trace_start |
INTEGER |
38 |
| plsql_trace_stop |
INTEGER |
39 |
| plsql_trace_set_flags |
INTEGER |
40 |
| plsql_trace_pause |
INTEGER |
41 |
| plsql_trace_resume |
INTEGER |
42 |
| plsql_trace_enter_vm |
INTEGER |
43 |
| plsql_trace_exit_vm |
INTEGER |
44 |
| plsql_trace_begin_call |
INTEGER |
45 |
| plsql_trace_elab_spec |
INTEGER |
46 |
| plsql_trace_elab_body |
INTEGER |
47 |
| plsql_trace_icd |
INTEGER |
48 |
| plsql_trace_rpc |
INTEGER |
49 |
| plsql_trace_end_call |
INTEGER |
50 |
| plsql_trace_new_line |
INTEGER |
51 |
| plsql_trace_excp_raised |
INTEGER |
52 |
| plsql_trace_excp_handled |
INTEGER |
54 |
| plsql_trace_sql |
INTEGER |
54 |
| plsql_trace_bind |
INTEGER |
55 |
| plsql_trace_user |
INTEGER |
56 |
| plsql_trace_nodebug |
INTEGER |
57 |
| plsql_trace_excp_unhandled |
INTEGER |
58 |
|
| Dependencies |
|
| Documented |
Yes |
| First Available |
8.1.5 |
| Security Model |
Owned by SYS with EXECUTE granted to PUBLIC |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmspbt.sql |
| Subprograms |
|
| |
| Demo Preparations |
| Enabling Tracing |
ALTER SESSION SET plsql_debug=TRUE;
or
ALTER [PROCEDURE | FUNCTION | PACKAGE BODY] <unit-name>
COMPILE DEBUG; |
| Call Tracing |
- Level 1: Trace all calls. This corresponds to the constant trace_all_calls.
- Level 2: Trace calls to enabled program units only. This corresponds to the constant trace_enabled_calls.
Enabling cannot be detected for remote procedure calls (RPCs); hence, RPCs are only traced with level 1. |
| Exception Tracing |
- Level 1: Trace all exceptions. This corresponds to trace_all_exceptions.
- Level 2: Trace exceptions raised in enabled program units only. This corresponds to trace_enabled_exceptions.
|
| Line Tracing |
- Level 1: Trace all lines. This corresponds to the constant trace_all_lines.
- Level 2: Trace lines in enabled program units only. This corresponds to the constant trace_enabled_lines.
|
| SQL Tracing |
- Level 1: Trace all SQL. This corresponds to the constant trace_all_sql.
- Level 2: Trace SQL in enabled program units only. This corresponds to the constant trace_enabled_sql.
|
| Trace Output Table Creation |
$ORACLE_HOME/rdbms/admin/tracetab.sql
desc plsql_trace_runs
desc plsql_trace_events |
| |
| CLEAR_PLSQL_TRACE |
| Stops trace data dumping in the current session |
dbms_trace.clear_plsql_trace; |
exec dbms_trace.clear_plsql_trace; |
| |
| COMMENT_PLSQL_TRACE |
| Add user comment to the trace table |
dbms_trace.comment_plsql_trace(comment IN VARCHAR2); |
exec dbms_trace.comment_plsql_trace('UW Demo Trace'); |
| |
| GET_PLSQL_TRACE_LEVEL |
| Returns the current trace level (a sum of the constants) |
dbms_trace.get_plsql_trace_level RETURN BINARY_INTEGER; |
SELECT dbms_trace.get_plsql_trace_level
FROM dual;
exec dbms_trace.set_plsql_trace(2);
SELECT dbms_trace.get_plsql_trace_level
FROM dual; |
| |
| GET_PLSQL_TRACE_RUNNUMBER |
| Return the trace run number |
dbms_trace.get_plsql_trace_runnumber RETURN BINARY_INTEGER; |
SELECT dbms_trace.get_plsql_trace_runnumber
FROM dual; |
| |
| INTERNAL_VERSION_CHECK |
| Verifies version is compatible with current instance |
dbms_trace.internal_version_check RETURN BINARY_INTEGER; |
SELECT dbms_trace.internal_version_check
FROM dual; |
| |
| LIMIT_PLSQL_TRACE |
| Limit the amount of data dumped by the trace (number of records) |
dbms_trace.limit_plsql_tracelimit IN BINARY_INTEGER := 8192); |
exec dbms_trace.limit_plsql_trace(2000); |
| |
| PAUSE_PLSQL_TRACE |
| Pause tracing |
dbms_trace.pause_plsql_trace; |
exec dbms_trace.pause_plsql_trace; |
| |
| PLSQL_TRACE_VERSION |
| Gets the version number of the trace package |
dbms_trace.plsql_trace_version(
major OUT BINARY_INTEGER,
minor OUT BINARY_INTEGER); |
set serveroutput on
DECLARE
maj_ver PLS_INTEGER;
min_ver PLS_INTEGER;
BEGIN
dbms_trace.plsql_trace_version(maj_ver, min_ver);
dbms_output.put_line('Major Version is: ' ||
TO_CHAR(maj_ver) || ' and Minor Version is: ' || TO_CHAR(min_ver));
END;
/ |
| |
| RESUME_PLSQL_TRACE |
| Resume tracing |
dbms_trace.resume_plsql_trace; |
exec dbms_trace.resume_plsql_trace; |
| |
| SET_PLSQL_TRACE |
| dbms_trace.set_plsql_trace(trace_level IN BINARY_INTEGER); |
dbms_trace.set_plsql_trace(trace_level IN BINARY_INTEGER); |
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE FUNCTION getosuser
RETURN user_users.username%TYPE IS
-- explain use of %TYPE
vOSUser user_users.username%TYPE;
-- explain INTO and return
BEGIN
SELECT osuser
INTO vOSUser
FROM gv$session
WHERE sid = (
SELECT sid
FROM v$mystat
WHERE rownum = 1);
RETURN vOSUser;
EXCEPTION
WHEN OTHERS THEN
RETURN 'UNK';
END getosuser;
/
ALTER FUNCTION getosuser COMPILE DEBUG;
set serveroutput on
DECLARE
x VARCHAR2(30);
BEGIN
dbms_trace.set_plsql_trace(1);
SELECT getosuser
INTO x
FROM dual;
dbms_output.put_line(x);
dbms_trace.pause_plsql_trace;
END;
/
conn sys@pdbdev as sysdba
SELECT runid, run_date, run_owner
FROM plsql_trace_runs;
set linesize 121
col event_proc_name format a20
col module format a20
SELECT event_seq, stack_depth, module, proc_unit, proc_line
FROM plsql_trace_events;
SELECT module
FROM plsql_trace_events; |