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

Hi Greg.

My preferred method is to revoke write privileges on all tables from "public", create a role with full access, then assign that as the default role for certain users. I have a script to generate the statements for all tables. Call me tomorrow, and I'll take you through the process. You will probably need to tighten up who can apply schema changes, but having a consistent object owner is a good thing anyway.

------------------------------
Doug Lawry
Oninit Consulting
------------------------------

HI

as said by Doug, roles is the best solution, see also Mr Lester paper :), 

https://advancedatatools.com/article/roles-a-new-security-feature-in-informix-online-7-10-ud1/

------------------------------
John Smith
------------------------------

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
------------------------------

Thanks everyone for your input.

I now have a handle on what needs to be done.

Regards,

Greg

------------------------------
Greg Bunting
------------------------------

Original Message:
Sent: Wed January 06, 2021 09:39 AM
From: Art Kagel
Subject: Setting a user to Read only access

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
------------------------------

Art:

Thank you for the wonderfully concise answer to Greg's question.  I am currently looking into tightening my company's IDS security practices and this thread helps me a lot. 

Happy New Year!

------------------------------
Best regards,
Martin Graney
Queues Enforth Development, Inc.
Stoneham, MA 02148
------------------------------

Original Message:
Sent: Wed January 06, 2021 09:39 AM
From: Art Kagel
Subject: Setting a user to Read only access

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
------------------------------