hi all,

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; ?????? !!!!

thanks a lot

Hi

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.

This is not possible as far as I know

You will have to grant at the table level

Cheers
Sathyaram

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;'
END
FROM sysibm.systables
WHERE (creator IN ('SCHEMA' )
AND type != 'A'
AND (name LIKE '%_T'
OR name LIKE '%_RT'
OR name LIKE '%_V'
OR name LIKE '%_AT'))

hi walter, sathyaram, dmmac

thanks for responses ..

another question:

what is the reason that i can change to a schema that not exists on DB ...

for example: set schema YYY

there is no schema YYY but i dont get any errors ... what is the reason?

thanks a lot

bab



@walter
hi walter, vielen dank für die antwort ...

I think, because you have SYSADM-authority.