|
Hi All, There is a server SA 17.0.9.4913 "Server17" on which there are two bases "Base1" and "Base2". In the "Base1" created remote access to the "Base2". CREATE SERVER "remTestRW" CLASS 'SAODBC' USING 'driver=SQL Anywhere 17;ENG=Server17;DBN=Test2;commlinks=tcpip(Host=192.168.0.1;ServerPort=9000;DoBroadCast=No);CS=cp1251;LOG=d:\\log_rw12.txt'; I do not specifically specify the username and password in the connection string, so that the connection with "Base2" is made on behalf of the current user who will access the "Base2" objects. In the connection string for remote access to "Base2" I have specified a parameter for logging this connection. From my program I am under the user "User1" (he has all the necessary rights) I connect to the base "base1" and call from it a remote stored procedure from the base "Base2". In the log file, I see the following connection string I "Base2": 10:41:32 Attempting to connect using: UID=dba;DBN=Test2;ServerName=Server17;CON=ASACIS_63fe3bc1819e4ea09b296762117da745_9;LOG=D:\log_rw.txt;LINKS='tcpip(Host=192.168.0.1;ServerPort=9000;DoBroadCast=No)';CS=cp1251 Note: The connection for some reason occurs on behalf of the user "dba", and not on behalf of the user "User1". And for some reason, there is no password in this connection string either. I do all of the above for the server SA 12.0.1.4134 "Server12". CREATE SERVER "remTestRW" CLASS 'SAODBC' USING 'driver=SQL Anywhere 12;ENG=Server12;DBN=Test2;commlinks=tcpip(Host=192.168.0.1;ServerPort=9000;DoBroadCast=No);CS=cp1251;LOG=d:\\log_rw12.txt'; Here is its connection string: 11:14:03 Attempting to connect using: UID=User1;PWD=********;DBN=Test2;ServerName=Server12;CON=ASACIS_9488ad2ad97046c7bd8f2630b116238b_2;LOG=d:\log_rw12.txt;LINKS='tcpip(Host=192.168.0.1;ServerPort=9000;DoBroadCast=No)';CS=cp1251 Here, as you can see in the connection string, the call from "Base1" to "Base2" comes on behalf of the current user "User1" with his password. I think this is a bug in SA 17.0.9.4913. Because of what now my program can not work with the SA 17. If this is some kind of new feature SA 17, then how can I create a connection string for a remote server, what would this connection be made on behalf of the current user ? P.S. Testing servers was done separately, that would not affect each other. |
|
That is a new feature of V16 and above, see the "extern_login_credentials" option. Basically, you can now choose whether exern logins are done using the logged in user or not. This has been explained here by Karim in details:
It's a rather complex topic, particularly when remote procedures are involved, as SQL SECURITY DEFINER vs. INVOKER play an additional role IIRC, but to have the pre-16 behaviour, simply try to change the option's value to "Login_user". (That's what we have done...) Your advice on using "Login_user" helped, thanks a lot. But is it possible to solve my problem (so that the remote procedure and, accordingly, the connection to the remote server is carried out on behalf of the current user) without changing the value of the "extern_login_credentials" option, which is used for compatibility with the old behavior ? Yes, and in the help when describing the option "extern_login_credentials" is also not. This is a remote procedure call in "Base1" (which the user "User1" execute from the base "Base1") CREATE PROCEDURE "dba"."ModifyTest_RW"(in "nBasisType" integer) at 'remTestRW;;DBA;ModifyTest_RW' This is the remote procedure itself in "Base2". CREATE PROCEDURE "dba"."ModifyTest_RW"(in "nBasisType" integer) BEGIN ... END; User "User1" is in both databases with the same password.
(08 Feb '19, 08:06)
Stalker
Replies hidden
Hm, I do not quite understand why setting the option is no real solution for you? Besides, you may be successful by resetting the option to its default and by changing the remote procedure(s) to use SQL SECURITY INVOKER because then the logged in user (i.e. the caller of the procedure) would be the one making the remote call. At least I think it will.
(08 Feb '19, 08:59)
Volker Barth
Your advice on using the value of "Login_user" "helped me in solving my problemma. Thank you very much for that. Just in the description of the option "extern_login_credentials" it says: "This option is provided for backwards compatibility. For security reasons, do not specify this option." And my recommendation is not to use this option. somewhat embarrassing. Option "SQL SECURITY INVOKER" should be used for remote procedures in the "Base1" or "Base2" ?
(08 Feb '19, 09:28)
Stalker
Replies hidden
For the database in which you define the procedure as a remote procedure, i.e. with the AT clause. That is in database base1, if my understanding is correct. Note, I guess the SQL SECURITY clause has the desired impact here, I have not tested that myself. In case all your remote accesses are done through remote procedures, another approach is to generally use the default SQL SECURITY DEFINER and the default extern_login_credentials option value 'Effective_user' and to specify an externlogin for the according procedure owner(s) "dba" in your case.
(08 Feb '19, 11:32)
Volker Barth
I found the reason why the "SQL SECURITY INVOKER" option does not work. It does not work if the remote procedure is called from a trigger. CREATE PROCEDURE "dba"."Proc1"() sql security invoker at 'remTestRW;;dba;Proc1' CREATE TRIGGER "TestTrigger1" AFTER INSERT ORDER 1 ON "dba"."TEST_B1" REFERENCING NEW AS new_f FOR EACH ROW BEGIN call dba.RemoteProc1(); END If the procedure is called directly (from the ISQL or from its program), then everything is normal. It seems that the procedure from the trigger is not called on behalf of the current user, but on behalf of the owner of the trigger. That's just how to solve this problem, I do not understand.
(11 Feb '19, 03:52)
Stalker
Replies hidden
Yes, triggers execute with the privileges of the owner of the according table, not with that of the user whose actions cause the trigger to fire, so they run as "SQL SECURITY DEFINER" by design... As such, that impacts the effect of calling a SECURITY INVOKER procedure from a trigger...
(11 Feb '19, 05:55)
Volker Barth
I understood it. And how can this effect (feature) of a trigger be circumvented ? I did not find such parameters (options) for the trigger or the "CALL" command. That is, what can be done so that the remote procedure is called from the trigger as an "SQL SECURITY INVOKER" ?
(11 Feb '19, 08:58)
Stalker
The fact that a simple SQL-code trigger is executed as "SQL SECURITY DEFINER" is correct. BUT that is why in the procedure called from the trigger its parameter "SQL SECURITY INVOKER" is ignored, this is not clear and looks like an error "SA 17".
(12 Feb '19, 01:43)
Stalker
Replies hidden
IMHO, I don't think this is an error. Say, there are two procedures Owner1.P1 with SQL SECURITY DEFINER and Owner2.P2 with SQL SECURITY INVOKER and P1 calls P2 within its body. Now say, User3 calls P1. Then P1 runs in the context of Owner1 (because that's the owner = definer) and P2 runs in the context of Owner1, too (because that is the caller = invoker). Basically the same appears with your trigger, where the trigger replaces P1.
(12 Feb '19, 02:45)
Volker Barth
Then P1 runs in the context of Owner1 (because that's the owner = definer) and P2 runs in the context of Owner1, too (because that is the caller = invoker). Not sure if this is correct. In this situation, I would understand by "invoker" not the owner of the P1 procedure, but the User3 user who started the entire call chain. That is, I disagree with the statement that the caller for the P2 procedure is the owner of the P1 procedure. IMHO caller can only be a real user, in your example it is User3. Returning to my question: Is it possible to call a procedure from the trigger (with the invoker option) so that it works on behalf of the current user, and not from the owner of the trigger (table) ?
(12 Feb '19, 06:17)
Stalker
Please have a look at the following doc article: Procedures and functions running with owner or invoker privileges There's a sample (number 3) that matches the one I have mentioned:
Within u3.p3, the effective user (as shown by the EXECUTING USER special value) is u2 because u2 has invoked p3. It's not u1. Note the statement from that topic:
So in my understanding, it is the owner (and not the caller) of the outer procedure (or in your case the trigger) that is treated as effective user of the inner procedure.
(12 Feb '19, 09:10)
Volker Barth
Hm, I don't know. You might have luck with explicit impersonation via SETUSER... But then again, in my limited understanding modifying the extern_login_credentials option or adding fitting extern logins should by way easier.
(12 Feb '19, 09:15)
Volker Barth
If I cannot find another way out, I will certainly use the solution with "extern_login_credentials", there is simply a warning in the documentation against using this option and it was made for compatibility with older versions of "SA". Concerning "extern logins" - if possible, write a small example of its use. Although, as I understand it, it will not be very convenient for me to use "extern logins", since I have quite a few users who can call the remote procedure and the rights of these users change periodically. Ideally, it would be nice if developers improved the design of "SQL SECURITY INVOKER" by adding to it an option (for example, "real user") that would clearly regulate what is meant by "INVOKER" - the real user (in your example "User3") or the one that called the procedure (in your example "Owner1"). Could you pass my suggestion to the developers of "SA 17" ?
(13 Feb '19, 02:46)
Stalker
I'm just another customer, so I have no other methods to make suggestions than you. Generally the SQL Anywhere engineers do read these questions. But if you want to make an explicit suggestion and get more attention, feel free to add it as a new question here (tagged with "product-suggestion").
(13 Feb '19, 05:27)
Volker Barth
More comments hidden
|
|
Well, the discussion has been stuck right now, therefore I try to take a different view. I'm posting it as an answer although it's a counter-question - just to prevent all too deep comment nesting... The general question is: How do you want your users of database Base1 to access the second database Base2?
I.e. User1 of Base1 should connect to Base2 as User2, User2 should connect to Base2 as User2 and the like. (Note, technically, they cannot be the "same users", as each SQL Anywhere database has its own exclusive user management, in contrast to other systems lise ASE or MS SQL Server who can share logins between different databases on the same server.)
I.e. User1 of Base1 should connect to Base2 as UserX, and User2 and all other users should also connect to Base2 as UserX. IMHO, that is often the case when the remote access to Base2 simply is done as part of a particular function, say to extract remote data from Base2 and store it in Base1 or vice versa. In such cases, the access might be done with a particular user of Base2 which has restriced access there just to access the relevant data. For solution 1, you would prefer extern_login_credentials = 'Login_User' (the pre-v16 method), and that would well with an "Invoker" privilege approach. In case user names or credentials are not identical in Base1 and Base2, you would need to add appropriate extern login mappings for each user. For solution 2, the v16 default extern_login_credentials setting (i.e. 'Effective_User') would be fitting. You would usually allow the access to Base2 only via stored procedures or functions with default DEFINER privileges, and you would usually have to specify an extern login for the procedure's owner to specify which User of Base2 should be used to connect. I hope that helps you to choose the desired approach. Once the approach is chosen, it's easier to have a look at the technical options... I have users who need access to "Base2" from the base "Base1" (I have several such users and they may change from time to time) have the same username and passwords in both databases. As I understand it, given the existing possibilities "SA 17", at the moment I have only one option - to use the value of the option "Login_User". If I can persuade the developers of "SA 17" to implement my proposal (which I voiced above), then it will be possible to work with "Effective_User".
(15 Feb '19, 07:35)
Stalker
Replies hidden
Hm, if you want the user logins from Base1 to also connect to Base2, then in my understanding the "Login_user" option does exactly that, so why do you ask for a different method? IMVHO, a behaviour change in the way the SQL SECURITY clause works is rather unlikely because - in my understanding - it fully works as desired. What you might ask for (again, in my mind!) would be to add a SQL SECURITY INVOKER clause for the CREATE TRIGGER statement to allow that the trigger code runs in the context of the user issuing the according DML statements. - If such an addition would be added, you could then use a stored procedure with SQL SECURITY INVOKER to do remote access. Now, say, a user would do an UPDATE, the according trigger would run in the user's context, as would a procedure called within the trigger doing the remote access.
(15 Feb '19, 07:48)
Volker Barth
|