Database Open in Read Only Mode

513596

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

Anurag Tibrewal

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

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

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

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....

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

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.