Attemp to Rollback a Session from Another Session (Failed)

If you have a session locking a table and nobody is controlling the session (as in the case where the person running the SQL went to lunch), you can only kill that session from your end. Wouldn't it be nice to do something more than that such as commit or rollback his session remotely, or only KILL QUERY as MySQL does? Or even inject some other SQLs?[note1] Hacking or not is a different topic. But obviously only a user already having certain permission can do so. Therefore security is not a concern. Before anybody files a feature enhancement request with Oracle, here's one attempt, albeit a failed one, to achieve the goal, i.e. inject SQL on behalf of a victim session.

The test was originally done on Oracle 8.1.7.0.0 OPS Redhat Linux 6.2, kernel 2.2.14-5.0. (But OPS should be irrelevant.) I recently also tested it on Oracle 10.1.0.2.0 with no success.[note2] If you use Solaris, just replace strace with truss. Other OSes have similar tracing facilities.

Login sqlplus (without using SQL*Net) and find shadow process for this sqlplus:

select spid from v$process where addr = (select paddr from v$session where sid in (select sid from v$mystat));

Suppose spid is 1009. Also find sqlplus pid from OS. Suppose it's 1008. Then in another window:

$ ls -l /proc/1008/fd | grep pipe
l-wx------    1 oracle   dba            64 Jan  9 17:05 8 -> pipe:[1143]
lr-x------    1 oracle   dba            64 Jan  9 17:05 9 -> pipe:[1144]
$ ls -l /proc/1009/fd | grep pipe
l-wx------    1 oracle   dba            64 Jan  9 17:06 10 -> pipe:[1144]
lr-x------    1 oracle   dba            64 Jan  9 17:06 7 -> pipe:[1143]

That says sqlplus (pid 1008) writes on file descriptor 8 to shadow process (1009) file descriptor 7. Note their write-only and read-only permission, respectively. And their pipe number is the same (pipe:[1143]), meaning the two file descriptors represent the two ends of the same pipe. So attach strace to fd 7 of 1009 for read:

$ strace -xx -v -e read=7 -p 1009

Back to sqlplus. Type "rollback;". Then in the strace window, we see:

$ strace -xx -v -e read=7 -p 1009
...
read(7, "\x00\x95\x00\x00\x06\x00\x00\x00\x00\x00\x11\x69\x1a\x98"..., 2064) = 149
 | 00000  00 95 00 00 06 00 00 00  00 00 11 69 1a 98 95 0f  ........ ...i.... |
 | 00010  08 01 00 00 00 01 00 00  00 03 5e 1b 21 00 00 00  ........ ..^.!... |
 | 00020  00 00 00 00 d8 8d 0f 08  09 00 00 00 e0 3e 0e 08  ........ .....>.. |
 | 00030  0a 00 00 00 00 00 00 00  04 3f 0e 08 00 00 00 00  ........ .?...... |
 | 00040  01 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  ........ ........ |
 | 00050  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  ........ ........ |
 | 00060  06 3f 0e 08 72 6f 6c 6c  62 61 63 6b 0a 01 00 00  .?..roll back.... |
 | 00070  00 01 00 00 00 00 00 00  00 00 00 00 00 00 00 00  ........ ........ |
 | 00080  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  ........ ........ |
 | 00090  00 04 00 00 00                                    .....             |
...

The above strace() shows that the shadow process has read 149 bytes from sqlplus, including the "rollback" command. (Alternatively, you could capture the bytes sent to the shadow process by other means. For instance, use SQL*Net instead of local connection. Then you can enable admin level (level 16) SQL*Net tracing, or use snoop or snort to capture them.)

In sqlplus, make a transaction without commit:

create table t (a number);
insert into t values (1);

(Before doing the above, you can ^C in strace window so that window is cleaner.)

What we want to do is send those 149 bytes from another shell window to shadow (1009). We can do this by creating a Perl script[see Appendix] which outputs the same binary data to a file. Then write (inject) the bytes in the file to shadow process file descriptor 7:

perl InjectRollback.pl > InjectRollback.bytes
cat InjectRollback.bytes > /proc/1009/fd/7

At this point, I expect to see that the uncommitted "insert into t" was rolled back by the cat command in the new window. Unfortunately, back to the sqlplus window, I can't continue:

SQL> select * from t;
SP2-0642: SQL*Plus internal error state 2090, context 45:0:0
Unsafe to proceed
SQL> insert into t values (2);
SP2-0642: SQL*Plus internal error state 2091, context 0:0:0
Unsafe to proceed
SQL> select * from t;
SP2-0642: SQL*Plus internal error state 2090, context 2:0:0
Unsafe to proceed
SQL> select * from sys.t;
Memory fault (core dumped) 

The error descriptions are not very helpful to me:

