 | | | All_Source Question | All_Source Question 2007-10-01 - By Alvaro Jose Fernandez
Back
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
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas
-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
p
{mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman";}
address
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";
font-style:italic;}
pre
{margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.EstiloCorreo17
{mso-style-type:personal;
font-family:Arial;
color:windowtext;}
span.EstiloCorreo19
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@(protected) Section1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=ES link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>Sam,<o:p></o:p></span><
/font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span><
/font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'>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. <o:p></o:p></span></font>
</p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span><
/font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>Try granting the
developers the ALTER SESSION privilege (to switch schema using SET
CURRENT_SCHEMA)<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span><
/font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span><
/font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>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:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span><
/font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>CREATE OR REPLACE
TRIGGER "SYS"."DDL_TRIG_JOHNDOE" BEFORE<o:p></o:p></span><
/font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>CREATE<o:p></o:p>
</span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>OR DROP<o:p></o:p
></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>OR ALTER ON
DATABASE<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>WHEN (
ora_login_user = 'JOHNDOE' )<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>BEGIN<o:p></o:p><
/span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>  
;
IF ( ora_dict_obj_owner != 'JOHNDOE') ---John is not working on it own
schema....he switched to another one...<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>  
;
then<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>  
;
IF NOT ( ora_dict_obj_name like 'ALLOWED_OBJECTS%' ) then<o:p></o:p></span><
/font></p>
<p class=MsoNormal style='text-indent:35.4pt'><font size=2 color=navy
face="Courier New"><span lang=EN-US style='font-size:10.0pt;font-family:
"Courier New";
color:navy'>
raise_application_error(-20122,’Only ALLOWED_OBJECTS are allowed!!' );<o
:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>  
;
END IF;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'><o:p> </o:p>
</span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>  
;
IF ( ora_sysevent = 'DROP' ) then<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:35.4pt'><font size=2 color=navy
face="Courier New"><span lang=EN-US style='font-size:10.0pt;font-family:
"Courier New";
color:navy'>
raise_application_error(-20123,’John...you cannot DROP any objects
besides your own ones!!.' );<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>  
;
END IF;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>  
;
END IF;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face="Courier New"><span lang=EN-US
style='font-size:10.0pt;font-family:"Courier New";color:navy'>END;<o:p></o:p><
/span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span><
/font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>regards,<o:p></o:p></span
></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span><
/font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>alvaro<o:p></o:p></span><
/font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span><
/font></p>
</div>
</body>
</html>
|
|
|