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

>>> [EMAIL PROTECTED] 07/05/01 12:03PM >>>
So what your DDL triggers are doing. And if they do not allow DDL to work
how you can execute DDL - disabling these triggers?

Alex Hillman

-----Original Message-----
Sent: Thursday, July 05, 2001 8:00 AM
To: Multiple recipients of list ORACLE-L


i 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 the
schema owner, which keeps at least the strucutures from being altered.

joe
paquette 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
> [EMAIL PROTECTED]
>
> ___________________________________________________________
> Do You Yahoo!? -- Pour faire vos courses sur le Net,
> Yahoo! Shopping : http://fr.shopping.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?paquette=20stephane?=
>   INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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 to
ask :)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Joe Testa
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hillman, Alex
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Reply via email to