informix-community

Open full view…

ESQL/C issue with trusted connections

sebflaesch
Thu, 07 Mar 2019 10:57:47 GMT

Hi all, (I hope this is the right place to report Informix database, dev tools and doc issues,) In the Informix SQL reference documentation, the syntax diagram of CONNECT TO shows that it should be possible to mix trusted connections and concurrent TX options: EXEC SQL CONNECT TO ... USER ... USING ... TRUSTED WITH CONCURRENT TRANSACTIONS ; However this does not compile with esql (version 4.10.FC9): Error -33051: Syntax error on identifier or symbol 'WITH'. 1) Is this connection options combination valid? 2) Seems that JDBC has a TRUSTED_CONTEXT=TRUE URL option but nothing for concurrent transactions.... why? Is a connection with JDBC always with concurrent TXs? 3) Found also some doc errors that should be fixed: https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sec.doc/ids_tru_010.htm You can see EXEC SQL CONNECT TO 'database_name' TRUSTED; which is not valid because the USER/USING clause is not specified... (again based on the syntax diagram) Thanks, Seb

andreasl
Thu, 07 Mar 2019 23:28:52 GMT

Hi Seb, this certainly is one place for raising any such questions. If in a position to open a support case, that would be a good alternative, esp. in cases of malfunction or wrong/misleading documentation. Apparently you're avoiding the compile time syntax error by putting 'TRUSTED' last, after WITH CONCURRENT TRANSACTION - I havent's tested its functionality, though. Will raise this as a doc error once getting confirmation this really works. And yes, "Error -33085: The username or the password was not provided in a TRUSTED connection. Both are required." would indicate, your second finding is true too. Will have to clarify the JDBC question... BR, Andreas

sebflaesch
Fri, 08 Mar 2019 09:04:36 GMT

Thanks for you answer Andreas! I can confirm that this ESQL/C code compiles and executes without SQL error: EXEC SQL CONNECT TO 'mydb' USER :un USING :pw WITH CONCURRENT TRANSACTION TRUSTED; In the ESQL/C code I can then switch between db users bound to the user identified in the connection, for the trusted context I have defined in my database. I have also tested with dbaccess, that syntax is accepted: sf@toro:~$ dbaccess - - > connect to 'test1' user 'basic' with concurrent transaction trusted; ENTER PASSWORD: Connected. But I wonder if the TRUSTED keyword is properly taken into account by dbaccess: I can switch to another user bound to 'basic' but when try to switch to another user again I get a not DBA permission error (this does not occur in the ESQL/C program when switching between db users): sf@toro:/tmp$ dbaccess - - > connect to 'test1' user 'basic3' with concurrent transaction trusted; ENTER PASSWORD: Connected. > set session authorization to 'user1'; Session authorization set. > set session authorization to 'user2'; 389: No DBA permission. Error in line 1 Near character position 35 I get the same behavior if I do not use the TRUSTED keyword in CONNECT TO. ... Regarding the syntax / doc: From my understanding, the TRUSTED keyword is strongly tied to the USER clause. If you try to compile this ESQL/C code you get an error: EXEC SQL CONNECT TO 'mydb' TRUSTED; esqlc: "tc.ec", line 10: Error -33085: The username or the password was not provided in a TRUSTED connection. Both are required. 1 error(s) found So the syntax diagram in the doc looks more natural to me than the grammar implemented in ESQL/C (and dbaccess). But I guess it's too late to change the these grammars now... so it becomes a documentation bug? We are about to support trusted connections in our Genero BDL language, but we need to be sure about the syntax... Can you confirm that the ESQL/C syntax is the real syntax to be used? BTW I have tested with c4gl 7.51.FC2, it does not support the TRUSTED keyword: MAIN DEFINE un, pw VARCHAR(50) CONNECT TO "test1" USER un USING pw WITH CONCURRENT TRANSACTION CONNECT TO "test1" USER un USING pw WITH CONCURRENT TRANSACTION TRUSTED |_____________________________________________________________________ _____^ | | A grammatical error has been found on line 4, character 76. | The construct is not understandable in its context. | See error number -4373. END MAIN Cheers! Seb

andreasl
Mon, 11 Mar 2019 14:02:56 GMT

Hi Seb, not sure 'TRUSTED' and 'dbaccess' should ever be thought together - you trust an application of which you know what to expect from, while you never can be sure what will come through dbaccess (any SQL possible). So dbaccess accepting the TRUSTED keyword probably isn't right from the beginning? Apparently it does work, though, partly... I'd have to play more with trusted connections, to be of real help. Still having to figure out JDBC and 'concurrent transactions'... BR, Andreas

sebflaesch
Mon, 11 Mar 2019 14:33:46 GMT

Hi Andreas, Yes dbaccess is not the best candidate for trusted connections... Was to check if it's working... it looks like dbaccess just ignores the "TRUSTED" keyword. I think it should just fail/deny it, instead of connecting silently and let the user think the trusted connection succeeded. My instinct is that CONNECT TO statements are parsed by dbaccess (because with a "USER" clause, dbaccess then asks you for a password), and the parsing is just missing the case of "TRUSTED" keyword usage... Our main concern for now is about the syntax, we don't know what to implement... CONNECT TO ... USER ... USING ... [WITH CONCURRENT TRANSACTION] TRUSTED (like esql/c) or CONNECT TO ... USER ... USING ... TRUSTED [WITH CONCURRENT TRANSACTION] (like the doc syntax diagram) ? Cheers, Seb

andreasl
Mon, 11 Mar 2019 14:43:57 GMT

...was my first thought too - but then I realized that you must have tried it with a user for which a 'trusted context' had been defined - which, I concluded, might be the reason your 'SET SESSIONAUTHORIZATION...' didn't require SETSESSIONAUTH privilege - something it did in my non-'trusted context' testing. ('SET SESSIONAUTHORIZATION...' is older than 'trusted context', apparently it now can be used with and without it, with slight differences.) Ultimately I'm not sure dbaccess' TRUSTED has an effect or not, had to further explore. BR, Andreas