Pete Finnigan's Oracle Security Weblog

Oracle has improved error messaging in a number of places over the years and we will discuss one of these now in this blog. Oracle added logging errors to a table in Oracle 11.1. This is a useful feature that I don't see used on sites I visit. This feature allows errors from interactive commands or scripted SQL and PL/SQL to be logged. So if something runs unattended or the screen buffer is too small you can still see the errors that were made.

First lets connect to my 23c Free database (XE) and have a look at it:

C:\>sqlplus orablog/orablog@//192.168.56.18:1521/freepdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 3 10:21:32 2023
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Fri Nov 03 2023 09:02:32 +00:00

Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

SQL>

The feature is turned off by default. The error logs are written to a table called SPERRORLOG. This is created by default. The table structure is:

SQL> desc sperrorlog
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                           VARCHAR2(256)
 TIMESTAMP                                          TIMESTAMP(6)
 SCRIPT                                             CLOB
 IDENTIFIER                                         VARCHAR2(256)
 MESSAGE                                            CLOB
 STATEMENT                                          CLOB

SQL>

Check if there are any error messages already in the table:

SQL> select count(*) from sperrorlog;

  COUNT(*)
----------
         0

SQL>

Where is this table and who owns it?

SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL>
SQL> select owner,object_name,object_type from dba_objects where object_name='SPERRORLOG';

OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
ORABLOG
SPERRORLOG
TABLE


1 row selected.

SQL>

So, the table is owned by ORABLOG, the user I was connected to at the start. This table is empty until we turn on error logging. Lets do that and run some errors deliberately without error logging:

SQL> connect orablog/orablog@//192.168.56.18:1521/freepdb1
Connected.
SQL>
SQL> alter user system identified by oracle;
alter user system identified by oracle
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select * from scott.emp;
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create or replace procedure test22 is
  2  negin
  3  null;
  4  end;
  5  /

Warning: Procedure created with compilation errors.

SQL>

We tried to use a system privilege we do not have and also access a table that does not exist and finally try and compile a PL/SQL procedure that has bugs. Check the error log:

SQL> select count(*) from sperrorlog;

  COUNT(*)
----------
         0

SQL>

Turn on error logging:

SQL> set errorlogging on

Now run the commands again:

SQL> alter user system identified by oracle;
alter user system identified by oracle
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select * from scott.emp;
select * from scott.emp
                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create or replace procedure test22 is
  2  negin
  3  null;
  4  end;
  5  /

Warning: Procedure created with compilation errors.

SQL>

Check the error log again:

SQL> set serveroutput on
SQL> @sc_print 'select * from sperrorlog'
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from sperrorlog','''','''''');
Executing Query [select * from sperrorlog]
USERNAME                      : ORABLOG
TIMESTAMP                     : 03-NOV-23 11.50.36.000000 AM
SCRIPT                        :
IDENTIFIER                    :
MESSAGE                       : ORA-01031: insufficient privileges
STATEMENT                     : alter user system
-------------------------------------------
USERNAME                      : ORABLOG
TIMESTAMP                     : 03-NOV-23 11.50.37.000000 AM
SCRIPT                        :
IDENTIFIER                    :
MESSAGE                       : ORA-00942: table or view does not exist
STATEMENT                     : select * from scott.emp
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

This is interesting as the PL/SQL error is not included in the error log. BUT, we can see the compile time errors at the time by running "sho err" and listing the source to compare the line number in the error and the original source. We can also see the PL/SQL error as follows:

SQL> @sc_print 'select * from user_errors'
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from user_errors','''','''''');
Executing Query [select * from user_errors]
NAME                          : TEST22
TYPE                          : PROCEDURE
SEQUENCE                      : 1
LINE                          : 3
POSITION                      : 1
TEXT                          : PLS-00103: Encountered the symbol "NULL" when
expecting one of the following:

   constant exception 
   double-quoted delimited-identifier> table columns long
   double ref char
standard time timestamp interval date binary
   national character nchar
ora_property_graph
   ora_vertex_input_property ora_vertex_output_property

ora_edge_input_property ora_edge_output_property

ATTRIBUTE                     : ERROR
MESSAGE_NUMBER                : 103
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

So, we can log errors from SQL*Plus from DDL and DML and also PL/SQL. Good. We can also set the identifier in the session or in a script so that this is also recorded in SPERRORLOG so that we can tell where errors come from. So lets set an identifier and run an error again:

SQL> set errorlogging on identifier pete_id
SQL> alter user system identified by oracle;
alter user system identified by oracle
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> select count(*) from sperrorlog;

  COUNT(*)
----------
         1

SQL> set serveroutput on
SQL> @sc_print 'select * from sperrorlog'
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from sperrorlog','''','''''');
Executing Query [select * from sperrorlog]
USERNAME                      : ORABLOG
TIMESTAMP                     : 03-NOV-23 12.11.36.000000 PM
SCRIPT                        :
IDENTIFIER                    : pete_id
MESSAGE                       : ORA-01031: insufficient privileges
STATEMENT                     : alter user system
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

Great, we get the error message and we can see the ID that we set is also now there.

OK, I am a security person so why the interest in this error table?

It is also possible to create your own error table first and then use this in the "set errorlogging" functionality. The table must pre-exist before the command is used.

If I am doing a forensic analysis of a database I look for these tables and include the contents in the analysis; if the tables exist of course and also USER_ERRORS. It makes sense to do this for completeness. Also from a security perspective (not forensics) then we would not want this data to exist. I connected to my ORABLOG schema and if I were able to exploit my ORABLOG schema say with SQL Injection then I can read this table and also USER_ERRORS to get insight to the actions occurred in the database that may give me an insight to attack the database.

Error logging is good and useful but it should not be exposed back to the real end user who could be an attacker

#oracleace #23c #oracle #security #error #message #attack #databreach