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
isql 192.168.0.201/3330:empty30 -user sysdba -pas masterke
Database: 192.168.0.201/3330:empty30, User: sysdba
SQL> create table autoid_test(id int generated by default as identity primary key, f01 int);
SQL> insert into autoid_test(f01) values(100);
SQL> insert into autoid_test(f01) values(200);
SQL> insert into autoid_test(f01) values(300);
SQL> commit;
SQL> select * from autoid_test; commit;
ID F01
============ ============
1 100
2 200
3 300
SQL> show sequ;
There are no generators in this database
SQL> commit;
-- now we create non_priviledged user and give him all possible rights for DML operations:
SQL> create user u30tmp1 password '123'; commit;
SQL> grant insert,update,delete,select on autoid_test to u30tmp1; commit;
-- connect under just created user and check what he really can do with that table:
SQL> connect '192.168.0.201/3330:empty30' user 'u30tmp1' password '123';
Database: '192.168.0.201/3330:empty30', User: u30tmp1
SQL> select * from autoid_test; -- OK
ID F01
============ ============
1 100
2 200
3 300
SQL> update autoid_test set f01=-f01 where id=1; -- OK
SQL> delete from autoid_test where id>1; -- OK
SQL> insert into autoid_test(f01) values(555); -- error
Statement failed, SQLSTATE = 28000
no permission for USAGE access to GENERATOR RDB$1
Funny enough, the SQL spec declares both IDENTITY and USAGE features but I failed to find how they are expected to correlate. Technically speaking, there are two options: grant the implicit generator's USAGE permission to PUBLIC or imply the USAGE permission every time an INSERT (and perhaps later UPDATE as well) permission is granted for a table. But for the latter, the question is whether USAGE should be revoked when INSERT is revoked.
Submitted by: @pavel-zotov
Votes: 1
isql 192.168.0.201/3330:empty30 -user sysdba -pas masterke
Database: 192.168.0.201/3330:empty30, User: sysdba
SQL> create table autoid_test(id int generated by default as identity primary key, f01 int);
SQL> insert into autoid_test(f01) values(100);
SQL> insert into autoid_test(f01) values(200);
SQL> insert into autoid_test(f01) values(300);
SQL> commit;
SQL> select * from autoid_test; commit;
============ ============
1 100
2 200
3 300
SQL> show sequ;
There are no generators in this database
SQL> commit;
-- now we create non_priviledged user and give him all possible rights for DML operations:
SQL> create user u30tmp1 password '123'; commit;
SQL> grant insert,update,delete,select on autoid_test to u30tmp1; commit;
-- connect under just created user and check what he really can do with that table:
SQL> connect '192.168.0.201/3330:empty30' user 'u30tmp1' password '123';
Database: '192.168.0.201/3330:empty30', User: u30tmp1
SQL> select * from autoid_test; -- OK
============ ============
1 100
2 200
3 300
SQL> update autoid_test set f01=-f01 where id=1; -- OK
SQL> delete from autoid_test where id>1; -- OK
SQL> insert into autoid_test(f01) values(555); -- error
Statement failed, SQLSTATE = 28000
no permission for USAGE access to GENERATOR RDB$1
Commits: 0b46869 FirebirdSQL/fbt-repository@b365fb1
The text was updated successfully, but these errors were encountered: