RE: Mview base on a view?

From: Jay Hostetter <jhostetter_at_decommunications.com>
Date: Wed, 07 Apr 2004 13:33:19 -0400
Message-ID: <s073f6a2.098@ephsvr25.dande.com>

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_at_kpmg.com 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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Apr 07 2004 - 13:40:49 CDT