Oracle stored procedure DB Link question

Question

I have a stored procedure FOO belonging to user A. User B has EXECUTE permission on FOO.

One of the procedures in FOO uses a DB Link to retrieve data from BARTABLE@REMOTEDB. A has a DB Link to REMOTEDB connecting as user AREMOTE while B has a DBLink with the same name connecting to REMOTEDB but as user BREMOTE.

When B executes FOO, will it use A's DB Link (connecting as AREMOTE) or B's DB Link (Connecting as BREMOTE)?

I ask because I assumed a procedure belonging to a particular user would only have access to that user's dblinks even if another user had EXECUTE on that procedure. But somehow this procedure is working properly even though the user AREMOTE does not have SELECT on BARTABLE@REMOTEDB, so I'm confused.

REW · Accepted Answer · 2010-06-30 20:16:26Z

2

The procedure should use the dblink that is defined for A's link. Any oracle procedure uses the rights of the owner to process, except where that procedure has been defined with "Invoker's Rights". Invoker's Rights

Invoker's rights defined for a procedure tells the code to use the rights and objects local to the calling user, and not the code owner. The reason that this is not done by default is typically an end user will have no rights to any hard objects for direct update, but be given proper access through an API, which would apply the correct business logic to updates, inserts and deletes, and then these objects would be exposed as accessible to the end user.

answered Jun 30, 2010 at 20:16

REW

1,2579 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle stored procedure DB Link question

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related