How to 'GRANT' to ALL tables in a database ?

[Ian Aitchison]

Here's a tricky one that I just can't find the answer to in any
documentation....

I'm trying to GRANT permissions to all tables in a database to a specific
user without having to run a GRANT command for each table in the database,
but there does not seem to be way of stating 'all tables'.

The command I am using is .....

GRANT SELECT,INSERT,UPDATE,DELETE ON <all_tables_in_database> TO My_User;

where <all_tables_in_database> has been tried as ALL, *, and a whole
variety of surreal word-combinations.

Does anyone out there know if this is actually possible? I really do not
want to have to rerun this command for EVERY table in the database (at
least 150, often over 200 tables in the database).

As every, feedback appreciated.

Ian Aitchison.

[Jim Egan]

Use this URL: http://www.dejanews.com/=dnt_kl/getdoc.xp?AN=370385387

It will take you to a search of DejaNews. I posted a solution to a
question that is similar to yours. The query will generate a result set
that is a list of SQL commands that you can use to set rights on ALL
tables.

DejaNews is a great source to lookup past questions and solutions.
--
Jim Egan [TeamPS]
Dynamic Data Solutions, Inc. http://www.dyn-data.com
Houston, TX

***********************************************************
Enterprise Applications in Your Future?
JumpStart '99 Enterprise Solutions Conference
http://www.JumpStart99.com
***********************************************************

[Leo Tohill]

Jim's deja news posting seemed incomplete so I'll give a similar idea here:

create procedure grantalltables()
begin
declare alltabs cursor for select table_name from systable where creator = {whatever the creator id
of the owner of the tables is};

declare s_tablename varchar(120);

open alltabs;
mainloop:
loop
fetch next alltabs into s_tablename;
if sqlstate = '02000' then leave mainloop end if;
execute immediate 'grant all on '|| s_tablename ||' to {yourgroupname} ';
end loop;
close alltabs;
end;

author not responsible to stupid mistakes!

HTH

Leo Tohill - Team Powersoft
-- Please post in newsgroup, not via email <

[Ian Aitchison]

Thanks to you both. My question is answered.

Ian.

Ian Aitchison <Ian.Ai...@royalblue.com> wrote in article
<01be5abc$bcc26300$8764a8c0@ukcis-pc128>...