To expand on the other responses, use roles. Revoke all but CONNECT from PUBLIC, then create two roles: a read role and a dba role. Then:
- 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.
Now the ODBC user does not have privileges to do anything except connect to the database and perform SELECTs.
You can automate creating the REVOKE and GRANT statements either using my dbscript utility from my utils2_ak package or using Lester's method to generate the SQL (ie: UNLOAD TO 'revoke.sql' DELIMITER ';' SELECT "REVOKE ALL ON "||trim(tabname)||" FROM PUBLIC" FROM systables where tabtype = 'T' and tabid > 99; )
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com------------------------------
Original Message:
Sent: Tue December 29, 2020 11:06 AM
From: Greg Bunting
Subject: Setting a user to Read only access
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
------------------------------
Greg Bunting
------------------------------
#Informix