Re: Connection Pooling with the .NET Provider on IIS
A design/philosophy debate rather than a bug report: can a pooled connection (e.g. .NET provider under IIS) switch user identity so the end user's ID is propagated through to the database for auditing, instead of all sessions using one generic application account? Fernando argues Oracle (proxy authentication) and DB2 (trusted contexts) support such re-authentication; others counter that this is a security risk and that tracking users via application-level session IDs/tokens is the usual workaround. Informix's SET SESSION AUTHORIZATION is raised but dismissed as too limited (DBA-only). No resolution is recorded — the thread ends with speculation about what IBM would need to change.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Ian Michael Gumby wrote: > With respect to Fernando's earlier post, you have to think about the > security risks if you can change your connection details within the > pool. Not a good idea. > I'd like a more elaborate response if you can. As I said, it's doable by other RDBMS. If you can authenticate securely you can of course change credentials in a secure manner... You may not want to do that for simplicity and performance but I can't think of any additional security issues... It's not like you simply say "authenticate me as john_doe/password" and then simply "now I'm informix, thank you!" Although usually J2EE applications just use a connection pool with a single user, some customers would like to be able to do end-to-end identity propagation. Specially if you're modernizing your application by introducing web services for example and you have old logging mechanisms on the database side based on the user ID. This is s situation customers may face if they try to use the new web services capabilities of newer versions of 4GL. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
On Jun 15, 6:16 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > Ian Michael Gumby wrote: > > With respect to Fernando's earlier post, you have to think about the > > security risks if you can change your connection details within the > > pool. Not a good idea. > > I'd like a more elaborate response if you can. As I said, it's doable by other > RDBMS. If you can authenticate securely you can of course change credentials in > a secure manner... You may not want to do that for simplicity and performance > but I can't think of any additional security issues... It's not like you simply > say "authenticate me as john_doe/password" and then simply "now I'm informix, > thank you!" > Again, which *other* databases? Unlike DB2 and IDS, do the other databases authenticate against the OS? > Although usually J2EE applications just use a connection pool with a single > user, some customers would like to be able to do end-to-end identity > propagation. Specially if you're modernizing your application by introducing > web services for example and you have old logging mechanisms on the database > side based on the user ID. This is s situation customers may face if they try > to use the new web services capabilities of newer versions of 4GL. > end to end identity propogation? You have that with connection pooling if you design your app appropriately and track your user via a session id/token. You really need to think about what you're saying.
grendal wrote: > On Jun 15, 6:16 pm, Fernando Nunes <domusonl...@gmail.com> wrote: >> Ian Michael Gumby wrote: >>> With respect to Fernando's earlier post, you have to think about the >>> security risks if you can change your connection details within the >>> pool. Not a good idea. >> I'd like a more elaborate response if you can. As I said, it's doable by other >> RDBMS. If you can authenticate securely you can of course change credentials in >> a secure manner... You may not want to do that for simplicity and performance >> but I can't think of any additional security issues... It's not like you simply >> say "authenticate me as john_doe/password" and then simply "now I'm informix, >> thank you!" >> > > Again, which *other* databases? > Unlike DB2 and IDS, do the other databases authenticate against the > OS? You can authenticate IDS users against a lot of sources... But that's not the point. Both Oracle and DB2 allow some form of re-authentication(DB" trusted context and Oracle proxy authentication). I didn't check SQL server, Sybase or others, and I don't recall ever seen any references, but at least SQL server probably has something. > end to end identity propogation? > You have that with connection pooling if you design your app > appropriately and track your user via a session id/token. > The idea is to propagate the end user ID into the database connection, without having to create connections for each user. Just the plain old connection pool with something extra. Your suggestion would require that you check if you already have a connection with that user and if not create a new one. For a great number of users that's not good. And you would have to code it. It can be done transparently for you without the overhead of creating a connection for each user (and expire it after some inactivity period) > You really need to think about what you're saying. I do. You just don't get it... Once again, exchanging ideas with you was a disappointment. But I truly believe you'll keep posting about this. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
grendal wrote: > On Jun 15, 6:16 pm, Fernando Nunes <domusonl...@gmail.com> wrote: >> Ian Michael Gumby wrote: >>> With respect to Fernando's earlier post, you have to think about the >>> security risks if you can change your connection details within the >>> pool. Not a good idea. >> I'd like a more elaborate response if you can. As I said, it's doable by other >> RDBMS. If you can authenticate securely you can of course change credentials in >> a secure manner... You may not want to do that for simplicity and performance >> but I can't think of any additional security issues... It's not like you simply >> say "authenticate me as john_doe/password" and then simply "now I'm informix, >> thank you!" >> > > Again, which *other* databases? > Unlike DB2 and IDS, do the other databases authenticate against the > OS? From what I've seen, both Oracle and MS Sql Server Authenticate the user based on well established Kerberos/NTLM Authentication methods. Neither of the above would ever allow a connection to be re-used by any other user than the originator. I care little if it is actually changing the authentication on the fly or if the connection pool is authentication aware and blocks the connection re-use. the point is that when developing a secure system you would never want your credentials to be reused and therefore written into DB logs by anyone other than yourself. > >> Although usually J2EE applications just use a connection pool with a single >> user, some customers would like to be able to do end-to-end identity >> propagation. Specially if you're modernizing your application by introducing >> web services for example and you have old logging mechanisms on the database >> side based on the user ID. This is s situation customers may face if they try >> to use the new web services capabilities of newer versions of 4GL. >> > end to end identity propogation? The concept of end-to-end propagation of identity is fundamental in developing a secure and robust system. I have full traceability from when the user first "Hits" the web page, into web service calls on remote systems which connect into databases. Every system's audit logs reference the user that completed the action not some system account that makes tracing individuals actions very difficult. > You have that with connection pooling if you design your app > appropriately and track your user via a session id/token. Why should I design anything into my App With other RDBMS's I relly on the infrastructure to provide this. It saves me time and leaves Identity management up to the specialists. If you rely on application level "Session Id" or "Tokens" you open yourself up to assorts of attack vectors. > > You really need to think about what you're saying. You really need to start thinking about security!
On Wed, Jun 17, 2009 at 1:09 PM, mc <mc@community.nospam> wrote: > grendal wrote: > > On Jun 15, 6:16 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > >> Ian Michael Gumby wrote: > >>> With respect to Fernando's earlier post, you have to think about the > >>> security risks if you can change your connection details within the > >>> pool. Not a good idea. > >> I'd like a more elaborate response if you can. As I said, it's doable by > other > >> RDBMS. If you can authenticate securely you can of course change > credentials in > >> a secure manner... You may not want to do that for simplicity and > performance > >> but I can't think of any additional security issues... It's not like you > simply > >> say "authenticate me as john_doe/password" and then simply "now I'm > informix, > >> thank you!" > >> > > > > Again, which *other* databases? > > Unlike DB2 and IDS, do the other databases authenticate against the > > OS? > > From what I've seen, both Oracle and MS Sql Server Authenticate the user > based on well established > Kerberos/NTLM Authentication methods. > > Neither of the above would ever allow a connection to be re-used by any > other user than the originator. Yes it will... You can define that a user account has special rights to "impersonate" another given certain conditions. That's how it can be done in a light way (no need to go through the authentication process for each request). As I wrote, both Oracle and DB2 allow that in some form. > > > I care little if it is actually changing the authentication on the fly or > if the connection pool is > authentication aware and blocks the connection re-use. the point is that > when developing a secure > system you would never want your credentials to be reused and therefore > written into DB logs by > anyone other than yourself. > > > > >> Although usually J2EE applications just use a connection pool with a > single > >> user, some customers would like to be able to do end-to-end identity > >> propagation. Specially if you're modernizing your application by > introducing > >> web services for example and you have old logging mechanisms on the > database > >> side based on the user ID. This is s situation customers may face if > they try > >> to use the new web services capabilities of newer versions of 4GL. > >> > > end to end identity propogation? > > The concept of end-to-end propagation of identity is fundamental in > developing a secure and robust > system. Most J2EE applications don't do it... The DB actions are sent by the application, and the security is built on the application. Most of them use the same user to connect to the DB. This user can be specifically created for that purpose, and should only have the need rights. > > > I have full traceability from when the user first "Hits" the web page, into > web service calls on > remote systems which connect into databases. Every system's audit logs > reference the user that > completed the action not some system account that makes tracing individuals > actions very difficult. You can achieve that using the DB2 trusted contexts for example. Informix also allows for user impersonation, but currently it's very limited and you must be DBA (SET SESSION AUTHORIZATION). This feature is not very useful in the context we're discussing > You really need to think about what you're saying. > > You really need to start thinking about security! I believe DB2 and Oracle engineers have thought about that, but as with any other feature it's up to the users to decided. If you don't feel comfortable with it... Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
> Date: Wed, 17 Jun 2009 13:09:14 +0100 > From: mc@community.nospam > Subject: Re: Connection Pooling with the .NET Provider on IIS > To: informix-list@iiug.org > > grendal wrote: > > On Jun 15, 6:16 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > >> Ian Michael Gumby wrote: > >>> With respect to Fernando's earlier post, you have to think about the > >>> security risks if you can change your connection details within the > >>> pool. Not a good idea. > >> I'd like a more elaborate response if you can. As I said, it's doable by other > >> RDBMS. If you can authenticate securely you can of course change credentials in > >> a secure manner... You may not want to do that for simplicity and performance > >> but I can't think of any additional security issues... It's not like you simply > >> say "authenticate me as john_doe/password" and then simply "now I'm informix, > >> thank you!" > >> > > > > Again, which *other* databases? > > Unlike DB2 and IDS, do the other databases authenticate against the > > OS? > > From what I've seen, both Oracle and MS Sql Server Authenticate the user based on well established > Kerberos/NTLM Authentication methods. > > Neither of the above would ever allow a connection to be re-used by any other user than the originator. > > I care little if it is actually changing the authentication on the fly or if the connection pool is > authentication aware and blocks the connection re-use. the point is that when developing a secure > system you would never want your credentials to be reused and therefore written into DB logs by > anyone other than yourself. > Sigh... IBM is going to hate me for saying this... Ok, look. two things... First: Normal authetication of DB2 and IDS is via the OS. (Normal == default method of authentication.) Normal authentication of Oracle, Sybase, SqlServer is to authenticate against the database. Alternative methods exist, because IDS can authenticate via PAM, you can use any method you want to develop provided that your OS supports PAM. (LDAP too but lets not go there because this is getting off the topic...) If you want to write a PAM module that will only authenticate you if your user name is Bruce, you can do that. Tough kibbles if your name happens to be 'Michael Baldwin'. (Its a Monty Python reference and if you don't get it, rule #1 applies) Second: Ok, so what you're really asking for is a connection pool where there really isn't a connection unless you can provide an authenticated user context. That is, you have separated the connection in to two layers a connection context that exists between the application pooling the connections and then an authenticated user context. The problem is that things like cursors and such which are currently associated with a connection, now need to be associated with a user context. So on the database server, when you authenticate a user, you create a user session context and then create an unique session id, cursors, etc ... (The connection uses your session information and not its underlying default permissions of the service user that started the connection.) When you as a user requests a connection from the connection pool, you get a connection. When you send a database request, you pass along your session id which the server then matches to a session context. So you now have your cursors etc ... and you can happily go on until you release the connection. Then the connection is returned to the pool and your user context goes dormant until your session times out or you release the session context. (Means you need to reauthenticate the next time you use the database.) Now when you touch the database, your user permissions are used and you're happy, right? Actually no. Now you need to modify your connection logic. So you have to rewrite your CSDKs, modify your java jdbc drivers, and then on the server side, you have a bit more work to do in handling your connections, along with some additional security checks ... The point is that this isn't a trival change. And for what? The work around is that when you write your application, you have a user authenticated at the application level against whatever your application want to use... (PAM, Kerberos, etc ...) and then when you store a record, you also store information about the authenticated user. Like user id and a timestamp. Thats a simple work around. So IBM has to decide if such a feature is economically feasible and if it poses any security issues that can't be easily solved. Of course since I've just outlined at a high level how you could implement this, it is now unpatentable because of prior art. (Ok so its taking user authentication from a web server and applying it to a database. Again prior art.) The point is that you *could* do this, however its not a trivial thing and means a lot of rework when there are work arounds that solve your problem and do not require any modifications to the existing database. Having said all of that, there is some merit to the idea. Since I am not currently an employee of IBM, nor under any NDA, I can actually speculate that if you were to look at revamping the authentication and connection model, it would be adventageous if you were revamping the entire framework of the database. This is something you could do with IDS since 10 and 11 were revamps of merging 7 and 9. (IDS and Illustra). The point would be if you were to logically follow IBM's 'Infosphere' concept, then you'd end up morphing the database and the web/app server together. Its a pandora's box. You have to be careful what you wish for. ;-) [Note a Relational Database Manager, but a Master Data Database Manager MDDM ] But hey! What do I know? I've just done a little thinking about this. Like I said, you've got a potential pandora's box... -G _________________________________________________________________ Insert movie times and more without leaving Hotmail®. http://windowslive.com/Tutorial/Hotmail/QuickAdd?ocid=TXT_TAGLM_WL_HM_Tutorial_QuickAdd_062009
On 17 June, 13:59, Fernando Nunes <domusonl...@gmail.com> wrote: > On Wed, Jun 17, 2009 at 1:09 PM, mc <m...@community.nospam> wrote: > > grendal wrote: > > > On Jun 15, 6:16 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > > >> Ian Michael Gumby wrote: > > >>> With respect to Fernando's earlier post, you have to think about the > > >>> security risks if you can change your connection details within the > > >>> pool. Not a good idea. > > >> I'd like a more elaborate response if you can. As I said, it's doable by > > other > > >> RDBMS. If you can authenticate securely you can of course change > > credentials in > > >> a secure manner... You may not want to do that for simplicity and > > performance > > >> but I can't think of any additional security issues... It's not like you > > simply > > >> say "authenticate me as john_doe/password" and then simply "now I'm > > informix, > > >> thank you!" > > > > Again, which *other* databases? > > > Unlike DB2 and IDS, do the other databases authenticate against the > > > OS? > > > From what I've seen, both Oracle and MS Sql Server Authenticate the user > > based on well established > > Kerberos/NTLM Authentication methods. > > > Neither of the above would ever allow a connection to be re-used by any > > other user than the originator. > > Yes it will... You can define that a user account has special rights to > "impersonate" another given certain conditions. That's how it can be done in > a light way (no need to go through the authentication process for each > request). > As I wrote, both Oracle and DB2 allow that in some form. You mean like http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.sqls.doc/ids_sqs_1189.htm SET SESSION AUTHORIZATION ??
david@smooth1.co.uk wrote: > On 17 June, 13:59, Fernando Nunes <domusonl...@gmail.com> wrote: >> On Wed, Jun 17, 2009 at 1:09 PM, mc <m...@community.nospam> wrote: >>> grendal wrote: >>>> On Jun 15, 6:16 pm, Fernando Nunes <domusonl...@gmail.com> wrote: >>>>> Ian Michael Gumby wrote: >>>>>> With respect to Fernando's earlier post, you have to think about the >>>>>> security risks if you can change your connection details within the >>>>>> pool. Not a good idea. >>>>> I'd like a more elaborate response if you can. As I said, it's doable by >>> other >>>>> RDBMS. If you can authenticate securely you can of course change >>> credentials in >>>>> a secure manner... You may not want to do that for simplicity and >>> performance >>>>> but I can't think of any additional security issues... It's not like you >>> simply >>>>> say "authenticate me as john_doe/password" and then simply "now I'm >>> informix, >>>>> thank you!" >>>> Again, which *other* databases? >>>> Unlike DB2 and IDS, do the other databases authenticate against the >>>> OS? >>> From what I've seen, both Oracle and MS Sql Server Authenticate the user >>> based on well established >>> Kerberos/NTLM Authentication methods. >>> Neither of the above would ever allow a connection to be re-used by any >>> other user than the originator. >> Yes it will... You can define that a user account has special rights to >> "impersonate" another given certain conditions. That's how it can be done in >> a light way (no need to go through the authentication process for each >> request). >> As I wrote, both Oracle and DB2 allow that in some form. > > You mean like > > http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=/com.ibm.sqls.doc/ids_sqs_1189.htm > > SET SESSION AUTHORIZATION > ?? > > > I've mentioned that in my post. It's been around since version 7, but is has some serious limitations. I personally don't think it could be used for this... Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...