Bug 1156465

Summary:	GetAllRolesByUserIdAndGroupIds should not return duplicate rows
Product:	[Retired] oVirt	Reporter:	Tim Speetjens <tspeetje>
Component:	ovirt-engine-webadmin	Assignee:	Eli Mesika <emesika>
Status:	CLOSED CURRENTRELEASE	QA Contact:	Pavel Stehlik <pstehlik>
Severity:	medium	Docs Contact:
Priority:	medium
Version:	3.5	CC:	bugs, ecohen, gklein, iheim, lsurette, mgoldboi, oourfali, rbalakri, tspeetje, yeylon
Target Milestone:	---
Target Release:	3.5.1
Hardware:	x86_64
OS:	Linux
Whiteboard:	infra
Fixed In Version:	ovirt-3.5.1_rc1	Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2015-01-21 16:06:21 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	Infra	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:

Description Tim Speetjens 2014-10-24 14:19:38 UTC

Description of problem:
The query defined for GetAllRolesByUserIdAndGroupIds in multi_level_administration_sp.sql returns duplicates

Version-Release number of selected component (if applicable):
ovirt 3.5

How reproducible:
100%

Steps to Reproduce:
Add some permissions in the permissions table for the user specified in the query, referencing, for example non-existing disks. (not limited to disks: nicprofiles, etc suffer from this too). Also, multiple other reasons for duplicates exist (as the tables definition currently allows this by intent)

Run the query, with a user id that is granted the permission

Actual results:
This query returns duplicates.

Expected results:
This query should not return duplicates, whatsoever.

The database doesn't contain a foreign key constraint from object_id to disk_id, or any other object in the permissions table (it's impossible, as depending on the object_type_id, it should be defined to another table). This may lead to orphaned data, as has been identified with real world databases.

In moderate to big environments, the list with duplicates becomes long enough to have an impact on the ovirt engine stability. (A customer database had > 1k records here, almost all duplicates)

Comment 1 Oved Ourfali 2014-10-26 09:04:45 UTC

Eli - please make sure to verify the fix, and make it part of oVirt 3.5.1.

Comment 2 Eli Mesika 2014-10-26 11:16:42 UTC

I had checked the disks and VNIC profiles and both are removing the relevant permission when deleted.

Please attach a database in which this occurs using the engine application (without manual modification of the database), since as far as I can see from the DB code,, this is handled properly by the various SPs that performes the relevant entity remove command

Comment 3 Eli Mesika 2014-10-26 11:17:58 UTC

Removing original patch since it will not handle the problem reported in this bug.

Comment 4 Tim Speetjens 2014-10-26 16:29:04 UTC

I could not find the source of the orphaned disk permissions, on a 3.4 installation. However, the database design still allows for duplicates in the query as it is defined.

One possible way to get it to return duplicates, is to have 'orphaned' disk permissions, however, I did just find that 'multiple' disk permissions, that still point to valid disks, have the same effect.

Also, when multiple VNicProfiles are created, with permissions granted, duplicates are present.

The patch doesn't avoid the orphaned permissions, but does remove the duplicates.

Comment 5 Sandro Bonazzola 2015-01-15 14:15:34 UTC

This is an automated message: 
This bug should be fixed in oVirt 3.5.1 RC1, moving to QA

Comment 6 Sandro Bonazzola 2015-01-21 16:06:21 UTC

oVirt 3.5.1 has been released. If problems still persist, please make note of it in this bug report.