Inconsistency between ALTER and USAGE privileges for sequences (generators). [CORE5937]

[firebird-automations]

Submitted by: @mrotteveel

Votes: 2

There appears to be an inconsistency between the ALTER and USAGE privileges for sequences.

Only users with ALTER permission on sequences are allowed to use ALTER SEQUENCE <name> RESTART WITH <new value>

Users with USAGE permission cannot execute that statement, but they can achieve the same effect with:

select gen_id(<name>, <new value> - gen_id(<name>, 0)) from rdb$database

Either this loophole needs to be closed (eg by disallowing values other than 0 or 1 without ALTER permission), which will likely break applications that rely on being able to use gen_id with a different value.

Or, better, we should relax the requirements a bit, and allow RESTART WITH (and only RESTART WITH) to users who have USAGE permission. Then at least the loophole is explicit and doesn't create a false sense of safety.

See also https://groups.yahoo.com/neo/groups/firebird-support/conversations/topics/133140 (archive)

[firebird-automations]

Commented by: @romansimakov

I think we can allow users to RESTART WITH if they have USAGE privilege like they can do it now.
We have no just ALTER privilege for database objects. Only ALTER ANY. For SEQUENCES it looks not enough.
The second option to implement ALTER on single SEQUENCE but it looks no to consistent with other ALTER ANY operations.
But I'd like to know other opinions.

[firebird-automations]

Commented by: @mrotteveel

This ticket is only about the inconsistency of the privilege, not about the suggestion raised in the firebird-support discussion to implement ALTER privilege for a single sequence.

[firebird-automations]

Commented by: @sim1984

The problem is much more complicated than it seems. The gen_id function is not standard for working with sequences.
According to the standard, sequences can be incremented only by the value specified in the INCREMENT BY parameter, which is specified when creating the sequence.
We could throw an exception when specifying an increment in gen_id that is not equal to what is specified in INCREMENT BY.

However, this solution has side effects:
1. This will break backward compatibility.
2. gen_id allows you to specify a large increment, which is useful for bulk insertion. This makes it possible to significantly reduce the number of hits to the generators page.

You could use the following simplified solution. If INCREMENT BY is a positive value, then prohibit the use of negative values ​​in gen_id.
If in INCREMENT BY negative values, then prohibit the use of positive values ​​in gen_id.

[firebird-automations]

Commented by: @mrotteveel

Alternatively, maybe we could just declare gen_id deprecated and remove it in Firebird 5?

[firebird-automations]

Commented by: @asfernandes

Let's be practical: what you want to avoid?

That someone does not increment the sequence in a way it should not?

How can you avoid that he writes a loop and increment one by one many times, causing the same kind of problem?

[firebird-automations]

Commented by: @mrotteveel

My primary concerns here is the inconsistency of the between the ALTER privilege required to use RESTART WITH and the fact you can achieve the exact same effect with a USAGE privilege and GEN_ID.

To repeat myself:

"""
Either this loophole needs to be closed (eg by disallowing values other than 0 or 1 without ALTER permission), which will likely break applications that rely on being able to use gen_id with a different value.

Or, better, we should relax the requirements a bit, and allow RESTART WITH (and only RESTART WITH) to users who have USAGE permission. Then at least the loophole is explicit and doesn't create a false sense of safety.
"""