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

EXECUTE IMMEDIATE in anonymous procedure ignores permissions

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Blocker
    • Resolution: Done
    • Affects Version/s: 8.12
    • Fix Version/s: 9.0, 8.7.6, 8.12.5, 8.13.2, 8.7.5.6_2
    • Component/s: Query Engine
    • Labels:
      None
    • Environment:

      teiid-8.12-Beta1 on Red Hat JBoss Enterprise Application Platform - Version 6.3.0.GA

    • Security Sensitive Issue:
      This issue is security relevant
    • Steps to Reproduce:
      Hide

      There are two determined virtual stored procedure in my vdb. First one, "testSelect()" procedure just runs "select 1 as selectResult" query and returns 1. Second one, "testSelect2()", just calls "testSelect()" procedure and must also return 1.

      <model visible = "true" type = "VIRTUAL" name = "procs">
              <metadata type = "DDL"><![CDATA[
                CREATE virtual procedure testSelect() 
                RETURNS (
                       "selectResult" integer
                ) AS
                BEGIN
                    select 1 as selectResult;
                END
       
                CREATE virtual procedure testSelect2()
                RETURNS (
                       "selectResult" integer
                ) AS
               BEGIN
                   SELECT p.* from (call procs.testSelect()) as p;
               END
              ]]>
              </metadata>
          </model>
      

      Here is common permissions for any authenticated user:

      <data-role name="RoleA" any-authenticated="true" allow-create-temporary-tables="true">
              <description>Allow read only</description>
              <permission>
                  <resource-name>procs.testSelect</resource-name>
                  <allow-execute>false</allow-execute> 
              </permission>
              <permission>
                  <resource-name>procs.testSelect2</resource-name>
                  <allow-execute>true</allow-execute> 
              </permission>
          </data-role>
      

      That is we have a forbidden "testSelect()" and an allowed "testSelect2()" procedures for execution.

      When running the following query:

      select s.* from (call procs.testSelect()) as s;
      

      I get the correct error message:

      Error: TEIID30492 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 User <user@teiid-security> is not entitled to action <EXECUTE> for 1 or more of the groups/elements/procedures.
      

      But when running the following query:

      begin
      execute immediate 'select s.* from (call procs.testSelect()) as s' as selectResult string into #temp;
      select * from #temp with return;
      end
      

      I get 1 as value without any error message that is wrong.

      If I run the following:

      select s.* from (call procs.testSelect2()) as s;
      

      I'll get 1 as value that is also wrong because we have no permissions to execute testSelect() procedure even from another procedure.

      Show
      There are two determined virtual stored procedure in my vdb. First one, "testSelect()" procedure just runs "select 1 as selectResult" query and returns 1. Second one, "testSelect2()", just calls "testSelect()" procedure and must also return 1. < model visible = "true" type = "VIRTUAL" name = "procs" > < metadata type = "DDL" > <![CDATA[ CREATE virtual procedure testSelect() RETURNS ( "selectResult" integer ) AS BEGIN select 1 as selectResult; END   CREATE virtual procedure testSelect2() RETURNS ( "selectResult" integer ) AS BEGIN SELECT p.* from (call procs.testSelect()) as p; END ]]> </ metadata > </ model > Here is common permissions for any authenticated user: < data -role name = "RoleA" any-authenticated = "true" allow-create-temporary-tables = "true" > < description >Allow read only</ description > < permission > < resource -name>procs.testSelect</ resource -name> < allow -execute>false</ allow -execute> </ permission > < permission > < resource -name>procs.testSelect2</ resource -name> < allow -execute>true</ allow -execute> </ permission > </ data -role> That is we have a forbidden "testSelect()" and an allowed "testSelect2()" procedures for execution. When running the following query: select s.* from (call procs.testSelect()) as s; I get the correct error message: Error: TEIID30492 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 User <user@teiid-security> is not entitled to action <EXECUTE> for 1 or more of the groups/elements/procedures. But when running the following query: begin execute immediate 'select s.* from (call procs.testSelect()) as s' as selectResult string into # temp ; select * from # temp with return ; end I get 1 as value without any error message that is wrong. If I run the following: select s.* from (call procs.testSelect2()) as s; I'll get 1 as value that is also wrong because we have no permissions to execute testSelect() procedure even from another procedure.
    • Bugzilla Update:
      Perform

      Description

      I've found a bug when executing a query in the EXECUTE IMMEDIATE clause. It ignores permissions.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dalex005 dalex dalex
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: