473,320 Members | 1,930 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

DB2 connect - user id question

Our websphere application uses a generic application userid to connect
and query db2 on z/os via DB2 connect. The end user,logs in to the
application using
his regular userid, which is then authenticated with the mainframe and
if its ok, then the application proceeds using the generic application
id. We are not
able to track the user in the DB2 on Mainframe.
Is there anyway I can pass the end userid along with the generic
userid, so I can know who is doing what ?

TIA
Nov 12 '05 #1
4 6022
Maybe you can use v8 registers like

CLIENT APPLNAME :

CLIENT USERID :

CLIENT WRKSTNNAME :

Have a look around these past threads :
http://groups.google.ca/groups?hl=en...abases.ibm-db2

latest db2 udb v8.x luw doc here:
http://publib.boulder.ibm.com/infoce...help/index.jsp

PM
"Wonderinguy" <wo*********@hotmail.com> a écrit dans le message de
news:69*************************@posting.google.co m...
Our websphere application uses a generic application userid to connect
and query db2 on z/os via DB2 connect. The end user,logs in to the
application using
his regular userid, which is then authenticated with the mainframe and
if its ok, then the application proceeds using the generic application
id. We are not
able to track the user in the DB2 on Mainframe.
Is there anyway I can pass the end userid along with the generic
userid, so I can know who is doing what ?

TIA

Nov 12 '05 #2
This is a fundamental problem with 3-tier architectures that use
connection pooling. For web usage scalability, most web site designers
feel that the overhead of establishing a database connection is too
severe, so connections are left up in a shared pool. A websphere
task is assigned a connection out of the pool to do a particular
task, and then releases the connection back to the pool. The
downside of this is that it defeats the database security mechanisms,
so you only grant a set of privileges to the websphere connection
account. Moreover, this password is stored on the application server
machine, so you have to secure this machine from general access, perhaps
only giving permission to a small number of administrators to login to it.

If you don't need scalability to 10's of thousands of very small
transactions, but instead are using websphere to develop an OLTP
application, then you could turn off connection pooling on websphere
so that the user is authenticated for each connection. DB2 on LUW
(you may need v. 8 though) supports agent pooling which accomplishes
scalability of requests, but on "the other side of the fence" ie within
the DBMS where the DBMS can keep a connection open across many allocations
of an agent to it, and hence use user authentication. This should provide
more than adequate scalability for most OLTP applications. I don't know
if DB2 on z/OS supports agent pooling.

If you need scalability for web site workloads and don't want to fully
defeat database security mechanisms, there are two ways you can proceed.
One possibility would be to have multiple connection pools, each with a
different account for making database connections. That is, each connection
pool will have exactly one such account, but different connection pools will
have different accounts. Then, each of these accounts will correspond to
an application role, and can be given a set of database privileges
appropriate for the role. For instance, you might have five roles in an
application: role1 might be read-only for most but not all tables, excluding
some sensitive information; role2 might be the same as role1 but also
include insert privileges on the same set of tables; role3 might be
read-only for all tables; role4 might be select and insert on all tables;
and role5 might be select/insert/update/delete on all tables.

In such a model you would have 5 connection pools, each with their own
account. Each account would be granted the privileges associated with the
role. The application server, in this case websphere, would ensure that
users may only login to the roles they are allowed to assume.

The other possibility would be to use Java type 4 JDBC drivers with
certificates enabled. These drivers are not very mature, the folks I know
who have tried to use them have not had good success in getting them to
work, but I would expect they would stabilize in the next several months.
You need Kerberos or some authentication server supported both by the
JDBC drivers and DB2 with this technique. The idea is that when a user
authenticates, they are given an unforgeable certificate that assures their
identity to a software system. With a type 4 JDBC driver you can
associate one of these certificates with a database request, and the DBMS
can then have the connection process requests under the identity certified
by the certificate until the connection is freed and a new assignment of
the connection to a task results in a different certificate being
transmitted. I have not investigated how far along IBM is with DB2 in
terms of supporting this-- it is not in common usage today, but should be
in the next 6-12 months.

I hope that helps.

Joseph

wo*********@hotmail.com (Wonderinguy) writes:
Our websphere application uses a generic application userid to connect
and query db2 on z/os via DB2 connect. The end user,logs in to the
application using
his regular userid, which is then authenticated with the mainframe and
if its ok, then the application proceeds using the generic application
id. We are not
able to track the user in the DB2 on Mainframe.
Is there anyway I can pass the end userid along with the generic
userid, so I can know who is doing what ? TIA

Nov 12 '05 #3
> I don't know
if DB2 on z/OS supports agent pooling.

I believe that access to DB2 z/OS requires DB2 Connect, which has connection
pooling.
Nov 12 '05 #4
The problem is your design that "uses a generic application userid to
connect". Why don't you just use the real userid? Unlike some competing
databases, DB2 has almost no performance hit doing end-to-end security.

"Wonderinguy" <wo*********@hotmail.com> wrote in message
news:69*************************@posting.google.co m...
Our websphere application uses a generic application userid to connect
and query db2 on z/os via DB2 connect. The end user,logs in to the
application using
his regular userid, which is then authenticated with the mainframe and
if its ok, then the application proceeds using the generic application
id. We are not
able to track the user in the DB2 on Mainframe.
Is there anyway I can pass the end userid along with the generic
userid, so I can know who is doing what ?

TIA

Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

22
by: James Kupernik | last post by:
Hello everyone! I'm hoping someone can shed some light on my ever daunting mysql/php problem. I'm currently trying to get a new server up and running using apache/php/mysql. Everything runs...
10
by: Ed Stevens | last post by:
A tale of woe, and a question . . . Last week my boss said to me "we've installed DB2 Connect on this Solaris box. Make it work." Now, I've barely seen DB2 Connect on Windows, having fumbled...
4
by: Terry Miller | last post by:
I have DB2 V8.2 loaded on a Redhat Enterprise Linux version 3 (RHEL 3) box. RHEL is configured to talk to LDAP for authenticaton besides the local passwd file. I can verify this by executing an su...
3
by: asemeiks | last post by:
I'm using Access 97, Jet 4.0. the data resides on a Win 2000 domain server. Using .Net 1.1 and IIS 5.0 on a local XPPro computer I am trying connect to a Jet database on the server. If the data...
5
by: news.telia.net | last post by:
Hi! I have a question. I have installed php and mysql on an apache-server on windows and I can't connect to the server. I tried to create a database (since I am trying to learn howto). My...
3
by: roxrox_1959 | last post by:
I'm attempting to connect to a DB2 database using DBD::DB2 running on a Linux device. I'm getting the following error: ** connect: Unable to get database handle: DBI...
3
by: =?Utf-8?B?ZGF2aWQ=?= | last post by:
Last week I asked a question about connection to database from client machine (developer machine). I have changed the database security setup for "SQL Server and Windows" under (local)Windows NT...
3
by: =?Utf-8?B?QWxpY2UgV2Vp?= | last post by:
Hi, I am not sure if this is the right place for me to post this question. I have a VB.NET program written up for a user-interface, and I wanted to connect this to a PHP script to allow back end...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.