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: Alexander Penev <alex_at_cska.net>
Date: 2000/05/15
Message-ID: <39200E2D.4CD5FA53@cska.net>#1/1

You can use just one table with a field state. You can define an insert trigger to write the current user in this field. You can make a views as select id, amt,budget_code from spendings where state=user. You can grant privileges only to this view and you have the functionality of your 50 tables , don't you?

Tim Romano 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
Received on Mon May 15 2000 - 00:00:00 CDT

Original text of this message

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