[first post bounced from fatcity.com with "/var/spool/mail/autoresp: Permission denied." among other errors]
I think I'm getting somewhere, but the research has given me the security heebie-jeebies. As I'm tracing at SUPPORT level on the server side of a test DB (can't trace on the client because it's production), two major differences pop out at me. First, the connect packets have the text in a different order: Perl/DBI: (CONNECT_DATA=(SID=testsid)(SRVR=DEDICATED)(CID=(PROGRAM=)(HOST=myclient)(US ER=rjesse)))(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver)(PORT=1521)) )) SQL*Plus: (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver)(PORT=1521)))(CONNECT_DA TA=(SID=testsid)(SRVR=DEDICATED)(CID=(PROGRAM=)(HOST=myclient)(USER=rjesse)) )(FADRL=(FC=)(FG=))) Second, all other packets to and from Perl/DBI have every byte zero-terminated, whereas SQL*Plus doesn't. (Not knowing exactly where the password is, I've "*"d out and "xx"d out some bytes where I believe the encoded password and some other sensitive data to be) Perl/DBI: nsprecv: 267 bytes from transport nsprecv: tlen=267, plen=267, type=6 nsprecv: packet dump nsprecv: 01 0B 00 00 06 00 00 00 |........| nsprecv: 00 00 03 51 03 00 17 B9 |...Q....| nsprecv: 10 00 00 00 06 00 17 DB |........| nsprecv: F2 00 00 00 11 00 00 00 |........| nsprecv: 00 00 00 00 00 00 00 00 |........| nsprecv: 00 00 00 00 00 00 17 DD |........| nsprecv: 6E 00 00 00 05 00 17 DF |n.......| nsprecv: 6C 00 00 00 04 00 17 DE |l.......| nsprecv: 6D 00 00 00 06 00 00 08 |m.......| nsprecv: 00 00 17 E0 6B 00 00 00 |....k...| nsprecv: 05 00 17 E1 6A 00 00 00 |....j...| nsprecv: 20 00 00 00 00 00 00 00 | .......| nsprecv: 00 00 00 00 00 00 00 00 |........| nsprecv: 00 00 00 00 00 00 00 00 |........| nsprecv: 00 00 00 00 00 00 xx 00 |......U.| nsprecv: xx 00 xx 00 xx 00 xx 00 |N.A.M.E.| nsprecv: xx 00 xx 00 xx 00 xx 00 |1.*.*.*.| nsprecv: xx 00 xx 00 xx 00 xx 00 |*.*.*.*.| nsprecv: 42 00 xx 00 44 00 45 00 |B.*.D.E.| nsprecv: 34 00 41 00 xx 00 38 00 |4.A.8.8.| nsprecv: 45 00 32 00 70 00 74 00 |E.2.p.t.| nsprecv: 73 00 2F 00 35 00 xx 00 |s./.5.*.| nsprecv: xx 00 xx 00 xx 00 72 00 |*.*.*.r.| nsprecv: 6A 00 65 00 73 00 73 00 |j.e.s.s.| nsprecv: 65 00 31 00 37 00 30 00 |e.1.7.0.| nsprecv: 30 00 39 00 63 00 75 00 |0.9.c.u.| nsprecv: 72 00 73 00 6F 00 72 00 |r.s.o.r.| nsprecv: 5F 00 73 00 68 00 61 00 |_.s.h.a.| nsprecv: 72 00 69 00 6E 00 67 00 |r.i.n.g.| nsprecv: 5F 00 40 00 xx 00 xx 00 |_.@.*.*.| nsprecv: xx 00 xx 00 20 00 28 00 |*.*. .(.| nsprecv: 54 00 4E 00 53 00 20 00 |T.N.S. .| nsprecv: 56 00 31 00 2D 00 56 00 |V.1.-.V.| nsprecv: 32 00 29 00 00 00 00 00 |2.).....| nsprecv: normal exit SQL*Plus: nsprecv: 193 bytes from transport nsprecv: tlen=193, plen=193, type=6 nsprecv: packet dump nsprecv: 00 C1 00 00 06 00 00 00 |........| nsprecv: 00 00 03 76 02 00 1B 51 |...v...Q| nsprecv: 20 00 00 00 06 00 00 00 | .......| nsprecv: 01 FF BE DC 48 00 00 00 |....H...| nsprecv: 04 FF BE DA B8 FF BE DA |........| nsprecv: B2 xx xx xx xx xx xx 00 |.UNAME1.| nsprecv: 00 00 0D 0D 41 55 54 48 |....AUTH| nsprecv: 5F 54 45 52 4D 49 4E 41 |_TERMINA| nsprecv: 4C 00 00 00 05 05 70 74 |L.....pt| nsprecv: 73 2F 35 00 00 00 00 00 |s/5.....| nsprecv: 00 00 13 13 41 55 54 48 |....AUTH| nsprecv: 5F 50 52 4F 47 52 41 4D |_PROGRAM| nsprecv: 5F 4E 4D 00 41 55 54 00 |_NM.AUT.| nsprecv: 00 00 18 18 73 71 6C 70 |....sqlp| nsprecv: 6C 75 73 40 xx xx xx xx |lus@****| nsprecv: 20 28 54 4E 53 20 56 31 | (TNS V1| nsprecv: 2D 56 33 29 00 00 00 00 |-V3)....| nsprecv: 00 00 00 0C 0C 41 55 54 |.....AUT| nsprecv: 48 5F 4D 41 43 48 49 4E |H_MACHIN| nsprecv: 45 00 00 00 04 04 xx xx |E.....**| nsprecv: xx xx 00 00 00 00 00 00 |**......| nsprecv: 00 08 08 41 55 54 48 5F |...AUTH_| nsprecv: 50 49 44 00 00 00 05 05 |PID.....| nsprecv: 31 39 32 37 38 00 00 00 |19278...| nsprecv: 00 00 00 00 00 00 00 00 |........| nsprecv: normal exit >From the Perl/DBI session trace, the next packet is the ORA-1017 sent to the client: nspsend: 162 bytes to transport nspsend: packet dump nspsend: 00 A2 00 00 06 00 00 00 |........| nspsend: 00 00 04 00 00 00 00 03 |........| nspsend: F9 00 00 00 00 00 00 00 |........| nspsend: 00 00 00 40 00 00 00 00 |...@....| nspsend: 00 00 00 00 00 00 00 00 |........| nspsend: 00 00 00 00 00 00 00 00 |........| nspsend: 00 03 00 00 00 00 00 00 |........| nspsend: FE 40 00 4F 00 52 00 41 |.@.O.R.A| nspsend: 00 2D 00 30 00 31 00 30 |.-.0.1.0| nspsend: 00 31 00 37 00 3A 00 20 |.1.7.:. | nspsend: 00 69 00 6E 00 76 00 61 |.i.n.v.a| nspsend: 00 6C 00 69 00 64 00 20 |.l.i.d. | nspsend: 00 75 00 73 00 65 00 72 |.u.s.e.r| nspsend: 00 6E 00 61 00 6D 00 65 |.n.a.m.e| nspsend: 00 2F 00 70 00 61 00 73 |./.p.a.s| nspsend: 00 73 26 00 77 00 6F 00 |.s&.w.o.| nspsend: 72 00 64 00 3B 00 20 00 |r.d.;. .| nspsend: 6C 00 6F 00 67 00 6F 00 |l.o.g.o.| nspsend: 6E 00 20 00 64 00 65 00 |n. .d.e.| nspsend: 6E 00 69 00 65 00 64 00 |n.i.e.d.| nspsend: 0A 00 00 00 00 00 00 00 |........| nspsend: normal exit The packet following this contains the UNSECURED username AND PASSWORD sent to the server! (Data obfuscated for obvious reasons) nsprecv: 245 bytes from transport nsprecv: tlen=245, plen=245, type=6 nsprecv: packet dump nsprecv: 00 F5 00 00 06 00 00 00 |........| nsprecv: 00 00 03 3A 04 00 17 B9 |...:....| nsprecv: 10 00 00 00 06 00 16 C8 |........| nsprecv: F8 00 00 00 06 00 00 00 |........| nsprecv: 00 00 00 00 00 00 00 00 |........| nsprecv: 00 00 00 00 00 00 17 DD |........| nsprecv: 6E 00 00 00 05 00 17 DF |n.......| nsprecv: 6C 00 00 00 04 00 17 DE |l.......| nsprecv: 6D 00 00 00 06 00 00 08 |m.......| nsprecv: 00 00 17 E0 6B 00 00 00 |....k...| nsprecv: 05 00 17 E1 6A 00 00 00 |....j...| nsprecv: 20 00 00 00 00 00 00 00 | .......| nsprecv: 00 00 00 00 00 00 00 00 |........| nsprecv: 00 00 00 00 00 00 00 00 |........| nsprecv: 00 00 00 00 00 00 xx 00 |......U.| nsprecv: xx 00 xx 00 xx 00 xx 00 |N.A.M.E.| nsprecv: xx 00 xx 00 xx 00 xx 00 |1.P.W.O.| nsprecv: xx 00 xx 00 xx 00 70 00 |R.D.1.p.| nsprecv: 74 00 73 00 2F 00 35 00 |t.s./.5.| nsprecv: 6E 00 6F 00 61 00 68 00 |n.o.a.h.| nsprecv: 72 00 6A 00 65 00 73 00 |r.j.e.s.| nsprecv: 73 00 65 00 31 00 37 00 |s.e.1.7.| nsprecv: 30 00 30 00 39 00 63 00 |0.0.9.c.| nsprecv: 75 00 72 00 73 00 6F 00 |u.r.s.o.| nsprecv: 72 00 5F 00 73 00 68 00 |r._.s.h.| nsprecv: 61 00 72 00 69 00 6E 00 |a.r.i.n.| nsprecv: 67 00 5F 00 40 00 6E 00 |g._.@.n.| nsprecv: 6F 00 61 00 68 00 20 00 |o.a.h. .| nsprecv: 28 00 54 00 4E 00 53 00 |(.T.N.S.| nsprecv: 20 00 56 00 31 00 2D 00 | .V.1.-.| nsprecv: 56 00 32 00 29 00 00 00 |V.2.)...| nsprecv: normal exit This leaves me with more questions than when I started. Why is there a difference in the packet formats between Perl/DBI and SQL*Plus on the same client? When the initial logon fails via Perl/DBI, what attempts the second try -- the Oracle client or Perl/DBI? Why and how does the second attempt send the password unencrypted? And why does the client not report the ORA-1017? Finally, a level 2 DBI_TRACE shows DBI to be at version 1.13 and DBD::Oracle at 1.03. Anyone???? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -----Original Message----- > From: Jesse, Rich > Sent: Monday, September 30, 2002 4:48 PM > To: Multiple recipients of list ORACLE-L > Subject: Perl::DBI problems after charset change > > > Hey all, > > We've just changed our charactersets on our 8.1.7.2 (and > 8.1.7.4) DBs from > US7ASCII to WE8ISO8859P1 using the Oracle-approved "ALTER > DATABASE CHARACTER > SET WE8ISO8859P1" and accompanying commands. Everything > works like a champ, > but our Perl::DBI connections now all cause an invisibile > "ORA-1017 invalid > username/password" error. The error never appears in the > client -- the only > reason I know this happens is because I'm auditing for it. > > Other than having our audit log tablespace fill up, this > leaves me a little > worried and I don't know how to track it down. Of course, > since the apps > all work without reporting the error we never caught it in > our testing. > > The version of Perl is 5.005_03, the Oracle client is > 8.0.5.0.0 on Solaris, > and I don't know what version of DBI or DBD::Oracle. I've > tested my much > newer versions of Perl, Oracle client and DBI/DBD::Oracle > under windows and > no audit is generated. I've also connected using SQL*plus from the > 8.0.5.0.0 client without audit. > > I'm going to try and debug this without affecting the > production systems, > but has anyone encountered this before? > > TIA! > Rich -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).