Informix

Hi,

We have been asked by a client who is using IDS 12.10 to set a particular user to have read only access to the database.

Just a bit of background - this is the user they will use to access the database using ODBC for reporting. The ODBC driver is a read-write driver.

By default we have grant full access to 'public' as we allow any user to be able to install updates to the software (and therefore the database). We can revoke all privileges to the 'odbc' user and then only grant select access but because public has dba access this will allows updates to take place. It's going to be high risk if we change the public access to connect only but how would we grant full access to all tables for the other users. When they add another user to the system then how can we guarantee the new user will have full access permissions to all database tables?

Any assistance on how we could achieve this would be appreciated.

Regards,

Greg

Hi,

We have been asked by a client who is using IDS 12.10 to set a particular user to have read only access to the database.

Just a bit of background - this is the user they will use to access the database using ODBC for reporting. The ODBC driver is a read-write driver.

By default we have grant full access to 'public' as we allow any user to be able to install updates to the software (and therefore the database). We can revoke all privileges to the 'odbc' user and then only grant select access but because public has dba access this will allows updates to take place. It's going to be high risk if we change the public access to connect only but how would we grant full access to all tables for the other users. When they add another user to the system then how can we guarantee the new user will have full access permissions to all database tables?

Any assistance on how we could achieve this would be appreciated.

Regards,

Greg

Hi,

We have been asked by a client who is using IDS 12.10 to set a particular user to have read only access to the database.

Just a bit of background - this is the user they will use to access the database using ODBC for reporting. The ODBC driver is a read-write driver.

By default we have grant full access to 'public' as we allow any user to be able to install updates to the software (and therefore the database). We can revoke all privileges to the 'odbc' user and then only grant select access but because public has dba access this will allows updates to take place. It's going to be high risk if we change the public access to connect only but how would we grant full access to all tables for the other users. When they add another user to the system then how can we guarantee the new user will have full access permissions to all database tables?

Any assistance on how we could achieve this would be appreciated.

Regards,

Greg

• Grant access to the DBA_ROLE role to all users except the ODBC user.
• Grant access to the READ_ONLY role to the ODBC user.
• Revoke all table and column level privileges except SELECT from PUBLIC for all tables.
• GRANT ALL ON <tablename> TO DBA_ROLE;  -- for every table.
• In the sysdbopen() function owned by the ODBC user id set that user's role to the READ_ONLY role.
• In the global sysdbopen() function owned by the database owner (presumably that's user informix) grant the DBA role to whatever user is connected.

Hi,

We have been asked by a client who is using IDS 12.10 to set a particular user to have read only access to the database.

Just a bit of background - this is the user they will use to access the database using ODBC for reporting. The ODBC driver is a read-write driver.

By default we have grant full access to 'public' as we allow any user to be able to install updates to the software (and therefore the database). We can revoke all privileges to the 'odbc' user and then only grant select access but because public has dba access this will allows updates to take place. It's going to be high risk if we change the public access to connect only but how would we grant full access to all tables for the other users. When they add another user to the system then how can we guarantee the new user will have full access permissions to all database tables?

Any assistance on how we could achieve this would be appreciated.

Regards,

Greg

• Grant access to the DBA_ROLE role to all users except the ODBC user.
• Grant access to the READ_ONLY role to the ODBC user.
• Revoke all table and column level privileges except SELECT from PUBLIC for all tables.
• GRANT ALL ON <tablename> TO DBA_ROLE;  -- for every table.
• In the sysdbopen() function owned by the ODBC user id set that user's role to the READ_ONLY role.
• In the global sysdbopen() function owned by the database owner (presumably that's user informix) grant the DBA role to whatever user is connected.

Hi,

We have been asked by a client who is using IDS 12.10 to set a particular user to have read only access to the database.

Just a bit of background - this is the user they will use to access the database using ODBC for reporting. The ODBC driver is a read-write driver.

By default we have grant full access to 'public' as we allow any user to be able to install updates to the software (and therefore the database). We can revoke all privileges to the 'odbc' user and then only grant select access but because public has dba access this will allows updates to take place. It's going to be high risk if we change the public access to connect only but how would we grant full access to all tables for the other users. When they add another user to the system then how can we guarantee the new user will have full access permissions to all database tables?

Any assistance on how we could achieve this would be appreciated.

Regards,

Greg