Re: Procedures problem in Webserver 2.0

This is because HTP, HTF, OWA, OWA_UTIL, etc all write to their own package states.  

Say you have 2 webagents, wa1, wa2.
They both installed the webserver developers toolkit in their own schema.  

wa1 has a procedure proc1
wa2 has a procedure proc2  

proc1 tries to call proc2.  

proc1 writes to wa1.htp.p which modifies wa1.htp.htbuf proc2 writes to wa2.htp.p which modifies wa2.htp.htbuf  

When OWA calls owa.get_page, it will call wa1.owa.get_page which will read wa1.htp.htbuf and NOT see anything that is in wa2.htp.htbuf.  

The same happens with OWA_UTIL.Get_Cgi_env. the cgi environment is stored in package variables. If you tried to access the wa2.owa_util.get_cgi_env in the above example, it would return NULL (in 1.x it will return NULL, it 2.x it will raise an exception) for everything since OWA called wa1.owa_util.set_cgi_env (hence wa2's package state is not modified and it won't see it).    

The workaround (documented in the users guide) is to  

• install the webserver developers toolkit ONCE in one schema. Now there is but one HTP, HTF, OWA, etc package.
• grant execute on the packages either to public or to each web agent account in turn
• create either public synonyms or create private synonyms for each web agent.

the only issues with one copy is that
- owa_util.table_print (a procedure I would not recommend using anyway) won't work properly.
- owa_util.showsource won't be able to see everyones source  

I've created the account and granted connect, resource, dba to it on my own machine. We ran
owains (in $ORACLE_HOME/ows2/admin) to install the toolkit. we granted execute on all the packages to public. We created public synonyms for all the packages. We then revoked connect, resource, dba from it (the account owning the packages). To allow
owa_util.showsource to work for all web agents, we granted "execute any procedure", "create any procedure" to it as well. (this last step is purely optional and would not normally be done in a real production environment. We did it to let people see our code internally)  

When we create another web agent, we will NOT install the web agents developers toolkit. this is *infinitely* better for your shared sql area as well.

On 16 Aug 1996 15:58:43 GMT, Jay Ramadorai <103422.1467_at_CompuServe.COM> wrote:

>I have OraWebserver2.0.3 connecting to Ora7.2.3 on Solaris 2.4. I
>have a DCD that connects to a non-DBA Oracle userid called A.
>There is another non-DBA Oracle userid B which owns stored
>procedure P that accesses several sensitive application tables as
>well as uses the web pl/sql extensions to format the data into an
>html template. B (of course) has select privileges on these tables
>but A does not. A has execute on B.P. Both A and B have the Web
>PL/SQL packages installed in their schemas but the DCD connects
>only to A. There is a private synonym SP owned by A for B.P. When
>I try to execute SP using this DCD via any webbrowser, there is no
>error msg but the page comes page empty except for a "Content
>Type html" message at the top. If the procedure were owned by A
>instead, the page works fine. But this means granting to A the
>sensitive select privileges which I don't want to do as anyone can
>connect to A over the web. I want A to only have the execute priv
>on B.P so that webusers can only see what B.P wants them to see.
>What are my options?
> It seems like any calls to the htp package from B.P use B.htp
>which in turn formats the html page in an area where the DCD
>connected to A cannot or does not use. There is no Oracle error. I
>can connect to A using sql*plus and execute SP just fine.
>Any ideas?
>Thanks
>jayr_at_softwarebuilders.com
>
>--
>Jay Ramadorai
>Software Builders
>jayr_at_softwarebuilders.com
>103422.1467_at_CompuServe.com

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Mon Aug 19 1996 - 00:00:00 CEST