Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-6053

SELECT * fails if a column is hidden

XMLWordPrintable

    • Icon: Enhancement Enhancement
    • Resolution: Unresolved
    • Icon: Major Major
    • 17.0
    • 14.0
    • Query Engine
    • 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 ;;
      

            Unassigned Unassigned
            dalex005 Dmitrii Pogorelov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated: