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