Re: Access to temp tables created dynamically

From: (wrong string) 조동욱 <>
Date: Sat, 7 Mar 2009 22:00:56 +0900
Message-ID: <>

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;


  • 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;


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

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


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 (english) (korean)

On Fri, Mar 6, 2009 at 3:18 AM, Ram Raman <> 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.

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

Original text of this message