4 Replies Latest reply on Nov 3, 2012 11:12 AM by shawkins

    User Impersonation and Teiid Parsing

    ileitch

      We would like to "impersonate" users in a middleware layer. Using the jtds jdbc driver for SQL Server directly, this works as expected:

       



      String sql = "exec ('select remarks from TEST;') as user='ABC'";     





      try {




      x.directExecuteSql(sql, connection, true);

       

       

      Using the Teiid jdbc driver instead, we get a parsing error:

       



      sql = "exec ('select remarks from TEST;') as user='ABC'";


      try {



      x.directExecuteSql(sql,_conn, false);

       

       

       

       

      Nov 1, 2012 10:55:37 AM org.teiid.logging.JavaLogger log

      WARNING: TEIID30020 Processing exception 'TEIID31100 Parsing error: Encountered "(" at line 1, column 6.

      Was expecting:

          <ID> ...

          ' for request opLqyADiLoxc.0.  Exception type org.teiid.api.exception.query.QueryParserException thrown from org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:197). Enable more detailed logging to see the entire stacktrace.

       

      This is in embedded Teiid.

       

      Is there a better way to do this?

      Thanks

        • 1. Re: User Impersonation and Teiid Parsing
          rareddy

          Teiid uses "exec" for executing the stored procedures, not literal string, so it does not support that format of the query. For dynamic switching of the users you need to use https://docs.jboss.org/author/display/TEIID/Reauthentication

           

          Since it is embedded the switching of users does not really mean anything, unless you have custom security domain configured with your embedded server.

           

          Ramesh..

          • 2. Re: User Impersonation and Teiid Parsing
            shawkins

            You can by-pass Teiid parsing by enabling the use of the native procedure call on your translator.  See https://docs.jboss.org/author/display/TEIID/Built-in+Translators and the SupportsNativeQueries and NativeQueryProcedureName properties

             

            You should then be able to call the native sql with a Teiid query to the native procedue:

             

            "call source.native('exec (''select remarks from TEST;) as user=''ABC'')"

             

            The results are available in a resultset containing a column of array values that have each of the column values from your call.  You can then use ARRAYTABLE, array_get, OBJECTTABLE, etc. to process the results.

             

            Steve

            • 3. Re: User Impersonation and Teiid Parsing
              ileitch

              Thanks for the replies. Can we also use "native" to bypass parsing for a plain-old SQL statement?

               

              If we do pass a statement through Teiid for parsing, is the original unmodified statement available anywhere?

               

              Thanks again.

              • 4. Re: User Impersonation and Teiid Parsing
                shawkins

                Yes, you can pass whatever you want via the native procedure.

                 

                If the statement is parsed/planned by Teiid the original sql is not currently available, but could easily be made avaiable via the command context.  Please log an enhancement request if that is something that you would want.

                 

                Steve