[JBoss JIRA] (TEIID-5801) Communications link failure during commit() error message when copying a MySQL table to a DB
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5801?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5801:
---------------------------------------
The first refinement is to have the connector code look at the transaction isolation level. If it's read committed (the default) or below we can safely execute a read-only command in parallel. If that is sufficient for your case, I'd leave the remaining case - repeatable read / serializable isolation level in a command or block transaction scope, such that if there's a single read it can be parallel - to a later date.
> Communications link failure during commit() error message when copying a MySQL table to a DB
> --------------------------------------------------------------------------------------------
>
> Key: TEIID-5801
> URL: https://issues.jboss.org/browse/TEIID-5801
> Project: Teiid
> Issue Type: Quality Risk
> 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: Major
> Attachments: server_1_fail.log, server_2_works.log, server_teiid.log
>
>
> When copying a MySQL table, for example, to PostgreSQL:
> {code:sql}
> insert into dwh_pg.test_target SELECT * FROM my.test_source ;;
> {code}
> in the end of the process Teiid throws out the following stacktrace (though rows are inserted in PostgreSQL successfully, seems that Teiid can't close read transaction for MySQL):
> {code}
> 2019-08-01 16:48:23,119 WARN [org.jboss.jca.core.connectionmanager.listener.TxConnectionListener] (Worker3_QueryProcessorQueue34) TidBkmeGWJN8 IJ000305: Connection error occured: org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@75284e6d[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@5b8d92c7 connection handles=0 lastReturned=1564670796599 lastValidated=1564670796598 lastCheckedOut=1564670796678 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@ae21718 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@338041b4[pool=lingoda_read_replica] xaResource=LocalXAResourceImpl@1fcd6b81[connectionListener=75284e6d connectionManager=20f22ec1 warned=false currentXid=null productName=MySQL productVersion=5.6.34-log jndiName=java:/lingoda_read_replica] txSync=TransactionSynchronization@1367866468{tx=Local transaction (delegate=TransactionImple < ac, BasicAction: 0:ffffc0a8008c:33252ff9:5d42fad3:11 status: ActionStatus.PREPARING >, owner=Local transaction context for provider JBoss JTA transaction provider) wasTrackByTx=true enlisted=true cancel=false}]: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during commit(). Transaction resolution unknown. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1700) at org.jboss.jca.adapters.jdbc.local.LocalManagedConnection.commit(LocalManagedConnection.java:96) at org.jboss.jca.core.tx.jbossts.LocalXAResourceImpl.commit(LocalXAResourceImpl.java:172) at com.arjuna.ats.internal.jta.resources.arjunacore.XAOnePhaseResource.commit(XAOnePhaseResource.java:120) at com.arjuna.ats.internal.arjuna.abstractrecords.LastResourceRecord.topLevelPrepare(LastResourceRecord.java:152) at com.arjuna.ats.arjuna.coordinator.BasicAction.doPrepare(BasicAction.java:2664) at com.arjuna.ats.arjuna.coordinator.BasicAction.doPrepare(BasicAction.java:2614) at com.arjuna.ats.arjuna.coordinator.BasicAction.prepare(BasicAction.java:2157) at com.arjuna.ats.arjuna.coordinator.BasicAction.End(BasicAction.java:1503) at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.end(TwoPhaseCoordinator.java:96) at com.arjuna.ats.arjuna.AtomicAction.commit(AtomicAction.java:162) at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1288) at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:126) at com.arjuna.ats.jbossatx.BaseTransactionManagerDelegate.commit(BaseTransactionManagerDelegate.java:89) at org.wildfly.transaction.client.LocalTransaction.commitAndDissociate(LocalTransaction.java:77) at org.wildfly.transaction.client.ContextTransactionManager.commit(ContextTransactionManager.java:71) at org.teiid.dqp.internal.process.TransactionServerImpl.commitDirect(TransactionServerImpl.java:384) at org.teiid.dqp.internal.process.TransactionServerImpl.commit(TransactionServerImpl.java:515) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.teiid.logging.LogManager$LoggingProxy.invoke(LogManager.java:117) at com.sun.proxy.$Proxy25.commit(Unknown Source) at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:514) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:43) at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:113)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:199) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745)
> {code}
> I tried to reproduce the problem with local MySQL and PostgreSQL but couldn't. The problem can be reproduced only when using remote MySQL and PostgreSQL. On my local machine the error appears with limit more than 200000 rows, on another machines the exception appeared when setting limit 400000 and more. It seems it's related maybe somehow with MySQL timeouts or network delays. If I copy the table from remote MySQL to local PostgeSQL the error doesn't appear, and vice versa, if I copy the table from local MySQL to remote PostgreSQL the error doesn't appear again. I don't have an access to the remote MySQL to have a look at its internal options. I also tried to set net_write_timeout=1800 jdbc property for data source of the remote MySQL, tcpKeepAlive=true, tried to set ThreadBound MySQL translator property to true value - it didn't help at all. What do you think, is it possible to avoid the error on Teiid level?
> I also attached a server log with org.teiid.CONNECTOR and org.teiid.PROCESSOR log outputs.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 5 months
[JBoss JIRA] (TEIID-5799) GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5799?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5799.
-----------------------------------
Resolution: Rejected
Marking as resolved based upon further investigations.
> GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
> -------------------------------------------------------------------------------
>
> Key: TEIID-5799
> URL: https://issues.jboss.org/browse/TEIID-5799
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 13.0
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: svc-vdb-example.ddl, svc-vdb.ddl
>
>
> Hello Steven,
> I just ran into the next issue with the generated key functionality. Attached you find an example. The relevant section is given in
> CREATE TRIGGER ON Diary INSTEAD OF INSERT AS
> with the block following:
> ELSE IF (new.fkDatabaseKey = 3)
> In the insert trigger I am trying to duplicate a record from table "UserDefinedProducts" and reference it in the row to be created from the trigger.
> I again the the error message:
> POST on 'Diary' failed; will be repeated automatically - Error: TEIID16016 Insert into Diary success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values.
> I am using the most recent sources checked out about an hour ago.
> Do you see a bug in my code, or is it again an issue I have hit? Thanks for your help!
> Let me know if I shall assemble a docker-compose file for you to debug the issue.
> Update: As I seem to be not able to delete attachments here. One further note. My example hat a permission error in one line where I have red from the wrong table. It should instead be:
> SELECT * INTO #tmpItem FROM UserDefinedProductsOfAllUsers WHERE fkProduct = new.fkProduct LIMIT 1;
> However, the previously described error stays the same.
> Best regards,
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 5 months
[JBoss JIRA] (TEIID-5802) Row based security and column masking don't work correctly for materialized tables (views)
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5802?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5802:
---------------------------------------
Now that I'm remembering this problem more there is a reason why we simply warn of this scenario. When you create a materialized target table the only way to preserve the masking / row security is to transitively determine all possible applications from the constituent tables/views and attempt to re-apply them to the materialization target table created without any security applied transitively as well. In general this is very difficult - for example many of the columns that have row based security may not even be exposed by the materialized view (which at a minimum requires creating an intermediate view), or are involved in aggregation, etc.
What you have here is a simple enough of a case, such that you could envision Teiid creating a materialization target table as a superset, then apply the row/column security on top of that. However with additional complexity that will quickly not work.
I agree though that things are in a weird middle ground. Materialization target tables are being created with potentially mixed application of row/column security based upon whoever the calling user that triggers the load. The simplest way to make this consistent is to create the materialization target tables without any security applied transitively, but without some automated process of applying security on top it would be up to the user to redefine the security on the materialization target.
There was a legacy feature of scoping materialized views to a user, but was removed by TEIID-5427 as it did not align with any materialization management strategy.
> Row based security and column masking don't work correctly 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, see below.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 5 months
[JBoss JIRA] (TEIID-5802) Row based security and column masking don't work correctly for materialized tables (views)
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5802?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5802:
---------------------------------------
What is happening here is that role based access is enforced prior to planning that substitutes the materialization target. Then after that substitution happens the application of row based security / masking is not applied to the materialization target.
We warn about applying row based security / masking to materialization because the identity of the user under which the snapshot is taken can influence the contents. I see that this should be corrected along with this issue - we should allow the snapshot to bypass the application of row based security / masking, and we should apply row based security / masking to both the view and the materialization target.
> Row based security and column masking don't work correctly 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, see below.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 5 months
[JBoss JIRA] (TEIID-5802) Row based security and column masking don't work correctly for materialized tables (views)
by Dmitrii Pogorelov (Jira)
[ https://issues.jboss.org/browse/TEIID-5802?page=com.atlassian.jira.plugin... ]
Dmitrii Pogorelov updated TEIID-5802:
-------------------------------------
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:
{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, see below.
was:
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.
> Row based security and column masking don't work correctly 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, see below.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 5 months
[JBoss JIRA] (TEIID-5802) Row based security and column masking don't work correctly for materialized tables (views)
by Dmitrii Pogorelov (Jira)
[ https://issues.jboss.org/browse/TEIID-5802?page=com.atlassian.jira.plugin... ]
Dmitrii Pogorelov updated TEIID-5802:
-------------------------------------
Summary: Row based security and column masking don't work correctly for materialized tables (views) (was: Row based security doesn't work for materialized tables (views))
> Row based security and column masking don't work correctly 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)
5 years, 5 months
[JBoss JIRA] (TEIID-5802) Row based security doesn't work for materialized tables (views)
by Dmitrii Pogorelov (Jira)
[ https://issues.jboss.org/browse/TEIID-5802?page=com.atlassian.jira.plugin... ]
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)
5 years, 5 months
[JBoss JIRA] (TEIID-5802) Row based security doesn't work for materialized tables (views)
by Dmitrii Pogorelov (Jira)
[ https://issues.jboss.org/browse/TEIID-5802?page=com.atlassian.jira.plugin... ]
Dmitrii Pogorelov updated TEIID-5802:
-------------------------------------
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:
{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.
was:
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}
> 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)
5 years, 5 months
[JBoss JIRA] (TEIID-5802) Row based security doesn't work for materialized tables (views)
by Dmitrii Pogorelov (Jira)
[ 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}
> 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)
5 years, 5 months
[JBoss JIRA] (TEIID-5802) Row based security doesn't work for materialized tables (views)
by Dmitrii Pogorelov (Jira)
Dmitrii Pogorelov created TEIID-5802:
----------------------------------------
Summary: 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
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)
5 years, 5 months