search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.23k stars 212 forks source link

User has a record on Permission table but firebird doesn't allow the update [CORE3133] #3510

Open firebird-automations opened 14 years ago

firebird-automations commented 14 years ago

Submitted by: Alan Braga (alansbraga)

Attachments: rfunc.rar

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

firebird-automations commented 14 years ago

Commented by: @helebor

Shouldn't your test be as follows?

SELECT * FROM RDB$USER_PRIVILEGES U WHERE U.RDB$USER = 'LUCICELIA' and u.RDB$RELATION_NAME = 'SPR_ITEM'

firebird-automations commented 14 years ago

Commented by: Alan Braga (alansbraga)

This user has permission to all the fields on table SPR_ITEM (RDB$FIELD_NAME = null)

There is a trigger on SPR_ITEM that update SPR_RI and lots of other things

firebird-automations commented 14 years ago

Commented by: @asfernandes

I was going to test, but found your database full of UDFs that I don't have.

Can't you create minimal possible test case?

firebird-automations commented 14 years ago

Commented by: Alan Braga (alansbraga)

UDF for the database

firebird-automations commented 14 years ago
Modified by: Alan Braga (alansbraga) Attachment: rfunc\.rar \[ 11781 \]
firebird-automations commented 14 years ago

Commented by: Alan Braga (alansbraga)

Sorry I had forgot the UDF. I attached here.

Unfortunately I don't know how to create the problem and it happens frequently in some of our clients. I can send you another database but the size and the structure is almost the same.

Thank you Helen and Adriano for the support

firebird-automations commented 14 years ago

Commented by: @asfernandes

The problem is that RDB$RELATIONS.RDB$RUNTIME is not correctly maintained for columns security classes, because these grants don't schedules a relation format change.

When a column security class is missing in RDB$RUNTIME, its got from RDB$RELATIONS.RDB$DEFAULT_CLASS. That is not correct.

As soon a backup is restored, RDB$RUNTIME is recreated and everything works as desired.