Execute procedure in other schema...

[athom]

User ABC execute a DEF.procedure

DEF.procedure : SELECT * FROM document;

User ABC need the "EXECUTE ON DEF.procedure" GRANT, that's ok.

But is it possible that "document" in DEF.procedure refere at ABC.document, if it's him who execute the procedure and refere the DEF.document if it's him who execute the procedure ?

At moment, if any user has the "EXECUTE ON DEF.procedure" GRANT, the document refere always to DEF.document ...

I tryed with Synonym and View to have a ABC.document....

Thanks, and scuse my bad english

Athom

 

[did02]

If your procedure is executed only by ABC, you can try with synonyms. Otherwise, to my mind, the easier way to do that is to set a parameter in the procedure to know in which scheme it must executes the query .

To execute the procedure
execute DEF.procudure(ABC);

And your procedure looks like :

create or replace procedure procedure (schema varchar2) is
V_CURSOR NUMBER;
SCHAINE VARCHAR2(300);
V_NUM_ROWS INTEGER;

BEGIN

V_CURSOR := DBMS_SQL.OPEN_CURSOR;
SCHAINE := 'select * from '||schema||'.document ;'
DBMS_SQL.PARSE(V_CURSOR, SCHAINE , DBMS_SQL.V7);
V_NUM_ROWS := DBMS_SQL.EXECUTE(V_CURSOR);
DBMS_SQL.CLOSE_CURSOR(V_CURSOR);

END;
END procedure

Good luck.
Did

 

[jee]

If I am understanding your problem, what you want to do is execute the procedure with "Invoker rights", rather than with "Definer rights". If a procedure executes with "Invoker" rights, any reference to an object that is unqualified with a schema will be resolved within the invoker's schema.

To make a procedure execute with Invoker rights, build it this way:

CREATE PROCEDURE def AUTHID CURRENT_USER AS
   var varchar2 (100);
BEGIN
   Select title from document into var;
   .
   .
   .
END;

If user XYZ creates this procedure, but user ABC executes it, the file read will be ABC.document.

 

[athom]

thanks

athom