[teiid-issues] [JBoss JIRA] (TEIID-5802) Row based security doesn't work for materialized tables (views)

[ https://issues.jboss.org/browse/TEIID-5802?page=com.atlassian.jira.plugin... ] Dmitrii Pogorelov updated TEIID-5802: ------------------------------------- Steps to Reproduce: 1. Add views.test_view1 and views.test_view2 virtual views in test-vdb.xml: {code: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> {code} 2. Add the following permissions (I have role1 in my case) in test-vdb.xml: {code: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> {code} 3. Running the following query: {code:sql} SELECT "col0", "col2", "col3" FROM "views.test_view2" ;; {code} Teiid returns: {code} col0 col2 col3 sa 1 true sa 2 false {code} 4. Running the same query but having not materialized views.test_view2 (or not materialized views.test_view1 and views.test_view2 at all): {code:sql} SELECT "col0", "col2", "col3" FROM "views.test_view2" ;; {code} Teiid returns correct result: {code} col0 col2 col3 sa 2 false {code} was: 1. Add views.test_view1 and views.test_view2 virtual views in test-vdb.xml: {code:xm} <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> {code} 2. Add the following permissions (I have role1 in my case) in test-vdb.xml: {code: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> {code} 3. Running the following query: {code:sql} SELECT "col0", "col2", "col3" FROM "views.test_view2" ;; {code} Teiid returns: {code} col0 col2 col3 sa 1 true sa 2 false {code} 4. Running the same query but having not materialized views.test_view2 (or not materialized views.test_view1 and views.test_view2 at all): {code:sql} SELECT "col0", "col2", "col3" FROM "views.test_view2" ;; {code} Teiid returns correct result: {code} col0 col2 col3 sa 2 false {code} -- This message was sent by Atlassian Jira (v7.12.1#712002)