ASA 8.0 how to assign permissions to more than one table at a time

[Former Member]

How do i assign the same permissions for a group to all tables in database at the same time?

from what i have found looks like one by one, i am not going to do that for 408 tables. any ideas?

[Former Member · Accepted solution]

This is what I use for doing those kinds of things, but I also use SA10 so this may need to change a little bit for v8:

for tableFor as tableCursor dynamic scroll cursor for
  select 
    st.table_name as @tableName 
  from 
    systab st 
  where 
    st.table_type = 1 
  order by
    st.table_name asc
  do
    execute immediate 'grant insert on ' || @tableName || ' to groupName';
end for;

Change the SELECT query as needed to further define tables, and change the EXECUTE IMMEDIATE to include the permissions you wish to grant and also the groupName at the end to your particular group name.

[Former Member]

thank you very much kind sir. i used. the only real change needed is systab needs to be systable other than that it worked awesome.

for tableFor as tableCursor dynamic scroll cursor for
select 
  st.table_name as @tableName 
from 
  systable st

order by
  st.table_name asc
do
  execute immediate 'grant update, select, insert on ' || @tableName || ' to TEST_NON_DBA';
end for;