User can not insert records into table with column "generated by default as 
identity" in its DDL
------------------------------------------------------------------------------------------------

                 Key: CORE-4161
                 URL: http://tracker.firebirdsql.org/browse/CORE-4161
             Project: Firebird Core
          Issue Type: Bug
          Components: Engine
    Affects Versions: 3.0 Alpha 1
            Reporter: Pavel Zotov


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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

------------------------------------------------------------------------------
Get your SQL database under version control now!
Version control is standard for application code, but databases havent 
caught up. So what steps can you take to put your SQL databases under 
version control? Why should you start doing it? Read more to find out.
http://pubads.g.doubleclick.net/gampad/clk?id=49501711&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Reply via email to