Re: How can I create an object in one user's schema using a stored procedure owned by another user?

Hi,

Is this the thing you want?

connect internal

grant create any synonym to tester;

connect tester/tester

create or replace procedure create_synonym(o varchar2) is  s varchar2(200);
 i integer;
begin
 s:='create synonym "'||user||'"."'||o||'" for "'||o||'"';  i:=dbms_sql.open_cursor;
 dbms_sql.parse(i,s,dbms_sql.v7);
 dbms_sql.close_cursor(i);
exception when others then
 if dbms_sql.is_open(i) then dbms_sql.close_cursor(i); end if;  raise;
end;
/

grant execute on create_synonym to safemaker;

connect safemaker/safemaker

drop synonym create_synonym;
create synonym create_synonym for tester.create_synonym;

execute create_synonym('LAGA_NESTED1');
execute create_synonym('LAGA_NESTED2');
execute create_synonym('LAGA_NESTED3');

Andrew Protasov

> The subject says it all!
>
> I would like create some synonyms in user B's schema from a procedure
> owned by user A. However, when user B executes user A's procedure, it
> executes in user A's schema, so the synonym is created there, and user B
> doesn't get the synonym.
>
> RDBMS version is 7.3.3.0.
>
> All suggestions for getting around this will be gratefully received...
>
>
Received on Wed Nov 25 1998 - 04:00:19 CST