In the last post we did our tests as a user ORABLOG and the log table SPERRORLOG is there in the ORABLOG schema. As it is in this schema how did Oracle know we needed this log table?
The table gets created first time we use SET ERRORLOGGING ON so each schema has its own table. Can we prove this?
First connect as SYS to create a sample user with just CREATE SESSION:
C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 3 14:20:18 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL>
We are connected to 23c Free and using an 11.2.0.4 client as thats what I happen to have around but its fine as errorlogging was added in Oracle 11.1 so the client supports it. Next create a sample user with just CREATE SESSION:
SQL> create user test33 identified by test33;
User created.
SQL> grant create session to test33;
Grant succeeded.
SQL>
Check what, if any SPERRORLOG tables exist:
SQL> col owner for a15
SQL> col object_name for a10
SQL> col object_type for a10
SQL> set lines 220
SQL> select owner,object_name,object_type from dba_objects where object_name='SPERRORLOG';
OWNER OBJECT_NAM OBJECT_TYP
--------------- ---------- ----------
ORABLOG SPERRORLOG TABLE
SQL>
So, no table exists for the user TEST33. What if we log in as TEST33, is the table created? Connect as TEST33 and then back as SYS and check
SQL> connect test33/test33@//192.168.56.18:1521/freepdb1
Connected.
SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> col owner for a15
SQL> col object_name for a10
SQL> col object_type for a10
SQL> set lines 220
SQL> select owner,object_name,object_type from dba_objects where object_name='SPERRORLOG';
OWNER OBJECT_NAM OBJECT_TYP
--------------- ---------- ----------
ORABLOG SPERRORLOG TABLE
SQL>
No, connecting didnt create the log table. The next test is to connect as TEST33 and enable logging, log back as SYS and check:
SQL> connect test33/test33@//192.168.56.18:1521/freepdb1
Connected.
SQL> set errorlogging on
ERROR:
ORA-01031: insufficient privileges
ERROR:
ORA-00942: table or view does not exist
SP2-1518: Errorlogging table SPERRORLOG does not exist in schema
SP2-1507: Errorlogging table, role or privilege is missing or not accessible
SQL>
SQL> desc sperrorlog
ERROR:
ORA-04043: Object sperrorlog does not exist.
Interesting, very interesting. Why was there no error for ORABLOG? Lets check out ORABLOG rights:
C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 3 15:00:59 2023
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> @find_all_privs
find_all_privs: Release 1.0.7.0.0 - Production on Fri Nov 03 15:00:06 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.
NAME OF USER TO CHECK [ORCL]: ORABLOG
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
User => ORABLOG has been granted the following privileges
====================================================================
SYS PRIV => CREATE PROCEDURE grantable => NO
SYS PRIV => CREATE SESSION grantable => NO
SYS PRIV => CREATE TABLE grantable => NO
SYS PRIV => UNLIMITED TABLESPACE grantable => NO
PL/SQL procedure successfully completed.
For updates please visit http://www.petefinnigan.com/tools.htm
SQL>
The clues were there in the error messages; The user who wants to use error logging needs CREATE TABLE privilege. This is good as otherwise the creation of the table would have to be done by another user and we dont have one in this context. Lets add CREATE TABLE to our user TEST33 and try again:
SQL> sho user
USER is "SYS"
SQL> grant create table to test33;
Grant succeeded.
SQL>
SQL> col owner for a15
SQL> col object_name for a10
SQL> col object_type for a10
SQL> set lines 220
SQL> select owner,object_name,object_type from dba_objects where object_name='SPERRORLOG';
OWNER OBJECT_NAM OBJECT_TYP
--------------- ---------- ----------
ORABLOG SPERRORLOG TABLE
1 row selected.
SQL>
Now connect to TEST33 and try and set errorlogging again
SQL> connect test33/test33@//192.168.56.18:1521/freepdb1
Connected.
SQL> set errorlogging on
SQL> desc sperrorlog
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------
USERNAME VARCHAR2(256)
TIMESTAMP TIMESTAMP(6)
SCRIPT CLOB
IDENTIFIER VARCHAR2(256)
MESSAGE CLOB
STATEMENT CLOB
SQL>
So, for completeness connect as SYS and check the SPERRORLOG tables
SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> col owner for a15
SQL> col object_name for a10
SQL> col object_type for a10
SQL> set lines 220
SQL> select owner,object_name,object_type from dba_objects where object_name='SPERRORLOG';
OWNER OBJECT_NAM OBJECT_TYP
--------------- ---------- ----------
ORABLOG SPERRORLOG TABLE
TEST33 SPERRORLOG TABLE
2 rows selected.
SQL>
The security is a little askew as we need to grant CREATE TABLE to any users where we want to log any errors. This is a privilege that is potentially not needed after the creation of the table. There are two options.
- We could grant CREATE TABLE, run SET ERRORLOGGING ON once and then revoke CREATE TABLE from the user
- We could pre-create the table for the user from a DBA and then run SET ERRORLOGGING ON
We could create a table with a different name and access this with SET ERRORLOGGON ON TABLE {my_table_name} - For this option we need to create the table with the same structure as SPERRORLOG.
For security we must pre-create the SPERRORLOG table or use a different name or REVOKE CREATE TABLE from the schema as soon as the logging has been used once. Pre-creation as a DBA is the better option as then we do not need to grant CREATE TABLE to the schema/user
There is another aspect to this; if the user has CREATE TABLE then SET ERRORLOGGING ON will just work. BUT the user would have a table he/she didnt create themselves directly. I like the idea of storing the errors for use in security auditing or forensics but I dont like the idea that a system creates objects silently for you.
#oracleace #23c #oracle #database #security #logging #error
