Hi,
If you are using Oracle8i, there is a way to do this.
Review the following post:
On Thu, 6 Jan 2000, Joseph Testa wrote:
> Why would you want to do that, well, i'm at a place where the developers
> have the schema owner password but we DBAs(being retentitive as we
are)dont
> want them to make any ddl changes.
>
>
> here are the steps:
>
> Feel free to change the names to your liking
>
> create user schema_control identified by <passwd>
> grant create any trigger to schema_control;
>
> edit the 3 following triggers, changing the <SCHEMA_NAME> to the schema
you
> want to put the control on:
>
> 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;
> /
>
>
> revoke alter any trigger from <SCHEMA_NAME>;
>
> the previous line is needed so they dont alter the trigger to disable it
:)
>
> the next time they attempt to create, drop or alter anything in that
schema
> they get error msg.
>
> This all assumes they are not dba :)
>
> hht, joe
>
Regards,
- Kirti Deshpande
Verizon Information Services
http://www.superpages.com
> -----Original Message-----
> From: Brijesh Satdev [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, June 14, 2001 12:11 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Product user profile & 3rd patry product
>
> Hi all,
>
> We have a third party product namely a drafting utility called
> microstation
> which requires connectivity to some schema in the database.
>
> It is neccesary for the database user to have create table system
> priviledge, but I want to restrict end users from deleting tables from
> this
> schema through microstation (there is a in built sql editor in
> microstation).
>
> I checked the product_user_profile table and it only seems to work for
> Sql*plus.
>
> Is there some other way to control the situation, as this poses a very
> high
> security risk for the database.
>
> I appreciate your time and effort.
>
> Regards,
> Brijesh.
> DBA Rolta India Ltd.
> OCP Oracle8 DBA.
>
> --
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
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).
