Project

General

Profile

bug #8219

Permission denied when creating a new reference with existing inreference for which submitter has no UPDATE permission

Added by Andreas Kohlbecker 7 months ago. Updated 6 months ago.

Status:
Rejected
Priority:
Highest
Category:
cdmlib
Target version:
-
Start date:
04/02/2019
Due date:
% Done:

10%

Severity:
blocker
Found in Version:

Description

This happened in the following case.

  • User andreas (submitter)
  • regStart view
  • create new reference with "A.W.F. Schmidt, Atlas Diatom.-Kunde, pl." as in reference
  • save
[phycobank_production] 18:23:34,320 ERROR [qtp759156157-110][or.hi.in.SessionImpl] - HHH000346: Error during managed flush [[UPDATE] not permitted for 'andreas' on Reference[uuid:a762f239-f56e-431c-b94d-ffb409b9cd26', toString:'Reference [type=Journal, abbrevTitle=A.W.F. Schmidt, Atlas Diatom.-Kunde, pl., id= 972, uuid=a762f239-f56e-431c-b94d-ffb409b9cd26]']]
[phycobank_production] 18:23:34,321 ERROR [qtp759156157-110][eu.et.va.mv.AbstractPopupEditor] - org.springframework.orm.hibernate5.HibernateSystemException: [UPDATE] not permitted for 'andreas' on Reference[uuid:a762f239-f56e-431c-b94d-ffb409b9cd26', toString:'Reference [type=Journal, abbrevTitle=A.W.F. Schmidt, Atlas Diatom.-Kunde, pl., id= 972, uuid=a762f239-f56e-431c-b94d-ffb409b9cd26]']; nested exception is eu.etaxonomy.cdm.database.PermissionDeniedException: [UPDATE] not permitted for 'andreas' on Reference[uuid:a762f239-f56e-431c-b94d-ffb409b9cd26', toString:'Reference [type=Journal, abbrevTitle=A.W.F. Schmidt, Atlas Diatom.-Kunde, pl., id= 972, uuid=a762f239-f56e-431c-b94d-ffb409b9cd26]']

picture262-1.png View (62.7 KB) Andreas Kohlbecker, 04/02/2019 06:34 PM

History

#1 Updated by Andreas Kohlbecker 7 months ago

this is most probably once again a problem related to a field of the reference modified during the data management done by the system for which the user should not be required to have according granted authorities,.

#2 Updated by Andreas Kohlbecker 7 months ago

#3 Updated by Andreas Kohlbecker 7 months ago

  • % Done changed from 0 to 10

in this case the following properties are changed behind the scenes:

modified property found: protectedTitleCache, previousState: false, currentState: true
modified property found: nomenclaturallyRelevant, previousState: false, currentState: true
modified property found: protectedAbbrevTitleCache, previousState: false, currentState: true

#4 Updated by Andreas Kohlbecker 7 months ago

  • Status changed from New to Feedback
  • Assignee changed from Andreas Kohlbecker to Andreas Müller

Does this come from the DefaultMatchStrategy?

What is this DefaultMatchStrategy for? This class completely lacky any documentation. This needs to be added urgently!

#5 Updated by Andreas Müller 7 months ago

Andreas Kohlbecker wrote:

Does this come from the DefaultMatchStrategy?

What is this DefaultMatchStrategy for? This class completely lacky any documentation. This needs to be added urgently!

Why do you think that DefaultMatchStrategy is somehow related? DefaultMatchStrategy is for finding "duplicates". I can't see any relation.

#6 Updated by Andreas Müller 7 months ago

Andreas Kohlbecker wrote:

in this case the following properties are changed behind the scenes:

modified property found: protectedTitleCache, previousState: false, currentState: true
modified property found: nomenclaturallyRelevant, previousState: false, currentState: true
modified property found: protectedAbbrevTitleCache, previousState: false, currentState: true

This is really strange. Definetely the protectedXXX values should not change when adding an inreference to an existing reference. This needs urgently to be checked how this happens. Especially nomenclaturallyRelevant is strange. The setter for nomenclaturallyRelevant is called only in tests and in BerlinModel import. So the change can only happen via direct access to the field, I guess.

#7 Updated by Andreas Kohlbecker 6 months ago

I now pinned down the cause to the .IIdentifiableEntityService.findByTitleWithRestrictions(..) methods.

What is happening here is mysteriously strange:

The SQL query which is being executed here in case of the situation of the inReference is

SELECT 
this_.id as id1_397_0_, this_.created as created2_397_0_, this_.createdBy_id as created43_397_0_, this_.uuid as uuid3_397_0_, this_.updated as updated4_397_0_, this_.updatedBy_id as updated44_397_0_, this_.lsid_authority as lsid_aut5_397_0_, this_.lsid_lsid as lsid_lsi6_397_0_, this_.lsid_namespace as lsid_nam7_397_0_, this_.lsid_object as lsid_obj8_397_0_, this_.lsid_revision as lsid_rev9_397_0_, this_.protectedTitleCache as protect10_397_0_, this_.titleCache as titleCa11_397_0_, this_.abbrevTitle as abbrevT12_397_0_, this_.abbrevTitleCache as abbrevT13_397_0_, this_.accessed as accesse14_397_0_, this_.authorityType as authori15_397_0_, this_.authorship_id as authors45_397_0_, this_.datePublished_verbatimDate as datePub16_397_0_, this_.datePublished_end as datePub17_397_0_, this_.datePublished_freeText as datePub18_397_0_, this_.datePublished_start as datePub19_397_0_, this_.doi as doi20_397_0_, this_.edition as edition21_397_0_, this_.editor as editor22_397_0_, this_.externalId as externa23_397_0_, this_.externalLink as externa24_397_0_, this_.inReference_id as inRefer46_397_0_, this_.institution_id as institu47_397_0_, this_.isbn as isbn25_397_0_, this_.issn as issn26_397_0_, this_.lastRetrieved as lastRet27_397_0_, this_.nomenclaturallyRelevant as nomencl28_397_0_, this_.organization as organiz29_397_0_, this_.pages as pages30_397_0_, this_.parsingProblem as parsing31_397_0_, this_.placePublished as placePu32_397_0_, this_.problemEnds as problem33_397_0_, this_.problemStarts as problem34_397_0_, this_.protectedAbbrevTitleCache as protect35_397_0_, this_.publisher as publish36_397_0_, this_.referenceAbstract as referen37_397_0_, this_.school_id as school_48_397_0_, this_.seriesPart as seriesP38_397_0_, this_.title as title39_397_0_, this_.refType as refType40_397_0_, this_.uri as uri41_397_0_, this_.volume as volume42_397_0_ 
from Reference this_ where this_.id in (select this_.id as y0_ from Reference this_ 
where ((1=1 and (lower(this_.titleCache) like 'A%')) and (this_.refType='JOU'))) order by this_.isbn asc, this_.issn asc, this_.titleCache asc;

which can be simplified to

SELECT this_.*
from Reference this_ where this_.id in (select this_.id as y0_ from Reference this_ 
where ((1=1 and (lower(this_.titleCache) like 'A%')) and (this_.refType='JOU'))) order by this_.isbn asc, this_.issn asc, this_.titleCache asc;

in both cases the field of protectedTitleCache, nomenclaturallyRelevant, protectedAbbrevTitleCache are returned as true for all records despite the actual value in the db

In the attempt to get a compact view on the result set i changed the query to the following, which is exactly the same as above, The only difference in that only a selection of fields is returned:

SELECT this_.id, this_.titleCache, this_.nomenclaturallyRelevant
from Reference this_ where this_.id in (select this_.id as y0_ from Reference this_ 
where ((1=1 and (lower(this_.titleCache) like 'A%')) and (this_.refType='JOU'))) order by this_.isbn asc, this_.issn asc, this_.titleCache asc;

In this case the query returns the actual values from the db

#8 Updated by Andreas Kohlbecker 6 months ago

The above described effect can be more isolated with the following queries:

Correct results:

SELECT 
this_.nomenclaturallyRelevant
from Reference this_ where this_.id in (select this_.id as y0_ from Reference this_ 
where ((1=1 and (lower(this_.titleCache) like 'A%')) and (this_.refType='JOU'))) order by this_.isbn asc, this_.issn asc, this_.titleCache asc;

modified results:

SELECT 
this_.nomenclaturallyRelevant, 
this_.externalLink
from Reference this_ where this_.id in (select this_.id as y0_ from Reference this_ 
where ((1=1 and (lower(this_.titleCache) like 'A%')) and (this_.refType='JOU'))) order by this_.isbn asc, this_.issn asc, this_.titleCache asc;

What is so special to the externalLink that it causes the mysql server to misbehave apparently ?

#9 Updated by Andreas Kohlbecker 6 months ago

The above bug seems only to occur on my laptop which is running

mysqld Ver 5.7.25-0ubuntu0.16.04.2 for Linux on x86_64 ((Ubuntu))

it can not be reproduced on the servers:

  • edit-database: mysqld Ver 10.1.37-MariaDB-0+deb9u1 for debian-linux-gnu on x86_64 (Debian 9.6)
  • edit-test: mysqld Ver 5.5.62-0+deb8u1 for debian-linux-gnu on x86_64 ((Debian))

#10 Updated by Andreas Kohlbecker 6 months ago

This could be related to the mysql bug https://bugs.mysql.com/bug.php?id=94795

#11 Updated by Andreas Kohlbecker 6 months ago

further reduced the query reproducing the bug to

SELECT 
this_.nomenclaturallyRelevant,
this_.externalLink
from Reference this_ where this_.id in (select this_.id as y0_ from Reference this_ 
where this_.refType='JOU') order by this_.externalLink asc;

#12 Updated by Andreas Kohlbecker 6 months ago

  • Status changed from Feedback to Rejected
  • Assignee changed from Andreas Müller to Andreas Kohlbecker
  • Target version deleted (Release 5.6)

I filed a bug report in the Oracle bug tracker:

https://bugs.mysql.com/bug.php?id=94973

since we can not do anything about this problem I will close this issue as rejected. We can only hope that this issue is not ocurring in any other version of mysql since bug can this can alter data arbitrarily

#13 Updated by Andreas Kohlbecker 6 months ago

  • Status changed from Rejected to In Progress

reopening since I seem to remember that this bug has been originally reported by Heba when working on the test or production system

#14 Updated by Andreas Kohlbecker 6 months ago

  • Target version set to Release 5.6

#15 Updated by Andreas Müller 6 months ago

Is the subselect necessary to reproduce the behavior in the above SQL statements. If not we should tear down to a single record with a specific id and see if it still exists.

#16 Updated by Andreas Müller 6 months ago

Andreas Kohlbecker wrote:

reopening since I seem to remember that this bug has been originally reported by Heba when working on the test or production system

Is it the same behavior there? What was the orginal error report? Did you try to reproduce on test server?

#17 Updated by Andreas Kohlbecker 6 months ago

Andreas Müller wrote:

Is the subselect necessary to reproduce the behavior in the above SQL statements. If not we should tear down to a single record with a specific id and see if it still exists.

Yes

Is it the same behavior there?

I am about to figure that out.

Please see also my bug report https://bugs.mysql.com/bug.php?id=94973 I consider this a really severe bug!

#18 Updated by Andreas Müller 6 months ago

What I do not understand is if already the select creates the wrong result how can hibernate (security) know that the data was updated. For hibernate it must look like the data was not updated as it does not know the state from the database. So I don't know if the above described bug can really lead to the Permission denied exception. But maybe I am missing something.

#19 Updated by Andreas Kohlbecker 6 months ago

Andreas Müller wrote:

What I do not understand is if already the select creates the wrong result how can hibernate (security) know that the data was updated. For hibernate it must look like the data was not updated as it does not know the state from the database. So I don't know if the above described bug can really lead to the Permission denied exception. But maybe I am missing something.

This is easy to explain: The same object has been loaded before without being modified through the msql optimizer and was cached in the transient entity cache. Subsequent interaction of the user with the UI casues the object to be loaded via another service method in which case the mysql bug becomes relevant
and the modified object in also stored in the cache. This is just as if the user has modified the object.

BTW: This mysql bug seems to affect BYTE fields only.

#20 Updated by Andreas Kohlbecker 6 months ago

  • Status changed from In Progress to Rejected
  • Target version deleted (Release 5.6)

I could not reproduce this bug in the test system. There where also no related error report in the mails or in Riot so closing this issue as 'rejected' is ok.

Also available in: Atom PDF

Add picture from clipboard (Maximum size: 40 MB)