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

Home -> Community -> Usenet -> c.d.o.server -> Re: can this be done in Oracle?

Re: can this be done in Oracle?

From: Ethan Post <epost1_at_my-deja.com>
Date: 2000/05/13
Message-ID: <8fkl5p$n2a$1@nnrp1.deja.com>#1/1

Tim,

I don't really understand the problem but I am pretty sure you are not implementing the best solution for it. 50 users each with there own spending table seems to be a very bad idea, why wouldn't you just add a column to the spending table called state and just have one?

-e

In article <391dbae3.0_at_news.dca.net>,
  "Tim Romano" <tim_at_dca.net> wrote:
> Here's what I want to do, but am having trouble doing:
>
> 1.
> CREATE USER FED IDENTIFIED BY FED
>
> CREATE USER NJ IDENTIFIED BY NJ
> CREATE USER PA IDENTIFIED BY PA
> CREATE USER MD IDENTIFIED BY MD
> etc etc .... that is, a "Federal" user and a user for each state in
 the
> country.
>
> 2. User FED creates a table: BUDGET_CODES and GRANTS SELECT and
 REFERENCES
> on this table to each State user.
>
> Now, each State user creates in its own schema a table called
 SPENDING;
> thus, there are tables called PA.SPENDING, NJ.SPENDING, MD.SPENDING
 etc.
>
> CREATE TABLE SPENDING (
> ID PRIMARY KEY NOT NULL
> AMT
> BUDGET_CODE FOREIGN KEY REFERENCES FED.BUDGET_CODES
> )
>
> 3. At this point, there are 50 spending tables, each of which
 references the
> FED.BUDGET_CODES table. So far, so good.
>
> 4. Now, user FED creates a table called SPENDING, the structure of
 which is
> identical to that of the {STATE}.SPENDING tables, all of which are
> identical. The FED table contains no data.
>
> 5. Now, user FED creates a VIEW:
>
> CREATE VIEW vSPEND as
> Select * from SPENDING, BUDGET_CODES
> where SPENDING.BUDGET_CODE = BUDGET_CODES.BUDGET_CODE
>
> 6. User FED grants select on this view to each of the 50 state users.
>
> HERE'S THE PROBLEM:
>
> When the state users select against this view, the error is that the
 table
> (SPENDING) does not exist, because when the FED user created the
 view, the
> view was based on FED.SPENDING.
>
> Is there any to have the state users execute this view against the
 SPENDING
> table in their own schemas, without having to create 50 separate
 views?
>
> Thanks in advance.
> Tim Romano
>
>

--
http://www.freetechnicaltraining.com/home/ethan


Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sat May 13 2000 - 00:00:00 CDT

Original text of this message

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