Database trigger advice

From: Jan Work <janwork2000_at_yahoo.com.au>
Date: Tue, 10 Aug 2004 09:45:07 +1000 (EST)
Message-ID: <20040809234507.55056.qmail@web52407.mail.yahoo.com>

Dear list,
I have a request recently where our business owner wishes to access tables that dynamic were created by the application. He wishes to access these tables outside of the application. Currently, he would inform me when he initiates the process which generates these tables via the application and I would grant him select access and create a synonym under his schema via sqlplus.  

To automate this process, I have recently found a script on the web to do this and have since modified it to suit my need. I have tested in our test environment and all look to be working as expected. Prior to place this trigger in the production environment I would like to seek the list opinion, recommendation or enhancement or unknown issue I need to consider. My database is Oracle 9.2.0.4.  

Many thanks in advance.
Jan  

sqlplus schema_owner/$schema_owner<<EOF1  create or replace trigger schema_owner.grant_tables  after CREATE on schema
 declare

     pragma autonomous_transaction;
     l_str varchar2(255);
     l_job number;
 begin
     if ( ora_dict_obj_type = 'TABLE' )
     then
         l_str := 'execute immediate "grant select on ' ||
                                      ora_dict_obj_name ||


                                     ' to user1";';

         dbms_job.submit( l_job, replace(l_str,'"','''') );
         l_str := 'execute immediate "create synonym user1.' ||
                                      ora_dict_obj_name ||


                                     ' for schema_owner.'||ora_dict_obj_name||'";';

         dbms_job.submit( l_job, replace(l_str,'"','''') );
       end if;
       commit;

 end;
/
EOF1

---

Find local movie times and trailers on Yahoo! Movies.

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Aug 09 2004 - 20:11:17 CDT