Mark an existing database as read-only?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Troy.Rudolph@ca.com - 17 Oct 2006 19:27 GMT

Is there a way to mark an existing database as read-only to protect it
from change? (It has 2700 tables.)

Thanks very much!

Reply to this Message

Karl & Betty Schendel - 17 Oct 2006 20:08 GMT

>Is there a way to mark an existing database as read-only to protect it
>from change? (It has 2700 tables.)

Well, I was going to say "no", but strangely enough there is a
flag in the database config file that sets a database to read-only.
It was meant to be turned on and off with the alterdb command.

Problem is, there's no alterdb command option that actually requests
the read-only operation! A bit of a hole there...

So it would seem that the answer is "no, although there was meant to be".
Your best bet is to use iitables to dump out all of the table
names (select for table_type = 'T' and table_owner <> '$ingres'),
and edit that into a bunch of "modify <table-name> to readonly" statements.

Karl

Reply to this Message

martin.bowes@ctsu.ox.ac.uk - 18 Oct 2006 11:35 GMT

Hi Troy

> Is there a way to mark an existing database as read-only to protect it
> from change? (It has 2700 tables.)

No. And in my experience its rarely useful to mark an entire database
as 'read-only'. The problem being that the vast majority of your
applications have been built on the assumption that they could at least
create 'temporary' work tables to do what they need to do.

The better option is to simply:
* modify table to readonly; and repeat for all the tables you wish to
protect. But this command is only available from IngresII R3 - I think.

* Alternatively drop all the grants and replace with select grants on the
tables you want to protect.

Martin Bowes

--
Random Duckman Quote #99:
Duckman: Corny, maybe you should stay too, not that I'm nervous or
scared or soiling myself, I just thought maybe you would like the chance
to do this with me.
Cornfed: Be a murder victim? Oooh, pinch me.

Reply to this Message

Roy Hann - 18 Oct 2006 11:59 GMT

> Hi Troy
>
[quoted text clipped - 5 lines]
> applications have been built on the assumption that they could at least
> create 'temporary' work tables to do what they need to do.

Good point.

> The better option is to simply:
> * modify table to readonly; and repeat for all the tables you wish to
> protect. But this command is only available from IngresII R3 - I think.

No, it's been available for a while. You used to have to make tables
read-only prior to doing parallel index builds using the old syntax. Does
it maybe go back to OI 2.0? I don't recall.

> * Alternatively drop all the grants and replace with select grants on the
> tables you want to protect.

Grants don't apply to the table owner or the DBA. However if one is
determined to do that sort of thing it is possible to write transition
constraints (using suitable rules and DBPs, see
http://www.rationalcommerce.com/resources/constraints.htm) that prevent even
the owner from doing updates.

Roy

Reply to this Message