OraFAQ Forum: SQL & PL/SQL » who called my procedure through a database link

Home » SQL & PL/SQL » SQL & PL/SQL » who called my procedure through a database link (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

who called my procedure through a database link [message #678135]

Tue, 12 November 2019 13:18

Bill B
Messages: 1968
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.

Report message to a moderator

Re: who called my procedure through a database link [message #678136 is a reply to message #678135]

Tue, 12 November 2019 14:35

Michel Cadot
Messages: 67234
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?

Report message to a moderator

Re: who called my procedure through a database link [message #678137 is a reply to message #678136]

Tue, 12 November 2019 21:28

Bill B
Messages: 1968
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

Report message to a moderator

Re: who called my procedure through a database link [message #678141 is a reply to message #678137]

Wed, 13 November 2019 01:23

Michel Cadot
Messages: 67234
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.

Report message to a moderator

Re: who called my procedure through a database link [message #678152 is a reply to message #678141]

Wed, 13 November 2019 05:40

Bill B
Messages: 1968
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.

Report message to a moderator

Re: who called my procedure through a database link [message #678155 is a reply to message #678152]

Wed, 13 November 2019 07:21

Michel Cadot
Messages: 67234
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?

Report message to a moderator

Re: who called my procedure through a database link [message #678183 is a reply to message #678155]

Thu, 14 November 2019 10:27

Bill B
Messages: 1968
Registered: December 2004

Senior Member

I agree. Thanks for the effort. I couldn't find out any way to do it either

Report message to a moderator

Re: who called my procedure through a database link [message #678184 is a reply to message #678135]

Thu, 14 November 2019 11:05

John Watson
Messages: 8294
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.

Report message to a moderator

Re: who called my procedure through a database link [message #678193 is a reply to message #678184]

Fri, 15 November 2019 10:22

Bill B
Messages: 1968
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

Report message to a moderator

Previous Topic:	problem to show result in join tables
Next Topic:	Return TYpe (tablet) as output parameters.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 07 21:13:53 CDT 2020