DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Create read only access view

  1. #1
    Join Date
    Feb 2003
    Posts
    15

    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

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530

    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
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  4. #4
    Join Date
    Feb 2003
    Posts
    15
    Thanks guys.

  5. #5
    Join Date
    Feb 2003
    Posts
    15
    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

  6. #6
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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."

    Amar's Blog  Get Firefox!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width