In DB2 V7 z/OS this is not possible. You can grant privileges on the database level, which include the SELECT-privilege. Unfortunately you will grant other privileges as well, which are inherited in the database privilege, that you might not want to. For instance, if you grant DBADM, the user can also update, delete, insert all tables in that database, he can create new ones or drop tables and much more including utility-privileges.
You can build a SELECT statement that builds the GRANT statements:
SELECT CASE WHEN type = 'V'
THEN 'GRANT SELECT ON ' || RTRIM(creator) || '.' || name || ' TO groupa, groupb;'
WHEN name LIKE '%_T'
THEN 'GRANT SELECT, INSERT ON ' || RTRIM(creator) || '.' || name || ' TO groupa, groupb;'
ELSE 'GRANT INSERT, SELECT, UPDATE, REFERENCES ON ' || RTRIM(creator) || '.' || name || ' TO groupc, groupd;'
WHERE (creator IN ('SCHEMA' )
AND type != 'A'
AND (name LIKE '%_T'
OR name LIKE '%_RT'
OR name LIKE '%_V'
OR name LIKE '%_AT'))
Originally posted by bab
how can i grant "select" on all tables in schema x to a user y without listing all names of tables in schema x ...
GRANT SELECT ON TABLE x.tab1 TO USER y;
GRANT SELECT ON TABLE x.tab2 TO USER y;
GRANT SELECT ON TABLE x.tab3 TO USER y;
GRANT SELECT ON TABLE x.tab4 TO USER y;
and so on ...
is there any commands like :
GRANT SELECT ON TABLE x.ALLTABLE TO USER y; ?????? !!!!