Miladin Modrakovic's Blog: Oraclue

For this exercise I will use Oracle Advanced Security Option ( ASO) with following parameters:

SQLNET.ORA file:

/* ASO Encryption */

SQLNET.CRYPTO_SEED = “fsdfsdfsdfsdfsdfsdfsdfsdfwrewrwerwerwesdfsdfsdfsd”
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (RC4_256)
SQLNET.ENCRYPTION_TYPES_CLIENT = (RC4_256)

/* ASO Checksumm */

SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (MD5)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (MD5)

* Tracing */

TRACE_LEVEL_CLIENT = 16
TRACE_UNIQUE_CLIENT=TRUE
TRACE_TIMESTAMP_CLIENT=TRUE
TRACE_FILE_CLIENT = sqlnet
TRACE_DIRECTORY_CLIENT =C:\TRACE

My sqlnet.ora file has parameters related to encryption, checksum and client tracing.

Using sqlplus I will log into database:

C:\>sqlplus demo@demo

SQL> /

SPID   SID  USERNAME
—————-
28079   134 DEMO

quick check for this session

set linesize 145
col network_service_banner format a110
select sid,network_service_banner  from v$session_connect_info
where sid=134

SID NETWORK_SERVICE_BANNER
———- ————————————————————————————————————–
134 TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 – Production
134 Oracle Advanced Security: encryption service for Linux: Version 11.1.0.7.0 – Production
134 Oracle Advanced Security: RC4_256 encryption service adapter for Linux: Version 11.1.0.7.0 – Produc
134 Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.1.0.7.0 – Production
134 Oracle Advanced Security: MD5 crypto-checksumming service adapter

5 rows selected.

and from session SID 134 I will run alter statement:

SQL> alter user demo identified by demo;

Looking at  sqlnet_3872.trc file I can see      that traffic is encrypted.

25-JUN-2010 13:26:33:091] nsdo: nsctxrnk=0
[25-JUN-2010 13:26:33:091] nsdo: normal exit
[25-JUN-2010 13:26:33:091] nsfull_pkt_rcv: entry
[25-JUN-2010 13:26:33:091] nsfull_pkt_rcv: cid=0, opcode=85, *bl=0, *what=0, uflgs=0×0, cflgs=0×0
[25-JUN-2010 13:26:33:091] nsfull_pkt_rcv: rank=64, nsctxrnk=0
[25-JUN-2010 13:26:33:091] nsfull_pkt_rcv: nsctx: state=8, flg=0x400d, mvd=0
[25-JUN-2010 13:26:33:091] nsfull_pkt_rcv: gtn=367, gtc=367, ptn=10, ptc=8137
[25-JUN-2010 13:26:33:091] nsfull_pkt_rcv: switching to application buffer
[25-JUN-2010 13:26:33:091] nsrdr: entry
[25-JUN-2010 13:26:33:091] nsrdr: recving a packet
[25-JUN-2010 13:26:33:091] nsprecv: entry
[25-JUN-2010 13:26:33:091] nsprecv: reading from transport…
[25-JUN-2010 13:26:33:091] nttrd: entry
[25-JUN-2010 13:26:33:137] nttrd: socket 304 had bytes read=129
[25-JUN-2010 13:26:33:137] nttrd: exit
[25-JUN-2010 13:26:33:137] nsprecv: 129 bytes from transport
[25-JUN-2010 13:26:33:137] nsprecv: tlen=129, plen=129, type=6
[25-JUN-2010 13:26:33:137] nsprecv: packet dump
[25-JUN-2010 13:26:33:137] nsprecv: 00 81 00 00 06 00 00 00  |……..|
[25-JUN-2010 13:26:33:137] nsprecv: 00 00 FF 2F A9 78 83 13  |…/.x..|
[25-JUN-2010 13:26:33:137] nsprecv: C6 AE 2B B6 45 34 40 77  |..+.E4@w|
[25-JUN-2010 13:26:33:137] nsprecv: 70 DF 3D 7D AE C4 C7 D6  |p.=}….|
[25-JUN-2010 13:26:33:137] nsprecv: AC B8 07 F9 00 76 74 06  |…..vt.|
[25-JUN-2010 13:26:33:137] nsprecv: 62 CA 9A F0 76 EA 4D 17  |b…v.M.|
[25-JUN-2010 13:26:33:137] nsprecv: E2 80 54 2D FD 56 80 2C  |..T-.V.,|
[25-JUN-2010 13:26:33:137] nsprecv: 3B 19 8E FF 0A E1 59 FA  |;…..Y.|
[25-JUN-2010 13:26:33:137] nsprecv: 09 80 13 4F 38 E6 62 17  |…O8.b.|
[25-JUN-2010 13:26:33:137] nsprecv: B4 8F EF 9B EA B3 91 5E  |…….^|
[25-JUN-2010 13:26:33:137] nsprecv: A5 B9 98 54 F9 2A 0B F7  |…T.*..|
[25-JUN-2010 13:26:33:137] nsprecv: B0 B9 FE 94 6A E4 AB 0E  |….j…|
[25-JUN-2010 13:26:33:137] nsprecv: FB 23 3A 62 BB FB D7 6E  |.#:b…n|
[25-JUN-2010 13:26:33:137] nsprecv: B1 C1 89 EA 0E CD 92 C0  |……..|
[25-JUN-2010 13:26:33:137] nsprecv: C2 D8 76 99 17 77 10 CF  |..v..w..|
[25-JUN-2010 13:26:33:137] nsprecv: 35 24 17 08 B0 BD D2 00  |5$……|
[25-JUN-2010 13:26:33:137] nsprecv: 01                       |.       |
[25-JUN-2010 13:26:33:137] nsprecv: normal exit
[25-JUN-2010 13:26:33:137] nsrdr: got NSPTDA packet
[25-JUN-2010 13:26:33:137] nsrdr: NSPTDA flags: 0×0
[25-JUN-2010 13:26:33:137] nsrdr: normal exit
[25-JUN-2010 13:26:33:137] nsfull_pkt_rcv: *what=1, *bl=8145
[25-JUN-2010 13:26:33:137] nsfull_pkt_rcv: nsctxrnk=0
[25-JUN-2010 13:26:33:137] nsfull_pkt_rcv: normal exit
[25-JUN-2010 13:26:33:137] nioqrc: exit

