Re: spawning a new connection from within pl/sql

Thanks for the reply.

To be more precise about my problem, it's concerning a call to the API of Designer 6i. This is the 3rd party application I had in mind.

Designer allows me (as administrator) to set a "versioning" privilege to the repository. This allows anyone with this privilege to check-out or check-in repository objects. But I want to be able to allow my users to check-out but NOT check-in. For this I created a database procedure "checkout_object" with the default of definer rights which I could then grant selected users "execute" privilege on. Sadly, this was no good because I then found out the Designer 6i packages must be issuing a lookup for the calling "user" against its own internal security table. (Even with definer rights I was getting an insufficient privileges message.)

So this meant I have to create a utility "wrapper" that hides a password inside to an account with full Designer 6i "versioning" privilege from which I can call the "checkout_object" procedure. If the password isn't hidden I run the risk of an enterprising user running Designer 6i as this account instead of their own or even run the risk of them getting in with sqlplus.

I think I now have a half solution. I've (today) set up this intermediate user account but granted almost no privileges to it. Just enough to create session and fire the D6I package procedures, plus my own checkout_object procedure. I've not hidden the password to it because in Designer, it turns out, I can restrict the tools that can be used by each user. This "versioning" account has been granted none at all, so it all has to happen through the API.

This isn't wonderfully safe because it relies my my users not knowing how to use the API! Well, it's the best I can think of at the moment!

Thanks for the input again.

Mark

"Ronald" <devnull_at_ronr.nl> wrote in message news:67ce88e7.0304100749.26af65e3_at_posting.google.com...
> "enquiry" <noone_at_hometownz.co.uk> wrote in message
news:<b73gmv$119$1_at_sparta.btinternet.com>...
> > I'm trying to work out how to spawn a new connection from within pl/sql.
> >
> > I want to embed a password in a stored (wrapped) procedure and then
issue
> > execute privilege on that procedure to selected users.
> >
> > I can't use the invoker/declarer rights as my control for access because
the
> > end package I need to run is a 3rd party pacakge with its own security
based
> > on it looking up the calling "user" against its own table of privileges.
So,
> > I need to set up this intermediate step where I make this middle
procedure
> > perform an account session switch. (And I don't want to start editing
the
> > 3rd party packages!)
> >
> > I've been trying to work out how to use the "proxy user" functionality
but
> > can't work out how to implement it. I can issue the grant "connect
through"
> > to users but then can't work out how to make it work. I may be barking
up
> > the wrong tree anyway.
>
> Hi Mark,
> connecting from pl/sql won't work.
> The proxy user functionality is best suited for multitier apps using a
> directory

<http://download-west.oracle.com/docs/cd/B10501_01/network.920/a96573/glossa ry.htm#433758>
> I don't know what exactly you are doing but if you want to have some
> real control you need to go to pro*c of oci to do the connects and
> call the package. Maybe something like an external procedure might
> work for you ?
>
> How does the package find out who is calling it ?
>
> Ronald.
> -----------------------
> http://ronr.nl/unix-dba
Received on Fri Apr 11 2003 - 10:27:35 CDT