RE: sys vs. "normal" User

We had couple of similar cases in the past. Certain Sys privileges were required to be built into the Application design. I did some analysis and identified that it was best to grant the access using a routine call, instead of directly giving away the privilege to another schema. We did put in concrete verification and Check in the routine to ensure that the caller is authentic. Audit also in place. Oracle provides excellent options to put in Strong checks.

Again, most application I have come across have an application based Super user that do need query access on dict tables. We have given direct access on required views to such app schemas (also using catalog roles, customized roles), ensuring that the design takes care of the security aspect.

My way, go overboard and understand your application needs. Get actively involved with the designs and promotes to production, and ensure security is not compromised.

Others may come out with better options.

Thanks!
Amar

-----Original Message-----

From: "Koppelaars, Toon" <T.Koppelaars_at_centraal.boekhuis.nl> To: "Joerg.Jost_at_unitrade.com" <Joerg.Jost_at_unitrade.com> Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: 4/09/07 12:44 PM
Subject: RE: sys vs. "normal" User

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jost," Jörg Sent: dinsdag 4 september 2007 9:52
To: oracle-l_at_freelists.org
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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Sep 04 2007 - 00:36:35 CDT