Re: Access to temp tables created dynamically

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@xxxxxxxxx> 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.
>
>

Other related posts: