How to make database temporarily READ-ONLY?
David Grove (IDS 12.10.FC14) asked how to make a database temporarily read-only and revert exactly. Paul Watson endorsed using a role granting SELECT on all tables. Mike Walker warned privileges cannot be revoked from table owners who are users, so David planned to change owners. Doug Lawry pointed to a script article. Clive Eisen suggested an HDR secondary.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
IDS 12.10.FC14 Solaris 10 1/13 We would like to make a database temporarily READ ONLY. Informix doesn't provide a built-in administrative function or button to make a database read-only. (And then, later, revertible with another function or button push.) So, I need to roll my own, so to speak. My very first thought was to copy the systabauth table, then revoke all permissions from all tables for all users, and then grant SELECT to all users for every table. Then, when wanting to revert to the original state, drop systabauth, and rename the copy back to systabauth. However, upon checking into the documentation, I read the caution to NOT do any manual update, etc, of the system catalog tables. So that means I should not attempt to do the above, right? If the above is not acceptable, would the following be a way to accomplish the objective? Create a role that has SELECT privilege on every table. Assign PUBLIC to that role. When time to revert, revoke the role from PUBLIC. But, suppose there are some users that have explicit grants of higher level privileges to a certain table. If user 'public' then is given the read-only role, which would govern? The reduced read-only privilege due to the new role assigned to 'public', or the higher level privilege already granted to a specific user? Summary: looking for the best way to temporarily make an entire database read-only (which means also being able to revert to the exact state of privileges that existed prior to making read-only. Thank you for any suggestions. Regards, DG ------------------------------ David Grove ------------------------------
As you suggest, I would go the role route
I had a similar issue trying to make a database read-only, and ran into a problem where roles didn't help. If I remember correctly, it had something to do with not being able to revoke permissions from the owner of a table. Because the table owner was also a user of the database, it was not possible to truly make the database read-only for that user, and had to revoke access for that user completely which was not ideal. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------
Thank you, Mike. That was something I hadn't thought of. Very good point. The overwhelming majority of tables is owned by a userid that is not a database user. BUT, there are a few that have owners that are users, so we would run into that. As a preemptive strategy, I will change those owners so that no table is owned by a user. Thank you, again, for a great warning. DG ------------------------------ David Grove ------------------------------
Hi David. This article contains a script which might save you some time: https://www.oninitgroup.com/faq-items/informix-read-only-access ------------------------------ Doug Lawry Oninit Consulting ------------------------------
Thank you, Doug. DG ------------------------------ David Grove ------------------------------
This may be a very silly answer but why not make it an hdr secondary? Even with a non existent primary I would expect this to make it read only? Or have I missed something. Regards, — Clive EIsen