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.
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.
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?
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.