CODEWITHSUNDEEP
oracle-database
ps0604
ps0604

Reputation: 1081

Same table name, same schema, different users in Oracle

I have the following requirement:

Is it possible to achieve this in Oracle?

Upvotes: 0

Views: 2084

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

Everything you wrote is possible, except

  • All TABLE1 tables should reside in the same schema

because you can't have two tables with the same name in the same schema.

A "schema" = user + its objects (tables, views, procedures, ...). Therefore, each user can have their own TABLE1 table.

It appears that there's yet another schema which aggregates data from everyone's TABLE1 table. In order to do that, you could create a view as

create or replace view v_table1 as
  select 'USER1' owner, t.* from user1.table1 t union all
  select 'USER2'      , t.* from user2.table1 t union all
  ...

It means that the final condition you wrote should be expanded a little bit, adding the WHERE clause, e.g.

select * from v_table1 where owner = 'USER2'

On the other hand, if every user has its own TABLE1, why would they query that "aggregated" (v_)TABLE1?

Finally, check VPD (virtual private database). In a few words:

Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.

More info here: https://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm#DBSEG98215

Upvotes: 2

Related Questions