Re: Access to temp tables created dynamically

From: (wrong string) 조동욱 <ukja.dion_at_gmail.com>
Date: Sat, 7 Mar 2009 22:00:56 +0900
Message-ID: <43c2e3d60903070500o67860a85y4e0317734ac4cd7f_at_mail.gmail.com>

You're stuck in the very bad desgin pattern, aren't you? :)

Anyway, following tricks would help.

• as sys user drop trigger ddl_trigger_test;

drop table t_log purge;

create table t_log(log varchar2(4000));

• create after create ddl trigger create or replace trigger ddl_trigger_test after create on database begin if ora_dict_obj_owner = 'UKJA' and ora_dict_obj_type = 'TABLE' then insert into t_log values('grant select on ' || ora_dict_obj_owner || '.' || ora_dict_obj_name || ' to ukja2'); end if;

  /* *-- this does not work, ORA-30511*
  if ora_dict_obj_owner = 'UKJA' and ora_dict_obj_type = 'TABLE' then     execute immediate 'grant select on ' || ora_dict_obj_owner || '.' ||

            ora_dict_obj_name || ' to ukja2';   end if;
  */

end;
/

• stored procedure executed every 5 sec create or replace procedure execute_grant is begin

  for r in (select * from t_log) loop

• execute immediate r.log;* end loop;

  delete from t_log;
  commit;

end;
/

• create *background job* to execute execute_grant procedure var job_no number;

begin
  *dbms_job.submit*(:job_no, 'execute_grant;', interval=>'sysdate+(* 1/24/60/12*)');
end;
/

commit;

Now, whenever ukja user creates table, background job process would execute grant statement and ukja2 user would be granted to access the ukja's table.

---

Dion Cho - Oracle Performance Storyteller

http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)

---

On Fri, Mar 6, 2009 at 3:18 AM, Ram Raman <veeeraman_at_gmail.com> wrote:

>
> Because of Sox requirements we are moving away from generic userids to
> individual ids. We have Peoplesoft applications that create tables on the
> fly. Developers want access to those tables that will be created on the fly,
> in case the process that creates it ends abnormally. I looked into granting
> via DDL triggers, it seemed like granting access via them is a problem. Is
> there a way to grant access other than doing "grant select any". Thanks.
>
> version: 10.2
>
> TYA.
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Mar 07 2009 - 07:00:56 CST