RE: Mview base on a view?
- From: "Jay Hostetter" <jhostetter@xxxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 07 Apr 2004 13:33:19 -0400
Ben,
This database resides on a box that is hit by applications that are used =
by the "outside world." Measures have been taken to make sure this box i=
s secure, but in the event that this box would become compromised, I don't =
want a hacker peeking back through a DB link at sensitive data. There are =
some columns in this table that are sensitive, so I want to keep them out o=
f the remote database in the first place. To do this, I created the materi=
alized view in the remote database, based on a view in the local database. =
I just wondered if this is supported, has gotchas, etc. All the documenta=
tion that I read talked about building the mview on a table (or another mvi=
ew) - not a "normal" view.
Thanks,
Jay
>>> bweiss@xxxxxxxx 04/07/04 11:36AM >>>
Hi Jay,
Why not build a view on the materialized view? Grant access to the
application database user only to the
view, not the materialized view. Both the materialized view and the view
would have the limited subset of columns that you want.
For security in database links, make a public database link only with the
using clause. Make another link with the same name in the user schema that
owns the materialized view, and in that link, provide the connect by and
identified values.
The application user will only have access to the view. It won't be able to
see the materialized view. It won't be able to select over the link by
itself to explore the remote database.
Another advantage of having views on top of materialized views is that you
can do maintenance on the materialized view and keep the application
running, since it queries the view. How? Repoint the view to a table that i=
s
a copy of the materialized view prior to doing maintenance. Of course, the
application won't see changes during the maintenance.
Ben
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the =
use of the individual or entity to which they are addressed and may contain=
information that is privileged, proprietary and confidential. If you are n=
ot the intended recipient, you may not use, copy or disclose to anyone the =
message or any information contained in the message. If you have received t=
his communication in error, please notify the sender and delete this e-mail=
message. The contents do not represent the opinion of D&E except to the ex=
tent that it relates to their official business.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
