DB2 connect - user id question

[Wonderinguy]

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

[PM \(pm3iinc-nospam\)]

Re: DB2 connect - user id question

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&lr=&ie=ISO-8859-1&q=APPLNAME&meta=group%3Dcomp.databases.ibm-db2

latest db2 udb v8.x luw doc here:

http://publib.boulder.ibm.com/infocenter/db2help/index.jsp

PM


"Wonderingu y" <wonderinguy@ho tmail.com> a écrit dans le message de
news:6950e82.03 12021257.2fa75a 71@posting.goog le.com...[color=blue]
> 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[/color]

[Joseph]

Re: DB2 connect - user id question

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

wonderinguy@hot mail.com (Wonderinguy) writes:
[color=blue]
>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 ?[/color]
[color=blue]
>TIA[/color]

[Mark A]

Re: DB2 connect - user id question

> I don't know[color=blue]
> if DB2 on z/OS supports agent pooling.
>[/color]
I believe that access to DB2 z/OS requires DB2 Connect, which has connection
pooling.

[Mark Yudkin]

Re: DB2 connect - user id question

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.

"Wonderingu y" <wonderinguy@ho tmail.com> wrote in message
news:6950e82.03 12021257.2fa75a 71@posting.goog le.com...[color=blue]
> 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[/color]