[prasan5984@gmail.]

How to lock a table in informix?
to be more specific, how to deny even the select access for a table in informix...
i tried lock table command .... it doesnt work..

[stanislav.ondac]

Table locks do not preclude table access. An exclusive lock prevents other users from obtaining a lock, but it cannot prevent them from opening the table for write operations that wait for the exclusive lock to be released, or for Dirty Read operations on the table.

Suppose you have database without logging in which the default isolation level is
dirty read. And dirty read isolation level does not place any lock on the table during the select statement. So it has no problem even if there is an exclusive lock on the table. And dirty read is the only isolation level allowed for databases that do not have logging turned on.

So changing the database logging mode(with ontape or ondblog) will solve your problem.

[ceinma]

if you really need block any select... only way is execute a revoke select on <table> from <user> ... (to all users)
This is not a nice solution... but works...
Or, do the inverse. by default keep the table without select permission and create a ROLE (create role / grant role) with select permission on the table, then, when you realy need execute a select just active the role : SET ROLE <xyz>

If you use IDS 11.10 , read about LBAC, this not do exactly the perferct solution for what you need, but you can make more adaptive to you needs....