1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
| CREATE OR REPLACE procedure CMD_GRANT
IS
cursor curcmd is
select command
from tabcmd;
BEGIN
for rec in curcmd loop
EXECUTE IMMEDIATE rec.command;
end loop;
END CMD_GRANT;
/
CREATE OR REPLACE TRIGGER TA_CREATE
AFTER CREATE
ON IMPUSR.SCHEMA
DECLARE
-- variables de reception
v_type VARCHAR2(20);
v_name VARCHAR2(50);
v_owner VARCHAR2(30);
v_ordre VARCHAR2(150);
v_ref_owner VARCHAR2(30) := 'IMPUSR';
v_ref_type VARCHAR2(30) := 'TABLE';
nres number;
BEGIN
SELECT SUBSTR(ora_dict_obj_name,1,50), LTRIM(RTRIM(ora_dict_obj_type)),
LTRIM(RTRIM(ora_dict_obj_owner))
into v_name, v_type, v_owner
from dual;
delete from tabcmd;
v_ordre := 'GRANT SELECT ON ' || v_owner || '.' || v_name || ' TO PUBLIC';
insert into tabcmd
values (v_ordre);
CMD_GRANT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/ |
Partager