 | | | sys vs. "normal " User | sys vs. "normal " User 2007-09-04 - By Stefan Knecht
Back
Hi
There's several issues I've ran into due to such objects being created.
What comes to mind is Full Export / Import. SYS doesn't get exported,
therefore a lot of stuff will be invalid when importing, because of possible
dependencies on objects that just weren't imported.
When doing database upgrades, the dictionary objects your code relies on may
change. This can't be prevented by putting the stuff in another user, but at
least you won't have corruption in SYS. I've run into several databases at
clients where some variants of custom V$ views and procedures were created,
that during an upgrade would point to no-longer-existant x$ objects, and
therefore raise nasty ORA-600 (See http://ORA-600.ora-code.com)'s.
And as Toon has already nicely put "it's just not done" :-)
Just my 2 cents
On 9/4/07, Koppelaars, Toon <T.Koppelaars@(protected)> wrote:
>
> Jorg,
>
> It wouldn't be my choice to create custom procedures in the SYS-schema.
> Other than "it's just not done" I am unaware of "something bad" when doing
> so.
>
> Instead I would create a new (third) schema. Grant it the create session
> and create procedure system privileges, and grant it the necessary object
> priviges on the SYS objects you mention.
> Then you can create your procedure in this schema, and finally grant
> execute on your procedure to the application schema.
>
> Toon
>
>
> -- --Original Message-- --
> From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]
> On Behalf Of Jost," J?rg
> Sent: dinsdag 4 september 2007 9:52
> To: oracle-l@(protected)
> Subject: sys vs. "normal" User
>
> Hello List,
>
> as often, there is a discussion between our developers and me, the
> dba ;-)
>
> Our application connects to Oracle via SQLNet as a normal User. Every
> application client connects as the same user, so there are many
> connections with the same username in v$session.
>
> At some important points this application locks rows with dbms_lock.
>
> The lockname is the rowid of the row. Sometimes an evil user stays
> forever at this row and other users are unable to change it.
>
> This case in mind, i have written a small procedure, which get the
> Primary Key of the locked rows and shows it via dbms_output.
>
> Because of the Tables/Views i need to query, this procedure belongs to
> SYS.
>
> My question is, is there something bad to install procedures as sys and
> grant the procedure to the application user? Is there a "Dogma" that
> says, never create or install self written packages as sys?
>
> Should i grant select on the underlying Tables/Views instead?
>
> The Objects i query are:
>
> dbms_lock_allocated
> dba_locks
> v$session
>
> Also this objects, which are no problem because they exists also for the
> normal user:
>
> dba_cons_columns
> dba_constraints
> dba_objects
>
> Thx in advance
>
> J?rg
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
--
=========================
Stefan P Knecht
Consultant
Infrastructure Managed Services
Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg
Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27
stefan.knecht@(protected)
http://www.trivadis.com
OCP SCSA SCNA
=========================
Hi<br><br>There's several issues I've ran into due to such objects
being created. <br><br>What comes to mind is Full Export / Import. SYS doesn&
#39;t get exported, therefore a lot of stuff will be invalid when importing,
because of possible dependencies on objects that just weren't imported.
<br>When doing database upgrades, the dictionary objects your code relies on
may change. This can't be prevented by putting the stuff in another user,
but at least you won't have corruption in SYS. I've run into several
databases at clients where some variants of custom V$ views and procedures were
created, that during an upgrade would point to no-longer-existant x$ objects,
and therefore raise nasty ORA-600 (See http://ORA-600.ora-code.com)'s.
<br><br>And as Toon has already nicely put "it's just not done" :
-)<br><br>Just my 2 cents<br><br><br><br><div><span class="gmail_quote">On 9/4
/07, <b class="gmail_sendername">Koppelaars, Toon</b> <<a href="mailto:T
.Koppelaars@(protected)">
T.Koppelaars@(protected)</a>> wrote:</span><blockquote class="gmail
_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0
.8ex; padding-left: 1ex;">Jorg,<br><br>It wouldn't be my choice to create
custom procedures in the SYS-schema. Other than "it's just not done
" I am unaware of "something bad" when doing so.
<br><br>Instead I would create a new (third) schema. Grant it the create
session and create procedure system privileges, and grant it the necessary
object priviges on the SYS objects you mention.<br>Then you can create your
procedure in this schema, and finally grant execute on your procedure to the
application schema.
<br><br>Toon<br><br><br>-- --Original Message-- --<br>From: <a href="mailto
:oracle-l-bounce@(protected)">oracle-l-bounce@(protected)</a> [mailto:<a
href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce@(protected)
</a>] On Behalf Of Jost," J?rg<br>Sent: dinsdag 4 september 2007 9:52<br
>To: <a href="mailto:oracle-l@(protected)">oracle-l@(protected)</a><br
>Subject: sys vs. "normal" User<br><br>Hello List,<br><br>as often,
there is a discussion between our developers and me, the
<br>dba ;-)<br><br>Our application connects to Oracle via SQLNet as a normal
User. Every<br>application client connects as the same user, so there are many
<br>connections with the same username in v$session.<br><br>At some important
points this application locks rows with dbms_lock.
<br><br>The lockname is the rowid of the row. Sometimes an evil user stays<br
>forever at this row and other users are unable to change it.<br><br>This case
in mind, i have written a small procedure, which get the<br>Primary Key of the
locked rows and shows it via dbms_output.
<br><br>Because of the Tables/Views i need to query, this procedure belongs to
<br>SYS.<br><br>My question is, is there something bad to install procedures as
sys and<br>grant the procedure to the application user? Is there a "Dogma
" that
<br>says, never create or install self written packages as sys?<br><br>Should i
grant select on the underlying Tables/Views instead?<br><br>The Objects i query
are:<br><br>dbms_lock_allocated<br>dba_locks<br>v$session<br>
<br>Also this objects, which are no problem because they exists also for the<br
>normal user:<br><br>dba_cons_columns<br>dba_constraints<br>dba_objects<br><br
>Thx in advance<br><br>J?rg<br><br>--<br><a href="http://www.freelists.org
/webpage/oracle-l">
http://www.freelists.org/webpage/oracle-l</a><br><br><br><br>--<br><a href=
"http://www.freelists.org/webpage/oracle-l">http://www.freelists.org/webpage
/oracle-l</a><br><br><br></blockquote></div><br><br clear="all"><br>--
<br>=========================<br><br>Stefan P Knecht<br>Consultant<br
>Infrastructure Managed Services<br><br>Trivadis AG<br>Europa-Strasse 5<br>CH
-8152 Glattbrugg<br><br>Phone +41-44-808 70 20<br>Fax +41-808 70 12<br>Mobile
+41-79-571 36 27
<br><a href="mailto:stefan.knecht@(protected)">stefan.knecht@(protected)</a>
<br><a href="http://www.trivadis.com">http://www.trivadis.com</a><br><br>OCP
SCSA SCNA<br>=========================
|
|
|