| 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 was deprecated decades ago but, likely for reasons of backward compatibility, is still present in the Oracle Database.
Use of this package is a low risk but it is still a risk so we recommend dropping the package if it is found in a database and the CREATE DATABASE statement, whether executed at the command line, by OUI, or by DBCA, does not create it. |
| |
| Recommended Security Rules |
NEVER
- Grant execute on this package to anyone.
WITH GREAT CARE
- Address the root reason why access to this package was requested or why it may have been used.
CAUTIONS
- Anyone using this package may be trying to slip under the radar and not have their activities detected. Follow-up is required if use is detected.
|
| |
| 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.
The package has only the most basic capabilities but it doesn't do anything significantly different than what can be done in SQL*Plus and is more secure in that the package is owned by SYS and no privileges are granted. |
| |
| DBMS_SUPPORT Information |
| AUTHID |
DEFINER |
| Dependencies |
| DBMS_STANDARD |
V$MYSTAT |
V$SESSION |
| DBMS_SYSTEM |
|
|
|
| Documented |
No |
| First Available |
7.2 |
| Installation |
conn / as sysdba
@?\rdbms\admin\dbmssupp.sql
SQL> @?\rdbms\admin\dbmssupp.sql
SP2-0808: Package created with compilation warnings
SP2-0810: Package Body created with compilation warnings
GRANT execute ON dbms_support TO uwclass;
CREATE PUBLIC SYNONYM dbms_support FOR dbms_support; |
| Security Model |
Owned by SYS with no privileges granted |
| Source |
{$ORACLE_HOME}/rdbms/admin/dbmssupp.sql |
| Subprograms |
|
| |
| MYSID |
| Return Session SID |
dbms_support.mysid RETURN NUMBER; |
SELECT sid FROM v$mystat WHERE rownum = 1;
SELECT SYS_CONTEXT('USERENV', 'SID') FROM dual;
SELECT dbms_support.mysid FROM dual; |
| |
| PACKAGE_VERSION |
| Returns the minimum Oracle version of PL/SQL supported |
dbms_support.package_version RETURN VARCHAR2; |
SELECT dbms_support.package_version
FROM dual; |
| |
| START_TRACE |
| Starts Tracing In The Current Session |
dbms_support.start_trace(
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE); |
DECLARE
w BOOLEAN := TRUE;
b BOOLEAN := TRUE;
BEGIN
dbms_support.start_trace(w,b);
END;
/ |
| |
| START_TRACE_IN_SESSION |
| Starts tracing in the specified session |
dbms_support.start_trace_in_session (
sid IN NUMBER,
serial IN NUMBER,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE); |
DECLARE
s gv$session.sid%TYPE;
r gv$session.serial#%TYPE;
w BOOLEAN := TRUE;
b BOOLEAN := TRUE;
BEGIN
SELECT sid, serial#
INTO s, r
FROM gv$session
WHERE username = 'UWCLASS';
dbms_support.start_trace_in_session(s, r, w, b);
dbms_lock.sleep(100);
dbms_support.stop_trace_in_session(s, r);
END;
/ |
| |
| STOP_TRACE |
| Stops tracing |
dbms_support.stop_trace; |
exec dbms_support.stop_trace; |
| |
| STOP_TRACE_IN_SESSION |
| Stops tracing within a specified session |
dbms_support.stop_trace_in_session(
id IN NUMBER,
serial IN NUMBER); |
| See START_TRACE_IN_SESSION Demo Above |