You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
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.
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.
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.
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.
"""
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)
The text was updated successfully, but these errors were encountered: