Error 32508 from a function when run time

Posted in 2011

Topics: Stored Procedures & SPL, Server Administration, Security, Permissions & Auditing

SAMS GEORGE — Thu, 06 Oct 2011 02:10:59 — source: IIUG Forums & Mailing Lists

Dear All, For Auditing purposes we have written the following function on IDS 11.70UC3 runs on SUSE 11. But it is getting the 32508 error during the run time. create function "informix".get_ctsinfo() returning char(100),char(100),char(100); define global_app_usr char(100) ; define global_app_usr1 char(100) ; define db_usr char(100); define host_name char(100); define l_trans char(1024); define l_trans_2 char(1024); set debug file to 'p.out'; trace on; let global_app_usr = 'appusr1'; LET l_trans = "GRANT SETSESSIONAUTH ON " ||global_app_usr||" to dbadmin"; LET l_trans_2 =" SET SESSION AUTHORIZATION TO 'appusr1'" ; EXECUTE IMMEDIATE l_trans; select USER into db_usr from systables where tabid =1; EXECUTE IMMEDIATE l_trans_2; select USER into global_app_usr1 from systables where tabid =1; select first 1 hostname into host_name from sysmaster:syssessions where username=USER ; return global_app_usr1,db_usr,host_name; trace off; end function; Pl. help me to revolve the issue . Thanks

↪ replying to SAMS GEORGE

Art Kagel — Thu, 06 Oct 2011 02:46:45 — source: IIUG Forums & Mailing Lists

You cannot execute the "SET SESSION AUTHORIZATION TO 'appusr1'" statement within a transaction. The calling session must be within a transaction. 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 Wed, Oct 5, 2011 at 10:11 PM, SAMS GEORGE <sams.george1971@gmail.com>wrote: > Dear All, > > For Auditing purposes we have written the following function on IDS > 11.70UC3 > runs on SUSE 11. But it is getting the 32508 error during the run time. > > create function "informix".get_ctsinfo() returning > char(100),char(100),char(100); > > define global_app_usr char(100) ; > define global_app_usr1 char(100) ; > define db_usr char(100); > define host_name char(100); > define l_trans char(1024); > define l_trans_2 char(1024); > set debug file to 'p.out'; > trace on; > let global_app_usr = 'appusr1'; > > LET l_trans = "GRANT SETSESSIONAUTH ON " ||global_app_usr||" to dbadmin"; > LET l_trans_2 =" SET SESSION AUTHORIZATION TO 'appusr1'" ; > > EXECUTE IMMEDIATE l_trans; > > select USER into db_usr from systables where tabid =1; > > EXECUTE IMMEDIATE l_trans_2; > > select USER into global_app_usr1 from systables where tabid =1; > select first 1 hostname into host_name from sysmaster:syssessions> > where username=USER ; > return global_app_usr1,db_usr,host_name; > > trace off; > end function; > > Pl. help me to revolve the issue . > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba21219ba6ceaa04ae98558d

↪ replying to Art Kagel

Sams George — Thu, 06 Oct 2011 02:58:49 — source: IIUG Forums & Mailing Lists

Thanks, What would be the workaround for this? Many Thanks On Thu, Oct 6, 2011 at 8:16 AM, Art Kagel <art.kagel@gmail.com> wrote: > You cannot execute the "SET SESSION AUTHORIZATION TO 'appusr1'" statement > within a transaction. The calling session must be within a transaction. > > 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 Wed, Oct 5, 2011 at 10:11 PM, SAMS GEORGE <sams.george1971@gmail.com > >wrote: > > > Dear All, > > > > For Auditing purposes we have written the following function on IDS > > 11.70UC3 > > runs on SUSE 11. But it is getting the 32508 error during the run time. > > > > create function "informix".get_ctsinfo() returning > > char(100),char(100),char(100); > > > > define global_app_usr char(100) ; > > define global_app_usr1 char(100) ; > > define db_usr char(100); > > define host_name char(100); > > define l_trans char(1024); > > define l_trans_2 char(1024); > > set debug file to 'p.out'; > > trace on; > > let global_app_usr = 'appusr1'; > > > > LET l_trans = "GRANT SETSESSIONAUTH ON " ||global_app_usr||" to dbadmin"; > > LET l_trans_2 =" SET SESSION AUTHORIZATION TO 'appusr1'" ; > > > > EXECUTE IMMEDIATE l_trans; > > > > select USER into db_usr from systables where tabid =1; > > > > EXECUTE IMMEDIATE l_trans_2; > > > > select USER into global_app_usr1 from systables where tabid =1; > > select first 1 hostname into host_name from sysmaster:syssessions> > > > where username=USER ; > > return global_app_usr1,db_usr,host_name; > > > > trace off; > > end function; > > > > Pl. help me to revolve the issue . > > > > Thanks > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --90e6ba21219ba6ceaa04ae98558d > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec520f163be8ca804ae9880f6

↪ replying to Sams George

Art Kagel — Thu, 06 Oct 2011 09:22:58 — source: IIUG Forums & Mailing Lists

Make sure that the users are not in a transaction when they run this procedure! 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 Wed, Oct 5, 2011 at 10:58 PM, Sams George <sams.george1971@gmail.com>wrote: > Thanks, > What would be the workaround for this? > > Many Thanks > > On Thu, Oct 6, 2011 at 8:16 AM, Art Kagel <art.kagel@gmail.com> wrote: > > > You cannot execute the "SET SESSION AUTHORIZATION TO 'appusr1'" statement > > within a transaction. The calling session must be within a transaction. > > > > 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 Wed, Oct 5, 2011 at 10:11 PM, SAMS GEORGE <sams.george1971@gmail.com > > >wrote: > > > > > Dear All, > > > > > > For Auditing purposes we have written the following function on IDS > > > 11.70UC3 > > > runs on SUSE 11. But it is getting the 32508 error during the run time. > > > > > > create function "informix".get_ctsinfo() returning > > > char(100),char(100),char(100); > > > > > > define global_app_usr char(100) ; > > > define global_app_usr1 char(100) ; > > > define db_usr char(100); > > > define host_name char(100); > > > define l_trans char(1024); > > > define l_trans_2 char(1024); > > > set debug file to 'p.out'; > > > trace on; > > > let global_app_usr = 'appusr1'; > > > > > > LET l_trans = "GRANT SETSESSIONAUTH ON " ||global_app_usr||" to > dbadmin"; > > > LET l_trans_2 =" SET SESSION AUTHORIZATION TO 'appusr1'" ; > > > > > > EXECUTE IMMEDIATE l_trans; > > > > > > select USER into db_usr from systables where tabid =1; > > > > > > EXECUTE IMMEDIATE l_trans_2; > > > > > > select USER into global_app_usr1 from systables where tabid =1; > > > select first 1 hostname into host_name from sysmaster:syssessions> > > > > > where username=USER ; > > > return global_app_usr1,db_usr,host_name; > > > > > > trace off; > > > end function; > > > > > > Pl. help me to revolve the issue . > > > > > > Thanks > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --90e6ba21219ba6ceaa04ae98558d > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --bcaec520f163be8ca804ae9880f6 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba21219b84c1f204ae9ddeda

Glossary

IDS (Informix Dynamic Server)