Loading...

Type: Bug
Resolution: Unresolved
Priority: Major
Fix Version/s: Backlog
Affects Version/s: 12.0
Component/s: Query Engine
Labels:
None

Steps to Reproduce:
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

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.

Details

Description

Attachments

Easy Agile Planning Poker

Activity

People

Dates