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?

asked 30 Aug '11, 17:29

izatt82's gravatar image

izatt82
1163410
accept rate: 0%


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.

permanent link

answered 30 Aug '11, 18:08

Calvin%20Allen's gravatar image

Calvin Allen
1.5k232638
accept rate: 25%

edited 30 Aug '11, 18:11

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;
permanent link

answered 31 Aug '11, 09:43

izatt82's gravatar image

izatt82
1163410
accept rate: 0%

edited 31 Aug '11, 09:46

Siger%20Matt's gravatar image

Siger Matt
3.3k5672101

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28

question asked: 30 Aug '11, 17:29

question was seen: 2,334 times

last updated: 31 Aug '11, 09:46