Hi Paul,

Thanks for your help.

I will obviously have to alter my update program.

Thanks again

Adam

--- In ib-support@yahoogroups.com, Paul Vinkenoog <paul@v...> wrote:
> Hello Adam,
>
> > I am writing a program that updates our customers using script
> > files, this works fine for most things, however if we add a new
> > table to the database obviously we need to grant rights to the
> > existing users, but as we are unaware what users exist the only
way
> > I could see to do it is to grant rights in a stored procedure.
> >
> > Is there anyway I can do this?
>
> GRANT is a DDL statement (it changes the metadata) so you can't
use it
> in an SP or trigger. But you can grant rights immediately after you
> create the table (also in a script), depending on the situation:
>
> If "existing users" means "all users known by the server" you can
> grant the rights to PUBLIC.
>
> If it means a subset of those users, the best procedure is:
>
> - create a ROLE (or several roles, for several types of users)
> - grant the role(s) to the intended users
> - grant privileges to the role(s) instead of to the users
>
> Now, if you add another table and you want to give a group of users
> certain rights on that table, you grant those rights to the role.
The
> users will automatically "inherit" those rights, but they (or your
> app) have to specify the role upon connecting to the database.
>
> If you don't want to grant privileges to PUBLIC and you don't want
to
> use roles, you can also take the low(-level) road:
>
> - check RDB$USER_PRIVILEGES (a system table in your database) to
see
> which users are granted what privileges on what objects;
> - use that information to decide whom you grant what on your new
table.
>
>
> Hope this helps,
> Paul Vinkenoog