We use the users and privileges from Firebird and we are facing the following problem:
Sometimes the user has the privilege to update a field but firebird throw a message which says the user doesn't have it.
You can download a database sample from here: http://www.poliview.com.br/arquivos/database.zip
Connect to the database as LUCICELIA and try the following command:
UPDATE SPR_ITEM SET ITEM_QUADRO = 2189 WHERE ITEM_EMPRD = 133 AND ITEM_REQ = 498;
The error message is going to be: "This user does not have privilege to perform this operation on this object.
no privilege for update/write access to COLUMN SPR_RI.RI_STATUS"
if you use the query: "SELECT * FROM RDB$USER_PRIVILEGES U WHERE U.RDB$USER = 'LUCICELIA' and u.RDB$RELATION_NAME = 'SPR_RI'; there is a record for this field.
If we delete this record and use the grant command again, the user still doesn't have the privilege to update the field.
The only way to correct this is backing up and restoring the database, but this is happening frequently and we can't do this all the time.
This already happened on firebird 1.5.x and we are trying the 2.5 now but the error still occurring