Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Grants access on generators (gen_id, next value for) [CORE2553] #2963

Closed
firebird-automations opened this issue Jul 13, 2009 · 18 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: eXandr (i.reg)

Is duplicated by CORE1141
Relate to CORE4806
Is related to QA644

Votes: 2

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}

====== Test Details ======

Test not needed for this ticket.
See core_4806.fbt instead.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

priority: Critical [ 2 ] => Major [ 3 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

summary: Grants access on generators (gen_id, next value for) => Grants access on generators (gen_id, next value for)

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: eXandr (i.reg)

If no permissions - rase error. Zero increment - wrong way.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE1141 [ CORE1141 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Vannus (vannus)

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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;

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE4806 [ CORE4806 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Re-closed, regression is already registered as CORE4806.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA644 [ QA644 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

Test Details: Test not needed for this ticket.
See core_4806.fbt instead.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment