Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5802

Row based security and column masking don't work correctly for materialized tables (views)

    XMLWordPrintable

Details

    • Bug
    • Resolution: Unresolved
    • Major
    • Backlog
    • 12.0
    • Query Engine
    • None
    • Hide

      1. Add views.test_view1 and views.test_view2 virtual views in test-vdb.xml:

          <model visible = "true" type = "VIRTUAL" name = "views">
              <metadata type = "DDL"><![CDATA[
                create view test_view1(col1 string, col2 integer, col3 boolean) OPTIONS (
      			MATERIALIZED 'TRUE', 
      			UPDATABLE 'TRUE',
      			MATERIALIZED_TABLE 'dsp.mat_test_view1', 
      			"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
      			"teiid_rel:MATVIEW_TTL" 20000,
      			"teiid_rel:MATVIEW_STATUS_TABLE" 'dsp.status',
      			"teiid_rel:MATERIALIZED_STAGE_TABLE" 'dsp.mat_test_view1_staging',
      			"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute dsp.native(''truncate table mat_test_view1_staging'');',
                     		"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" '
      					execute dsp.native(''ALTER TABLE mat_test_view1 RENAME TO mat_test_view1_temp;ALTER TABLE mat_test_view1_staging RENAME TO mat_test_view1;ALTER TABLE mat_test_view1_temp RENAME TO mat_test_view1_staging;'');
      					' 
      	  ) as
                   SELECT 's', 1, true
                   UNION ALL
                   SELECT 's', 2, false
                   UNION ALL
                   SELECT 'a', 3, true
                   UNION ALL
                   SELECT 'a', 4, false;
                create view test_view2(col0 string, col2 integer, col3 boolean) OPTIONS (
      			MATERIALIZED 'TRUE', 
      			UPDATABLE 'TRUE',
      			MATERIALIZED_TABLE 'dsp.mat_test_view2', 
      			"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
      			"teiid_rel:MATVIEW_TTL" 20000,
      			"teiid_rel:MATVIEW_STATUS_TABLE" 'dsp.status',
      			"teiid_rel:MATERIALIZED_STAGE_TABLE" 'dsp.mat_test_view2_staging',
      			"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute dsp.native(''truncate table mat_test_view2_staging'');',
                     		"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" '
      					execute dsp.native(''ALTER TABLE mat_test_view2 RENAME TO mat_test_view2_temp;ALTER TABLE mat_test_view2_staging RENAME TO mat_test_view2;ALTER TABLE mat_test_view2_temp RENAME TO mat_test_view2_staging;'');
      					' 
      	  ) as
                   SELECT col1 || 'a' as col0, col2, col3 FROM views.test_view1
              ]]>
              </metadata>
          </model>
      

      2. Add the following permissions (I have role1 in my case) in test-vdb.xml:

          <data-role name="role1" any-authenticated="true" allow-create-temporary-tables="true">
              <description>Allow read only</description>
      
              <permission>
                  <resource-name>dsp</resource-name>
                  <allow-read>true</allow-read>
              </permission>
      
              <permission>
                  <resource-name>views.test_view1</resource-name>
                  <allow-read>true</allow-read>
                  <condition constraint="false">col2 > 1</condition>
              </permission>
      
              <permission>
                  <resource-name>views.test_view2</resource-name>
                  <allow-read>true</allow-read>
                  <condition constraint="false">col0 = 'sa'</condition>
              </permission>
          </data-role>
      

      3. Running the following query:

      SELECT "col0", "col2", "col3" FROM "views.test_view2" ;;
      

      Teiid returns:

      col0   col2    col3
      sa       1        true
      sa       2        false
      

      4. Running the same query but having not materialized views.test_view2 (or not materialized views.test_view1 and views.test_view2 at all):

      SELECT "col0", "col2", "col3" FROM "views.test_view2" ;;
      

      Teiid returns correct result:

      col0   col2    col3
      sa       2        false
      
      Show
      1. Add views.test_view1 and views.test_view2 virtual views in test-vdb.xml: <model visible = "true" type = "VIRTUAL" name = "views" > <metadata type = "DDL" > <![CDATA[ create view test_view1(col1 string, col2 integer, col3 boolean) OPTIONS ( MATERIALIZED 'TRUE' , UPDATABLE 'TRUE' , MATERIALIZED_TABLE 'dsp.mat_test_view1' , "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true' , "teiid_rel:MATVIEW_TTL" 20000, "teiid_rel:MATVIEW_STATUS_TABLE" 'dsp.status' , "teiid_rel:MATERIALIZED_STAGE_TABLE" 'dsp.mat_test_view1_staging' , "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute dsp.native(' 'truncate table mat_test_view1_staging' ');' , "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" ' execute dsp.native( ''ALTER TABLE mat_test_view1 RENAME TO mat_test_view1_temp;ALTER TABLE mat_test_view1_staging RENAME TO mat_test_view1;ALTER TABLE mat_test_view1_temp RENAME TO mat_test_view1_staging;' '); ' ) as SELECT 's' , 1, true UNION ALL SELECT 's' , 2, false UNION ALL SELECT 'a' , 3, true UNION ALL SELECT 'a' , 4, false; create view test_view2(col0 string, col2 integer, col3 boolean) OPTIONS ( MATERIALIZED 'TRUE' , UPDATABLE 'TRUE' , MATERIALIZED_TABLE 'dsp.mat_test_view2' , "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true' , "teiid_rel:MATVIEW_TTL" 20000, "teiid_rel:MATVIEW_STATUS_TABLE" 'dsp.status' , "teiid_rel:MATERIALIZED_STAGE_TABLE" 'dsp.mat_test_view2_staging' , "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute dsp.native(' 'truncate table mat_test_view2_staging' ');' , "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" ' execute dsp.native( ''ALTER TABLE mat_test_view2 RENAME TO mat_test_view2_temp;ALTER TABLE mat_test_view2_staging RENAME TO mat_test_view2;ALTER TABLE mat_test_view2_temp RENAME TO mat_test_view2_staging;' '); ' ) as SELECT col1 || 'a' as col0, col2, col3 FROM views.test_view1 ]]> </metadata> </model> 2. Add the following permissions (I have role1 in my case) in test-vdb.xml: <data-role name= "role1" any-authenticated= "true" allow-create-temporary-tables= "true" > <description> Allow read only </description> <permission> <resource-name> dsp </resource-name> <allow-read> true </allow-read> </permission> <permission> <resource-name> views.test_view1 </resource-name> <allow-read> true </allow-read> <condition constraint= "false" > col2 > 1 </condition> </permission> <permission> <resource-name> views.test_view2 </resource-name> <allow-read> true </allow-read> <condition constraint= "false" > col0 = 'sa' </condition> </permission> </data-role> 3. Running the following query: SELECT "col0" , "col2" , "col3" FROM "views.test_view2" ;; Teiid returns: col0 col2 col3 sa 1 true sa 2 false 4. Running the same query but having not materialized views.test_view2 (or not materialized views.test_view1 and views.test_view2 at all): SELECT "col0" , "col2" , "col3" FROM "views.test_view2" ;; Teiid returns correct result: col0 col2 col3 sa 2 false

    Description

      Row based security doesn't work for materialized tables (views). For example, having the following permissions for materialized views.test_view1 and views.test_view2 views according to query plan Teiid applies ApplySecurity rule only for views.test_view2 view but should also apply for views.test_view1 view:

          <data-role name="role1" any-authenticated="true" allow-create-temporary-tables="true">
              <description>Allow read only</description>
      
              <permission>
                  <resource-name>dsp</resource-name>
                  <allow-read>true</allow-read>
              </permission>
      
              <permission>
                  <resource-name>views.test_view1</resource-name>
                  <allow-read>true</allow-read>
                  <condition constraint="false">col2 > 1</condition>
              </permission>
      
              <permission>
                  <resource-name>views.test_view2</resource-name>
                  <allow-read>true</allow-read>
                  <condition constraint="false">col0 = 'sa'</condition>
              </permission>
          </data-role>
      

      The same situation is for column masking feature, see below.

      Attachments

        Activity

          People

            Unassigned Unassigned
            dalex005 Dmitrii Pogorelov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: