Hi, SQL Anywhere 16.0.0.2471. We get "Remote server '...' could not be found" error (SQLCODE=-659) when non-dba user executes a procedure (owned by dba) which selects remote data from proxy table into temporary table. I've prepared sample scripts to reproduce the problem. Remote database script (version is not important, our customer uses SA16, I tried on SA11, error shows up on both instances): // Create test_user GRANT CONNECT TO test_user IDENTIFIED BY 'test_user'; // Create simple table CREATE TABLE IF NOT EXISTS dba.test_table(id INTEGER); // Grant table permissions GRANT SELECT ON dba.test_table TO test_user;
// Create test_user GRANT CONNECT TO test_user IDENTIFIED BY 'test_user';
// 1. Works good for both dba and test_user select * from dba.p_test_table The last two statements fail with different errors: Is this a bug (or bugs) or am I missing something? Are there any quick workarounds, at least for the 5th case? So far I could not find them. I think execute immediate would work in this simple example but original procedure is not that simple. Thanks. Edit: For now we've set option extern_login_credentials value to "Login_user" as a workaround. asked 23 Jun '17, 02:46 Arthoor |
After reporting this to SAP, it turned out that these "bugs" actually are not bugs. It works as designed, only the error message of the 5th statement is misleading. SAP explanation (full text here): A remote server connection, by default, uses the current EXECUTING USER to build the UID,PWD credentials for the connection string. Only the login user password can be accessed so if the EXECUTING USER is not the same as the user that logged in, the PWD is undefined (the failures seen in 4 and 5 are the result of invalid user id or password). The default EXECUTING USER of a procedure is its owner. It turns out that there must be external login declared for the owner of the procedure (despite the owner's credentials are the same as in remote database) when
answered 04 Aug '17, 07:56 Arthoor 1
Sometimes the "easy to use defaults" make life harder, with remote server credentials being a prime example. FWIW Foxhound uses the ALTER SERVER USING clause to explicitly specify a connection string containing UID= and PWD= credentials for the remote database. The DSN= value is also included if that's what the user specified, as opposed to a complete connection string, but that's a red herring here. The point is, sometimes you just have . . . to . . . take . . . control :) Example: I just spent an hour wrestling with IE11 not displaying an ancestor DIV background-color properly, with the solution being changing the descendent DIV STYLE="background-color: inherit;" to the specific color value of the ancestor... ...I detest inheritance almost as much as polymorphism :) ( what was that Volker said about abstractions? :)
(04 Aug '17, 10:01)
Breck Carter
|
Wild guessing: What is your setting of option "extern-login-credentials"? - I could imagine this makes a difference here. For more, see Karim's discussion here... Wild guessing, as stated. answered 23 Jun '17, 03:20 Volker Barth Yes, it seems to have something to do with that option. Current setting is "Effective_user" (default). When changed to "Login_user" then everything seems to work as expected. But regarding my examples of select statements which work for both users, except the last statement, that really looks like a bug, doesn't it?
(26 Jun '17, 01:27)
Arthoor
Replies hidden
1
Just another hint, as you are using SELECT * FROM STP: It may also have to do with a query rewrite optimization: AFAIK, a stored procedure whose body just consists of one SELECT statement may be "inlined" when called within the FROM clause, i.e. the procedure call may be replaced by the single SELECT statement (and according casts of the arguments). (That has been documented within a SQL Anywhere white paper.) In my understanding, that would only apply for the sp_test_proc_without_temp_table() procedure and may also lead to that subtle difference. FWIW: Does this behaviour (which I would consider a bug, too) also apply when using the INTO clause with an explicit temporary table specification, by using SELECT ... INTO LOCAL TEMPORARY TABLE tmp, or when you explicitly create the table beforehand with a DECLARE LOCAL TEMPORARY TABLE statement? (I'm not sure whether the SELECT ... INTO #tmp create the same kind of temp table, as there are several variants...)
(26 Jun '17, 03:20)
Volker Barth
Yes, query rewriting seems to play a role here. I've added additional procedure (without temp table) which avoids inlining, and that gives another unexpected error (I've updated the question).
(26 Jun '17, 07:53)
Arthoor
FWIW, here's the link to the mentioned whitepaper by Ani Nica, cf. page 16 bottom.
(26 Jun '17, 08:08)
Volker Barth
1
Have you tried to test with CREATE PROCEDURE ... SQLSECURITY DEFINER (default) vs. INVOKER? That might also do the trick.
(26 Jun '17, 08:11)
Volker Barth
Yes, I have tried SQL SECURITY INVOKER, which seemed to work.
(27 Jun '17, 03:59)
Arthoor
|