SQL Rewrite blocking access to nodes that have a Referencing entity relationship in views

Hi
I'm experiencing that too, any solution yet ?

I have the same behavior. Any solution ?

I'm having a similar issue with a custom entity which has a property referencing a nid. This is provided by a custom module using the entity API, not using Entity Reference.

It's pretty much the same problem. The entity doesn't show up in views if the reference is null, unless the user has 'bypass access control' permission or we turn off rewriting. This suggests that the problem is not specific to the Entity reference module and is related to how Views handles relationships and access control.

First of all: i'm really non expert of D7, and i don't know PHP at all, i only have a bit of understanding in different programming languages.
So my conclusion could be totally wrong: but i'm here to propose a point of view, and i apologise in advance if this is not the right one! :)
I found that the problem seems to be generated by the query 'altered' by content access,
in the helper function '_node_query_node_access_alter' in the core "node" module (7.14 and 7.18 seems to be the same)

I found that this function try to add, for any table in the query generated by views, a piece of SQL code to check if the user has the right access grant to view the nodes.
Assume we have 2 content types:
- 'TypeA'
- 'TypeB'

and assume Type A has a reference to a 'TypeB' element, via fild 'ref_b' (the full name is node_field_data_field_ref_b).
In our view we try to extract a flat table with titles of both TypeA and related TypeB.
But assume we have an 'ElementA' (TypeA) but with the 'ref_b' field null: the problem comes here.
Due to the DB representation used in D7, the select query generated by 'views' is something like this (you can see this giving grant to administer views to a user with a role different by administrator, and configuring views to show the query generated):

