| Security Advisory |
This package is one of our favorites. It does something really important, introduces almost no risk to the system, and Oracle seems to have taken great care in getting the security model correct.
We're not sure why this is an undocumented unsupported package but it is, no one except SYS, by default, can access it, and yet the actions it performs, writing to the Database Alert Log seem relatively safe. |
| |
| Recommended Security Rules |
NEVER
- Introduce this into a production application where its failure, or a change introduced by Oracle, could result in a loss of service.
WITH GREAT CARE
- Grant EXECUTE to applications that monitor security or create warnings and exceptions that you want to persist in the Alert Log.
CAUTIONS
- This package is undocumented and officially unsupported so Oracle reserves to itself the right to make changes without notice.
One example of this was when Oracle removed this functionality from DBMS_SYSTEM where it was prior to version 12.1 and put it into its own package.
|
| |
| How Oracle Works |
| The code examples below are sufficient to demonstrate real-world use of this package. |
| How DBMS_LOG can be used in a positive way to
improve a database environment by logging maintenance related activities |
Coming in the next update
CREATE OR REPLACE TRIGGER ddl_trap
AFTER DDL
ON DATABASE
DECLARE
sql_text ora_name_list_t;
n NUMBER;
x VARCHAR2(30);
BEGIN
n := ora_sql_txt(sql_text);
x := ora_login_user;
dbms_log.ksdfls;
dbms_log.ksdwrt(2, 'DDL: ' || sql_text(1));
dbms_log.ksdwrt(2, 'By: ' || x);
END ddl_trap;
/
Log into the database as a user and perform a bit of maintenance.
SQL> conn uwclass/uwclass
SQL> CREATE TABLE zzyzx (testcol DATE);
Table created.
SQL> ALTER TABLE zzyzx ADD (newcol INTEGER);
Table altered.
SQL> DROP TABLE zzyzx PURGE;
Table dropped.
-- here is what is written to the alert log
2019-07-11T16:56:49.744662-05:00
DDL: CREATE TABLE zzyzx (testcol DATE)
By: C##UWCLASS
2019-07-11T16:57:15.269619-05:00
DDL: ALTER TABLE zzyzx ADD (newcol INTEGER)
By: C##UWCLASS
2019-07-11T16:57:24.439400-05:00
DDL: DROP TABLE zzyzx PURGE
By: C##UWCLASS
What makes this technique more valuable than traditional auditing, is the incredible granularity. every system event function written by Oracle can be used including SYS_CONTEXT plus you can write your own.
The most powerful audit logs are those that 90% needles ... not 99.99% irrelevancies. |
| How DBMS_LOG could be used to generated a near endless stream of problems and outages. |
There are two possible exploits of this package that should be considered although there are so many far better tools,
with EXECUTE granted to PUBLIC, that it seems unlikely anyone would use DBMS_LOG for such purposes.
The first potential exploit would be to write massive amounts of data to the Alert Log creating a DDOS attack either through the resources consumed or by filling available space and causing a hang.
We can think of far easier ways to accomplish either goal so rate this low risk.
Of far higher risk, but again not guaranteed to produce damage would be to put messages into the Alert Log that would lead a reasonable DBA to take an action. The following code demonstrates how it might work:
SQL> conn / as sysdba
BEGIN
dbms_log.ksdfls;
dbms_log.ksdddt; -- print the current date-time to make this look official
dbms_log.ksdwrt(2, 'ORA-00600: do not look, something really bad just happened');
dbms_log.ksdwrt(2, 'ORA-00911: open a Sev 4 service request at MyOracleSupport');
dbms_log.ksdwrt(2, 'ORA-07445: drink
beer until Oracle
responds');
dbms_log.ksdwrt(2, 'ORA-07446:
after the 10th beer run the following SQL statement');
dbms_log.ksdwrt(2, 'ORA-07447:
DROP USER SYSTEM CASCADE;');
END write_to_log;
/
The output will look like this:
2019-06-01T22:16:50.486716-05:00
Errors in file /u01/orabaseapps19/diag/rdbms/orabase/orabase/trace/orabase_j000_3392.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_7855"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 49538
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 881
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21631
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 23763
ORA-06512: at "SYS.DBMS_STATS", line 49526
2019-06-01T22:17:22.693591-05:00
ORA-00600: do not look, something really bad just happened
ORA-00911: open a Sev 4 service request at MyOracleSupport
ORA-07445: drink beer until Oracle responds
ORA-07446: after the 10th beer run the following SQL statement
ORA-07447: DROP USER SYSTEM CASCADE;
If found in an alert log consider the impact it might have. The world isn't ready for happy DBAs.
Neither is it ready for the serious damage a well crafted message could
initiate.
All jokes aside, could someone plant a message in the alert log that might lead to something bad happening? Something to keep in mind if you choose to grant EXECUTE on this package to a user.
Note: I categorically deny ever using this for an April Fools prank.
"I didn't do it, nobody saw me do it, you can prove anything!" |
| |
| DBMS_LOG Package Information |
| AUTHID |
DEFINER |
| Dependencies |
|
| Documented |
No |
| First Available |
12.1 |
| Security Model |
Owned by SYS with no privileges granted |
| Source |
{ORACLE_HOME}/rdbms/admin/prvtlog.plb |
| Subprograms |
|
| |
| KSDDDT |
| Prints the date stamp to the target file (alert log and/or trace file) |
dbms_log.ksdddt; |
exec dbms_log.ksdddt; |
| |
| KSDFLS |
| Flushes any pending output to the target alert log or trace file |
dbms_log.ksdfls; |
exec dbms_log.ksdfls; |
| |
| KSDIND |
| Indents before the next write (ksdwrt) by printing that many colons (:) before the next write |
dbms_log.ksdind(lvl IN BINARY_INTEGER);
Range of valid values from 0 to 30. |
exec dbms_log.ksdind(5);
exec dbms_log.ksdwrt(3, 'Test Message'); |
| |
| KSDWRT |
| Prints a message to the target file (alert log and/or trace file) |
dbms_log.ksdwrt (dest IN BINARY_INTEGER, tst IN VARCHAR2);
1: Write to the standard trace file
2: Write to the alert log
3: Write to both files at once |
exec dbms_system.ksdwrt(3, '-- Start Message --');
exec dbms_system.ksdwrt(3, 'Test Message');
exec dbms_system.ksdwrt(dbms_system.alert_file, '-- End Message --'); |