Forum Stats

  • 3,836,937 Users
  • 2,262,206 Discussions
  • 7,900,148 Comments

Discussions

Database Open in Read Only Mode

513596
513596 Member Posts: 80
edited Aug 24, 2009 12:41AM in General Database Discussions
Hi

I would like to open database in read only mode

like

SQL> startup mount

SQL> ALTER DATABASE OPEN READ ONLY

this will open database for read only mode for all user

i want to give read write access on database to one user

any idea how i can proccess

Thanks

Chetan
Tagged:

Answers

  • Anurag Tibrewal
    Anurag Tibrewal Member Posts: 3,901 Gold Trophy
    Hi,

    It is not possible to open the database in read only mode and allow any one user to write .

    The only thing I can see is open the databse in read/write mode and remove the write privilege on other users.

    You may like to think about
    Change the password of the user who owns the table and create the synonym in new users with select privilege only.

    Regards
    Anurag Tibrewal
  • 340744
    340744 Member Posts: 70
    Hi there,

    opening a db read only means read only for all, even for sys.
    maybe you can set all tablespaces to read only, except those for the user you want to allow read write.
    Another option which I never tested could be the resource manager. You can assign undo quotas
    to users. If you give all users except the one an undo quota of zero they should not be able to
    change any data. Happy testing.

    HTH Mathias
  • 340744
    340744 Member Posts: 70
    Hi there,

    i couldn't stand it not to test it right away. the answer is: its a pitty. update, delete, create table are not
    working with undo quota 0. but unfortunately insert is :-(
    Maybe another solution:
    how about triggers on all tables that you want to protect?
    in the trigger body you check the user, if you do not allow them you do not allow the change.

    but there are still things to consider. for example, what about grants?

    HTH Mathias
  • vasu77
    vasu77 Member Posts: 108
    Hi,

    Alter database open read only;

    this should work fine and everything will be in read only mode. Hence if any auditing is going on and audit_trial=db then it fails to open the database in read only mode.

    If you want to control only users to stop writing to it, you may remove tablespace quotas for those users. Also, you may control them through grants. Remove any current grants assigned to them or to roles through which the grants are assigned.

    Several ways to do this. If you tell us the particular scenario then we might help in getting a solution.

    Regards,
    Vasu.
  • Aman....
    Aman.... Member Posts: 22,919 Gold Crown
    ChetanS wrote:
    Hi

    I would like to open database in read only mode

    like

    SQL> startup mount

    SQL> ALTER DATABASE OPEN READ ONLY

    this will open database for read only mode for all user

    i want to give read write access on database to one user

    any idea how i can proccess
    What that user is? Is it a normal or DBA user? What's the version of the db and o/s ?

    HTH
    Aman....
  • Sanghai
    Sanghai Member Posts: 184
    hi
    since it is not posible to open the database in read and write for some user and read only for some user.
    so you batter open the databse in read and write mode and isolate the read only users using roles and privillages.

    Thanks.
This discussion has been closed.