Home » SQL & PL/SQL » SQL & PL/SQL » who called my procedure through a database link (Oracle 10g)
icon5.gif  who called my procedure through a database link [message #678135] Tue, 12 November 2019 13:18 Go to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I have a procedure sitting in a schema with definer rights and I only want it to be able to be run from the XYZ user who is accessing the procedure through a database link. The procedure is not in the schema that is being connected to through the database link. I have checked everywhere I can think of and I haven't found anything. I want the information directly from oracle so the calling user can't be faked by passing a password or something like that. Anyone have any ideas. Just a little background. The database link is going into a read only schema. The procedure works just fine using definer rights but I want to lock it down so only the specific user can call it.
Re: who called my procedure through a database link [message #678136 is a reply to message #678135] Tue, 12 November 2019 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do you want the original user that calls the procedure or the user you are connected to the remote database through the database link.
I mean if user USERA in database A connects to database B with user USERB through the database link, do you want USERA or USERB?

Re: who called my procedure through a database link [message #678137 is a reply to message #678136] Tue, 12 November 2019 21:28 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I want usera in the other database. Userb would be simple using v$session or USER. Thanks for getting back to me
Re: who called my procedure through a database link [message #678141 is a reply to message #678137] Wed, 13 November 2019 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't think this information (USERA) is passed to the remote database (B) when activating the database link.

Re: who called my procedure through a database link [message #678152 is a reply to message #678141] Wed, 13 November 2019 05:40 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Unfortunately I think your right, I couldn't find anything that would show it. I appreciate your looking into it.
Re: who called my procedure through a database link [message #678155 is a reply to message #678152] Wed, 13 November 2019 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I set up a test case and then searched in all X$ tables of remote database (B) nowhere the original caller (USERA) is in.
I think it is possible to get it using a reverse database link and, using the global transaction id, return back to the source of this one but how this would be reliable?

Re: who called my procedure through a database link [message #678183 is a reply to message #678155] Thu, 14 November 2019 10:27 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I agree. Thanks for the effort. I couldn't find out any way to do it either
Re: who called my procedure through a database link [message #678184 is a reply to message #678135] Thu, 14 November 2019 11:05 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You said 10g, is that both databases? If the remote database is 12.x you might do this with unified audit, which shows the audsid from the source database in unified_audit_trail.additional_info that should match the v$session.audsid in the source database.
Re: who called my procedure through a database link [message #678193 is a reply to message #678184] Fri, 15 November 2019 10:22 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Unfortunately it is between 10g and 8i

We are planning to upgrade the application (hopefully within the year) to Oracle 19
Previous Topic: problem to show result in join tables
Next Topic: Return TYpe (tablet) as output parameters.
Goto Forum:
  


Current Time: Thu Mar 28 15:12:20 CDT 2024