RE: Perl::DBI problems after charset change (MORE INFO -- longish

[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
Rich.Jesse_at_qtiworld.com              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: Rich.Jesse_at_qtiworld.com

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: ListGuru_at_fatcity.com (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).