Hi,

may be a basic question...but does anyone know that how to give select(or any privilege ) on all the tables for that database in one command to a particular user.

like if i want to give only select access to a user on all the database den i have to give a seperate commad for all the table. So I wanted to give in using one command.

please let me know if anyone has any idea

There is no generic GRANT table-privilege for all tables of a database. You have to list each table.
Of course you can generate the GRANT-statements with SQL.

e.g.

SELECT
' GRANT SELECT ON TABLE '
concat STRIP(creator)
concat '.'
concat STRIP(name)
concat ' TO userid ; '
FROM SYSIBM.SYSTABLES
WHERE DBNAME = 'your-DBname' ;

But by granting the DBADM privilege, the user gets SELECT auth (and much more) on all tables of a database -- that is, on DB2 for z/OS.
Is this also the case for L/U/W or iSeries?

It is the same for LUW.

Andy

But I want to only give select authority i.e. read only.

In that case your only option is to give it on a table-by-table basis.
Use UMayer's suggestion for generating the GRANT statements.
Be aware that there is no way of automatically granting SELECT auth for "future", i.e., not yet existing, tables!

I wonder if it is possible to put an insert trigger on syscat.tables to do this?

Good idea! Never tried that.
Actually, shouldn't it be on sysibm.systables instead? I seem to recall that syscat.tables is a view on sysibm.systables.
There could be restrictions on putting GRANT statements in triggers, though. I've had issues with that on z/OS.

no.
Catalog tables, auxiliary tables, temporary tables and MQTs
can't be specified as the triggering table
( alias or synonyms can't be specified, too )