Issue Details (XML | Word | Printable)

Key: CORE-2553
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: eXandr
Votes: 2
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Grants access on generators (gen_id, next value for)

Created: 13/Jul/09 03:07 AM   Updated: 30/Jan/16 04:29 PM
Component/s: Engine
Affects Version/s: 2.0.0, 1.5.4, 2.0.1, 2.0.2, 2.0.3, 1.5.5, 2.1.0, 2.0.4, 2.5 Alpha 1, 2.1.1, 2.0.5, 2.1.2, 2.5 Beta 1
Fix Version/s: 3.0 Alpha 1

Issue Links:
Duplicate
 
Relate

QA Status: Done successfully
Test Details:
Test not needed for this ticket.
See core_4806.fbt instead.


 Description  « Hide
Need to manage the privileges for the generators, like RED Database:

GRANT SELECT | UPDATE ON GENERATOR|SEQUNCE {generator}
TO {user | role} [WITH GRANT OPTION]

REVOKE SELECT | UPDATE ON GENERATOR|SEQUNCE
{generator} FROM {user | role}

REVOKE GRANT OPTION FOR SET | GET ON
GENERATOR|SEQUNCE {generator} FROM {user | role}

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 13/Jul/09 03:21 AM
The SQL specification declares only the USAGE permission for sequences (NEXT VALUE FOR) and I strongly believe we should go this way. However, I might agree that SELECT/UPDATE sounds more appropriate for GEN_ID. From another side, what is SELECT for GEN_ID? Zero increment? But this value may be unknown at the prepare stage when the permissions are validated.

eXandr added a comment - 17/Jul/09 02:29 AM
If no permissions - rase error. Zero increment - wrong way.

Vannus added a comment - 13/Jan/13 05:10 AM
Just agreeing with op. At the moment, any user can run the SQL below and cause primary key problems.

ALTER SEQUENCE Table_ID RESTART WITH 123

- V

Pavel Zotov added a comment - 09/May/15 09:39 PM
Seems that this ticket should be reopened.

Consider following (do it on empty database and completely new security3.fdb):

C:\FBTESTING\qa\fbt-repo\tmp>C:\1INSTALL\FIREBIRD\fb30sC\isql.exe localhost/3330:e30
Database: localhost/3330:e30
SQL> create or alter user maverick password '123'; commit;
SQL> revoke all on all from maverick; commit;
Warning: ALL on ALL is not granted to MAVERICK.
SQL> create sequence g_main; commit;
SQL> set list on;
SQL> select p.* from rdb$user_privileges p where trim(p.rdb$relation_name) = 'G_MAIN';

RDB$USER SYSDBA
RDB$GRANTOR SYSDBA
RDB$PRIVILEGE G
RDB$GRANT_OPTION 1
RDB$RELATION_NAME G_MAIN
RDB$FIELD_NAME <null>
RDB$USER_TYPE 8
RDB$OBJECT_TYPE 14


SQL> commit; connect 'localhost/3330:e30' user 'maverick' password '123';
Database: 'localhost/3330:e30', User: maverick
SQL> show sequ;
Generator G_MAIN, current value: 0, initial value: 0, increment: 1 --- STRANGE! user `maverick` should not even *see* value of sequence

SQL> alter sequence g_main restart with 567891004;
Statement failed, SQLSTATE = 28000
unsuccessful metadata update
-ALTER SEQUENCE G_MAIN failed
-no permission for ALTER access to GENERATOR G_MAIN -- it's OK


SQL> select gen_id(g_main, -123654789) from rdb$database;

GEN_ID -123654789 ----------------------- Why no error here ?


SQL> show sequ;
Generator G_MAIN, current value: -123654789, initial value: 0, increment: 1 --------- :(((

SQL> select * from mon$attachments where mon$attachment_id=current_connection;

MON$ATTACHMENT_ID 5
MON$USER MAVERICK
MON$ROLE NONE
MON$REMOTE_PROTOCOL TCPv4
MON$REMOTE_ADDRESS 127.0.0.1
MON$REMOTE_PROCESS C:\1INSTALL\FIREBIRD\fb30sC\isql.exe
MON$CLIENT_VERSION WI-T3.0.0.31828 Firebird 3.0 Beta 2
MON$REMOTE_VERSION P13
MON$REMOTE_HOST balaha
MON$REMOTE_OS_USER john smith
MON$AUTH_METHOD Srp
MON$SYSTEM_FLAG 0

SQL> quit;

Pavel Zotov added a comment - 31/May/15 01:47 PM
Reopen ticket - see my issue of 09/May/15 09:39 PM. Currently (WI-T3.0.0.31846) its all the same:

C:\FBTESTING\qa\fbt-repo\tmp>C:\1INSTALL\FIREBIRD\fb30sC\isql.exe /3330:e30 -user sysdba -pas masterke
Database: /3330:e30, User: sysdba
SQL> create sequence g; commit;
SQL> create user maverick password '123';
SQL> commit;
SQL> revoke all on all from maverick;
Warning: ALL on ALL is not granted to MAVERICK.
SQL> commit;
SQL> exit;

C:\FBTESTING\qa\fbt-repo\tmp>C:\1INSTALL\FIREBIRD\fb30sC\isql.exe /3330:e30 -user maverick -pas 123
Database: /3330:e30, User: maverick
SQL> show sequ;
Generator G, current value: 0, initial value: 0, increment: 1
SQL> select gen_id(g, -891289121) from rdb$database;

               GEN_ID
=====================
           -891289121

SQL> show sequ;
Generator G, current value: -891289121, initial value: 0, increment: 1
SQL>

Dmitry Yemanov added a comment - 31/May/15 06:35 PM
Re-closed, regression is already registered as CORE-4806.