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]