How to call GRANT ALL to all of an owner's tables

[former_member1021736]

Hi,

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:

BEGIN

  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);

  SELECT xSQL;

END

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?

Thanks,

Edgard

[Former Member · Accepted solution]

Hi Edgard,

    here a procedure we wrote to grant all users of a Group

Bye

sp_grant_table.txt.zip

[former_member1021736 · Accepted solution]

Grazie molto Georgio!

This should work perfectly!

Edgard