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()