I am trying to create a fine grained access priviledge for the following table:
CREATE FOREIGN TABLE UserDefinedProducts (
fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'), # foreign key to some product id in Products table
fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'), # foreign key to id of the user who created the record in this table, references Account table
CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile)
) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE');
- What I am trying to achieve is to to allow everyone read access to all rows in this table.
- At the same time I only like to allow update, delete operations for the user who is the owner of this row
When I am working with a row constraint:
GRANT ON TABLE "my_nutri_diary.UserDefinedProducts" CONDITION 'UserDefinedProducts.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36) )' TO odata;
This constraint is applied to all CRUD operations. Seems it is not possible to constrain it to be only applied on CUD operations.
1. Is there a clean way to this which I have not found out so far?
The only workaround I so far came up with is to specify a second table to the same source table, to split read and data manipulation operations to two tables with different access priviledges, hence 2. table =
CREATE FOREIGN TABLE UserDefinedProductsOfAllUsers (...) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE');
GRANT SELECT ON TABLE "my_nutri_diary.UserDefinedProductsOfAllUsers" TO odata;
REVOKE INSERT,UPDATE,DELETE,EXECUTE,ALTER ON TABLE "my_nutri_diary.UserDefinedProductsOfAllUsers" FROM odata;
and use this table for READ access without row constraints. The first table could than be used with row constraints for create, update, delete operations.
2. In case I have to use this dirty workaround, I would be interested if I would run into trouble if I specify two tables in teiid which represent the same foreign source table?
From my current understanding of how Teiid works, I assume, that there is no caching implemented in Teiid and each query leads to a query against the foreign source table. At least as long as the table is not materialized. Hence, I assume there should not be a problem.
3. Is this assumption correct?
In case there is caching it would also be great to get an explanation on the unerlaying mechanisms. Thanks for your help!