Subject Re: Granting user rights
Author Adam Stewart <>
Hi Paul,

Thanks for your help.

I will obviously have to alter my update program.

Thanks again


--- In, 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
> > 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.
> 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
> use roles, you can also take the low(-level) road:
> - check RDB$USER_PRIVILEGES (a system table in your database) to
> which users are granted what privileges on what objects;
> - use that information to decide whom you grant what on your new
> Hope this helps,
> Paul Vinkenoog