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

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

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.

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

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

Hi David.

This article contains a script which might save you some time:

https://www.oninitgroup.com/faq-items/informix-read-only-access

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

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