On 5-10-2018 15:13, Aldo Caruso aldo.caruso@...
[firebird-support] wrote:

> Hello,
>
>     I'm migrating from Firebird 2 to Firebird 3
>
>     When setting a generator, I get the following error:
>
> SET GENERATOR GEN_AGENDA_NRO to 287390;
>
> unsuccessful metadata update
> SET GENERATOR GEN_AGENDA_NRO failed
> no permission for ALTER access to GENERATOR GEN_AGENDA_NRO
>
> The same happens if I use the new syntax:
>
> ALTER SEQUENCE GEN_AGENDA_NRO RESTART WITH 287390;
>
> Reading documentation I found that there is a new SQL command for
> granting privileges to metadata objects like sequences
>
> GRANT ALTER ANY <object-type>
> TO [USER | ROLE] <user-name> | <role-name> [WITH GRANT OPTION];
>
> I'm a bit confused about how to specify the sequence
>
> Should I use
>
> GRANT ALTER ANY SEQUENCE TO ROLE <role-name>
>
> or
>
> GRANT ALTER SEQUENCE TO ROLE <role_name>

From the release notes:

"""
- If the ANY option is used, the user will be able to perform any
operation on any object

- If the ANY option is absent, the user will be able to perform
operations on the object only if he owns it
"""

So, the first will allow the user/role granted that privilege to alter
**any** sequence, the second will allow the user/role to only alter
sequences he owns.

> Why isn't a way to define a specific sequence ? i.e.
>
> GRANT ALTER SEQUENCE <sequence-name> TO ROLE <role-name>

Because that wasn't considered when this was implemented; consider
filing an improvement request.

There is a bit of an ugly hack that still allows you to do it while only
having the USAGE privilege on the specific sequence:

select gen_id(GEN_AGENDA_NRO, 287390 - gen_id(GEN_AGENDA_NRO , 0)) from
rdb$database

This does make you wonder if maybe RESTART WITH shouldn't fall under
USAGE instead of the ALTER privilege (or alternatively if using gen_id
with anything other than 0 or 1 shouldn't fall under the ALTER privilege).

Mark
--
Mark Rotteveel