How to call GRANT ALL to all of an owner's tables
I want to call "GRANT ALL ON table-name TO group-name" for all the tables whose owner is "DBA".
Instead of doing
GRANT ALL ON dba.iBodegas TO "TRIPODE";
GRANT ALL ON dba.iInv TO "TRIPODE";
etc... etc.... etc....
I wanted to do it in on block.
I started doing this:
DECLARE xIdDBA INTEGER;
DECLARE xUserName VARCHAR(50);
DECLARE xSQL LONG VARCHAR;
SET xUserName = 'TRIPODE';
SET xIdDBA = (SELECT User_Id FROM SysUserPerm WHERE User_Name ='DBA');
SET xSQL = (SELECT STRING('GRANT MEMBERSHIP ON "',a.table_name,'" TO "',xUserName,'";')
FROM systable a join sysuserperm b on a.creator=b.user_Id WHERE a.creator=xIdDBA);
But that doesn't work becasue I can't load the result set into the xSQL variable.
I wanted to load all the GRANT statements into a variable and then EXECUTE IMMEDIATE that :-)
Any better ideas?
here a procedure we wrote to grant all users of a Group