Beside regular sqlnet client tracing Oracle introduced Event 10079 -  Dumping Oracle Net network packets.

This event can be used to enable dumps for sessions which are already established.That is what I am going to use for the purpose of this exersize.

Common dump levels are:

Level 1 – Trace network operations to/from client
Level 2 – level 1 + dump data
Level 4 – Trace operations to/from db link
Level 8 – level 4 + dump data

As always you can combine dump levels.

In my next scenario I will set event 10079 for same sql session ( SID 134) by running oradebug command
using sqlplus from Oracle Client running on Windows:

SQL> oradebug setospid 28079
Oracle pid: 29, Unix process pid: 28079, image: oracle@demo02
SQL> oradebug event 10079 trace name context forever, level 2
Statement processed.
SQL> oradebug event 10079 trace name context off
Statement processed.
SQL>

and run same alter statement again from same session SID 134

alter user demo identified by demo…

Quick look at trace file  trace file:
SQL> oradebug tracefile_name
/oracle/trace/demo_ora_28079.trc

demo_ora_28079.trc

IFCDSEND: (bp=0x9d4aaa0, bl=1, isdblink=0)
Dump of memory from 0x0000000009D4AAA0 to 0x0000000009D4AAA1
009D4AAA0 00000008                             [....]
IFCDRECV: (bp=0x7fff16e831e4, bl=1, isdblink=0)
100 bytes sent via fast-path
Dump of memory from 0x0000000017BA45A1 to 0x0000000017BA4605
017BA45A0 4E000608 040AAE15 04000000 00000000  [...N............]
017BA45B0 00000000 00000000 00000000 00000000  [................]
017BA45C0 04000000 00000001 00000018 00000000  [................]
017BA45D0 00000000 00000004 0000002B 00000000  [........+.......]
017BA45E0 00000000 00000000 00000000 00000000  [................]
017BA45F0 0100001A 00000000 00000000 00000000  [................]
017BA4600 00000000 D8C2C000                    [........]

again no password exposed.

This time I will enable event 10079 but  on Oracle Database Server itself ( on my Linux server)

SQL> oradebug setospid 28079
Oracle pid: 29, Unix process pid: 28079, image: oracle@demo02
SQL> oradebug event 10079 trace name context forever, level 2
Statement processed.

and run same alter statement from same session SID 134:

alter user demo identified by test_4bla.

checking demo_ora_28079.trc again:

181 bytes received via SQL*Net
Dump of memory from 0x00007FFF16E831E4 to 0x00007FFF16E831E5
7FFF16E831E0          00002B11                        [.+..]
Dump of memory from 0x0000000017B9DCB1 to 0x0000000017B9DD65
017B9DCB0 011D6911 00000001 00000002 211E5E03  [.i...........^.!]
017B9DCC0 00000080 01000000 0000002E 00000D01  [................]
017B9DCD0 00010100 01000000 00000000 00000000  [................]
017B9DCE0 00000000 01000100 00000101 00000000  [................]
017B9DCF0 01010000 00000000 00000000 00000000  [................]
017B9DD00 612E0000 7265746C 65737520 6D6D2072  [......alter user]
017B9DD10 6172646F 69766F6B 64692063 69746E65  [demo identified ]
017B9DD20 64656966 20796220 74736574 6C62345F  [by test_4bla....]
017B9DD30 00000161 00000100 00000000 00000000  [................]
017B9DD40 00000000 00000000 00000000 00000700  [................]
017B9DD50 00000000 00000000 00000000 00000000  [................]
017B9DD60 00000000 66154700                    [.....G.f]
IFCDSEND: (bp=0x9d4aaa0, bl=1, isdblink=0)
Dump of memory from 0x0000000009D4AAA0 to 0x0000000009D4AAA1
009D4AAA0 00000008                             [....]
IFCDRECV: (bp=0x7fff16e831e4, bl=1, isdblink=0)
100 bytes sent via fast-path

Now I can see password in clear text!! Although I have ASO enabled I can see network data UNENCRYPTED!

Here is last one that I use all the time when resetting passwords:

changing password using sqlplus and passw command

passw demo
Changing password for demo
New password:
Retype new password:
Password changed

and from trace file

866 bytes received via SQL*Net
Dump of memory from 0x00007FFF16E831E4 to 0x00007FFF16E831E5
7FFF16E831E0          00002B11                        [.+..]
Dump of memory from 0x0000000017BA2581 to 0x0000000017BA28E2
017BA2580 861F6B11 05000000 01000056 11000000  [.k......V.......]
017BA2590 01012069 03000000 03000000 0B012173  [i ..........s!..]
017BA25A0 12000000 01000001 00000007 6D0B0101  [...............m]
017BA25B0 72646F6D 766F6B61 00106369 41100000  [demo.....A]
017B425C0 5F485455 5045444E 57535341 40444424  [UTH_NEWPASSWORD@]
017BA25D0 40000000 43344443 30445462 3234843  [...@C10C16F0C862]
017BA25E0 38333137 38373844 41303043 38383143  [7138D878C00AC188]

Password is protected.