Hi, everybody!
I'm trying the above example extracted from metalink.
This is what happen to my db:
- Create table scott.test
- Trigger fire (insert into event_table)
- Trigger call grant_job
- Trigger call grant_proc
- no grants succeded.
I try call manually
begin grant_job('grant_proc'); end;
/
but no grants succeded.
I try
begin grant_proc; end;
/
and it works.
Any ideas?
Thank you.
Marco Zamuner
Italy
drop table scott.event_table;
create table scott.event_table
(ora_dict_obj_owner varchar2(30), ora_dict_obj_name varchar2(30));
create or replace PROCEDURE scott.grant_proc AS
own VARCHAR2(30);
nam VARCHAR2(30);
v_cur INTEGER;
cursor pkgs is
select ora_dict_obj_owner, ora_dict_obj_name from scott.event_table;
BEGIN
open pkgs;
loop
fetch pkgs into own, nam;
exit when pkgs%notfound;
v_cur := dbms_sql.open_cursor;
dbms_sql.parse(v_cur,'grant SELECT, INSERT, UPDATE, DELETE on
'||own||'.'||nam|| ' to R1', dbms_sql.native);
dbms_sql.close_cursor(v_cur);
commit;
delete from scott.event_table;
commit;
end loop;
end;
/
create or replace procedure grant_job(procname varchar2) as
jobid number := 0;
procnm varchar2(30);
begin
ProcNm := 'Begin '||ProcName||'; End;';
dbms_job.submit(jobid, procnm);
commit;
end;
/
CREATE or REPLACE TRIGGER scott.role_update
AFTER CREATE on scott.schema
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF( ora_sysevent='CREATE' and ora_dict_obj_type = 'TABLE') THEN
insert into scott.event_table values (ora_dict_obj_owner,
ora_dict_obj_name);
grant_job('grant_proc');
END IF;
END;
/
Received on Fri Jun 25 2004 - 08:27:09 CDT
