Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

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 replied

Hi Edgard,

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

Bye

1 View this answer in context
Not what you were looking for? View more on this topic or Ask a question