[teiid-issues] [JBoss JIRA] (TEIID-6035) Allow compound filters in POLICY
by Alf Lervåg (Jira)
[ https://issues.redhat.com/browse/TEIID-6035?page=com.atlassian.jira.plugi... ]
Alf Lervåg edited comment on TEIID-6035 at 9/22/20 4:50 AM:
------------------------------------------------------------
I'm testing this using the [odata|https://github.com/teiid/teiid-spring-boot/tree/master/samples... sample, modified to go against a local PostgreSQL database.
I changed the above to go against a simpler modification of the odata example which I documented in my other issue.
was (Author: alfborge):
I'm testing this using the [odata|https://github.com/teiid/teiid-spring-boot/tree/master/samples... sample, modified to go against a local PostgreSQL database.
> Allow compound filters in POLICY
> --------------------------------
>
> Key: TEIID-6035
> URL: https://issues.redhat.com/browse/TEIID-6035
> Project: Teiid
> Issue Type: Bug
> Reporter: Alf Lervåg
> Assignee: Steven Hawkins
> Priority: Major
>
> The documentation isn't quite good as I see different ways of doing this documented. None of them seem to work for me however.
> To reproduce, see TEIID-6036 where I describe my setup.
> According to the [Permissions|http://teiid.github.io/teiid-documents/master/content/reference/r_permissions.html] page, I should be able to do this:
> {code}
> CREATE POLICY limit_on_account_ids
> ON public.ACCOUNT
> FOR SELECT
> TO user_role USING ('ACCOUNT_ID > 19980002 and ACCOUNT_ID < 19980012');
> {code}
> The result for me is that no policy is applied.
> According to [data roles|http://teiid.github.io/teiid-documents/master/content/reference/r_ddl-deployment-mode.html#_data_roles] I should be able to do this:
> {code}CREATE POLICY limit_on_account_ids
> ON public.ACCOUNT
> FOR SELECT
> TO user_role USING (ACCOUNT_ID > 19980002 and ACCOUNT_ID < 19980012);
> {code}
>
> This errors out because the SQL parser doesn't expect the {{and}} in the {{USING}} clause. This aligns with the [BNF for SQL Grammar|http://teiid.github.io/teiid-documents/master/content/reference/r_bnf-for-sql-grammar.html#createPolicy].
> Am I missing something?
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month
[teiid-issues] [JBoss JIRA] (TEIID-6035) Allow compound filters in POLICY
by Alf Lervåg (Jira)
[ https://issues.redhat.com/browse/TEIID-6035?page=com.atlassian.jira.plugi... ]
Alf Lervåg updated TEIID-6035:
------------------------------
Description:
The documentation isn't quite good as I see different ways of doing this documented. None of them seem to work for me however.
To reproduce, see TEIID-6036 where I describe my setup.
According to the [Permissions|http://teiid.github.io/teiid-documents/master/content/reference/r_permissions.html] page, I should be able to do this:
{code}
CREATE POLICY limit_on_account_ids
ON public.ACCOUNT
FOR SELECT
TO user_role USING ('ACCOUNT_ID > 19980002 and ACCOUNT_ID < 19980012');
{code}
The result for me is that no policy is applied.
According to [data roles|http://teiid.github.io/teiid-documents/master/content/reference/r_ddl-deployment-mode.html#_data_roles] I should be able to do this:
{code}CREATE POLICY limit_on_account_ids
ON public.ACCOUNT
FOR SELECT
TO user_role USING (ACCOUNT_ID > 19980002 and ACCOUNT_ID < 19980012);
{code}
This errors out because the SQL parser doesn't expect the {{and}} in the {{USING}} clause. This aligns with the [BNF for SQL Grammar|http://teiid.github.io/teiid-documents/master/content/reference/r_bnf-for-sql-grammar.html#createPolicy].
Am I missing something?
was:
The documentation isn't quite good as I see different ways of doing this documented. None of them seem to work for me however.
According to the [Permissions|http://teiid.github.io/teiid-documents/master/content/reference/r_permissions.html] page, I should be able to do this:
{code}CREATE POLICY hide_late_albums
ON api.album
FOR SELECT
TO admin_role USING ('album_id > 10 and album_id < 20');
{code}
The result for me is that no policy is applied.
According to [data roles|http://teiid.github.io/teiid-documents/master/content/reference/r_ddl-deployment-mode.html#_data_roles] I should be able to do this:
{code}CREATE POLICY hide_late_albums
ON api.album
FOR SELECT
TO admin_role USING (album_id > 10 and album_id < 20);
{code}
This errors out because the SQL parser doesn't expect the {{and}} in the {{USING}} clause.
According to the [BNF for SQL Grammar|http://teiid.github.io/teiid-documents/master/content/reference/r_bnf-for-sql-grammar.html#createPolicy] it seems that what I want to do is not supported.
> Allow compound filters in POLICY
> --------------------------------
>
> Key: TEIID-6035
> URL: https://issues.redhat.com/browse/TEIID-6035
> Project: Teiid
> Issue Type: Bug
> Reporter: Alf Lervåg
> Assignee: Steven Hawkins
> Priority: Major
>
> The documentation isn't quite good as I see different ways of doing this documented. None of them seem to work for me however.
> To reproduce, see TEIID-6036 where I describe my setup.
> According to the [Permissions|http://teiid.github.io/teiid-documents/master/content/reference/r_permissions.html] page, I should be able to do this:
> {code}
> CREATE POLICY limit_on_account_ids
> ON public.ACCOUNT
> FOR SELECT
> TO user_role USING ('ACCOUNT_ID > 19980002 and ACCOUNT_ID < 19980012');
> {code}
> The result for me is that no policy is applied.
> According to [data roles|http://teiid.github.io/teiid-documents/master/content/reference/r_ddl-deployment-mode.html#_data_roles] I should be able to do this:
> {code}CREATE POLICY limit_on_account_ids
> ON public.ACCOUNT
> FOR SELECT
> TO user_role USING (ACCOUNT_ID > 19980002 and ACCOUNT_ID < 19980012);
> {code}
>
> This errors out because the SQL parser doesn't expect the {{and}} in the {{USING}} clause. This aligns with the [BNF for SQL Grammar|http://teiid.github.io/teiid-documents/master/content/reference/r_bnf-for-sql-grammar.html#createPolicy].
> Am I missing something?
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month
[teiid-issues] [JBoss JIRA] (TEIID-6036) Unable to MASK data using odata-sample
by Alf Lervåg (Jira)
[ https://issues.redhat.com/browse/TEIID-6036?page=com.atlassian.jira.plugi... ]
Alf Lervåg updated TEIID-6036:
------------------------------
Description:
According to the [data roles|http://teiid.github.io/teiid-documents/master/content/reference/r_ddl-deployment-mode.html#_data_roles] documentation, I should be able to do this:
{code:sql}
CREATE DATABASE test VERSION '1.0.0';
USE DATABASE test VERSION '1.0.0';
CREATE ROLE user_role WITH FOREIGN ROLE ROLE_user;
CREATE SERVER accounts FOREIGN DATA WRAPPER h2;
CREATE SCHEMA public SERVER accounts;
IMPORT FOREIGN SCHEMA PUBLIC FROM SERVER accounts INTO public;
GRANT SELECT ON TABLE public.ACCOUNT TO user_role;
GRANT SELECT ON COLUMN public.ACCOUNT.SSN
MASK 'xxxx'
TO user_role;
{code}
Unfortunately I can't get it to work.
I'm using a modified version of the [odata|https://github.com/teiid/teiid-spring-boot/tree/master/samples... sample from the spring-boot project. My modifications are:
# Add a dependency on spring-boot-starter-security
# Delete the tests (they break due to the dependency)
# Add the above SQL to a file called teiid.ddl in the resources folder
# Add the following lines to the application.properties
{code}
spring.security.user.username=user
spring.security.user.password=user
spring.security.user.roles=user
{code}
was:According to the [data roles
> Unable to MASK data using odata-sample
> --------------------------------------
>
> Key: TEIID-6036
> URL: https://issues.redhat.com/browse/TEIID-6036
> Project: Teiid
> Issue Type: Bug
> Reporter: Alf Lervåg
> Assignee: Steven Hawkins
> Priority: Major
>
> According to the [data roles|http://teiid.github.io/teiid-documents/master/content/reference/r_ddl-deployment-mode.html#_data_roles] documentation, I should be able to do this:
> {code:sql}
> CREATE DATABASE test VERSION '1.0.0';
> USE DATABASE test VERSION '1.0.0';
> CREATE ROLE user_role WITH FOREIGN ROLE ROLE_user;
> CREATE SERVER accounts FOREIGN DATA WRAPPER h2;
> CREATE SCHEMA public SERVER accounts;
> IMPORT FOREIGN SCHEMA PUBLIC FROM SERVER accounts INTO public;
> GRANT SELECT ON TABLE public.ACCOUNT TO user_role;
> GRANT SELECT ON COLUMN public.ACCOUNT.SSN
> MASK 'xxxx'
> TO user_role;
> {code}
> Unfortunately I can't get it to work.
> I'm using a modified version of the [odata|https://github.com/teiid/teiid-spring-boot/tree/master/samples... sample from the spring-boot project. My modifications are:
> # Add a dependency on spring-boot-starter-security
> # Delete the tests (they break due to the dependency)
> # Add the above SQL to a file called teiid.ddl in the resources folder
> # Add the following lines to the application.properties
> {code}
> spring.security.user.username=user
> spring.security.user.password=user
> spring.security.user.roles=user
> {code}
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month
[teiid-issues] [JBoss JIRA] (TEIID-6035) Allow compound filters in POLICY
by Alf Lervåg (Jira)
[ https://issues.redhat.com/browse/TEIID-6035?page=com.atlassian.jira.plugi... ]
Alf Lervåg commented on TEIID-6035:
-----------------------------------
I'm testing this using the [odata|https://github.com/teiid/teiid-spring-boot/tree/master/samples... sample, modified to go against a local PostgreSQL database.
> Allow compound filters in POLICY
> --------------------------------
>
> Key: TEIID-6035
> URL: https://issues.redhat.com/browse/TEIID-6035
> Project: Teiid
> Issue Type: Bug
> Reporter: Alf Lervåg
> Assignee: Steven Hawkins
> Priority: Major
>
> The documentation isn't quite good as I see different ways of doing this documented. None of them seem to work for me however.
> According to the [Permissions|http://teiid.github.io/teiid-documents/master/content/reference/r_permissions.html] page, I should be able to do this:
> {code}CREATE POLICY hide_late_albums
> ON api.album
> FOR SELECT
> TO admin_role USING ('album_id > 10 and album_id < 20');
> {code}
> The result for me is that no policy is applied.
> According to [data roles|http://teiid.github.io/teiid-documents/master/content/reference/r_ddl-deployment-mode.html#_data_roles] I should be able to do this:
> {code}CREATE POLICY hide_late_albums
> ON api.album
> FOR SELECT
> TO admin_role USING (album_id > 10 and album_id < 20);
> {code}
>
> This errors out because the SQL parser doesn't expect the {{and}} in the {{USING}} clause.
> According to the [BNF for SQL Grammar|http://teiid.github.io/teiid-documents/master/content/reference/r_bnf-for-sql-grammar.html#createPolicy] it seems that what I want to do is not supported.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month
[teiid-issues] [JBoss JIRA] (TEIID-6035) Allow compound filters in POLICY
by Alf Lervåg (Jira)
[ https://issues.redhat.com/browse/TEIID-6035?page=com.atlassian.jira.plugi... ]
Alf Lervåg updated TEIID-6035:
------------------------------
Description:
The documentation isn't quite good as I see different ways of doing this documented. None of them seem to work for me however.
According to the [Permissions|http://teiid.github.io/teiid-documents/master/content/reference/r_permissions.html] page, I should be able to do this:
{code}CREATE POLICY hide_late_albums
ON api.album
FOR SELECT
TO admin_role USING ('album_id > 10 and album_id < 20');
{code}
The result for me is that no policy is applied.
According to [data roles|http://teiid.github.io/teiid-documents/master/content/reference/r_ddl-deployment-mode.html#_data_roles] I should be able to do this:
{code}CREATE POLICY hide_late_albums
ON api.album
FOR SELECT
TO admin_role USING (album_id > 10 and album_id < 20);
{code}
This errors out because the SQL parser doesn't expect the {{and}} in the {{USING}} clause.
According to the [BNF for SQL Grammar|http://teiid.github.io/teiid-documents/master/content/reference/r_bnf-for-sql-grammar.html#createPolicy] it seems that what I want to do is not supported.
was:
The documentation isn't quite good as I see different ways of doing this documented. None of them seem to work for me however.
According to the [Permissions|http://teiid.github.io/teiid-documents/master/content/reference/r_permissions.html] page, I should be able to do this:
{{
CREATE POLICY hide_late_albums
ON api.album
FOR SELECT
TO admin_role USING ('album_id > 10 and album_id < 20');
}}
The result for me is that no policy is applied.
According to [data roles|http://teiid.github.io/teiid-documents/master/content/reference/r_ddl-deployment-mode.html#_data_roles] I should be able to do this:
{{
CREATE POLICY hide_late_albums
ON api.album
FOR SELECT
TO admin_role USING (album_id > 10 and album_id < 20);
}}
This errors out because the SQL parser doesn't expect the {{and}} in the {{USING}} clause.
According to the [BNF for SQL Grammar|http://teiid.github.io/teiid-documents/master/content/reference/r_bnf-for-sql-grammar.html#createPolicy] it seems that what I want to do is not supported.
> Allow compound filters in POLICY
> --------------------------------
>
> Key: TEIID-6035
> URL: https://issues.redhat.com/browse/TEIID-6035
> Project: Teiid
> Issue Type: Bug
> Reporter: Alf Lervåg
> Assignee: Steven Hawkins
> Priority: Major
>
> The documentation isn't quite good as I see different ways of doing this documented. None of them seem to work for me however.
> According to the [Permissions|http://teiid.github.io/teiid-documents/master/content/reference/r_permissions.html] page, I should be able to do this:
> {code}CREATE POLICY hide_late_albums
> ON api.album
> FOR SELECT
> TO admin_role USING ('album_id > 10 and album_id < 20');
> {code}
> The result for me is that no policy is applied.
> According to [data roles|http://teiid.github.io/teiid-documents/master/content/reference/r_ddl-deployment-mode.html#_data_roles] I should be able to do this:
> {code}CREATE POLICY hide_late_albums
> ON api.album
> FOR SELECT
> TO admin_role USING (album_id > 10 and album_id < 20);
> {code}
>
> This errors out because the SQL parser doesn't expect the {{and}} in the {{USING}} clause.
> According to the [BNF for SQL Grammar|http://teiid.github.io/teiid-documents/master/content/reference/r_bnf-for-sql-grammar.html#createPolicy] it seems that what I want to do is not supported.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month
[teiid-issues] [JBoss JIRA] (TEIID-6035) Allow compound filters in POLICY
by Alf Lervåg (Jira)
[ https://issues.redhat.com/browse/TEIID-6035?page=com.atlassian.jira.plugi... ]
Alf Lervåg updated TEIID-6035:
------------------------------
Description:
The documentation isn't quite good as I see different ways of doing this documented. None of them seem to work for me however.
According to the [Permissions|http://teiid.github.io/teiid-documents/master/content/reference/r_permissions.html] page, I should be able to do this:
{{
CREATE POLICY hide_late_albums
ON api.album
FOR SELECT
TO admin_role USING ('album_id > 10 and album_id < 20');
}}
The result for me is that no policy is applied.
According to [data roles|http://teiid.github.io/teiid-documents/master/content/reference/r_ddl-deployment-mode.html#_data_roles] I should be able to do this:
{{
CREATE POLICY hide_late_albums
ON api.album
FOR SELECT
TO admin_role USING (album_id > 10 and album_id < 20);
}}
This errors out because the SQL parser doesn't expect the {{and}} in the {{USING}} clause.
According to the [BNF for SQL Grammar|http://teiid.github.io/teiid-documents/master/content/reference/r_bnf-for-sql-grammar.html#createPolicy] it seems that what I want to do is not supported.
> Allow compound filters in POLICY
> --------------------------------
>
> Key: TEIID-6035
> URL: https://issues.redhat.com/browse/TEIID-6035
> Project: Teiid
> Issue Type: Bug
> Reporter: Alf Lervåg
> Assignee: Steven Hawkins
> Priority: Major
>
> The documentation isn't quite good as I see different ways of doing this documented. None of them seem to work for me however.
> According to the [Permissions|http://teiid.github.io/teiid-documents/master/content/reference/r_permissions.html] page, I should be able to do this:
> {{
> CREATE POLICY hide_late_albums
> ON api.album
> FOR SELECT
> TO admin_role USING ('album_id > 10 and album_id < 20');
> }}
> The result for me is that no policy is applied.
> According to [data roles|http://teiid.github.io/teiid-documents/master/content/reference/r_ddl-deployment-mode.html#_data_roles] I should be able to do this:
> {{
> CREATE POLICY hide_late_albums
> ON api.album
> FOR SELECT
> TO admin_role USING (album_id > 10 and album_id < 20);
> }}
> This errors out because the SQL parser doesn't expect the {{and}} in the {{USING}} clause.
> According to the [BNF for SQL Grammar|http://teiid.github.io/teiid-documents/master/content/reference/r_bnf-for-sql-grammar.html#createPolicy] it seems that what I want to do is not supported.
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month
[teiid-issues] [JBoss JIRA] (TEIID-6034) Teiid - Queries Containing Both String Filters With Quotes and Timestamp Fail
by John Cockson (Jira)
[ https://issues.redhat.com/browse/TEIID-6034?page=com.atlassian.jira.plugi... ]
John Cockson updated TEIID-6034:
--------------------------------
Steps to Reproduce:
-- Fails
Select day_date, month_name from warehouse.dim_date
where
"day_date" >= TimestampAdd(SQL_TSI_DAY, 1, '2020-09-17')
and
month_name != 'Tuesday''s'
limit 5
;
--Succeeds and pushes down the date filter
Select day_date, month_name from warehouse.dim_date
where
"day_date" >= TimestampAdd(SQL_TSI_DAY, 1, '2020-09-17')
-- and
-- month_name != 'Tuesday''s'
limit 5
;
-- Succeeds and pushes down the string filter
Select day_date, month_name from warehouse.dim_date
where
-- "day_date" >= TimestampAdd(SQL_TSI_DAY, 1, '2020-09-17')
-- and
month_name != 'Tuesday''s'
limit 5
;
limit 5
;
was:
-- Fails
Select * from warehouse.dim_date
where
"day_date" >= cast({fn TimestampAdd(SQL_TSI_MONTH, -24, {fn TimestampAdd(SQL_TSI_DAY, {fn DAYOFMONTH({d '2020-09-17'})} * -1 + 1, {d '2020-09-17'})})} as date)
and
month_name != 'Tuesday''s'
limit 5
;
--Succeeds and pushes down the date filter
Select * from warehouse.dim_date
where
"day_date" >= cast({fn TimestampAdd(SQL_TSI_MONTH, -24, {fn TimestampAdd(SQL_TSI_DAY, {fn DAYOFMONTH({d '2020-09-17'})} * -1 + 1, {d '2020-09-17'})})} as date)
-- and
-- month_name != 'Tuesday''s'
limit 5
;
-- Succeeds and pushes down the string filter
Select * from warehouse.dim_date
where
-- "day_date" >= cast(\{fn TimestampAdd(SQL_TSI_MONTH, -24, {fn TimestampAdd(SQL_TSI_DAY, {fn DAYOFMONTH({d '2020-09-17'})} * -1 + 1, \{d '2020-09-17'})})} as date)
-- and
month_name != 'Tuesday''s'
limit 5
;
> Teiid - Queries Containing Both String Filters With Quotes and Timestamp Fail
> -----------------------------------------------------------------------------
>
> Key: TEIID-6034
> URL: https://issues.redhat.com/browse/TEIID-6034
> Project: Teiid
> Issue Type: Bug
> Components: XML Planner/Processor
> Affects Versions: 8.6
> Reporter: John Cockson
> Assignee: Steven Hawkins
> Priority: Major
>
> Combining filter with quotes in strings and TimestampAdd causes query to fail:
>
--
This message was sent by Atlassian Jira
(v7.13.8#713008)
6 years, 1 month