| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Mview base on a view?
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 is a copy of the materialized view prior to doing maintenance. Of course, the application won't see changes during the maintenance.
Ben
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jay Hostetter
Sent: Tuesday, April 06, 2004 10:22 AM
To: oracle-l_at_freelists.org
Subject: Mview base on a view?
I was just wondering if it is a valid practice to create a materialized vie= w based on a view. I have some columns in a table that I want to replicate= to another database. However, there are other columns in the table that I= want to hide from the remote database. I am specifically concerned about = somebody querying through my db link back to the source database. So my da= tabase link will only be able to access a view, which hides some of the col= umns in the table. I was browsing through the docs and didn't find this sc= enario. I was curious if anybody else does this or if there is another met=hod I should use.
Thank you,
Jay
/* In Local Database */
create table user_list (
u_name varchar2(30), u_password varchar2(30), u_created_date date
create materialized view log on user_list; grant select on mlog$_user_list to remote_user;
create view vu_user_list as
select u_name,u_created_date
from user_list;
grant select on vu_user_list to remote_user;
/* In remote database */
create database link source_db connect remote_user identified by secretpwd = using 'db1';
create materialized view vu_user_list
refresh with rowid
as (select * from user_a.vu_user_list_at_source_db);
alter materialized view vu_user_list refresh fast;
**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.
If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. When addressed to our clients any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing KPMG client engagement letter.
 |
 |