Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Here's a poser...any takers???

Re: Here's a poser...any takers???

From: Paul de Anguera <nospam_at_quidnunc.net>
Date: Fri, 10 Sep 1999 06:21:19 GMT
Message-ID: <7ra867$3t1$3@news.chatlink.com>


In article <Jn%z3.671$_61.104781_at_news.uswest.net>, "Kurt" <rkdata_at_uswest.net> wrote:
>Here's the scenario for Oracle 8i and/or WebDB on an NT4 server:
>
>10 different companies say that want to use the database to store their
>sales. No other company should be able to access it.
>
>Each company wants one person to act as a MANAGER. MANAGERs have access to
>the sales of those whom report to them. The MANAGERs should also be able to
>add SALESMAN as their respective companies grow.
>
>Each SALESMAN should only see his own sales.
>
>3 of the companies have a SALESMAN named "John Smith".
>
>Any recommendations/comments for setting up logins/security? How about the
>scenario itself? How do I keep John Smith from Company A separate from John
>Smith of Company B (apparently he's a boogie woogie bugle boy salesman). Am
>I making a mountain out of a mole hill? All the documentation I find seems
>to rely on the fact that it's only ONE company.

I don't feel comfortable with building the business rules into the application code / web page if you can do it in the data model and DDL. Let me take a shot at this:

COMPANY <-|-----o->> EMPLOYEE <-|-----o->> TRANSACTION COMPANY columns:
compid PK
compname

I don't see any problem with multiple companies! It isn't clear what the requirement about other companies not accessing the database means. Companies don't access data, users do. Just don't grant access to PUBLIC and you should be covered.

EMPLOYEE columns:
emplid PK
emplname
reports_to_emplid FK
compid FK

We deliver extra value here, because there can be a hierarchy of managers managing managers and they can also sell directly. There's a hierarchical extension to Oracle's SELECT command that lets you pull off an indented list of the tree. (See the typical SELECTs from the PLAN_TABLE for an example of how to code it.)

TRANSACTION columns:
seqno PK
compid FK
emplid FK
whatever else ... add columns of actual data to these tables as needed, I just put in the bare skeleton. Constraints per the ERD I started with would be nice. My system enforces them at the app. level and I confess I don't remember the DDL to do them in the database. Need some help here, folks!

OK, let's say the employee ID is also the Oracle user ID. userid is a pseudocolumn we can include in any SELECT; Oracle will supply the Oracle userid in its place at run time. (Crude, yes, but superior to identifying employees by their names!)

CREATE ROLE SALESMAN; Everybody's a salesman, including managers. So we could call this role EMPLOYEE but that might limit some future enhancement creating more kinds of employees.

GRANT SALESMAN TO user1, user2, user3 ... ;

CREATE VIEW SALES_TRANSACTION AS
SELECT * FROM TRANSACTION t WHERE
emplid = userid
OR
emplid IN (
SELECT emplid FROM EMPLOYEE e WHERE
e.reports_to_emplid = userid);

This lets managers tinker with their own sales as well as their subordinates'. Ordinary mortals, to whom nobody else reports, can only tinker with their own sales.

GRANT VIEW SALES_TRANSACTION TO SALESMAN; CREATE VIEW MGR_EMPLOYEE AS
SELECT * FROM EMPLOYEE WHERE
reports_to_emplid = userid
AND
not (emplid = userid);

The last part keeps a manager from claiming he reports to himself in order to change data about himself. This needs more work, since nobody can change data about the top manager.

GRANT SELECT, INSERT, UPDATE, DELETE ON MGR_EMPLOYEE TO SALESMAN; You could have a separate MANAGER role, but it seems unnecessary -- maybe you could use it to deal with the problem I noted above. Create the database in a schema that is not any of the employees' userids and you should be pretty close to the scenario we started with.

Paul de Anguera | "You can't write a chord ugly enough to say
Reply to:       | what you want to say sometimes, so you have to
deanguer@       | rely on a giraffe filled with whipped cream."
quidnunc.net    | - Frank Zappa
Received on Fri Sep 10 1999 - 01:21:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US