RE: All_Source Question

From: Alvaro Jose Fernandez <alvaro.fernandez_at_sivsa.com>
Date: Mon, 1 Oct 2007 23:36:25 +0200
Message-ID: <607D6181D9919041BE792D70EF2AEC482432F4@LIMENS.sivsa.int>

Sam,  

Is there a way to enable developers to view all source code for selected schemas without providing additional privileges? I'd like to avoid giving dba_source privileges.  

Try granting the developers the ALTER SESSION privilege (to switch schema using SET CURRENT_SCHEMA)    

Later, you can assure they cannot ALTER (alter trigger .... alter procedure...) objects other as intended (or any object if you need to), via an after logon trigger whose owner is SYS. For example:  

CREATE OR REPLACE TRIGGER "SYS"."DDL_TRIG_JOHNDOE" BEFORE CREATE OR DROP OR ALTER ON DATABASE WHEN ( ora_login_user = 'JOHNDOE' )

BEGIN         IF ( ora_dict_obj_owner != 'JOHNDOE') ---John is not working on it own schema....he switched to another one...

        then

                IF NOT ( ora_dict_obj_name like 'ALLOWED_OBJECTS%' )

then

               raise_application_error(-20122,'Only ALLOWED_OBJECTS are allowed!!' );

                END IF;

 

                IF ( ora_sysevent = 'DROP' ) then

                raise_application_error(-20123,'John...you cannot DROP

any objects besides your own ones!!.' );

                END IF;

        END IF;

END;   regards,  

alvaro  

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 01 2007 - 16:36:25 CDT