yeppers, if you want to prevent ddl on tables when you are
required to give out the schema owner password, you put those triggers on all of
the objects. This assumes the user logging does NOT have alter any
trigger. The triggers are owned by a separate userid and look like
this(besides you can alter the trigger to let specific userids do the alter
without having to disable them).
create or replace trigger create_control_trigger before
create on <SCHEMA_NAME>.schema begin
raise_application_error(-20001,'NO CREATE DDL
ALLOWED'); end;/ create or replace trigger
drop_control_trigger before drop on
<SCHEMA_NAME>.schema begin
raise_application_error(-20001,'NO DROP DDL
ALLOWED'); end;/ create or replace trigger
alter_control_trigger before alter on
<SCHEMA_NAME>.schema begin
raise_application_error(-20001,'NO ALTER DDL
ALLOWED'); end;/
Joe>>> Alex.Hillman_at_usmint.treas.gov 07/05/01 12:03PM
>>>So what your DDL triggers are doing. And if they do not allow
DDL to workhow you can execute DDL - disabling these triggers?Alex
Hillman-----Original Message-----Sent: Thursday, July 05, 2001 8:00
AMTo: Multiple recipients of list ORACLE-Li try to play by the
same rule as you.but if i can't then i put the "ddl" triggers on the
tables owned by theschema owner, which keeps at least the strucutures from
being altered.joepaquette stephane wrote:> > Hi
all,> > I'm a fan of having the processing done by a user>
different than the owner of the data.> Am I alone ?> > For
example, we're on a datawarehouse system where the> data owner is DWH.
The etl tool repository owner is> TOOL_POWERMART and the reporting tool
repository owner> is TOOL_BOWEBI. The etl processing is done by
user> DWH_PM_TRTMNT and the reporting processing is done by> user
DWH_BO_TRTMNT.> > This way, nobody is connecting as the data's
owner.> The developpers and Informatica (Powermart) consultant>
would prefer working directly as DWH.> > What do you think
?> > =====> Stéphane Paquette> DBA Oracle,
consultant entrepôt de données> Oracle DBA, datawarehouse
consultant> stephane_paquette_at_yahoo.com> >
___________________________________________________________> Do You
Yahoo!? -- Pour faire vos courses sur le Net,> Yahoo! Shopping : <A
href="
http://fr.shopping.yahoo.com">
http://fr.shopping.yahoo.com>
--> Please see the official ORACLE-L FAQ: <A
href="
http://www.orafaq.com">
http://www.orafaq.com> -->
Author: =?iso-8859-1?q?paquette=20stephane?=> INET:
stephane_paquette_at_yahoo.com> > Fat City Network
Services -- (858) 538-5051 FAX: (858) 538-5051>
San Diego, California -- Public
Internet access / Mailing Lists>
--------------------------------------------------------------------> To
REMOVE yourself from this mailing list, send an E-Mail message> to:
ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in> the
message BODY, include a line containing: UNSUB ORACLE-L> (or the name of
mailing list you want to be removed from). You may> also send the
HELP command for other information (like subscribing)-- Joe
Testa Performing Remote DBA Services, need some backup DBA
support?For Sale: Oracle-dba.com domain, its not going cheap but feel free
toask :)-- Please see the official ORACLE-L FAQ: <A
href="
http://www.orafaq.com">
http://www.orafaq.com-- Author: Joe
Testa INET: teci_at_the-testas.netFat City Network
Services -- (858) 538-5051 FAX: (858) 538-5051San
Diego, California -- Public Internet
access / Mailing
Lists--------------------------------------------------------------------To
REMOVE yourself from this mailing list, send an E-Mail messageto:
ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list
you want to be removed from). You mayalso send the HELP command for
other information (like subscribing).--Please see the official ORACLE-L
FAQ:
http://www.orafaq.com--Author:
Hillman, Alex INET: Alex.Hillman_at_usmint.treas.govFat City
Network Services -- (858) 538-5051 FAX: (858)
538-5051San Diego, California --
Public Internet access / Mailing
Lists--------------------------------------------------------------------To
REMOVE yourself from this mailing list, send an E-Mail messageto:
ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list
you want to be removed from). You mayalso send the HELP command for
other information (like subscribing).
Received on Thu Jul 05 2001 - 10:13:54 CDT
