Description:
Under certain circumstands, a value in a table column will not be updated, if the update command is wrapped by a dbproc.
To provocate the bug:
- The problem table should contain a SERIAL column and a column that is not SERIAL.
- The problem dbproc should update the non-serial column.
- The dbproc should be called by an other user than the owner, who is allowed to call by GRANT EXECUTE.
- The user that is not the owner should be SET a ROLE.
Please read the exact constellation in the lines below, it is a bit complex but I cannot describe it easier.
How to repeat:
Part 1: to create a new database according to the examples in the compiled html handbook. This part ensures that the problem happens at a fresh database.
1.1 dbmcli> db_create problem prosys,123
1.2 dbmcli> param_startsession
1.3 dbmcli> param_init
1.4 dbmcli> param_put MAXUSERTASKS 5
1.5 dbmcli> param_checkall
1.6 dbmcli> param_commitsession
1.7 dbmcli> param_createvolume 1 LOG LOG_001 F 2000
1.8 dbmcli> param_createvolume 1 DATA LOG_001 F 10000
1.9 dbmcli> db_admin
1.10 dbmcli> db_activate proman,123
1.11 dbmcli> load_systab -ud 123
1.12 dbcmli> db_online
1.13 dbmcli> quit
Part 2: to create the problem infrastructure and to control its correctness:
2.1 sqlcli> \c -u proman,123 -d problem
2.2 sqlcli> CREATE USER John PASSWORD 123 DBA NOT EXCLUSIVE
2.3 sqlcli> \c -u John,123 -d problem
2.4 sqlcli[John]> CREATE TABLE simple(value INT, problemcolumn INT DEFAULT SERIAL PRIMARY KEY)
2.5 sqlcli[John]> CREATE DBPROC setvalue(IN value INT) AS UPDATE John.simple SET value = :value;
2.6 sqlcli[John]> SELECT * FROM simple
¦ VALUE ¦ PROBLEMCOLUMN ¦
¦-------------¦------------------¦
¦ 0 ¦ 1¦
2.7 sqlcli[John]> CALL setvalue(4)
2.8 sqlcli[John]> SELECT * FROM simple
¦ VALUE ¦ PROBLEMCOLUMN ¦
¦-------------¦------------------¦
¦ 4 ¦ 1¦
(So far this works fine.)
Part 3: to provocate the problem:
3.1 sqlcli[John]> CREATE USER Kai PASSWORD 123 RESOURCE NOT EXCLUSIVE
3.2 sqlcli[John]> GRANT EXECUTE ON setvalue TO Kai
3.3 open a second terminal, logon with Kai
3.3 sqlcli[Kai]>CALL John.setvalue(5)
control at John:
3.4 sqlcli[John]> SELECT * FROM simple
¦ VALUE ¦ PROBLEMCOLUMN ¦
¦-------------¦------------------¦
¦ 5¦ 1¦
(Still everything is ok. Now the the bug itself happens:)
3.5 sqlcli[john]>CREATE ROLE kai_role
3.6 sqlcli[john]>GRANT kai_role TO kai
3.7 sqlcli[kai]> SET ROLE kai_role
3.8 sqlcli[kai]> CALL john.setvalue(6)
3.9 sqlcli[john]> SELECT * FROM simple
¦ VALUE ¦ PROBLEMCOLUMN ¦
¦-------------¦------------------¦
¦ 5 ¦ 1¦
*************************
*** Value should be 6 ***
*************************
Remrks:
- Write SET ROLE NONE at John and the call at 3.8 works fine again.
- The column "problem column" was called like this because the problem doesn't happen without this column. According to what I've seen I think the problem is rather SERIAL than PRIMARY KEY. Please note that I don't try to write into the problem column.
- An UPDATE to the table john.simple from the terminal Kai without a dbproc call is no problem.
Suggested fix:
To evide, dont' use DEFAULT SERIAL in tables that are updated by DBPROCes. Write a code line that increments the number manually instead.
