5 Replies Latest reply on Sep 22, 2015 2:56 PM by Steven Hawkins

    Duplicate security constraints, how to prevent that?

    Bas Piepers Novice

      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]