Re: read-only access

Graeme Muirhead wrote: > > Is there an easy way of restricting users to have read only (ie select) > access to all tables ie so that users using ODBC for querying the database > do not accidentally delete rows. We are using INFORMIX-SE 7.24 with Informix > ODBC Driver, version 3.3/Client SDK 2.30. If you need to remove default permissions from the database, then you can do something like: OUTPUT TO PIPE "dbaccess <My Database>" WITHOUT HEADINGS SELECT "REVOKE INSERT, UPDATE, DELETE ON " ||tabname ||" FROM public;" FROM systables WHERE tabid > 99 AND tabtype = "T" Notice that if you have not granted permissions to specific users, then this effectively makes your database read-only. If you need to remove specific users permissions, then you can do something like: OUTPUT TO PIPE "dbaccess <My Database>" WITHOUT HEADINGS SELECT "REVOKE INSERT, UPDATE, DELETE ON " ||tabname ||" FROM " ||grantee ||";" FROM systabauth a, systables t WHERE a.tabid = t.tabid AND a.tabid > 99 AND tabtype = "T" AND grantee NOT IN ("informix", "<My Special Users>", ...) AND ( tabauth[2,2] != "-" -- Update OR tabauth[4,4] != "-" -- Insert OR tabauth[5,5] != "-" -- Delete ) Be very careful running these scripts though. Run the SELECT first to see what permissions will be removed. You might also want to save the existing permissions in case you need to restore any. ;-) Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+