[JBoss JIRA] (TEIID-5792) Permissions don't work with virtual procedures' ResultSet
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5792?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5792:
---------------------------------------
I didn't have immediate plans for inclusion, but we can leave this open as an enhancement. You can certainly lobby for its inclusion if the workaround is not acceptable for your situation.
It's a couple hours of work as it complicates the authorization logic as we have to scan inline views to determine whether the columns are coming from a procedure call or not.
> Permissions don't work with virtual procedures' ResultSet
> ---------------------------------------------------------
>
> Key: TEIID-5792
> URL: https://issues.jboss.org/browse/TEIID-5792
> Project: Teiid
> Issue Type: Enhancement
> 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
>
> Teiid doesn't work with ResultSet of a virtual procedure. For example, if we have procs.testProc virtual procedure which can return two values in ResultSet: a and b and we specify a permission for one of these columns in ResultSet, the permission won't work:
> {code:xml}
> <permission>
> <resource-name>procs.testProc.a</resource-name>
> <allow-read>false</allow-read>
> </permission>
> {code}
> I think it would be great to set permissions also for ResultSets of virtual procedures, so AuthorizationValidationVisitor.validateEntitlements method for a GroupSymbol, which is a procedure, should analyze also its ResultSet. At the same time permissions work for virtual views and we can set permissions for some views' columns separately.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 11 months
[JBoss JIRA] (TEIID-5792) Permissions don't work with virtual procedures' ResultSet
by Dmitrii Pogorelov (Jira)
[ https://issues.jboss.org/browse/TEIID-5792?page=com.atlassian.jira.plugin... ]
Dmitrii Pogorelov commented on TEIID-5792:
------------------------------------------
Hi [~rareddy] and [~shawkins], thx a lot for your explanation. As you set type of the issue to Enhancement it means that you're planning to implement a permission control for resultset in scope of the issue or not?
> Permissions don't work with virtual procedures' ResultSet
> ---------------------------------------------------------
>
> Key: TEIID-5792
> URL: https://issues.jboss.org/browse/TEIID-5792
> Project: Teiid
> Issue Type: Enhancement
> 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
>
> Teiid doesn't work with ResultSet of a virtual procedure. For example, if we have procs.testProc virtual procedure which can return two values in ResultSet: a and b and we specify a permission for one of these columns in ResultSet, the permission won't work:
> {code:xml}
> <permission>
> <resource-name>procs.testProc.a</resource-name>
> <allow-read>false</allow-read>
> </permission>
> {code}
> I think it would be great to set permissions also for ResultSets of virtual procedures, so AuthorizationValidationVisitor.validateEntitlements method for a GroupSymbol, which is a procedure, should analyze also its ResultSet. At the same time permissions work for virtual views and we can set permissions for some views' columns separately.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 11 months
[JBoss JIRA] (TEIID-5792) Permissions don't work with virtual procedures' ResultSet
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5792?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5792:
----------------------------------
Issue Type: Enhancement (was: Bug)
Priority: Major (was: Blocker)
This is the currently expected behavior. Functions/procedures support just the notion of execute, not fine grained permissions on the resultset. The workaround is to introduce a view that relies on procedural / relational invocation and assign permissions to the view.
> Permissions don't work with virtual procedures' ResultSet
> ---------------------------------------------------------
>
> Key: TEIID-5792
> URL: https://issues.jboss.org/browse/TEIID-5792
> Project: Teiid
> Issue Type: Enhancement
> 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
>
> Teiid doesn't work with ResultSet of a virtual procedure. For example, if we have procs.testProc virtual procedure which can return two values in ResultSet: a and b and we specify a permission for one of these columns in ResultSet, the permission won't work:
> {code:xml}
> <permission>
> <resource-name>procs.testProc.a</resource-name>
> <allow-read>false</allow-read>
> </permission>
> {code}
> I think it would be great to set permissions also for ResultSets of virtual procedures, so AuthorizationValidationVisitor.validateEntitlements method for a GroupSymbol, which is a procedure, should analyze also its ResultSet. At the same time permissions work for virtual views and we can set permissions for some views' columns separately.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 11 months
[JBoss JIRA] (TEIID-5792) Permissions don't work with virtual procedures' ResultSet
by Ramesh Reddy (Jira)
[ https://issues.jboss.org/browse/TEIID-5792?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5792:
-------------------------------------
The permission set on a procedure is "execute" or not, if you need more fine-grained like what you are asking, you need to design your procedure using the function checks with functions like "hasRole".
> Permissions don't work with virtual procedures' ResultSet
> ---------------------------------------------------------
>
> Key: TEIID-5792
> URL: https://issues.jboss.org/browse/TEIID-5792
> 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
>
> Teiid doesn't work with ResultSet of a virtual procedure. For example, if we have procs.testProc virtual procedure which can return two values in ResultSet: a and b and we specify a permission for one of these columns in ResultSet, the permission won't work:
> {code:xml}
> <permission>
> <resource-name>procs.testProc.a</resource-name>
> <allow-read>false</allow-read>
> </permission>
> {code}
> I think it would be great to set permissions also for ResultSets of virtual procedures, so AuthorizationValidationVisitor.validateEntitlements method for a GroupSymbol, which is a procedure, should analyze also its ResultSet. At the same time permissions work for virtual views and we can set permissions for some views' columns separately.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 11 months
[JBoss JIRA] (TEIID-5792) Permissions don't work with virtual procedures' ResultSet
by Dmitrii Pogorelov (Jira)
Dmitrii Pogorelov created TEIID-5792:
----------------------------------------
Summary: Permissions don't work with virtual procedures' ResultSet
Key: TEIID-5792
URL: https://issues.jboss.org/browse/TEIID-5792
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
Teiid doesn't work with ResultSet of a virtual procedure. For example, if we have procs.testProc virtual procedure which can return two values in ResultSet: a and b and we specify a permission for one of these columns in ResultSet, the permission won't work:
{code:xml}
<permission>
<resource-name>procs.testProc.a</resource-name>
<allow-read>false</allow-read>
</permission>
{code}
I think it would be great to set permissions also for ResultSets of virtual procedures, so AuthorizationValidationVisitor.validateEntitlements method for a GroupSymbol, which is a procedure, should analyze also its ResultSet. At the same time permissions work for virtual views and we can set permissions for some views' columns separately.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 11 months
[JBoss JIRA] (TEIID-5782) When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5782:
---------------------------------------
Hello Steven,
Thanks for letting me know. Could you please provide such an enhancement? Should I add an extra issue for it or will we cover it here?
Best regards,
Christoph
> When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5782
> URL: https://issues.jboss.org/browse/TEIID-5782
> Project: Teiid
> Issue Type: Quality Risk
> Components: OData
> Affects Versions: 12.3
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
>
> Hello,
> in the past we had an issue in the odata layer, that the generated primary key of an autoincrement key was not corretly transfered back in the odata response. This issue was fixed.
> However, the current issues derives from the discussion in
> https://issues.jboss.org/browse/TEIID-5763
> where we havewritten an INSTEAD OF INSERT trigger. According to my last comment to https://issues.jboss.org/browse/TEIID-5763, there are two situations which can arrise. I have to set the 1. key group explicitely or 2. it is set implicitely.
> Now in both situations I observe the same result. When I POST an INSERT with a dummy primary key, say key=0 (as it was NOT NULL in the past, and I am still using the old sources) I retrieve key=0 in the odata response. Correct would be to get a response with the generated key instead.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 11 months
[JBoss JIRA] (TEIID-5782) When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5782:
---------------------------------------
Yes, the code is assuming that each of the pk columns will need to be marked as auto increment. In this case you have a mixed scenario. That would require an enhancement to support.
> When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5782
> URL: https://issues.jboss.org/browse/TEIID-5782
> Project: Teiid
> Issue Type: Quality Risk
> Components: OData
> Affects Versions: 12.3
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
>
> Hello,
> in the past we had an issue in the odata layer, that the generated primary key of an autoincrement key was not corretly transfered back in the odata response. This issue was fixed.
> However, the current issues derives from the discussion in
> https://issues.jboss.org/browse/TEIID-5763
> where we havewritten an INSTEAD OF INSERT trigger. According to my last comment to https://issues.jboss.org/browse/TEIID-5763, there are two situations which can arrise. I have to set the 1. key group explicitely or 2. it is set implicitely.
> Now in both situations I observe the same result. When I POST an INSERT with a dummy primary key, say key=0 (as it was NOT NULL in the past, and I am still using the old sources) I retrieve key=0 in the odata response. Correct would be to get a response with the generated key instead.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 11 months
[JBoss JIRA] (TEIID-5782) When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin... ]
Christoph John edited comment on TEIID-5782 at 7/15/19 5:43 PM:
----------------------------------------------------------------
Hello Steven,
I am currently running in the next issue with the insert trigger. When trying to set the key value, I run into the following error:
TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol key.fkProduct is specified with an unknown group context
Have you an idea what is going wrong here? Could it be related to the compound primary key that I am using? Let me know if I should provide a docker-compose example. The code:
{noformat}
-- ///////////
CREATE VIEW UserDefinedProducts (
fkProduct long NOT NULL AUTO_INCREMENT,
fkProfile long NOT NULL,
idCode long,
product_name string(48) NOT NULL,
origins string(64),
brands string(64) NOT NULL,
quantity string(64),
serving_quantity double,
nova_group char(1),
nutrition_grade_fr char(1),
energy_100g double NOT NULL,
carbohydrates_100g double NOT NULL,
sugars_100g double NOT NULL,
proteins_100g double NOT NULL,
fat_100g double NOT NULL,
saturated_fat_100g double NOT NULL,
saturated_fat_modifier char(1),
salt_100g double NOT NULL,
salt_modifier char(1),
fiber_100g double,
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(UPDATABLE 'TRUE')
AS
SELECT
udp.fkProduct as fkProduct,
udp.fkProfile as fkProfile,
udp.idCode as idCode,
udp.product_name as product_name,
udp.origins as origins,
udp.brands as brands,
udp.quantity as quantity,
udp.serving_quantity as serving_quantity,
udp.nova_group as nova_group,
udp.nutrition_grade_fr as nutrition_grade_fr,
udp.energy_100g as energy_100g,
udp.carbohydrates_100g as carbohydrates_100g,
udp.sugars_100g as sugars_100g,
udp.proteins_100g as proteins_100g,
udp.fat_100g as fat_100g,
udp.saturated_fat_100g as saturated_fat_100g,
udp.saturated_fat_modifier as saturated_fat_modifier,
udp.salt_100g as salt_100g,
udp.salt_modifier as salt_modifier,
udp.fiber_100g as fiber_100g
FROM UserDefinedProducts_SRC udp;
CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS
FOR EACH ROW
BEGIN ATOMIC
DECLARE long vIdProduct;
DECLARE integer vNumRecords;
-- if a product code was given, check that the user has not already created a product for this code
-- this situation should actualy never appear
IF (new.idCode IS NOT NULL)
BEGIN
vNumRecords = SELECT 1 FROM UserDefinedProducts_SRC WHERE
fkProfile = new.fkProfile AND idCode = new.idCode
LIMIT 1;
IF(vNumRecords > 0)
BEGIN
DECLARE EXCEPTION e = SQLEXCEPTION 'User defined product already exists' SQLSTATE '45000';
RAISE e;
END
END
-- check if the user has already reached his MAX amount of UserDefinedProducts;
vNumRecords = SELECT COUNT(*) FROM UserDefinedProducts_SRC WHERE fkProfile = new.fkProfile;
IF(vNumRecords >= 1000)
BEGIN
DECLARE EXCEPTION e = SQLEXCEPTION 'MAX amount of user defined products exceeded for this account' SQLSTATE '45000';
RAISE e;
END
-- if all checks have been passed we can safely add the product to the database
INSERT INTO
Products(fkDatabaseKey)
VALUES
(0);
-- create a new record for the user defined product
vIdProduct = cast(generated_key('idProduct') as long);
INSERT INTO
UserDefinedProducts_SRC(fkProduct, fkProfile, idCode, product_name, origins, brands, quantity,
serving_quantity, nova_group, nutrition_grade_fr, energy_100g, carbohydrates_100g,
sugars_100g, proteins_100g, fat_100g, saturated_fat_100g, saturated_fat_modifier,
salt_100g, salt_modifier, fiber_100g)
VALUES
(vIdProduct, new.fkProfile, new.idCode, new.product_name, new.origins, new.brands, new.quantity,
new.serving_quantity, new.nova_group, new.nutrition_grade_fr, new.energy_100g, new.carbohydrates_100g,
new.sugars_100g, new.proteins_100g, new.fat_100g, new.saturated_fat_100g, new.saturated_fat_modifier,
new.salt_100g, new.salt_modifier, new.fiber_100g);
-- supply the key value
key.fkProduct = vIdProduct;
END;
{noformat}
was (Author: cjohn001):
Hello Steven,
I am currently running in the next issue with the insert trigger. When trying to set the key value, I run into the following error:
TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol key.fkProduct is specified with an unknown group context
Have you an idea what is going wrong here? Could it be related to the compound primary key that I am using? The code:
{noformat}
-- ///////////
CREATE VIEW UserDefinedProducts (
fkProduct long NOT NULL AUTO_INCREMENT,
fkProfile long NOT NULL,
idCode long,
product_name string(48) NOT NULL,
origins string(64),
brands string(64) NOT NULL,
quantity string(64),
serving_quantity double,
nova_group char(1),
nutrition_grade_fr char(1),
energy_100g double NOT NULL,
carbohydrates_100g double NOT NULL,
sugars_100g double NOT NULL,
proteins_100g double NOT NULL,
fat_100g double NOT NULL,
saturated_fat_100g double NOT NULL,
saturated_fat_modifier char(1),
salt_100g double NOT NULL,
salt_modifier char(1),
fiber_100g double,
CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(UPDATABLE 'TRUE')
AS
SELECT
udp.fkProduct as fkProduct,
udp.fkProfile as fkProfile,
udp.idCode as idCode,
udp.product_name as product_name,
udp.origins as origins,
udp.brands as brands,
udp.quantity as quantity,
udp.serving_quantity as serving_quantity,
udp.nova_group as nova_group,
udp.nutrition_grade_fr as nutrition_grade_fr,
udp.energy_100g as energy_100g,
udp.carbohydrates_100g as carbohydrates_100g,
udp.sugars_100g as sugars_100g,
udp.proteins_100g as proteins_100g,
udp.fat_100g as fat_100g,
udp.saturated_fat_100g as saturated_fat_100g,
udp.saturated_fat_modifier as saturated_fat_modifier,
udp.salt_100g as salt_100g,
udp.salt_modifier as salt_modifier,
udp.fiber_100g as fiber_100g
FROM UserDefinedProducts_SRC udp;
CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS
FOR EACH ROW
BEGIN ATOMIC
DECLARE long vIdProduct;
DECLARE integer vNumRecords;
-- if a product code was given, check that the user has not already created a product for this code
-- this situation should actualy never appear
IF (new.idCode IS NOT NULL)
BEGIN
vNumRecords = SELECT 1 FROM UserDefinedProducts_SRC WHERE
fkProfile = new.fkProfile AND idCode = new.idCode
LIMIT 1;
IF(vNumRecords > 0)
BEGIN
DECLARE EXCEPTION e = SQLEXCEPTION 'User defined product already exists' SQLSTATE '45000';
RAISE e;
END
END
-- check if the user has already reached his MAX amount of UserDefinedProducts;
vNumRecords = SELECT COUNT(*) FROM UserDefinedProducts_SRC WHERE fkProfile = new.fkProfile;
IF(vNumRecords >= 1000)
BEGIN
DECLARE EXCEPTION e = SQLEXCEPTION 'MAX amount of user defined products exceeded for this account' SQLSTATE '45000';
RAISE e;
END
-- if all checks have been passed we can safely add the product to the database
INSERT INTO
Products(fkDatabaseKey)
VALUES
(0);
-- create a new record for the user defined product
vIdProduct = cast(generated_key('idProduct') as long);
INSERT INTO
UserDefinedProducts_SRC(fkProduct, fkProfile, idCode, product_name, origins, brands, quantity,
serving_quantity, nova_group, nutrition_grade_fr, energy_100g, carbohydrates_100g,
sugars_100g, proteins_100g, fat_100g, saturated_fat_100g, saturated_fat_modifier,
salt_100g, salt_modifier, fiber_100g)
VALUES
(vIdProduct, new.fkProfile, new.idCode, new.product_name, new.origins, new.brands, new.quantity,
new.serving_quantity, new.nova_group, new.nutrition_grade_fr, new.energy_100g, new.carbohydrates_100g,
new.sugars_100g, new.proteins_100g, new.fat_100g, new.saturated_fat_100g, new.saturated_fat_modifier,
new.salt_100g, new.salt_modifier, new.fiber_100g);
-- supply the key value
key.fkProduct = vIdProduct;
END;
{noformat}
> When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-5782
> URL: https://issues.jboss.org/browse/TEIID-5782
> Project: Teiid
> Issue Type: Quality Risk
> Components: OData
> Affects Versions: 12.3
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
>
> Hello,
> in the past we had an issue in the odata layer, that the generated primary key of an autoincrement key was not corretly transfered back in the odata response. This issue was fixed.
> However, the current issues derives from the discussion in
> https://issues.jboss.org/browse/TEIID-5763
> where we havewritten an INSTEAD OF INSERT trigger. According to my last comment to https://issues.jboss.org/browse/TEIID-5763, there are two situations which can arrise. I have to set the 1. key group explicitely or 2. it is set implicitely.
> Now in both situations I observe the same result. When I POST an INSERT with a dummy primary key, say key=0 (as it was NOT NULL in the past, and I am still using the old sources) I retrieve key=0 in the odata response. Correct would be to get a response with the generated key instead.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
4 years, 11 months