Re: newbie: a couple of quick questions on design

Comments embedded

On Sun, 13 May 2001 08:52:15 +0100, "Steve Holland" <S.Holland_at_LineOne.net> wrote:

>Hi all,
>
>I am new to Oracle 8i, and I wondered if someone would be so kind as to
>answer a few quick questions. I have had previous experience with MSSQL7.
>I'm trying to get to grips with security and tablespaces.
>
>I'm creating a DB for a web application, it is a sort of library app in that
>it will require users to log in before they are able to have access to Word
>doc's/PDF's/JPG's etc. which I will be storing in the DB.
>
>So far I have created a new tablespace (which is where all my tables,
>procedures, triggers etc will be located)
Triggers and procedures are stored in the datadictionary, so in the tablespace SYSTEM

and a user who has this tablespace
>as their default, this user has DBA priveledges(create/edit/delete tables,
>triggers, procedures etc).

DBA privilege is way too powerful. Anyone with DBA can screw up almost everything. Connect and resource should be sufficient and even in that case you can argue whether that's not too much. DBA implies you can create *any* table/procedure/trigger, so you will end up with a database without security.

I am using this login as the equivilant to sa in
>MSSQL7,

the equivalent Oracle user is SYSTEM or SYS and both are way too powerful.

I will then create a second user who has restricted rights as user,
>i.e. what the Web app will use to login. Am I heading in the right direction
>with this or should I be doing this differently?
>
>As I mentioned above I wish to store Adobe Acrobat documents(PDF), MS Word
>Documents, Images (gif, jpg, png), XML Documents in the DB, which datatype
>should I use for these, I'd like to store all of these in the same column,
>is this practical? is it practical to store all of these in the DB, or
>should I just store external links to these?

Your choices are BFILE (will store pointers only) and BLOB (will store the complete document). I wouldn't set up my own 'pointer system'
>
>Any help would be gratefully received, I have a feeling that I will be
>asking quite a few questions over the next few weeks, until I get a bit more
>used to oracle, if anyone would be willing to take me under there wing so I
>can bounce a few questions/thoughts at them, it would be a great help.
>
>Cheers in Advance
>
>Steve
>

I can only give you two advices (to avoid you are going to do the same which *everyone* converting from sqlserver to Oracle does) - don't try to port your sqlserver habits to Oracle, try to learn Oracle!
- please read those manuals! They aren't provided for nothing you know   Getting Started
  Oracle Concepts
  Oracle Application Developers Guide
Your post betrays you already have started just hacking away! Taking you under someone's'wings' can *never* replace reading manuals.

Regards,

Sybrand Bakker, Oracle DBA Received on Sat May 12 2001 - 05:16:55 CDT