on 2017 Jun 23 3:46 AM
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;
Local database script (SQL Anywhere 16.0.0.2471):
// Create test_user GRANT CONNECT TO test_user IDENTIFIED BY 'test_user';// Drop remote table and server IF EXISTS (select 1 from SYS.SYSTABLE where table_name = 'p_test_table') THEN DROP TABLE dba.p_test_table; END IF; IF EXISTS (select 1 from SYS.SYSSERVER where srvname = 'remote_database') THEN DROP SERVER "remote_database"; END IF;
// Create remote server and table (users and passwords conform so external logins are not necessary) IF NOT EXISTS(select 1 from SYS.SYSSERVER where srvname = 'remote_database') THEN CREATE SERVER "remote_database" CLASS 'SAODBC' USING 'ENG=empty11;DBN=empty;LINKS=TCPIP;DRIVER=SQL Anywhere 16'; END IF; CREATE EXISTING TABLE dba.p_test_table AT 'remote_database;;dba;test_table';
// Grant table permissions GRANT SELECT ON dba.p_test_table TO test_user;
// Create simplified procedure without temp table CREATE OR REPLACE PROCEDURE dba.sp_test_proc_without_temp_table() RESULT (id INTEGER) BEGIN SELECT id FROM dba.p_test_table; END;
// Grant procedure permissions GRANT EXECUTE ON dba.sp_test_proc_without_temp_table TO test_user;
// Create not inline'able procedure without temp table CREATE OR REPLACE PROCEDURE dba.sp_test_proc_without_temp_table_not_inline() RESULT (id INTEGER) BEGIN DECLARE @id INTEGER; // this avoids procedure inlining SELECT id FROM dba.p_test_table; END;
// Grant procedure permissions GRANT EXECUTE ON dba.sp_test_proc_without_temp_table_not_inline TO test_user;
// Create simplified procedure with temp table CREATE OR REPLACE PROCEDURE dba.sp_test_proc_with_temp_table() RESULT (id INTEGER) BEGIN SELECT id INTO #tmp FROM dba.p_test_table; SELECT id FROM #tmp; END;
// Grant procedure permissions GRANT EXECUTE ON dba.sp_test_proc_with_temp_table TO test_user;
Then here are sample select statements:
// 1. Works good for both dba and test_user select * from dba.p_test_table// 2. Works good for both dba and test_user select * into #tmp from dba.p_test_table
// 3. Works good for both dba and test_user select * from dba.sp_test_proc_without_temp_table()
// 4. Works good for dba but FAILS for test_user with sqlcode -656 select * from dba.sp_test_proc_without_temp_table_not_inline()
// 5. Works good for dba but FAILS for test_user with sqlcode -659 select * from dba.sp_test_proc_with_temp_table()
The last two statements fail with different errors:
* The 4th statement fails with error "Unable to connect to server 'remote_database': ... Invalid user ID or password" when executed by test_user. But both users have identical credentials in both databases, and according to the docs "By default, SQL Anywhere uses the names and passwords of its clients whenever it connects to a remote server on behalf of those clients", so with my understanding, external logins are not necessary here.
* The 5th statement fails with error "Remote server 'remote_database' could not be found" when executed by test_user.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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? 🙂
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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...)
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).
And yes, this behavior also apply when using other ways to declare temp table (SELECT ... INTO LOCAL TEMPORARY TABLE and DECLARE LOCAL TEMPORARY TABLE statements).
FWIW, here's the link to the mentioned whitepaper by Ani Nica, cf. page 16 bottom.
Have you tried to test with CREATE PROCEDURE ... SQLSECURITY DEFINER (default) vs. INVOKER? That might also do the trick.
User | Count |
---|---|
75 | |
10 | |
9 | |
7 | |
6 | |
6 | |
6 | |
6 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.