Re: How to convert schema to readonly schema.

From: Andre van Winssen <dreveewee_at_gmail.com>
Date: Mon, 22 Jun 2009 16:19:19 +0200
Message-ID: <9b46ac490906220719h38ab74agdddc88f0ce91d96d_at_mail.gmail.com>



if it's 11g db you can alter the table to readonly for the owner (and other users):

SQL> create table readonly (c1 number);
Table created.
SQL> alter table readonly read only;
Table altered.
SQL> insert into readonly values (1);
insert into readonly values (1)

            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "READONLY"

But this does not prevent you (as owner) from dropping the table

SQL> drop table readonly;
Table dropped.

--
with following simple script you can do this for all tables in your schema:

begin
  for rec in (select table_name from user_tables) loop
   execute immediate 'alter table '||rec.table_name||' read only';
 end loop;
end;
/

to undo use
alter table xx read write;

Regards,
Andre
2009/6/22 Subodh Deshpande <subodh_deshpande_at_yahoo.com>


> two ways I guess,
> 0) put the eniter schema in a read only table space
> 1.1) create another schema which will be the owner and for rest all give
> only select access, sys, system and the new schema owner passwords should be
> kept secret or
> 1.2) other than the present owner give everybody as select access, and sys,
> system and the orginal schema owner passwords should be kept secret
>
> thanks and take care..subodh
> ------------------------------
> *From:* Avadhani mys <avadhanimys_at_gmail.com>
> *To:* oracle-l <oracle-l_at_freelists.org>
> *Sent:* Monday, 22 June, 2009 17:27:34
> *Subject:* How to convert schema to readonly schema.
>
> Hi Gurus,
>
> I have a requirement to convert schema to readonly schema. The application
> users should have select privilege on the objects he owns and application
> team would like to use this schema for reference.
>
> Thanks in Advance
> Aavadhani
>
>
-- http://www.freelists.org/webpage/oracle-l
Received on Mon Jun 22 2009 - 09:19:19 CDT

Original text of this message