SELECT node.title AS node_title, node.nid AS nid, node_field_data_field_ref_b.title AS node_field_data_field_ref_b_title, node_field_data_field_ref_b.nid AS node_field_data_field_ref_b_nid, node.created AS node_created
FROM
node node
LEFT JOIN field_data_field_ref_b field_data_field_ref_b ON node.nid = field_data_field_ref_b.entity_id AND (field_data_field_ref_b.entity_type = 'node' AND field_data_field_ref_b.deleted = '0')
LEFT JOIN node node_field_data_field_ref_b ON field_data_field_ref_b.field_ref_b_target_id = node_field_data_field_ref_b.nid
WHERE (( (node.status = '1') AND (node.type IN ('typeA')) ))
AND (( EXISTS (SELECT na.nid AS nid
FROM node_access na
WHERE (
( (na.gid = '0') AND (na.realm = 'all') )
OR( (na.gid = '14') AND (na.realm = 'content_access_author') )
OR( (na.gid = '2') AND (na.realm = 'content_access_rid') )
OR( (na.gid = '5') AND (na.realm = 'content_access_rid') )
)
AND (na.grant_view >= '1')
AND (node.nid = na.nid) )
)
AND
( EXISTS (SELECT na.nid AS nid
FROM node_access na
WHERE (
( (na.gid = '0') AND (na.realm = 'all') )
OR( (na.gid = '14') AND (na.realm = 'content_access_author') )
OR( (na.gid = '2') AND (na.realm = 'content_access_rid') )
OR( (na.gid = '5') AND (na.realm = 'content_access_rid') )
)
AND (na.grant_view >= '1')
AND (node_field_data_field_ref_b.nid = na.nid) )
)
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

So the function "_node_query_node_access_alter" in the 'Node' module adds a specific piece of code to check if exists a grant for the node being viewed, and for the other nodes referenced by the ones extracted by the query.
With this query, a node of TypeA, with a ref_b field void, will not be extracted, even if the current user has access to both TypeA and TypeB elements.
This is because of the second 'EXISTS' query will always fail: if the 'ref_b' field is void, there is nothing to extract joining this null with node_access table.
I think the right query would be:

SELECT node.title AS node_title, node.nid AS nid, node_field_data_field_ref_b.title AS node_field_data_field_ref_b_title, node_field_data_field_ref_b.nid AS node_field_data_field_ref_b_nid, node.created AS node_created
FROM
node node
LEFT JOIN field_data_field_ref_b field_data_field_ref_b ON node.nid = field_data_field_ref_b.entity_id AND (field_data_field_ref_b.entity_type = 'node' AND field_data_field_ref_b.deleted = '0')
LEFT JOIN node node_field_data_field_ref_b ON field_data_field_ref_b.field_ref_b_target_id = node_field_data_field_ref_b.nid
WHERE (( (node.status = '1') AND (node.type IN ('TypeA')) ))
AND
(( EXISTS (SELECT na.nid AS nid
FROM node_access na
WHERE (
( (na.gid = '0') AND (na.realm = 'all') )
OR( (na.gid = '14') AND (na.realm = 'content_access_author') )
OR( (na.gid = '2') AND (na.realm = 'content_access_rid') )
OR( (na.gid = '5') AND (na.realm = 'content_access_rid') )
)
AND (na.grant_view >= '1')
AND (node.nid = na.nid) )
)
OR node.nid IS NULL
)
AND
(( EXISTS (SELECT na.nid AS nid
FROM node_access na
WHERE (
( (na.gid = '0') AND (na.realm = 'all') )
OR( (na.gid = '14') AND (na.realm = 'content_access_author') )
OR( (na.gid = '2') AND (na.realm = 'content_access_rid') )
OR( (na.gid = '5') AND (na.realm = 'content_access_rid') )
)
AND (na.grant_view >= '1')
AND (node_field_data_field_ref_b.nid = na.nid) )
)
OR node_field_data_field_ref_b.nid IS NULL
)
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

This means that the user can have access to any node if:
- the EXISTS query can extract records from node_access for the referencing field
- or the referencing field is null and there is no access control to apply.

To do this i modified the node.module, in the _node_query_node_access_alter function, doing this substitution in the code:

FROM:
$query->exists($subquery);

TO:
$new_conditions = db_or();
$new_conditions->exists($subquery);
$new_conditions->isNull("$nalias.$field");
$query->condition($new_conditions);

This seems to be working for me!
With this code:
- i see node with a void referencing filed
- i see node with a reference field populated, only if i have access to the referenced node

So, due to my almost null experience in D7 and PHP, the big question now is: am i right?
Have i done everything alright or am i missing something important? (i dont know: maybe the node module is ok, and the problem is in the access module, in views or in entityreference/entity module???)
But if my conclusion is right, who can fix this problem in the core module?
So: what to do now?
Thanks in advance for any kind of support
Marco

I have exactly the same scenario, node type A with access restrictions.
A second node type B that has a entity reference field pointing to a node of type A.
I have created a couple of views that list nodes of type A.
I am using Drupal 7.18 and Views 7.x-3.5.
This all works well with Entityreference 7.x-1.0-rc5 but when I update to 7.x-1.0 and create new nodes of type A , they are not displayed anymore. However, the old ones, created with 7.x-1.0-rc5 are still visible under 7.x-1.0.

Based on what codisman mentioned I created a patch and posted to the core issue queue http://drupal.org/node/1969208. I think core's node module is responsible in this case but let's wait and see what community says about it.

I have tested the patch posted by Ericmaster in #1969208: Query node access alter filtering out accesible nodes and works for both Drupal 7.22/7.23 and Entity Reference 7.x-1.0. Good news in deed.

To me this issue is major since entities are missing in the View. It broke my site's editing functionality since a entity reference field used the View for validation / auto-complete.

Not sure if this can be easily fixed in this module itself, but that would be great.

NB! I don't use content_access or any other node access module - the problem is more general than the issue summary indicates.

just fixed the html tags

I marked #2152309: Add entity reference to a view cause the the view to display empty pages for non admin users. as a duplicate.

Potentially a duplicate of #1969208: Query node access alter filtering out accesible nodes which has a patch.