 | | | sys vs. "normal " User | sys vs. "normal " User 2007-09-04 - By Clarke, Andrew
Back
I confess to having created objects in the SYS schema in the past but I think
this was wrong, and I wouldn't do it again. There is a philosophical reason
why: everything in the SYS and SYSTEM schemas ought to be Oracle sourced.
There is also a practical reason: it makes exporting and importing your
application harder.
My preferred solution would be to create a new user whose password is as
tightly controlled as SYS. Have SYS grant the necessary privileges to that
user and then that user can build the procedure in its schema and grant it to
the general users. It is important to keep this new user tightly controlled
simply to prevent misuse of those granted SYS privileges, which can be quite
powerful.
Cheers, APC
-- --Original Message-- --
From: oracle-l-bounce@(protected) on behalf of Jost," J?rg
Sent: Tue 04/09/2007 08: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
This e-mail and any attachment is for authorised use by the intended recipient
(s) only. It may contain proprietary material, confidential information and/or
be subject to legal privilege. It should not be copied, disclosed to, retained
or used by, any other party. If you are not an intended recipient then please
promptly delete this e-mail and any attachment and all copies and inform the
sender. Thank you.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859 (See http://iso-8859.ora-code.com)-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7650.28">
<TITLE>RE: sys vs. "normal" User</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<BR>
<P><FONT SIZE=2> I confess to having created objects in the SYS schema in
the past but I think this was wrong, and I wouldn't do it again. There is
a philosophical reason why: everything in the SYS and SYSTEM schemas ought to
be Oracle sourced. There is also a practical reason: it makes exporting
and importing your application harder.<BR>
<BR>
My preferred solution would be to create a new user whose password is as
tightly controlled as SYS. Have SYS grant the necessary privileges to
that user and then that user can build the procedure in its schema and grant it
to the general users. It is important to keep this new user tightly
controlled simply to prevent misuse of those granted SYS privileges, which can
be quite powerful.<BR>
<BR>
Cheers, APC<BR>
<BR>
<BR>
-- --Original Message-- --<BR>
From: oracle-l-bounce@(protected) on behalf of Jost," J?rg<BR>
Sent: Tue 04/09/2007 08:52<BR>
To: oracle-l@(protected)<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>
</FONT>
</P>
<br><br>
<P align=center><FONT style="BACKGROUND-COLOR: #ffffff">This e-mail and any
attachment is for authorised use by the intended recipient(s) only. It may
contain proprietary material, confidential information and/or be subject to
legal privilege. It should not be copied, disclosed to, retained or used by,
any other party. If you are not an intended recipient then please promptly
delete this e-mail and any attachment and all copies and inform the sender.
Thank you.</FONT></A></P>
</body>
</HTML>
|
|
|