-
Enhancement
-
Resolution: Unresolved
-
Major
-
14.0
-
None
-
Undefined
It's not a bug but some kind of a request for an improvement (if it's doable).
SELECT * from a view or table fails if some column is hidden. At the same time, the user can not see the hidden columns at all (via a JDBC client or in the SYS.Columns table). Will it be possible to improve the behavior? For example, adjust projected symbols according to permissions (hidden columns) when using MultipleElementSymbol in Select object so that "select *" won't fail and will return only allowed columns?
Steps to reproduce the problem:
1. In PostgreSQL create the test_a table using the following scripts:
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);
2. Add PostgreSQL database configuration in standalone-teiid.xml:
<datasource jndi-name="java:/test_tables_pg" pool-name="test_tables_pg" enabled="true" use-java-context="true"> <connection-url>jdbc:postgresql://localhost:5432/test?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>
3. Add in test-vdb.xml java:/test_tables_pg configured in the previous step as a data source:
<model name="test_tables_pg"> <property name="importer.useFullSchemaName" value="false"/> <property name="importer.tableTypes" value="TABLE,VIEW"/> <property name="importer.importKeys" value="false"/> <source name="test_tables_pg" translator-name="myPg" connection-jndi-name="java:/test_tables_pg"/> </model>
4. Add in test-vdb.xml the following role and permissions:
<data-role name="role1" any-authenticated="true" allow-create-temporary-tables="true"> <description>Allow read only</description> <permission> <resource-name>test_tables_pg.test_a</resource-name> <allow-execute>true</allow-execute> <allow-read>true</allow-read> </permission> <permission> <resource-name>test_tables_pg.test_a.a</resource-name> <allow-read>false</allow-read> </permission> </data-role>
5. Run the following queries:
-- fails Error: TEIID30492 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 User <user> is not entitled to action <READ> for 1 or more of the groups/elements/procedures. select * from test_tables_pg.test_a ;; -- fails, expected behavior select a, b from test_tables_pg.test_a ;; -- doesn't fail, expected behavior select b from test_tables_pg.test_a ;;
