How to grant usage on generator syntax

Question

I'm usually not the one working with Firebird language, but I have to fix access to generators in order to continue working. There is one line in my script: set generator order_gen to 0 that doesn't work anymore.

I'm trying: GRANT USAGE ON GENERATOR ORDER_GEN TO USER MAHE, but I still get the error ...no permission for ALTER access to GENERATOR ORDER_GEN ....

Should I be granting a different privilege to be able to set the generator, or am I not using the correct syntax here?

Answer 1

Assuming Firebird 3.0 or higher, the USAGE privilege only grants the privilege for using NEXT VALUE FOR <sequence> and GEN_ID(<sequence>, <n>). As mentioned in the documentation:

For sequences (generators), the USAGE privilege only grants the right to increment the sequence using the GEN_ID function or NEXT VALUE FOR. The SET GENERATOR statement is a synonym for ALTER SEQUENCE …​ RESTART WITH …​, and is considered a DDL statement. By default, only the owner of the sequence and administrators have the rights to such operations. The right to set the initial value of any sequence can be granted with GRANT ALTER ANY SEQUENCE, which is not recommend for general users.

As mentioned, GRANT ALTER ANY SEQUENCE should be a last resort. Depending on the actual problem being solved, you could also do something like calling gen_id(order_gen, 0 - gen_id(order_gen, 0)) (e.g. as select gen_id(order_gen, 0 - gen_id(order_gen, 0)) from rdb$database) (this is a loophole/workaround, see also Inconsistency between ALTER and USAGE privileges for sequences (generators). in the Firebird issue tracker).

Alternatively, you should ask your database administrator to execute this script/statement, or - if you have the RDB$ADMIN privilege yourself - to ensure you specify that role when connecting to the database (if you don't explicitly specify the role, you don't get the administrator privileges).