首页 > 技术点滴 > Repost: Oracle Protocol

Repost: Oracle Protocol

2013年12月10日 baoz 阅读评论

Oct 23, 2012 / By Gwen Shapira

Tags: ,

On 18th May 2003, a man named Ian Redfern published a paper documenting the  TNS protocol internals. The paper, entitled Oracle Protocol, is clearly the result of painstaking research.

Over the years, the paper became a classic in our field. It is widely referenced by security professionals and performance-monitoring experts, both of whom need to perform detailed analysis of the data Oracle communicates over the network.

The original paper, however, became nearly impossible to find. It seemed to have only been published on the ukcert website, and after it was removed from their servers, the only place to find it was web.archive.org. Web Archive is wonderful, but it is a very unreliable way to preserve one of the most important papers published in our field.

Fortunately, Ian Redfern released his paper into the public domain. I can now reproduce it here in full to prevent it from disappearing forever:

 

Oracle Protocol

This document is an attempt to document the network protocol used by Oracle
database clients to communicate with Oracle database servers in order to allow
developers to decode this traffic and construct new, interoperable client and
server software.

The network protocol is known variously as SQL*Net, Net8, TNS and TTC7 – I
shall refer to it as Net8. It can be run over a number of transports, but I
shall only discuss the TCP/IP variant. I believe the details are valid for all
Oracle versions since Oracle 7.2

Basics

All Net8 traffic goes over an ordinary TCP connection to port 1521 on the
server, although this can be overridden. After logging in, multiple transactions
are carried over the connection until it is closed after logout.

Every packet begins with a length, a checksum, a type and a flags byte. Like
all Net8 integers, these are Big-Endian. The maximum length of a packet is the
SDU (Session Data Unit), which is at most 4086 bytes. By default the SDU is 4086
and the TDU (Transport Data Unit) is 32767 (also its maximum) – the TDU is never
smaller than the SDU.

Possible packet types are:

Packet type Meaning
1 Connect
2 Accept
3 Acknowledge
4 Refuse
5 Redirect
6 Data
7 Null
9 Abort
11 Resend
12 Marker
13 Attention
14 Control Information

The checksum is either the ones complement of the sum of the packet header or
whole packet (like an IP checksum) or – in reality – zero.

Connect

A Connect packet is of type 1. Its length is 34 unless there is connection
data. Connection data is a string of the form
(SOURCE_ROUTE=yes)(HOP_COUNT=0)(CONNECT_DATA=((SID=)CID=(PROGRAM=)(HOST=)(USER=)))
or similar.

If the connection data is longer than 221 bytes, it is carried immediately
after the CONNECT packet and the CONNECT packet length is 34 bytes, as if there
were no connection data.

Sample connection data:

The response to this is a packet of type Accept (2), Redirect (5) or Refuse
(4). A Redirect carries a new set of connection information as its payload.
Resend (11) should be ignored.

Accept

An Accept packet has the following form:

Refuse

A Refuse packet has the following form:

Data

All traffic after the initial handshake is sent as Data packets, with the
exception of Marker packets, used for interruption.

A Data packet has the following form:

The connection is terminated by a Data packet with DataFlags = 0×0040:

Additional Network Option Negotiation

After the Accept packet is received, there may be an optional ANO
negotiation, where the client and server indicate which ANO drivers they want to
use.

Potential data types are String(0), UB2Array(1), UB1(2), UB2(3), UB4(4),
Version(5), Status(6)

The response is:

It should be acceptable to use these canned packets for negotiations – they
simply disable all ANO facilities.

Types and marshalling

This is not a true self-descriptive mechanism like ASN.1 or XML, but it does
deal with variable-length binary data, and so it has a marshalling mechanism for
doing so.

There are four native types: B1, B2, B4 and PTR. Each one can be shipped as
native, universal, LSB or (universal and LSB). Native values are big-endian,
universal ones are length-byte-preceeded and LSB ones are little-endian.

By default, B1 types (signed and unsigned bytes) are native, B2, B4 and PTR
are universal. Universal types are a length followed by the non-zero bytes of
data, so 0 is represented as just as zero byte. Negative values are indicated by
setting the high bit of the length.

The following types fit into this scheme:

  • UB1, unsigned byte length 1 (B1)
  • SB1, signed byte length 1, never negative, B1
  • UB2, unsigned byte length 2 (B2)
  • SB2, signed byte length 2 (B2)
  • UB4, unsigned byte length 4 (B4)
  • SB4, signed byte length 4 (B4)
  • UWORD, unsigned word length 4 (B4)
  • SWORD, signed word length 4 (B4)
  • RefCusror, signed word length 4 (B4)
  • B1Array, array of B1, written as native
  • UB4Array, array of UB4, written as multiple UB4s
  • Ptr, pointer, byte 0 if null, otherwise byte 1
  • O2U, boolean, byte 0 if false, byte 1 if true
  • NULLPTR, byte 0
  • PTR, byte 1
  • CHR, character array, written as native or CLR if conversion
  • CLR, byte array
  • DALC, byte array, either 0 (if null/empty) or SB4 length followed by CLR
  • UCS2, single unicode character
  • TEXT, 0-terminated array of B1

