 | | | sys vs. "normal " User | sys vs. "normal " User 2007-09-04 - By Niall Litchfield
Back
I'd agree with Toon , with the additional step of revoking create session (
unless you decide to create/use a dedicated dba utility account for this
sort of purpose) when you are done. As described this account does not need
to be able to create sessions on an ongoing basis.
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
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
I'd agree with Toon , with the additional step of revoking create session (
unless you decide to create/use a dedicated dba utility account for this sort
of purpose) when you are done. As described this account does not need to be
able to create sessions on an ongoing basis.
<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>Niall Litchfield<br>Oracle DBA<br><a href="http://www
.orawin.info">
http://www.orawin.info</a>
|
|
|