Database View fields not visible via a Report

[simonbourne]

I'm running Istanbul.  I have a Database View combining the Incident and Problem tables.  I have generated a Single Score report counting the number of Active problems.  The report has been shared with Everyone.  The report runs successfully and clicking on the Single Score should show a List of the Problem records, but it is resulting in a Number of rows removed from this list by Security constraints message.

I have an ACL set up on the Database View to allow ITIL users to Read (--none--) the Records and both the Incident and Problem tables are available to the ITIL users.  Ironically, the same report works on our Development instance.  As far as I can see, the Database View, Report and ACLs are the same...what am I missing?

[SanjivMeher]

Ok Great. You dont need the ACL with Table.* . Only keep Table. None

[simonbourne]

Is that because the ACL Table.None provides read access to the columns within the Database View, based on the ACLs defined for the underlying tables?

I'm still getting the same "Security constraints" issue.  The report works fine if run from the Incident Table and fine if run from the Problem table.  This suggests to me that the issue lies with one or more fields in the Database View itself.

I've checked all the Read ACLs configured on the Incident and Problem tables and there is nothing apart from some with ITIL user restrictions.

Admins can run the report fine.  Does the Database View look OK to you?

[SanjivMeher]

The database view looks fine to me. You can unmark the left join on incident.

The only issue I found was the table.*. Because when you add table.*, you will have to start specifying individual field that the role needs access to.

Also if you removed table.*, also add public role to table.None. and do a cache.do and try again.

[simonbourne]

All working as designed now.  I rebuilt the DB View and ACLs.  Adding the specific fields to the DB View was the finishing touch 🙂

[Hiranmayee Moha]

Hi @simonbourne & @SanjivMeher !

I am getting below error. kindly help me solve this.

Thanks