topic Re: Creating synonym inside a procedure in Operating System - HP-UX

Creating synonym inside a procedure

Parthiban Dhananjeyan — Sun, 24 Nov 2002 01:53:42 GMT

I tried to create a private synonym(on another schema) with a procedure but it throws a"insufficient previliges" error. But when I execute the cmd in SQL prompt it works. Here is my procedure..

create or replace procedure grant_read
as
cursor T1cursor IS
select tname
from tab;
tname tab.tname%type;

begin

open T1cursor;

loop
FETCH T1cursor into tname;

exit when T1cursor%NOTFOUND;

execute immediate 'create synonym gisreader.' || tname || ' for ' || tname;
/* execute immediate 'grant select on ' || tname || ' to gisreader'; */

end loop;

close T1cursor;

end;

Thanx much for the help!!!!!!

Parthiban

Re: Creating synonym inside a procedure

Brian Crabtree — Sun, 24 Nov 2002 18:32:33 GMT

As the system or sys user, grant the "create synonym" privilege directly to the user. The reason for this is that procedures cannot inherit privileges from roles. It should work normally after that.

Brian

Re: Creating synonym inside a procedure

Kimathi Njeru — Mon, 25 Nov 2002 19:28:46 GMT

I may be mistaken but as far as i know, you cannot execute DDL statements inside a stored procedure without the use of the oracle supplied utility DBMS_SQL or the statement EXECUTE IMMEDIATE.

It is worth a check at http://metalink.oracle.com/
Hope this helps.
Kim