Database Reference
In-Depth Information
Can we audit
-prelim
connections another way? Yes—using syslog, of course. First, let's try to connect using
the -
prelim
option:
C:\Windows\System32>sqlplus
-prelim
sys/a@192.168.1.3/pdborcl as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Sep 10 18:04:46 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-24542: PRELIM mode logon not allowed to a pluggable database
Sep 10 18:03:41 orlin Oracle Audit[19670]: LENGTH : '169' ACTION :[7] 'CONNECT' DATABASE USER:[3]
'sys'
PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[7] 'abfb378' CLIENT TERMINAL:[13] 'SHOPBUILD6621'
STATUS:[1] '0' DBID:[9] '751089987'
C:\Windows\System32>sqlplus sys/a@192.168.1.3/orcl3 as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Sep 10 18:06:04 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Sep 10 18:03:58 orlin Oracle Audit[19675]: LENGTH : '169' ACTION :[7] 'CONNECT' DATABASE USER:[3]
'SYS'
PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[7] 'abfb378' CLIENT TERMINAL:[13] 'SHOPBUILD6621'
STATUS:[1] '0' DBID:[9] '751089987'
What we notice from the above is that the connection as
-prelim
has “
sys
” in lower case, whereas without
-prelim
it is uppercase “
SYS
. ” The
-prelim
option appears to be causing the case of the logon command to be
preserved, for some reason. Normally DBAs will stick to lower case, as it is quicker to type. This can be a useful
signature, because
-prelim
provides a powerful ability to connect to the database to use oradebug, but bypassing
triggers. If you see lowercase
"sys"
connections, then those could be
-prelim
connections, though this is not a hard
and fast observation.
Another way to identify oradebug usage is to examine the automatically created trace files on the OS. This
example shows oradebug being used and then the log file that is created, thus showing that usage:
SQL> oradebug setmypid
Statement processed.
SQL> ORADEBUG TRACEFILE_NAME
/home/oracle/app/oracle2/diag/rdbms/orcl3/orcl3/trace/orcl3_ora_22747.trc
[oracle@orlin shell]$ ls -shalt /home/oracle/app/oracle2/diag/rdbms/orcl3/orcl3/trace/orcl3_
ora_22747.trc
8.0K -rw-r-----. 1 oracle dba 5.3K Sep 10 21:44 /home/oracle/app/oracle2/diag/rdbms/orcl3/orcl3/
trace/orcl3_ora_22747.trc
But the log file can be deleted by the oradebug user as follows on 11g by using tabs instead of spaces in the
quoted system command below:
SQL> oradebug call system "rm home/oracle/app/oracle2/diag/rdbms/orcl3/orcl3/trace/orcl3_ora_22747.trc"
On 12c though, oradebug resists with this interesting new error message:
ORA-32519: insufficient privileges to execute ORADEBUG command: OS debugger privileges required
for client