A CLR is a byte array in 64-byte blocks. If its length <=64, it is just
length-byte-preceeded and written as native. Null arrays can be written as the
single bytes 0×0 or 0xff. If length >64, first a LNG byte (0xfe) is written,
then the array is written in length-byte-preceeded chunks of 64 bytes (although
the final chunk can be shorter), followed by a 0 byte. A chunk preceeded by a
length of 0xfe is ignored.

A UCS2 character is (if B2 is universal, as is usual) prefixed by a byte of 1
or 2. The character then follows in one or two bytes, reversed if B2 is LSB
(which it usually isn’t).

In this document I will not mark B1 types as they are always raw bytes.

Logon

First we get the v8 TTI protocol negotiation. The client passes in its client
type and a list of versions – presumably those it is compatible with. The TTI7
client handles up to version 4, sqlplus up to 5 and the JDBC client up to 6.

I shall document the latest protcol, version 6, as used by the JDBC client,
as it is the current version.

The response is:

If Version >= 6, this is followed by two length-byte-preceeded byte
arrays.

The next stage sorts out any differences in type representation:

If the server version is at least 6, this is foll,owed by two more
length-byte-preceeded byte arrays, CTcap (17×0, 3, 0, 0, 0) and RTcap (2).

The response is:

Authentication step

The response is:

Standard error

An error object has the following structure:

The authentication step is now repeated, but this time with the password
encrypted with the session key.

The response is:

It is possible to receive a warning:
0f Warning UB2 return code UB2
warning flag CLR [warning message if return code != 0]

Version

The client then asks the server for its version details:

The response is:

Auto-commit

The next thing the driver does is set auto-commit to ‘on’:

The response is:

At this point, the client may send the following SQL:

We have now logged in.

Password algorithm

The Oracle password encryption mechanism is based on DES, and uses a random
challenge from the server which the client must encrypt. The algorithm is quite
complex, and is most easily described in the attached Perl source
– you will need Crypt::DES and Crypt::CBC to use it.
There is now also a C version, orapasswd.c
by Xue Yong Zhi, which requires OpenSSL.

SQL

First, it is necessary to open a statement:

The response is:

Here is a SQL query:

Bitmap for options:

1 PARSE
8 BIND
16 DEFINE
32 EXECUTE
64 FETCH
128 CANCEL
256 COMMIT
512 EXACTFE
1024 SNDIOV
32768 NOPLSQL

The value of options is constructed in the following manner:
If call is
parse_execute, options = NOPLSQL|EXECUTE|PARSE. If call is fetch, options =
NOPLSQL|FETCH.

If call is execute_fetch, check the SQL. If select or with, options =
NOPLSQL|EXECUTE_FETCH. If begin, call or declare, options = SNDIOV|EXECUTE (if
binds depth nonzero) otherwise 32. If insert, delete, update or other, options =
NOPLSQL|EXECUTE. If call is parse_execute_fetch, options is same as
execute_fetch plus PARSE.

In all cases, if binds depth is nonzero, set the BIND bit, and if defines
depth is nonzero, set the DEFINE bit.

inVector[0] is always 1. inVector[1] is 1 if not NOPLSQL. If (EXECUTE and not
FETCH), inVector[1] = binds depth (or 1 if binds depth is 0). If FETCH,
inVector[1] = defines depth.

The response is:

If there are binds or defines, these are now marshalled and sent to the
server. I shall describe these at a later date.

This is followed by a version 8 array request (for Oracle 8.0.3 and above) to
describe the columns coming back:

The response is:

There follows a series of UDSes, each of which describes a column. Each UDS
begins with a v7OAC:

If v7oacdty==NUMBER, v7oacmxl=22. If v7oacdty==DATE, v7oacmxl=7. If
v7oacdty==TIMESTAMPTZ, v7oacmxl=13.

This is followed by the rest of a v8oac:
00 DALC v8oactoid 00 UB2 v8oacvsn
00 UB2 v8ncs (charset) 00 v8FormOfUse

We then get the rest of the UDS:

The next UDS looks like:

These continue until:

This is then followed by an oer:

Valid database types are:

Type # Type
1 VARCHAR
2 NUMBER
6 VARNUM
8 LONG
11 ROWID (deprecated), equiv to 104
12 DATE
23 RAW
24 LONG_RAW
96 CHAR
102 RESULT_SET
104 ROWID
109 NAMED_TYPE
111 REF_TYPE
112 CLOB
113 BLOB
114 BFILE
180 TIMESTAMP
181 TIMESTAMPTZ
182 INTERVALYM
183 INTERVALDS
231 TIMESTAMPLTZ
998 PLSQL_INDEX_TABLE
999 FIXED_CHAR

After the execute follows the fetch:

This is followed by all the column definitions as oacs:

Here, note that if oacdty==CHAR, oacflag=33. If oacdty==FIXED_CHAR, instead
use CHAR. If oadty==ROWID (either 11 or 104), use VARCHAR. If oacdty==RESULTSET,
oacmxl=1.

The reply to this is the rows:

This continues over many packets until all the columns and rows are
returned.

We end a query result set with a cancel:

The response is:

The response is:


Release 0.9 – 18th May 2003Ian Redfern
(Ian.Redfern@LogicaCMG.com)

This document and its accompanying source code samples are in
the public domain, and you may do anything with them that you
wish. The author takes no responsibility for the accuracy of their
contents. Some of the terms in this document are trademarks of Oracle
and other companies. No trade secrets or other privileged information
has been used in its compilation, and the author has no relationship
with Oracle.

  1. 本文目前尚无任何评论.