[teiid-issues] [JBoss JIRA] (TEIID-5802) Row based security doesn't work for materialized tables (views)
Dmitrii Pogorelov (Jira)
issues at jboss.org
Thu Aug 8 08:49:00 EDT 2019
[ https://issues.jboss.org/browse/TEIID-5802?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
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}
> Row based security doesn't work for materialized tables (views)
> ---------------------------------------------------------------
>
> Key: TEIID-5802
> URL: https://issues.jboss.org/browse/TEIID-5802
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 12.0
> Environment: teiid-12.0.0 on WildFly Full 14.0.1.Final (WildFly Core 6.0.2.Final)
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Blocker
>
> 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:
> {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}
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
More information about the teiid-issues
mailing list