Re: Make table read-only

From: Hans Forbrich <news.hans_at_telus.net>
Date: Mon, 18 Oct 2004 20:39:18 GMT
Message-ID: <WRVcd.18492$cr4.16508@edtnps84>

Ralf Zwanziger wrote:

> Is ist possible to make an oracle table read-only?
> (without moving it to a read-only tablespace).
> I haven't found any command like "alter table xxx read only" in the
> docs.
>

You are correct that you can not alter a table to become r/o. So far read-only is not an attribute of table, but is a security mechanism controlled by the 'ACL. SO you need to look to the security subsystem, not the object subsystem.

What is your basic security model? Do you allow everyone to access the userid that owns the schema (and therefore the table)? Who will not be allowed to update the table? When will the table be 'Read-Only'?

There are several ways to accomplish this. I show one long-winded variant at the bottom, based on the following model:

All users log in under their own userid (otherwise how do you tell it's user A vs user B???);
Each user is granted access to the table(s), view(s) and other database objects, either explicitly or through a role;
The Grant command allows the administrator to decide which combination of SELECT, UPDATE, INSERT, DELETE a user/role gets to use;
A 'GRANT SELECT TO user' without any additional UPDATE, INSERT or DELETE is equiv. to read-only.

A read only table could be accomplished by creating the table under a separate schema, exposing that table to the primary schema via a view, granting only select on the table to the 'public' schema, and exposing the view to the rest of the world. Following show that: ro_schema contains the table, gen_schema contains the view, ro_user is the end user denied update.

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 18 13:09:45 2001

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> create user gen_schema identified by x;

User created.

SQL> create user ro_schema identified by x;

User created.

SQL> grant connect, resource to gen_schema, ro_schema;

Grant succeeded.

SQL> create user ro_user identified by x;

User created.

SQL> grant connect to ro_user;

Grant succeeded.

SQL> connect ro_schema/x_at_ora1
Connected.
SQL> create table abc (
2 a1 varchar2(4) );

Table created.

SQL> insert into abc values ('A1');

1 row created.

SQL> insert into abc values ('B2');

1 row created.

SQL> commit;

Commit complete.

SQL> grant select on abc to gen_schema with grant option;

Grant succeeded.

SQL> connect gen_schema/x_at_ora1
Connected.
SQL> create view abc as select * from ro_schema.abc;

View created.

SQL> select * from abc;

A1
B2

SQL> insert into abc values ('C2');
insert into abc values ('C2')

*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> grant select on abc to ro_user;

Grant succeeded.

SQL> connect ro_user/x_at_ora1
Connected.
SQL> select * from ro_schema.abc;
select * from ro_schema.abc

ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from gen_schema.abc;

A1
B2

SQL> select * from abc;
select * from abc

*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create synonym abc for gen_schema.abc;

Synonym created.

SQL> select * from abc;

A1
B2

SQL> insert into abc values ('D1');
insert into abc values ('D1')

*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> update abc set A1='C2' where A1='B2'; update abc set A1='C2' where A1='B2'

*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> Received on Mon Oct 18 2004 - 15:39:18 CDT