Hello,
i need a trigger which grants permissions to views.
I have the following trigger/procedure for TABLES- that works:
[code]
create or replace PROCEDURE myddl
(p_ddl IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_ddl;
END;
[/code]
[code]
create or replace TRIGGER gfc_grant
AFTER CREATE ON REEWSTAGE.schema
DECLARE
l_jobno NUMBER;
BEGIN
IF (ora_dict_obj_type = 'TABLE') THEN
dbms_job.submit(l_jobno,'myddl(''GRANT SELECT ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO USER'');');
END IF;
END;
[/code]
Bur i need it for tables and for views - so i tried:
[code]
create or replace TRIGGER gfc_grant
AFTER CREATE ON REEWSTAGE.schema
DECLARE
l_jobno NUMBER;
BEGIN
IF (ora_dict_obj_type = 'TABLE' or ora_dict_obj_type = 'VIEW') THEN
dbms_job.submit(l_jobno,'myddl(''GRANT SELECT, INSERT, UPDATE, ALTER, DELETE ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO USER'');');
END IF;
END;
[/code]
It works for tables but not for views. Someone an idea why and can help please?
Thank you very much
Best regards
George