Duplicate security constraints, how to prevent that?

[bpiepers]

In Teiid 8.4, I have one table that is "protected" by a row based security constraint. The corresponding role is set to be for all users that log on. I join the protected view with the tables that I want to limit the rows on. What I notice, however, is that in case a client uses two tables that are joining the protected view, that the corresponding security constraint and the joins themselves are duplicated. Is there a way to prevent this?

Example: I created a role called DATA_USER_MACHINE that filters on a USERNAME column in that table:

<permission>

     <resource-name>vbl.USER_MACHINE_SECURE</resource-name>

     <condition constraint="true">USERNAME=ucase(substring( user(), 1, locate( '@', user())-1))</condition>

</permission>     

Then I have two tables in my View model that use the secured view:

Table EVENT:

SELECT

[bunch of fields]

  FROM

  source.EVENT AS E, USER_MACHINE_DIM_SECURE AS S

  WHERE

  E.MACHINE_ID = S.ID

Table MACHINE:

SELECT

[bunch of fields]

FROM

source.MACHINE AS M, USER_MACHINE_SECURE AS S

WHERE

M.MACHINE_ID = S.ID

If I connect to the corresponding VDB and invoke a SELECT statement where boh EVENT and MACHINE are used:

SELECT ef.column1, md.column1, [more columns]

FROM

MACHINE md

JOIN

EVENT ef

ON md.MACHINE_ID = ef.MACHINE_ID

WHERE

[where clause ]

This gets translated like this:

SELECT

[columns]

FROM

source.MACHINE AS g_0,

source.USER_MACHINE AS g_1,

sourceEVENT AS g_2,

source.USER_MACHINE AS g_3

WHERE (g_2.MACHINE_ID = g_3.MACHINE_ID) AND (g_0.MACHINE_ID = g_1.MACHINE_ID) AND (g_0.MACHINE_ID = g_2.MACHINE_ID)

(g_1.USERNAME = 'TESTUSER')

AND (g_3.USERNAME = 'TESTUSER')

AND [rest of where clause client provided]

[shawkins]

> What I notice, however, is that in case a client uses two tables that are joining the protected view, that the corresponding security constraint and the joins themselves are duplicated. Is there a way to prevent this?

Taking the security component out of this, you would still see the same initial structure for the join - that is the USER_MACHINE will be repeated.  [TEIID-160] Remove redundant self-joins - JBoss Issue Tracker covers generally using query minimization logic to remove redundant self-joins, but has not yet been worked. 

[bpiepers]

Thank you Steven. That's not very good news then. I suspect similar logic wrt the security constraint.

[shawkins]

> I suspect similar logic wrt the security constraint.

The security constraint is entirely secondary as it will always be added to the applicable tables/views in the plan to filter in the appropriate manner. 

Another thing to consider is that if two subplans produce identical pushdown queries, then the results will re-reused in the query plan.  Otherwise if you have a situation where having the redundant self-join is causing a performance issue, then please update the issue with your details and we can get it addressed with a higher priority.

[bpiepers]

The results of what will be re-used? We are currently investigating the performance penalty of having these redundant self joins but they are pushed down anyway so I'm not sure what you mean with "if two subplans produce identical pushdown queries, the the results will be re-reused"...

[shawkins]

> The results of what will be re-used?

Meaning that is two subplans result in the same pushdown query, then the results of the pushdown query will be reused rather than re-queried.  However that doesn't apply here if everything is pushed down.