$ oerr sp2 642
00642, 0, "SQL*Plus internal error state %lu, context %lu:%lu:%lu\n"
// *Cause:
// *Action:
$ oerr ora 2090
02090, 00000, "network error: attempted callback+passthru"
// *Cause: internal error.
//
$ oerr ora 2091
02091, 00000, "transaction rolled back"
// *Cause: Also see error 2092. If the transaction is aborted at a remote
//         site then you will only see 2091; if aborted at host then you will
//         see 2092 and 2091.
// *Action: Add rollback segment and retry the transaction.
$ oerr ora 2092
02092, 00000, "out of transaction table slots for distributed transaction"
// *Cause: The transaction is assigned to the system rollback segment and is
//         trying to get into the PREPARED state, but the required number
//         of non-PREPARED slots are not available, hence the transaction
//         is rolled back.
// *Action: Add a rollback segment and retry the transaction.

It looks like the bits and bytes received by a real sqlplus session can't be used exactly the same from a hacking session. Nevertheless, nice try. If somebody knows what each of the 149 bytes means, then we may be able to achieve our goal. Only Oracle knows these secrets. So asking them to add such feature won't add too much work on their part. If you do so, tell them other RDBMS's (such as Microsoft SQL) can only *kill* another session like Oracle does. Hopefully this will instigate Oracle to expedite this effort.

Yong Huang

__________________
Notes: If you repeat "rollback" or "ROLLBACK" commands in sqlplus, most bytes received by shadow are the same. Obviously the actual commands are different ("rollback" vs. "ROLLBACK"). But the 13th byte, among a few others, is definitely different; it seems to increment by an unknown count. The following is another ROLLBACK recorded in strace:
read(7, "\x00\x95\x00\x00\x06\x00\x00\x00\x00\x00\x11\x69\x27\xec"..., 2064) = 149
 | 00000  00 95 00 00 06 00 00 00  00 00 11 69 27 ec 7e 0d  ........ ...i'.~. |
 | 00010  08 01 00 00 00 01 00 00  00 03 5e 28 21 00 00 00  ........ ..^(!... |
 | 00020  00 00 00 00 58 36 0e 08  09 00 00 00 80 33 0f 08  ....X6.. .....3.. |
 | 00030  0a 00 00 00 00 00 00 00  a4 33 0f 08 00 00 00 00  ........ .3...... |
 | 00040  01 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  ........ ........ |
 | 00050  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  ........ ........ |
 | 00060  a6 33 0f 08 52 4f 4c 4c  42 41 43 4b 0a 01 00 00  .3..ROLL BACK.... |
 | 00070  00 01 00 00 00 00 00 00  00 00 00 00 00 00 00 00  ........ ........ |
 | 00080  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  ........ ........ |
 | 00090  00 04 00 00 00                                    .....             |



APPENDIX

The following is the Perl script InjectRollback.pl based on the first captured byte streams for "rollback" command:

#!/usr/bin/perl -w
print chr(hex("00")),chr(hex("95")),chr(hex("00")),chr(hex("00")),chr(hex("06")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("11")),chr(hex("69")),chr(hex("1a")),chr(hex("98")),chr(hex("95")),chr(hex("0f")),
chr(hex("08")),chr(hex("01")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("01")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("03")),chr(hex("5e")),chr(hex("1b")),chr(hex("21")),chr(hex("00")),chr(hex("00")),chr(hex("00")),
chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("d8")),chr(hex("8d")),chr(hex("0f")),chr(hex("08")),chr(hex("09")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("e0")),chr(hex("3e")),chr(hex("0e")),chr(hex("08")),
chr(hex("0a")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("04")),chr(hex("3f")),chr(hex("0e")),chr(hex("08")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),
chr(hex("01")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),
chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),
chr(hex("06")),chr(hex("3f")),chr(hex("0e")),chr(hex("08")),chr(hex("72")),chr(hex("6f")),chr(hex("6c")),chr(hex("6c")),chr(hex("62")),chr(hex("61")),chr(hex("63")),chr(hex("6b")),chr(hex("0a")),chr(hex("01")),chr(hex("00")),chr(hex("00")),
chr(hex("00")),chr(hex("01")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),
chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),chr(hex("00")),
chr(hex("00")),chr(hex("04")),chr(hex("00")),chr(hex("00")),chr(hex("00"));

________________
[note1] "SQL Injection" talked about here is a different concept from that
discussed by Pete Finnigan at
http://online.securityfocus.com/infocus/1644/
or Tom Kyte at
http://www.oracle.com/technology/oramag/oracle/05-jan/o15asktom.html
Their SQL injection is appending unintended SQL strings to an expected one,
much like one of the most popular Perl/CGI script hacking 10 years ago.

[note2] I got even less encouraging result in 10g. sqlplus showed just one error ORA-3113 when I
came back to sqlplus and typed "select * from t", after I injected the binary bytes on cmdline. The server
process immediately got killed leaving a trace in udump, showing:
FATAL ERROR IN TWO-TASK SERVER: error = 12592

oerr ora 12592 is
12592, 00000, "TNS:bad packet"
// *Cause: An ill-formed packet has been detected by the TNS software.
// *Action: For further details, turn on tracing and reexecute the
// operation. If error persists, contact Oracle Customer Support.

The trace file also has call stack:
ksedmp()+493
opitsk()+6015
opiino()+1070
opiodr()+1598
opidrv()+550
sou2o()+103
main()+225
__libc_start_main()