-
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 REMOTE_TAB@REMOTE.WORLD;
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 REMOTE_TAB@REMOTE.WORLD with read only;
HTH
Amar
"There is a difference between knowing the path and walking the path."
-
Re: Create read only access view
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
oracle-support@indiatimes.com
-
-
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 B@remote.world 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.
Amar
"There is a difference between knowing the path and walking the path."
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|