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
| CREATE OR REPLACE TRIGGER do_grant
after CREATE ON schema
declare
l_str varchar2(255);
l_job number;
begin
IF ( INSTR(ora_dict_obj_name,'EDI_') = '1' )
then
IF ( ora_dict_obj_type = 'TABLE' )
then
l_str := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to CADASTRE_CONSULT";';
dbms_job.submit( l_job, REPLACE(l_str,'"','''') );
l_str := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to CADASTRE_CONSULT_EDI";';
dbms_job.submit( l_job, REPLACE(l_str,'"','''') );
elsif ( ora_dict_obj_type = 'VIEW' )
then
l_str := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to CADASTRE_CONSULT";';
dbms_job.submit( l_job, REPLACE(l_str,'"','''') );
l_str := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to CADASTRE_CONSULT_EDI";';
dbms_job.submit( l_job, REPLACE(l_str,'"','''') );
end IF;
else
IF ( ora_dict_obj_type = 'TABLE' )
then
l_str := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to CADASTRE_CONSULT";';
dbms_job.submit( l_job, REPLACE(l_str,'"','''') );
elsif ( ora_dict_obj_type = 'VIEW' )
then
l_str := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to CADASTRE_CONSULT";';
dbms_job.submit( l_job, REPLACE(l_str,'"','''') );
end IF;
end IF;
end; |
Partager