Create read only access view

Hi,

Can someone help me in creating a read only access view from one database to another? I have had a look around and I haven't managed to get the commands needed.

Thanks

J

Assuming u have user A on local DB and user B on remote DB. You want to give read only on table B.REMOTE_TAB to user A.

1. Create a user C on remote DB.
2. Grant select on table B.REMOTE_TAB.
3. Create a public synonym REMOTE_TAB for B.REMOTE_TAB.
4. Connect as user A and create a DB link from local DB to remote DB with user C (create database link REMOTE.WORLD connect to C identified by password using 'REMOTE_CONNECT_STRING').
5. Create view as select * from [email protected]D;

This will be a read only view.

or simply

1. Create a database link
2. Create a view with read only option.
3. Create view as select * from [email protected]D with read only;

HTH

Hi,
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view name [alias[, alias]�)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint] ] [WITH READ ONLY]

You can ensure that no DML operations occur by adding the WITH READ ONLY option to your view definition.
Example:
SQL> CREATE OR REPLACE VIEW myview2
AS SELECT empno, ename, sal
FROM emp
WHERE deptno=10 WITH READ ONLY OPTION;

Any attempt to perform DML on any row in the view results in an Oracle server error: ORA-01725

Regards,
Rohit Nirkhe,oracle/apps DBA,OCP 8i,9i
[email protected]

Thanks guys.

Amar,

I think I'll create a dblink then view as you mentioned. But I need to make sure that I've got all the details correct.

My scenario is I need to have read only access from DB A to DB B.

1) Database link

CREATE PUBLIC DATABASE LINK A (Do I need to edit tnsnames.ora to add this entry in the source area or target area?)


2) Create view AB as select * from [email protected]d with read only;

Can you let me know if this is correct?


Thanks

J

I would go with the first method... cos in the second one even if you create a read only view over database link, you will be actually accessing the remote db as the remote user and the remote database user will have all the access.

So any one can make changes to the table by directly accessing the table rather than the view.

Create a private database link and do read the Database Administration Manual.