ORA-01017 ... but only when script is run as CGI

I'm using current versions of DBI and DBD::Oracle in a CGI script, Oracle
10.2 client talking to an Oracle 9 database, RedHat Linux. The script
previously ran successfully on a similar machine with the Oracle 9 client.

I have a simple test script, in which the username, password and SID are
hardcoded. If I run this script from the command line, it connects without
problems.

If I call the script as a CGI script, I get ORA-01017 in OCISessionBegin,
which is the bad username or password error.

As far as I can tell, environment variables are the same in both contexts,
the same tnsnames.ora file is used, and so forth. I have identified and
resolved permissions errors, so I think that isn't the problem.

Has anyone encountered something similar, or does anyone have any
suggestions for other things that I should check in order to resolve this?

Thanks,

Angus

RE: ORA-01017 ... but only when script is run as CGI

What happens if you try to connect to that same DB instance using
SQL*plus? Does your CGI have use the same tnsnames.ora file as the
command line user?

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Angus McIntyre [mailto:angus@pobox.com]=20
Sent: Friday, August 18, 2006 10:39 AM
To: dbi-users@perl.org
Subject: ORA-01017 ... but only when script is run as CGI

I'm using current versions of DBI and DBD::Oracle in a CGI script,
Oracle
10.2 client talking to an Oracle 9 database, RedHat Linux. The script
previously ran successfully on a similar machine with the Oracle 9
client.

I have a simple test script, in which the username, password and SID are
hardcoded. If I run this script from the command line, it connects
without
problems.

If I call the script as a CGI script, I get ORA-01017 in
OCISessionBegin,
which is the bad username or password error.

As far as I can tell, environment variables are the same in both
contexts,
the same tnsnames.ora file is used, and so forth. I have identified and
resolved permissions errors, so I think that isn't the problem.

Has anyone encountered something similar, or does anyone have any
suggestions for other things that I should check in order to resolve
this?

Thanks,

Angus


This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

RE: ORA-01017 ... but only when script is run as CGI

Angus McIntyre wrote:
> I'm using current versions of DBI and DBD::Oracle in a CGI script,
> Oracle=20
> 10.2 client talking to an Oracle 9 database, RedHat Linux. The script
> previously ran successfully on a similar machine with the Oracle 9
> client.=20
>=20
> I have a simple test script, in which the username, password and SID
> are hardcoded. If I run this script from the command line, it
> connects without problems.
>=20
> If I call the script as a CGI script, I get ORA-01017 in
> OCISessionBegin, which is the bad username or password error.
>=20
> As far as I can tell, environment variables are the same in both
> contexts, the same tnsnames.ora file is used, and so forth. I have
> identified and resolved permissions errors, so I think that isn't the
> problem.=20
>=20
> Has anyone encountered something similar, or does anyone have any
> suggestions for other things that I should check in order to resolve
> this?=20

I have had this problem before. I'm not positive, but I think it was
caused by ORACLE_HOME not being passed through by Apache. If you're
running Apache, you'll need this directive in your httpd.conf:
PassEnv ORACLE_HOME

hth
Philip

RE: ORA-01017 ... but only when script is run as CGI

On Fri, August 18, 2006 1:25 pm, Garrett, Philip \(MAN-Corporate\) wrote:
> Angus McIntyre wrote:
>> If I call the script as a CGI script, I get ORA-01017 in
>> OCISessionBegin, which is the bad username or password error.
>
> I have had this problem before. I'm not positive, but I think it was
> caused by ORACLE_HOME not being passed through by Apache. If you're
> running Apache, you'll need this directive in your httpd.conf:
> PassEnv ORACLE_HOME

Thanks for the suggestion. Unfortunately, that doesn't seem to have cured
it. Tests show that the correct ORACLE_HOME was getting passed through to
Perl anyway, and adding PassEnv didn't improve matters.

Thanks again

Angus

RE: ORA-01017 ... but only when script is run as CGI

On Fri, August 18, 2006 1:21 pm, Reidy, Ron wrote:
> What happens if you try to connect to that same DB instance using
> SQL*plus?

sqlplus connects without problems, using the same username and password as
the script (as mentioned, the script runs fine from the command line, but
fails to connect when run as a CGI).

> Does your CGI have use the same tnsnames.ora file as the
> command line user?

As far as I know, yes. It's using the same $ORACLE_HOME. The
'tnsnames.ora' file is world-readable (and in any case, the 'apache' user
has been added to the 'oracle' group that owns all the files in the Oracle
install).

Thanks for your help,

Angus

> --
> Ron Reidy
> Lead DBA
> Array BioPharma, Inc.
>
> -----Original Message-----
> From: Angus McIntyre [mailto:angus@pobox.com]
> Sent: Friday, August 18, 2006 10:39 AM
> To: dbi-users@perl.org
> Subject: ORA-01017 ... but only when script is run as CGI
>
> I'm using current versions of DBI and DBD::Oracle in a CGI script,
> Oracle
> 10.2 client talking to an Oracle 9 database, RedHat Linux. The script
> previously ran successfully on a similar machine with the Oracle 9
> client.
>
> I have a simple test script, in which the username, password and SID are
> hardcoded. If I run this script from the command line, it connects
> without
> problems.
>
> If I call the script as a CGI script, I get ORA-01017 in
> OCISessionBegin,
> which is the bad username or password error.
>
> As far as I can tell, environment variables are the same in both
> contexts,
> the same tnsnames.ora file is used, and so forth. I have identified and
> resolved permissions errors, so I think that isn't the problem.
>
> Has anyone encountered something similar, or does anyone have any
> suggestions for other things that I should check in order to resolve
> this?
>
> Thanks,
>
> Angus
>
>
> This electronic message transmission is a PRIVATE communication which
> contains
> information which may be confidential or privileged. The information is
> intended
> to be for the use of the individual or entity named above. If you are not
> the
> intended recipient, please be aware that any disclosure, copying,
> distribution
> or use of the contents of this information is prohibited. Please notify
> the
> sender of the delivery error by replying to this message, or notify us by
> telephone (877-633-2436, ext. 0), and then delete it from your system.
>
>
>

