[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 11:06:00 EDT 2019
[ https://issues.jboss.org/browse/TEIID-5802?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13768691#comment-13768691 ]
Dmitrii Pogorelov commented on TEIID-5802:
------------------------------------------
Pretty the same case for column masking feature:
1. In postgresql create the test_a table using the following script:
{code:sql}
CREATE TABLE public.test_a
(
a integer,
b integer
);
INSERT INTO public.test_a(a, b) VALUES (1, 1);
INSERT INTO public.test_a(a, b) VALUES (1, 2);
INSERT INTO public.test_a(a, b) VALUES (2, 1);
INSERT INTO public.test_a(a, b) VALUES (2, 2);
INSERT INTO public.test_a(a, b) VALUES (3, 2);
INSERT INTO public.test_a(a, b) VALUES (3, 10);
{code}
2. Add postgresql database configuration in standalone-teiid.xml:
{code:xml}
<datasource jndi-name="java:/test_pg" pool-name="test_pg" enabled="true" use-java-context="true">
<connection-url>jdbc:postgresql://localhost:5432/test_db?charSet=utf8</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<driver>org.postgresql</driver>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>70</max-pool-size>
<prefill>false</prefill>
<use-strict-min>false</use-strict-min>
<flush-strategy>FailingConnectionOnly</flush-strategy>
</pool>
<security>
<user-name>XXXXX</user-name>
<password>XXXXX</password>
</security>
<validation>
<check-valid-connection-sql>select 0</check-valid-connection-sql>
</validation>
<timeout>
<blocking-timeout-millis>120000</blocking-timeout-millis>
<idle-timeout-minutes>5</idle-timeout-minutes>
</timeout>
</datasource>
{code}
3. Add in test-vdb.xml java:/test_pg configured in previous step as datasource:
{code:xml}
<model name="dsp">
<property name="importer.useFullSchemaName" value="false"/>
<property name="importer.tableTypes" value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false"/>
<source name="test_pg" translator-name="myPg" connection-jndi-name="java:/test_pg"/>
</model>
{code}
4. Configure in the test-vdb.xml the following virtual view:
{code:xml}
<model visible = "true" type = "VIRTUAL" name = "views">
<metadata type = "DDL"><![CDATA[
create view colMask_view3(col1 integer, col2 integer) OPTIONS (
MATERIALIZED 'TRUE',
UPDATABLE 'TRUE',
MATERIALIZED_TABLE 'dsp.mat_colMask_view3',
"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_colMask_view3_staging',
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute dsp.native(''truncate table mat_colMask_view3_staging'');',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" '
execute dsp.native(''ALTER TABLE mat_colMask_view3 RENAME TO mat_colMask_view3_temp;ALTER TABLE mat_colMask_view3_staging RENAME TO mat_colMask_view3;ALTER TABLE mat_colMask_view3_temp RENAME TO mat_colMask_view3_staging;'');
'
) as
SELECT * FROM dsp.test_a
]]>
</metadata>
</model>
{code}
5. Add the following permissions 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>dsp.test_a.a</resource-name>
<allow-read>true</allow-read>
<condition constraint="false">a > 2</condition>
<mask order="1">1</mask>
</permission>
<permission>
<resource-name>views.colMask_view3.col1</resource-name>
<allow-read>true</allow-read>
<condition constraint="false">col1 = 1</condition>
<mask order="1">999</mask>
</permission>
<permission>
<resource-name>views.colMask_view3</resource-name>
<allow-read>true</allow-read>
</permission>
</data-role>
{code}
6. Run the query before materialization of views.colMask_view3 and after and compare results:
{code:sql}
SELECT * FROM "views.colMask_view3" ;;
{code}
before materialization Teiid will return:
{code}
col1 col2
999 1
999 2
2 1
2 2
999 2
999 10
{code}
after:
{code}
col1 col2
999 1
999 2
2 1
2 2
3 2
3 10
{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}
> The same situation is for column masking feature. If you need a test case I can prepare it.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
More information about the teiid-issues
mailing list