Suppose we log in as userA and have two SQL SECURITY DEFINER procedures called userB.p() and userC.p2(). Because both of these stored procedures are defined as SQL SECURITY DEFINER, the effective user will change to either userB or userC while the stored procedure is executing whereasthe logged in user will remain as userA.

Now suppose we start by querying a proxy table on remote server rem before we call either of the two stored procedures. In this case, the effective user is userA and the logged in user is userA.

Next we call userB.p() which queries another proxy table on the same remote server rem. For this second remote request, the effective user is userB while the logged in user remains as userA.

Once userB.p() exits, let's now assume that we call userC.p2() which again queries a proxy table on the same remote server rem. So, for this third remote request, the effect user is userC while the logged in user remains as userA.

In SA 12 and below, the remote connection would always be made for the logged in user regardless of what the current effective user was. Hence the server would only look at the extern login for userA and would only make one connection to the remote server. This behavior, while efficient in the usage of remote connections, goes against the idea of explicitly defining a procedure as either SQL SECURITY INVOKER or SQL SECURITY DEFINER. What's more, this behavior also makes it difficult to lock the database and remote server down by having explicit extern logins for a small set of users, not having extern logins for all the other users in the database, but still allowing other users to have controlled access to remote servers by explicitly granting them execute permissions on certain stored procedures.

In SA 16, we have changed the behavior so that remote connections now get established based on the effective user. In our example above, the first remote request would establish a connection based on the extern login of userA, the second remote request would force a second remote connection to be established based on the extern login of userB, and the third remote request would force a third remote connection to be established based on the extern login of userC. These remote connections persist so that if you then make additional remote calls with the effective user being either userA, userB or userC; then the remote connections get reused rather than forcing new connections to be established. Persisting the connections also allow state information for a given remote connection to persist.

Note that these "effective user" remote connections are still on a per local connection basis. Hence if local connection A makes a remote request with effective user userB and local connection B makes a remote request with the same effective user userB, then two remote connections for the extern login of userB are made, one for connection A and one for connection B.

Some additional notes:

1) The new DROP REMOTE CONNECTION statement in SA 16 can be used to drop a specific remote connection that is no longer needed, or all remote connections for a specific effective user, or all connections to a remote server.

2) If you are using an SA 12 or below database with an SA 16 server, then you will get the old legacy behavior. The new "effective user" behavior will only take effect if an SA 16 database is used with an SA 16 server.

3) If you are using an SA 16 database with an SA 16 server and would like to get back the old legacy behavior, then you can use the new extern_login_credentials option to revert back to the legacy behavior.

4) If you upgrade a database using SA 16 and specify either "dbupgrad -pd n" or "ALTER DATABASE UPGRADE ... SYSTEM PROCEDURE AS DEFINER OFF", then the extern_login_credentials option will be set to "Effective_user". The idea here is that if you have explicitly asked for legacy behavior when it comes to system stored procedures, then you probably want legacy behavior for remote connections as well.