can this be done in Oracle?

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 Received on Sat May 13 2000 - 00:00:00 CDT