Jim,
Below is a series of steps I did based on you suggested proc to find the cause of my problem. This post is also a bit long.
First I verified that the two users .cshrc & .login are the same. Both what inside and what permission are of the files. As the below commads show no difference in the files and the permissions are seem to be the same.
adamski cars# diff ~sury/.cshrc ~ury/.cshrc
adamski cars# diff ~sury/.login ~ury/.login
adamski cars# ll ~sury/.cshrc
-rw-r--r-- 1 sury financial 347 Mar 26 2007 /home/carsids/sury/.cshrc
adamski cars# ll ~ury/.cshrc
-rw-r--r-- 1 ury financial 347 Nov 7 2003 /home/carsids/ury/.cshrc
adamski cars# ll ~sury/.login
-r--r--r-- 1 root financial 317 Oct 29 10:52 /home/carsids/sury/.login
adamski cars# ll ~ury/.login
-r--r--r-- 1 root financial 317 Oct 29 10:53 /home/carsids/ury/.login
adamski cars#
I then tried setting the path to nothing and then doing the csh to show what the path is.
sury cars: set PATH
sury cars: echo $PATH
sury cars: csh
sury cars: echo $PATH
/opt/carsi/install/cis:/opt/perl514/bin:/bin:/usr/bin:/usr/local/bin:/opt/carsi/install/utl:/opt/carsi/install/bin:/opt/informix/bin:/opt/aCC/bin:/opt/gnu/bin:/opt/java6/bin:/opt/openldap/bin:/usr/contrib/bin:/usr/bin/X11:/usr/contrib/bin/X11:/opt/imake/bin:/opt/less/bin:/opt/ncftp/bin:/opt/pine/bin:/opt/ytalk/bin/X11:/opt/carstrain/install/scp/graceland/
ury cars: set PATH
ury cars: echo $PATH
ury cars: csh
ury cars: echo $PATH
/opt/carsi/install/cis:/opt/perl514/bin:/bin:/usr/bin:/usr/local/bin:/opt/carsi/install/utl:/opt/carsi/install/bin:/opt/informix/bin:/opt/aCC/bin:/opt/gnu/bin:/opt/java6/bin:/opt/openldap/bin:/usr/contrib/bin:/usr/bin/X11:/usr/contrib/bin/X11:/opt/imake/bin:/opt/less/bin:/opt/ncftp/bin:/opt/pine/bin:/opt/ytalk/bin/X11:/opt/carstrain/install/scp/graceland/
I then created the script per your suggestion and change the proc to run the script.
adamski cars: cat /tmp/test.csh
#!/bin/csh -f
##
printenv | sort > /tmp/test.log
id -r >> /tmp/test.log (print "real" ID of user running this script)
id -u >>/tmp/test.log (print "effective" ID of user running it)
adamski cars: cat testsysfail.sql
drop procedure testsysfail;
create procedure testsysfail()
returning char(32);
on exception in (-668)
return "Failed, error 668";
end exception;
--system "/bin/env > /tmp/1";
SYSTEM " /tmp/test.csh";
return "succeded";
end procedure;
grant execute on testsysfail to public;
After this I logged in as the person that has been able to run the proc without problems. I get the 668 error now on them but it runs the script and I have the output below.
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Run the current SQL statements.
----------------------- cars@carsitcp ---------- Press CTRL-W for Help --------
execute procedure testsysfail()
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Run the current SQL statements.
----------------------- cars@carsitcp ---------- Press CTRL-W for Help --------
(expression)
Failed, error 668
1 row(s) retrieved.
ury cars: ll /tmp/test.log
-rw-rw---- 1 ury financial 791 Oct 31 15:48 /tmp/test.log
ury cars: cat /tmp/test.log
CLIENT_LOCALE=en_US.8859-1
DBCENTURY=C
DBPATH=:OBJ:/opt/carsi/schema/common:/opt/carsi/install/frm/common:.:
DBTEMP=/tmp
DB_LOCALE=en_US.819
INFORMIXDIR=/opt/informix
INFORMIXSERVER=newt
LC_COLLATE=en_US.iso88591
LC_CTYPE=en_US.iso88591
LC_MONETARY=en_US.iso88591
LC_NUMERIC=en_US.iso88591
LC_TIME=en_US.iso88591
ONCONFIG=onconf.cars
PATH=/opt/carsi/install/cis:/opt/perl514/bin:/bin:/usr/bin:/usr/local/bin:/opt/carsi/install/utl:/opt/carsi/install/bin:/opt/informix/bin:/opt/aCC/bin:/opt/gnu/bin:/opt/java6/bin:/opt/openldap/bin:/usr/contrib/bin:/usr/bin/X11:/usr/contrib/bin/X11:/opt/imake/bin:/opt/less/bin:/opt/ncftp/bin:/opt/pine/bin:/opt/ytalk/bin/X11:/opt/carstrain/install/scp/graceland/
SERVER_LOCALE=en_US.819
SHELL=/bin/csh
SQLPID=13835058056514756656
TZ=CST6CDT
_=/tmp/test.csh
ury cars:
I then logged on to the user that has always been getting the error and get the error but the script is not run and there is no output.
NEW: ESC = Done editing CTRL-A = Typeover/Insert CTRL-R = Redraw
CTRL-X = Delete character CTRL-D = Delete rest of line
----------------------- cars@carsitcp ---------- Press CTRL-W for Help --------
execute procedure testsysfail()
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Run the current SQL statements.
----------------------- cars@carsitcp ---------- Press CTRL-W for Help --------
(expression)
Failed, error 668
1 row(s) retrieved.
sury cars: ll /tmp/test.log
/tmp/test.log not found
sury cars:
None of this seems to point to any cause to me. Does anything I'm shown here point to something?
John
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of jim-cramer@uiowa.edu
Sent: Wednesday, October 31, 2012 11:51 AM
To: ids@iiug.org
Subject: RE: I have a puzzling situation [28686]
John,
Thanks for your reply and the info.
I have several comments about what you have said below. Unfortunately that has made this message very long and I hope you can preserver until the end of it.
The MOST IMPORTANT items from the below are:
- a test procedure I suggest that should eliminate all variables and uncertainty and tell you exactly what is happening in the Environment that your scripts/ commands are failing in when run by SYSTEM.
- a very interesting and probably not too well known security feature that could cause the .cshrc file to NOT be executed in the sub-shell created.
That
would leave $PATH and other important Env Vars of the sub-shell unset and cause problems!!!
1) is the SYSTEM command used in a database server Stored Procedure in SPL or is in a User-Defined Routine (UDR) ? I am familiar with using SYSTEM from SPL but not had much UDR experience. I am assuming that the term "procs" you are using is the same as Stored Procedure.
2) have you tried explicitly specifying the path to everything on the command-line run with SYSTEM? If so, did it help?
3) in your message you were checking PATH to make sure that /bin was in it. I would think that it should also have /usr/bin too.
3) From my experience with this same problem you are having, I feel that you have to be careful about the exact context for the environment that you echo $PATH from.
So, below when you show the TEST results from running "echo $PATH" and "which", were those commands run from an interactive login shell?
Or were they run from inside a Stored Procedure with the SPL command SYSTEM " echo $PATH ; which ... "
4) Regarding what you said below:
" Their .cshrc seem to set their PATH based on logon so if I'm reading it correctly the logon and sub-shell should have the same PATH."
The .cshrc should execute at login after the system-wide login scripts and it should execute when SYSTEM creates a sub-shell to run your command.
But when you say that the login and sub-shell should have the same PATH, that is not necessarily true. That would depend upon which script is setting $PATH:
a. ~/.chsrc - which is executed at beginning of both login and sub shell b. ~/.login - which is executed only at the start of login shell c. /etc/csh.login - which is executed only at login before either of the 2 above
So, with reference to what Art said in an earlier reply to this, only the ..cshrc file is going to be executed when SYSTEM creates a sub-shell.
You have to make sure that the minimal path needed for things that are run by the sub-shell created by SYSTEM are set in the .cshrc file, such as /bin, /usr/bin, and any others that the command run by SYSTEM needs. If the command run by SYSTEM is a script, it will inherit the environment and Env Vars from the sub-shell that gets created, so it will have the PATH that the ~/.cshrc file creates when the sub-shell is created.
5) One way to be fairly sure that these necessary directories are present in the $PATH of the sub-shell created by SYSTEM is to do this:
- login to the account under which your db client app (yes, dbaccess is good for this test) will connect to the IDS server.
- unset PATH in this login shell
- run a sub-shell by typing: csh
- echo $PATH and see if the PATH in the sub-shell has been restored by ~/.cshrc file (or any files ~/.cshrc in turn executes to alter the environment)
6) One way to make darn sure of the PATH and other Environment Variables in the sub-shell created by the SYSTEM call is to do this:
- create a shell script , for example: /tmp/test.csh
- include in this script the commands:
printenv | sort > /tmp/test.log
id -r >> /tmp/test.log (print "real" ID of user running this script)
id -u >>/tmp/test.log (print "effective" ID of user running it)
- alter the Stored Procedure SYSTEM command to run this debugging script:
SYSTEM " /tmp/test.csh"
- from whichever user account is experiencing the problem, execute dbaccess and run the Stored Procedure.
The 1st command in the debug script is to be exactly sure of the environment under which your command or script is run as a result of using SYSTEM.
The reason for the next two commands is not at all clear and was suggested to me by our very experienced, lead Unix/Linux Systems Administrator after reading the following part of a man page, although it is for the Bash Shell on Linux:
" If the shell is started with the effective user (group) ID not equal to the real user (group) id, and the -p option is not supplied, no startup files are read, shell functions are not inherited from the environment, the SHELLOPTS variable, if it appears in the environment, is ignored, and the effective user id is set to the real user id. If the -p option is supplied at invocation, the startup behavior is the same, but the effective user id is not reset."
What if the "real User ID" that starts the sub-shell is that of oninit, which is run under either root or informix User ID, and then before starting to execute your command/script in the sub-shell the process that created the shell (probably the "system" C Language call) then changes the "effective" User ID" that your command/script is run under to that of the user running the Stored Procedure and SPL SYSTEM command??
If this actually is how things are implemented when using SYSTEM, and if this behavior I quoted from the man page for "bash" is the way that the Operating System of the host system for your IDS behaves, then your .cshrc file will NOT get executed by the sub-shell and whatever it is supposed to do to Env Vars in the sub-shell environment will NOT HAPPEN.
You could end up
with $PATH completely unset for your command or script that is run.
The only way to be sure is to use the test procedure immediately above.
7) My problems with -668 errors, which also began after upgrading to 11.70, have been drastically reduced by explicitly specifying the path to every command used in the command-line specified by SYSTEM.
If you find out anything else and report that back to me, I would appreciate it.
Jim
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John Adamski
Sent: Wednesday, October 31, 2012 9:58 AM
To: ids@iiug.org
Subject: RE: I have a puzzling situation [28683]
Jim,
Our developers use the SYSTEM command for a number of things from 'echo' to running scripts. Most of the procs seem to run a script and this is causing us the most problem. One of the procs I know of does an echo to a file in /tmp.
And this echo does fails and this seems to have started around the time we upgraded from 11.50 to 11.70.
The question on PATH, I have taken my two test users out of the menu system and they have command line access on our DR/test system. I got what looks like normal $PATH and they are the same between the two users. I did this change to remove one layer of complexity, I just now logon as them and run dbaccess to
test the procs.
echo $PATH
/opt/carsi/install/cis:/opt/perl514/bin:/bin:/usr/bin:/usr/local/bin:/opt/ca
rsi/install/utl:/opt/carsi/install/bin:/opt/informix/bin:/opt/aCC/bin:/opt/g
nu/bin:/opt/java6/bin:/opt/openldap/bin:/usr/contrib/bin:/usr/bin/X11:/usr/c
ontrib/bin/X11:/opt/imake/bin:/opt/less/bin:/opt/ncftp/bin:/opt/pine/bin:/op
t/ytalk/bin/X11:/opt/carstrain/install/scp/graceland/
which echo
/bin/echo
which mailx
/bin/mailx
And /bin is in the path.
Their .cshrc seem to set their PATH based on logon so if I'm reading it correctly the logon and sub-shell should have the same PATH. I have testing at the command line doing a echo like the proc and it works, and then try the proc and it fails. Which would indicate that I am reading what the .cshrc is
doing and it somehow is changing the PATH making the proc fail.
I think I will change the test proc to use /bin/echo or /bin/mailx instead of echo/mailx to see if that makes any difference.
Will report back.
John
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of jim-cramer@uiowa.edu
Sent: Tuesday, October 30, 2012 1:22 PM
To: ids@iiug.org
Subject: RE: I have a puzzling situation [28677]
John,
Based on my reasoning, mentioned below, my question to you is:
Do all commands, such as "echo", that are included in the string that SYSTEM
hands to the shell to execute, have *explicit paths* ?
If you know the answer to this question, would you mind replying with it to this post?
I have been experiencing what sounds like the same problem that you are having. My problem is with some Stored Procedures which use the SYSTEM call and which had run without problem for years under IDS 10. My problems with the
-668 error began right away after switching to IDS 11.7.FC1GE. I would like to determine if what is going on is caused by our environment & code and fix it
or, if this is a bug, if I can upgrade to a 11.7 maintenance release where it has been addressed.
If I understand correctly , you said in you post that you thought your tests
had been failing on the "echo" command,.
As Art says below in his response about this issue, SYSTEM will execute the command formed by the string that you hand it in a *sub-shell* and not by a login shell.
Perhaps some or all of the users who are having the -668 SYSTEM Call error have the /usr/bin and /bin parts of their $PATH set by the system scripts run by the login shell, and not by their personal "dot files"?
This could scenario could cause a failure of the shell command run by SYSTEM
if everything in that command line is not explicitly path'd..
Regards,
Jim Cramer
Univ of Iowa
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel
Sent: Monday, October 29, 2012 12:23 PM
To: ids@iiug.org
Subject: Re: I have a puzzling situation [28668]
The user's complete environment will not be set up when SYSTEM is called as the csh profile scripts (/etc/login and $HOME/.login) is only executed by a login shell, not a sub-shell which is what the engine will be using to execute the command. The sub-shel will only execute the user's $HOME/.cshrc script.
Maybe the users who have the problem have more of their environment set up in /etc/login or $HOME/.login and are missing something in their .cshrc or maybe they don't have write privs on /tmp (unusual I know, but worth a quick check).
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
On Mon, Oct 29, 2012 at 12:28 PM, John Adamski <adamski@graceland.edu>wrote:
> Art, Mark & Paul:
>
> I have verified that the two users I am using in testing have the same
> default shell (csh) and that their PATHs are the same. At least an
> echo $PATH returns the same. Our ERP does strange things with the path
> variable so still could be a PATH thing.
>
> I can't upgrade to FC5 or FC6 as our ERP provider is, hmm how to say
> this politely - doesn't believe in keeping up to date with the latest
> release
of
> software they us. So, that option is not an option. :-(
>
> Since this sounds more like an OS permission problem I will continue
> searching for what is different between my two test users.
>
> Side note: I'm not exactly sure what the production procedure does as
> I don't think the developer told the name of the production proc. He
> just gave me his test proc.
>
> Since all the people they are talking about are in the same department
> (accounting) and I guessing which proc is having the problem. It
> echo's
the
> script call with parameters to a file in /tmp then calls the script.
> It looks like it fails on the echo.
>
> The test proc was created as it is time consuming to setup a situation
> and
> get
> the production proc to run.
>
> Thanks for the help.
>
> John
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Monday, October 29, 2012 9:13 AM
> To: ids@iiug.org
> Subject: Re: I have a puzzling situation [28657]
>
> Missed the procedure source below. Make sure that everyone has mailx
> (or
> whatever) in their default PATH or script the commands with a PATH set
> in the script.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own
> opinions and do not reflect on my employer, Advanced DataTools, the
> IIUG, nor any other
> organization with which I am associated either explicitly, implicitly,
> or by inference. Neither do those opinions reflect those of other
> individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Mon, Oct 29, 2012 at 10:09 AM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > If the SYSTEM command is running a shell script, check to see if the
> > users who are getting the -668 error have a different default shell
> > or have a shell other than their default shell set in the SHELL
> > environment variable that may be returning an error due to some
> > syntax that these users' shells are not recognizing. Or possibly the
> > script lists a shell in the first line (#!bash) but not a full path
> > and these users' PATHs are not set the same as everyone else's. A
> > simple fix to either problem may be to make sure that the script
> > that is executed includes a "#!" line that includes that fill path
> > to the shell it needs
> to
> run properly.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> > opinions and do not reflect on my employer, Advanced DataTools, the
> > IIUG, nor any other organization with which I am associated either
> > explicitly, implicitly, or by inference. Neither do those opinions
> > reflect those of other individuals affiliated with any entity with
> > which I am affiliated nor those of the entities themselves.
> >
> >
> >
> >
> > On Mon, Oct 29, 2012 at 9:48 AM, John Adamski <adamski@graceland.edu
> >wrote:
> >
> >> HPUX 11.31 on BL8709c Integrity server IDS 11.70.FC4
> >>
> >> A little over a month ago we upgraded to 11.70 and the developers
> >> say that since then some users have been having problems executing
> >> procedures that have the SYSTEM command in them.
> >>
> >> One of the developers has created a test procedure to help identify
> >> the cause of this reported problem. So far we know of two users
> >> that can't run the test procedure, they get a 668 error. All other
> >> users we have tried can execute the procedure successfully.
> >>
> >> Checking permission on the users that work and the ones that don't
> >> did not identify any cause. We check both OS and Informix.
> >>
> >> We are testing using dbaccess using this command
> >>
> >> execute procedure testsysfail()
> >>
> >> The two users that fail get this message when they run.
> >>
> >> SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
> >> Run the current SQL statements.
> >>
> >> ----------------------- cars@carsitcp ---------- Press CTRL-W for
> >> Help
> >> --------
> >>
> >> (expression)
> >>
> >> Failed, error 668
> >>
> >> 1 row(s) retrieved.
> >>
> >> I have included the latest version of the test procedure that the
> >> developer is using below. Does anyone have any idea what might be
> >> causing these two users to get a 668 error when they run the
> >> procedure? Or what I might do to resolve this?
> >>
> >> {
> >> Revision Information (Automatically maintained by 'make' - DON'T
> >> CHANGE)
> >> -------------------------------------------------------------------
> >> --
> >> ----
> >> $Header: procskeleton,v 8.0 2002/05/09 08:36:56 debarthe Released $
> >> -------------------------------------------------------------------
> >> --
> >> ----
> >> }
> >> drop procedure testsysfail;
> >>
> >> create procedure testsysfail()
> >> returning char(32);
> >>
> >> {The SQL data type subset includes all the SQL data types except
> >> BYTE, SERIAL, and TEXT.} {char, nchar, date, datetime, decimal,
> >> numeric, float, integer, money smallfloat, smallint, real, varchar,
> >> nvarchar {define global var1 integer default default 0;} {define
> >> var2 like id_rec.ss_no;} {define var3 char(10);}
> >>
> >> define sys_var char(256);
> >>
> >> {This exception traps for error 284 multiple records from subquery
> >> and must immediately follow the defines} on exception in (-668)
> >>
> >> return "Failed, error 668";
> >> end exception;
> >>
> >> Set isolation to dirty read;
> >>
> >> set optimization low;
> >>
> >> --set debug file to "debugxxx" with append; --trace on;
> >> -- let sys_var = "echo this is a test > /tmp/"||USER||"test.test";
> >>
> >> let sys_var = "mailx -s 'This is a test' debarthe"; system sys_var;
> >>
> >> return "succeded";
> >> end procedure;
> >>
> >> grant execute on testsysfail to public;
> >>
> >> {Add an execute for testing and to try to force optimization}
> >>
> >> --execute procedure testsysfail();
> >>
> >>
> >>
> >>
>
>
>
****************************************************************************
***
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >>
> >
>
> --e89a8f3ba7836ca74204cd3342fd
>
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f3b9c3f13a6f204cd35eb62
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
