I use a program that uses impersonate to log into the SQL Anywhere database. The registration (with integrated login) works fine. The mapped user is selected. The osuser is set correctly when logging in for the first time. But this osuser remains the same for the following logins with different integrated users. The AppInfo also has this old user. Can it be cached somehow? Or is the a possibility to get the originating user for integrated login (login mappings)?

Let me be a bit more precise on what our program is doing:

  • List item
  • A thread is started in the Local System user context
  • When a "piece of work" arrives from "User1"
  • The thread impersonates as "User1" and opens a database connection using integrated login. This works well.
  • After the piece of work is done the thread reverts back to Local System
  • A second piece of work arrives from "User 2"
  • The thread impersonates as "User2"
  • Since the users are different, the program does not re-use the existing connection but opens a new database connection, once more with integrated login.
  • The database connection is opened, User2 is mapped to the correct database user (which is different from the one User 1 is mapped to), but connection_property('osuser') returns "User 1".

asked 19 Jan, 08:44

Miro%20Marek's gravatar image

Miro Marek
913510
accept rate: 0%

edited 19 Jan, 11:11

But this osuser remains the same for the following logins with different integrated users.

What does that mean, are connections re-used, say via connection pooling?

We are using integrated logins with v16, and the "OsUser" connection property works as expected, i.e. different OS users are mapped to identical database users, and the OsUser property does return the correct OS user name...

(19 Jan, 09:51) Volker Barth
Replies hidden

Hi Volker, yes. The program use connection pooling. It is a service for e-mail registration. New user is correctly recognized and used for login. Also th binaries recognize correctly the windows user. Only triggers in the database don´t get the right value. The SQL Anywher version is 17.

(19 Jan, 10:02) Miro Marek

Please tell us exactly what kind of connection pooling you are using, as discussed in this Help topic Improve Application Performance with Connection Pooling; e.g., SQL Anywhere server side pooling versus .NET versus "other pooling product", etc etc etc.

(19 Jan, 10:29) Breck Carter

Just in case you can't get OSUSER to do what you want, setting CON=whatever in the connection string and using CONNECTION_PROPERTY ( 'NAME' ) in the trigger code might help.

(19 Jan, 10:36) Breck Carter

Only triggers in the database don´t get the right value.

Is there a possibility there's "database user impersonation" via SETUSER or via nested procedures at work, i.e. that the (second) connection calls procedure that run in different contexts (see SQL SECURITY DEFINER etc.) and thereby are mapped to different users? (Note, even if so, I would not know whether this would affect connection property OsUser.)

(19 Jan, 13:09) Volker Barth

After the piece of work is done the thread reverts back to Local System

So that does close the current database connection and give it back to the connection pool?

(19 Jan, 13:11) Volker Barth
Replies hidden

We actually do not use CPOOL from connection parameter. This is done with custom code. But as I know the database connection stays open until it is needed for another request.

(19 Jan, 13:41) Miro Marek
1

> This is done with custom code

How does that "custom code" work? If you don't know what it is doing, it's unlikely anyone here will know :)

To put it another way, that custom code probably holds the key to your problem.

(19 Jan, 14:12) Breck Carter

No, the connection is kept open for the next piece of work from User 1.

I'm not sure if we are talking about the same thing when mentioning "connection pooling". Our application keeps a set of database connections, to reduce the overhead of reconnecting. Each connection is reserved for use to a specific OS user. After some idle time connections are closed. Though we sometimes call this "connection pooling" (since this is what we do) we do not use the ConnectionPool parameter of Anywhere.

BTW, Miro an I are colleagues, we're both working on this issue.

(20 Jan, 03:43) tedfroehlich

Each connection is reserved for use to a specific OS user.

Hm, if this is true, why then do you notice wrong OS user names?


If you really do use an existing connection, how would you expect the database server to know that the OS user running the process that uses the same connection has changed in-between? I'm quite sure the APPINFO connection is gathered during the connection's initialization and then kept unchanged.

A further note: What about temporary tables, connection variables and other connection-specific data - are they "cleaned up" when the according thread ends? AFAIK SQL Anywhere does have to do a lot of "cleaning up" when a connection is given back to the connection pool to make it look like a new one for the next usage, and I guess a home-brewn pooling would have to do the same, if that is possible at all...

(20 Jan, 05:53) Volker Barth

I agree to your first point, I'd also expect that APPINFO always contains the OS user who opened the connection.

But we see the wrong OS user in a newly opened second connection. User 2 opens a new connection (in her own user context), because she isn't User 1, otherwise she'd use the existing one.

Concerning the second paragraph, since every connection is reserved for a specific OS user, it indeed is intended behaviour that nothing is cleaned up! So nothing has to be set up again when the next request comes in.

Just as background: this implementation originates many years ago, from a time when (AFAIK) connection pooling was not yet supported in Anywhere.

(20 Jan, 10:29) tedfroehlich

Is there any login_procedure "magic" at work?

You may also try to diagnose the logins via Connect/Disconnect events and/or the LOG connection parameter...

(20 Jan, 10:45) Volker Barth

> this implementation originates many years ago, from a time when (AFAIK) connection pooling was not yet supported in Anywhere.

Thank you for that critically important information.

If you have access to the custom code which implements the connection pooling, please show us the actual code that establishes a new connection to SQL Anywhere that ends up with the wrong value being reported by CONNECTION_PROPERTY ( 'OsUser' );

If you don't have access to that code (i.e., it is a black box to you) then Volker's suggestions about adding diagnostic code on the database side are good.

(20 Jan, 14:16) Breck Carter
showing 3 of 13 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×43

question asked: 19 Jan, 08:44

question was seen: 780 times

last updated: 20 Jan, 14:16