Search This Blog

2009-08-19

Run Procedure with additional priviledge

Following example show how to create a procedure which allow other users to run it with original user's privilege

CREATE OR REPLACE procedure PORTAL_DEV.test_db_links
AUTHID DEFINER
is
cursor links_to_test is
select db_link from user_db_links;
begin

for link in links_to_test
loop

begin
execute immediate 'select * from dual@' || link.db_link ;
execute immediate 'commit';
execute immediate 'ALTER SESSION CLOSE DATABASE LINK ' || link.db_link ;
dbms_output.put_line ( link.db_link || ' OK.');
exception
when others then
dbms_output.put_line ( link.db_link || ' Failed.');
end;

end loop;

end;
/
grant execute on PORTAL_DEV.test_db_links to health;

No comments: