Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
firebird-automations opened this issue Sep 10, 2010 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Commented by: Alan Braga (alansbraga)

UDF for the database

@firebird-automations
Copy link
Collaborator Author

Modified by: Alan Braga (alansbraga)

Attachment: rfunc.rar [ 11781 ]

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant