Re: How to structure a database?

Sounds like we are talking about 2 different levels of security: Application and Database:

It depends on how your users are going to connect to Oracle:

1. Users connect through App: 1.1 Connect as same user(PROD, I assume this is your case) The app should take care of security issue and sounds like it did: "Those users are assigned to a number of projects, and can freely switch between those projects, which they have been granted access to." 1.2 Connect as individual user (this will be the same issue as the users connect to db directly)
2. Users connect db using Oracle tools(SQL*Plus...) or through app but using their own login(you have 60+ users in the db) In this case, you'll need set up roles: projectA(create,update,delete projectA tables), ProjectB.... and assign those roles to the users.
3. Combination of previous 2 methods.

If your app smart enough, it should take care of the user security issue(something like Oracle Applications(Financials,Manufacturing....) handle the security), and the only thing you need worry about is direct connection. If this is not the case, some work need to be done to find out how your app make the connection/"switch projects" and enable/disable roles for the session

Hope this will help, Kurt.

In article <37b6e7e9.68540976_at_news.inet.tele.dk>,   hemmer_at_postREMOVESPAM.tele.dk wrote:
> We have just installed Oracle 8i on a AIX 4.3.2 server, and
> are now considering how to structure our database.
>
> We have about 60 users who will be using an application that
> interacts with Oracle. Those usere are assigned to a number
> of projects, and can freely switch between those projects,
> which they have been granted access to. Projects vary from
> lasting a few days to several years. Each project will typically
> hold between 3 to 10 tables of data of only a few thousand
> records.
>
> As I'm new to Oracle (but learning fast), I'm trying to
> figure out, what would be the best way to map this situation,
> into Oracle - Schemas, Users, Roles, Tablespaces.
>
> I would like to have each project reside in it's own
> tablespace, and create a role for the ~60 users that
> will be accessing those projects. A user assigned to a project
> would never be allowed to create tables, but should be allowed
> to create, update and delete records in tables created by
> our development department.
>
> In order to implement this, I'm unsure of how to structure
> this within Oracle. Could some kind soul please enlighten
> me of how to structure this, and a short explanation on the
> steps involved - that would really help - thanks!
>
> PS: What confuses me, is the fact that tables are created
> within Schema's and tablespaces, which in turn are
> user-assigned not project-related!?
>
> +---------------------------------------------------+
> | Schema: PROD |
> | |
> | +--------------+ |
> | | Role: USER | |
> | +-------+------+ |
> | | |
> | +--------------+--------------+ |
> | | | | |
> | +-----V-----+ +------V-----+ +------V-----+ |
> | |Project: A | | Project: B | | Project: C | |
> | |Tblspace: A| | Tblspace: B| | Tblspace: C| |
> | +-----------+ +------------+ +------------+ |
> | |
> +---------------------------------------------------+
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Aug 16 1999 - 11:46:18 CDT