RE: read-only access

Yes Mark. This works fine but what if the user needs to access the DB with ODBC because they use some MSoft product, but still needs to use applications that runs on the application server {UNIX - Telnet}, full access. The only secure way is to use OPENLINK ODBC drivers {Or some other type of middleware product}. No use to give the user 2 logins, because he/she can use the non secure one any time with ODBC. According to the research we have done at the previous GSM telecom comp I worked for {Sure U know who I am talking about}, the only ODBC driver that had security build in was OPENLINK. This seems to be a major draw back of ODBC/JDBC drivers. I think I is a major draw back of the design of the ODBC layer. Passwords can sniffed out on the net. Easiest thing to break in on a network. Openlink has the option to encrypt data, but I am not sure of passwords. But then they have the option to limit from were you allowed to connect. Old saying, can U trust your employees ? PS. Today cold in SA. Feels like the UK. Cheers Hannes -----Original Message----- From: Mark D. Stock [SMTP:mdstock@mydas.freeserve.co.uk] Sent: Wednesday, April 05, 2000 11:02 PM To: Graeme Muirhead Cc: informix-list@iiug.org Subject: 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 |/ ////////| +----------------------+-----------------------------------+-----------+