Re: SELECT from multiple schemas at ones?

"Sten Westerback" <sten.westerback_at_NO_SPAMnokia.com> wrote in message news:<E2_xc.18696$k4.373403_at_news1.nokia.com>...
> Hi
>
> We have a Oracle database server with a handful of
> schemas with the same set of tables (but different data).
> Each schema is accessible with different account&password.
>
> Is it possible to make a query that would search all the
> tables with same name at ones? Or can you for instance
> make a view that joins the tables together with account
> and passwords included and if so.. is that secure?

Depending on what you are actually trying to accomplish, one way would be to create another user, and grant select access on each table from each schema to the user. Then you could have a view that does unions of the selects from each table.

Do you need this dynamic or are the desired queries and schemata stable and well defined?

Don't crosspost between comp.database.oracle.* groups, and be sure to include platform/version information.

jg

--
@home.com is bogus.
http://ic.arc.nasa.gov/projects/psa/