RE: ORA-01017 ... but only when script is run as CGI

So, there is no private tnsnames.ora nor $TNS_ADMIN defined on the
server (or in httpd.conf) at all? I ask again, because if either of
these situations exists, that would explain the problem.

BTW - Having Apache in the same group as the oracle software might be a
gaping security hole. Just a thought.

rr

-----Original Message-----
From: Angus McIntyre [mailto:angus@pobox.com]=20
Sent: Friday, August 18, 2006 12:07 PM
To: dbi-users@perl.org
Subject: RE: ORA-01017 ... but only when script is run as CGI

On Fri, August 18, 2006 1:21 pm, Reidy, Ron wrote:
> What happens if you try to connect to that same DB instance using
> SQL*plus?

sqlplus connects without problems, using the same username and password
as
the script (as mentioned, the script runs fine from the command line,
but
fails to connect when run as a CGI).

> Does your CGI have use the same tnsnames.ora file as the
> command line user?

As far as I know, yes. It's using the same $ORACLE_HOME. The
'tnsnames.ora' file is world-readable (and in any case, the 'apache'
user
has been added to the 'oracle' group that owns all the files in the
Oracle
install).

Thanks for your help,

Angus

> --
> Ron Reidy
> Lead DBA
> Array BioPharma, Inc.
>
> -----Original Message-----
> From: Angus McIntyre [mailto:angus@pobox.com]
> Sent: Friday, August 18, 2006 10:39 AM
> To: dbi-users@perl.org
> Subject: ORA-01017 ... but only when script is run as CGI
>
> I'm using current versions of DBI and DBD::Oracle in a CGI script,
> Oracle
> 10.2 client talking to an Oracle 9 database, RedHat Linux. The script
> previously ran successfully on a similar machine with the Oracle 9
> client.
>
> I have a simple test script, in which the username, password and SID
are
> hardcoded. If I run this script from the command line, it connects
> without
> problems.
>
> If I call the script as a CGI script, I get ORA-01017 in
> OCISessionBegin,
> which is the bad username or password error.
>
> As far as I can tell, environment variables are the same in both
> contexts,
> the same tnsnames.ora file is used, and so forth. I have identified
and
> resolved permissions errors, so I think that isn't the problem.
>
> Has anyone encountered something similar, or does anyone have any
> suggestions for other things that I should check in order to resolve
> this?
>
> Thanks,
>
> Angus
>
>
> This electronic message transmission is a PRIVATE communication which
> contains
> information which may be confidential or privileged. The information
is
> intended
> to be for the use of the individual or entity named above. If you are
not
> the
> intended recipient, please be aware that any disclosure, copying,
> distribution
> or use of the contents of this information is prohibited. Please
notify
> the
> sender of the delivery error by replying to this message, or notify
us by
> telephone (877-633-2436, ext. 0), and then delete it from your system.
>
>
>

Re: ORA-01017 ... but only when script is run as CGI

I think at this point, you are going to have to post some code, and
maybe throw in the output of adding:

use Data::Dumper;
warn Dumper %ENV;

in order to get much meaningful help.




The darkest places in hell are reserved for those who maintain their
neutrality in times of moral crisis.
Dante Alighieri (1265 - 1321)

They who would give up an essential liberty for temporary security,
deserve neither liberty or security.
Benjamin Franklin

Our lives begin to end the day we become silent about things that matter.
Martin Luther King

The right of the people to be secure in their persons, houses, papers,
and effects, against unreasonable searches and seizures, shall not be
violated, and no warrants shall issue, but upon probable cause,
supported by oath or affirmation, and particularly describing the
place to be searched, and the persons or things to be seized.

Amendment IV to the Constitution of the United States
------------------------------------------------------------ --------------------------------------------------

RE: ORA-01017 ... but only when script is run as CGI

On Fri, August 18, 2006 2:16 pm, Reidy, Ron wrote:
> So, there is no private tnsnames.ora nor $TNS_ADMIN defined on the
> server (or in httpd.conf) at all? I ask again, because if either of
> these situations exists, that would explain the problem.

No, there's just one 'tnsnames.ora' file in use.

I finally found the solution by adding code to the script to print out
environment variables. Comparison of the output in command-line and CGI
mode revealed one difference, which was that when run as a CGI the
environment included a value for NLS_LANG, set by a line in 'httpd.conf'
that read:

SetEnv NLS_LANG AMERICAN_AMERICA.AL16UTF16

Removing that line fixed the problem, and the script (and other scripts)
now run fine when invoked via CGI.

> BTW - Having Apache in the same group as the oracle software might be a
> gaping security hole. Just a thought.

And a good thought. Apache now runs as 'nobody'.

Thank you all for your help,

Angus