Knut Stolze, IBM 信息集成部, IBM 德国
Paul Yip, DB2 合作伙伴支持部, IBM 多伦多实验室
2003 年 8 月 一切终于大白于天下！获取应用程序标识并将之用于诸如测试连接这样的目的，以及用于连接合用体系结构中的审计比您所认为的简单。还包含了样本二进制。
本文是为 IBM® DB2® Universal Database™ v8.1 for Linux、UNIX® 和 Windows® 而编写的
重要：在阅读本文之前请先阅读 免责声明。
简介
客户常常问我们 DB2 中是否有任何会话或应用程序标识的概念，如果有，如何访问它。应用程序可以使用这一信息确定连接的状态，或用于用户修改敏感数据时的审计目的。
唔，有好消息，也有坏消息。好消息是每个至数据库的连接确实都有一个应用程序标识。坏消息是 DB2 不提供轻松检索这个标识的内置 SQL 函数 － 您将需要自己编写一个。编写或构建该函数不难，而且本文的 第 1 部分将向您展示如何快速地这么做。本文的 第 2 部分针对如何使用应用程序标识讨论了一些样本方案。
第 1 部分. 构建一个函数来获取连接的应用程序标识
要构建这个函数，需要安装 DB2 应用程序开发客户机（DB2 Application Development Client）和 C 或 Java 编译器。（如果没有 C 编译器，请参阅 安装二进制文件）。以数据库实例所有者身份（例如， db2inst1）在服务器本地发出所有命令。
首先，该函数从 DB2 检索到的信息称为应用程序标识。检索到的应用程序标识就是您从 DB2 命令行处理器（Command Line Processor，CLP）发出 LIST APPLICATIONS 时所看到的值：
清单 1. CLP 上 LIST APPLICATIONS 的输出
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- ------------ ------ ------------------------ -------- -----
DB2ADMIN db2bp.exe 5 *LOCAL.DB2.00BE85034416 SAMPLE 1
|
清单 1 中，第四列表示连接的应用程序标识是 *LOCAL.DB2.00BE85034416 。对于远程连接， *LOCAL 就被替换成客户机机器 IP 地址的十六进制表示。
C 函数
以下 C 函数（ 清单 2中）将允许我们检索连接的应用程序标识。
清单 2. 检索连接的应用程序标识的 C 函数
#include <string.h>
#include <sqludf.h>
void SQL_API_FN getApplicationId(
SQLUDF_CHAR *applId, SQLUDF_NULLIND *applId_ind,
SQLUDF_TRAIL_ARGS, SQLUDF_DBINFO *dbinfo)
{
strncpy(applId, dbinfo->appl_id, 128);
*applId_ind = 0;
}
|
构建 C 函数
要构建这个函数，需要一个定义库入口点的导出文件。该导出文件根据您平台的不同将有所不同：
在放置 C 源代码文件（ application_id.c ）的目录中创建了这两个文件中的一个（取决于您的平台）后，编译该代码并进行链接。DB2 提供了一个构建例程脚本（ bldrtn ）来简化这个过程。该脚本位于 sqllib/samples/c 目录。
DB2 V7：对于 DB2 UDB 7.x 版本，在 UNIX 上请使用脚本 bldudf ，在 Windows 上请使用 bldmudf 。其调用语法与 V8 中的稍有不同。请查看这些文件，以了解有关如何运行这些构建脚本的细节。
要构建用户定义的函数（UDF），请执行：
INSTHOME
/sqllib/samples/c/bldrtn application_id
|
其中， INSTHOME 是实例主目录的路径。例如：
c:\\program files\\ibm\\sqllib\\samples\\c\\bldrtn application_id
或
/home/db2inst1/sqllib/samples/c/bldrtn application_id
|
该脚本将完成两件事：
- 编译 UDF 并将其链接到共享库。
- 将产生的共享库（在 UNIX 系统上名为
application_id ，而在 Windows 系统上名为 application_id.dll ）复制到 sqllib/function 目录
Java 函数
通过使用 Java 编程语言可以实现上述 C 函数所提供的相同功能，如 清单 4所示。该函数的代码同样很简短。
清单 4. 检索连接的应用程序标识的 Java 函数
import java.sql.*;
import COM.ibm.db2.app.*;
public class appl_id extends UDF
{
public void getApplicationId(String result) throws Exception
{
try {
// set the output parameter based on DBINFO
set(1, getDBapplid());
}
catch (Exception e) {
setSQLstate("38XXX");
if (e.getMessage().length() > 0) {
setSQLmessage("Exception '" + e.getMessage() +
"' encountered.");
}
else {
setSQLmessage("Exception '" + e.toString() +
"' encountered.");
}
}
}
}
|
构建 Java 函数
编译这样简短的 Java 函数很容易完成。使用 Java 编译器 javac 将源文件转换成类文件中的 Java 字节码，然后将该产生的类文件复制到 sqllib/function 目录，如 清单 5所示。请注意，“cp”通常是 UNIX 的复制文件命令，对于 Windows 系统，应该使用“copy”。
清单 5. 编译并安装 Java 函数
javac appl_id.java
cp appl_id.class INSTHOME/sqllib/function
|
安装二进制文件
为了更进一步简化该过程，可以使用从 C 代码预构建的库或由 Java 代码所生成的类文件。
从 C 代码构建的库都命名为 application_id （在 Windows 系统上则是 application_id.dll ）。根据您数据库服务器平台的不同，您应该将这个库从可下载压缩文件中各自所在的目录复制到 DB2 实例的 sqllib/function 目录。请注意，32 位和 64 位实例的二进制文件有所不同。
从 Java 代码构造而来的类文件命名为 appl_id.class ，可以在该压缩文件的“Java”子目录中找到它。
您可以在 这里找到带有预构建库的压缩文件。
注册函数
最后一步是发出 CREATE FUNCTION语句以在您的数据库中注册该外部函数。根据使用的是 C 代码还是 Java 代码，您必须使用不同的 CREATE FUNCTION 语句。 清单 6a显示了用 C 实现的 UDF 语句，而清单 6b 是 Java 函数的语句。用您的数据库名称来替换 DBNAME ：
清单 6a. 注册 C 函数
db2 -td$
CONNECT TO
DBNAME
$
CREATE FUNCTION application_id()
RETURNS VARCHAR(128)
SPECIFIC applId EXTERNAL NAME 'application_id!getApplicationId'
NOT FENCED LANGUAGE C PARAMETER STYLE SQL DETERMINISTIC
NO SQL NO EXTERNAL ACTION ALLOW PARALLEL DBINFO $
|
清单 6b. 注册 Java 函数
db2 -td$
CONNECT TO
DBNAME
$
CREATE FUNCTION application_id()
RETURNS VARCHAR(128)
SPECIFIC applId EXTERNAL NAME 'appl_id.getApplicationId'
NOT FENCED LANGUAGE JAVA PARAMETER STYLE DB2GENERAL
DETERMINISTIC
NO SQL NO EXTERNAL ACTION ALLOW PARALLEL DBINFO $
|
在上面的示例中，我们已经将命令行上的缺省定界符更改成了 $（通过使用 db2 -td$ ），它让我们方便地输入多行命令。要表示命令结束，请输入 $，随后按下 Enter键。如果您从 DB2 控制中心（DB2 Control Center）执行 CREATE FUNCTION 语句，那么可以在“Tools Settings”菜单中更改语句终止符。
在您连接时，测试您的函数：
SELECT application_id() AS appid FROM SYSIBM.SYSDUMMY1 $
APPID
----------------------------------
*LOCAL.DB2.00B045155621
1 record(s) selected.
|
如果您看到的输出与 清单 1中的相同，那么您就成功了！
第 2 部分. 应用程序标识使用方案
在 第 1 部分中，我们向您展示了如何构建检索连接的应用程序标识的函数。现在，我们描述如何使用应用程序标识解决实际问题。我们演示了两个方案：
确定是否存在连接
自 V8.1 开始，DB2 通用数据库提供了一套表函数，它可以让您通过 SQL 来访问 DB2 快照监视器数据。
函数 SNAPSHOT_APPL_INFO() 返回所有当前连接着的应用程序的信息，包括它们的应用程序标识。使用该信息，我们可以确定给定应用程序标识是否属于现有的连接。以下 SELECT 语句使用该快照函数返回所有至数据库的现有连接的应用程序标识。请注意，表函数返回了更多信息，但本文不作讨论。请参考 SQL 参考大全中该函数的文档，以获取更多细节。
SELECT appl_id
FROM TABLE (SNAPSHOT_APPL_INFO(CURRENT SERVER, -2)) AS t
|
如果存在两个连接，则输出看上去可能象这样：
APPL_ID
--------------------------------
*LOCAL.stolze.0AFDD4122938
*LOCAL.stolze.0AD7F4121810
2 record(s) selected.
|
从 DB2 监视器访问所有特定于应用程序的信息强制了某些限制。出于安全性原因，缺省情况下只有拥有 SYSADM、SYSMAINT 或 SYSCTRL 特权的用户才可以访问监视器。对于访问应用程序标识的常规方法（每个用户都可能需要用这种方法来验证是否存在某一连接），这局限性太大了。为了允许对快照函数进行无限制的访问，可以使用 DB2_SNAPSHOT_NOAUTH 注册表变量。如果执行以下命令，则在访问其中一个快照函数时，DB2 不会对当前用户的特权进行验证：
db2set DB2_SNAPSHOT_NOAUTH=on
|
请注意，现在任何用户都可以使用 SNAPSHOT_APPL_INFO（及相关的）函数并处理其结果。
让我们在实际示例中验证一下这一特性。以下的这个例子中未设置 DB2_SNAPSHOT_NOAUTH 注册表变量。USER3 是一个无特权用户。
$ db2set DB2_SNAPSHOT_NOAUTH
DBI1303W Variable not set.
$ db2 "connect to test user USER3 using somepwd"
$ db2 "SELECT appl_id FROM TABLE ( SNAPSHOT_APPL_INFO( CURRENT
SERVER, -2 ) ) AS t"
APPL_ID
--------------------------------
SQL0443N Routine "*PPL_INFO" (specific name "") has returned an
error
SQLSTATE with diagnostic text "SQL1092 Reason code or token: USER3
, ". SQLSTATE=3855
|
就如可从错误信息 SQL1092 得出的，USER3 没有访问监视器函数的必备特权，因此，不能检索当前连接及其应用程序标识的列表。
现在，实例 SYSADM 用户开启对快照函数有较少限制的权限需求，并重新启动 DB2。此后，USER3 就能够使用函数 SNAPSHOT_APPL_INFO，而不会有任何进一步的限制了。
SYSADM 用户执行：
$ db2set DB2_SNAPSHOT_NOAUTH=on
$ db2stop force
SQL1064N DB2STOP processing was successful.
$ db2start
SQL1063N DB2START processing was successful.
|
在正确设置了 DB2_SNAPSHOT_NOAUTH 之后，USER3 执行以下语句：
$ db2 connect to test
Database Connection Information
Database server = DB2/6000 8.1.0
SQL authorization ID = USER3
Local database alias = TEST
$ db2 "SELECT appl_id FROM TABLE ( SNAPSHOT_APPL_INFO
(CURRENT SERVER, -2 ) ) AS t"
APPL_ID
--------------------------------
*LOCAL.stolze.095A34142226
1 record(s) selected.
|
现在，任何用户都可以使用常规的 SQL 来使用来自函数 SNAPSHOT_APPL_INFO 以及我们的用户定义的函数 application_id() 的结果以验证是否仍然存在连接。
请考虑这样一个方案：不管出于何种原因，您的应用程序必须维护其自己给对象上的锁。让我们实现以下逻辑：
- 每当用户连接至数据库时，该用户就获取对当前数据的共享锁。（这是特定于应用程序的锁，而不是 DB2 锁。）在该用户断开与数据库的连接之前，该用户一直保留该锁。
- 批处理按常规执行，但必须先确保要处理的对象上不存在任何其它的共享锁（除了其自己的以外）。
该逻辑非常简单，且不会强加任何困难，只有一种情况除外：
如果用户连接到了数据库，获取了共享锁，然后连接异常终止了，那会怎么样呢？或许是网络中断了，可能是断电了，也可能是用户没有正确关闭客户机应用程序，而只是杀死了它。在这样的情况中，我们的共享锁仍存储在数据库某处，但是它不再有效，只是留下垃圾数据。
在这种情况下使用应用程序标识可以让我们轻松实现检查给定共享锁是否仍有效的方法。当用户在连接时请求共享锁时，我们也使用我们的 UDF application_id() 检索相关的应用程序标识。稍后当执行批处理时，我们验证所有与共享锁相关的应用程序标识是否仍有效。如果无效，那么无效锁会被安全地忽略，因为连接不再存在。
下表演示了一个表模式，它可以用来以所有用户都可以访问的方式存储共享锁。
CREATE TABLE shared_locks (
USER_NAME VARCHAR(18) NOT NULL,
APPL_ID VARCHAR(128) NOT NULL,
OBJECT INTEGER )
|
该表中的每一行都显示了哪个用户当前正在访问数据。当打开新连接时，客户机应用程序的第一个操作是以下 SQL 语句：
INSERT
INTO shared_locks(user_name, appl_id, object)
VALUES ( USER, application_id(), ?)
|
当批处理要处理对象 X 时，我们先检查对它是否有任何锁，接着确保验证了该锁的每个应用程序标识。以下组合 SQL 语句返回针对对象 X 数据的任何其余有效锁。所有无效锁均被忽略。
SELECT appl_id
FROM shared_locks AS l,
TABLE (SNAPSHOT_APPL_INFO(CURRENT SERVER, -2)) AS t
WHERE t.appl_id = l.appl_id AND
l.object = objectX;
|
另外，DELETE 语句可以用来删除所有分配给不再存在的连接的锁。
DELETE
FROM shared_locks
WHERE appl_id NOT IN
( SELECT t.appl_id
FROM TABLE ( SNAPSHOT_APPL_INFO(
CURRENT SERVER, -2) ) AS t )
|
删除这些锁之后，您就知道在下一个连接意外断开之前，shared_locks 表中的所有锁都是有效的。
在连接池体系结构中创建审计跟踪
一种对数据更改强制实施审计跟踪的简单方法是通过对包含敏感信息的表定义 INSERT、UPDATE 和 DELETE 触发器。跟踪中可能包含的信息有：
- 旧值，如果适用的话
- 新值，如果适用的话
- 与 INSERT、UPDATE 或 DELETE 操作相关联的连接的用户标识
但是，在现代连接池体系结构中，所有应用程序都使用一个公共中间层用户标识来访问数据；该应用程序（一般）在数据库表中维护其自己的有关应用程序用户的信息。换句话说，用户“Sally”和“John”可能是应用程序用户标识，但在内部，这些用户都使用定义在中间层的用户标识 AppUser 来连接到数据库。为了便于讨论，让我们定义两个术语，它们显示在 图 1中：
- 应用程序用户。由应用程序定义和维护的用户。
- 数据库用户。在应用程序服务器上定义的代表应用程序用户连接至数据库的用户。
图 1. 三层体系结构中的用户
因为池中的连接与数据库没有真正断开，所以随着时间的推移，使用特定数据库用户的连接很可能会由许多应用程序用户共享。因此，要维护正确的审计跟踪，只用应用程序标识（或 DB2 专用寄存器 USER 上的值）就不够了。
要解决这个问题，我们可以结合使用 application_id() 和应用程序用户标识。让我们定义下面的表 LOGIN 以使连接的应用程序标识和应用程序用户标识暂时关联。
CREATE TABLE LOGIN (
appl_id VARCHAR(128) not null,
user_id VARCHAR(128) not null,
PRIMARY KEY (appl_id, user_id))
|
该方法的工作原理如下：
- 当应用程序用户登录到应用程序时，应用程序自动将一行数据插入（INSERT）到 LOGIN 表中，这样就使数据库连接的应用程序标识（通过使用 UDF application_id()）和应用程序用户标识关联。现在，在用户从应用程序注销之前，这对值就唯一地标识用户连接。例如：
INSERT INTO LOGIN VALUE ('*LOCAL.DB2.00BE85034416', 'SALLY')
|
或
INSERT INTO LOGIN VALUE (application_id(), 'JOHN')
|
- 如果出现用户对敏感数据执行 INSERT、UPDATE 或 DELETE，那么该表的触发器就激活，并在审计跟踪中记录日志。审计跟踪是一个包含两个步骤的过程：
- 触发器首先调用 application_id() 函数以获取执行 SQL 操作的连接的应用程序标识。
- 接着，触发器对 LOGIN 表执行查询以使应用程序标识与实际的应用程序用户相匹配，然后编写必要的审计信息。
- 当用户从应用程序注销时，应用程序就删除 LOGIN 表中的相关项。
以下代码演示了这个方法。请注意，只演示了 INSERT 触发器，但 UPDATE 和 DELETE 触发器与之类似。有关触发器的更多信息，请参阅 如何在 DB2 通用数据库中暂时禁用触发器一文。
db2 -td$
CONNECT TO
DBNAME
$
CREATE TABLE LOGIN (
appl_id VARCHAR(128) not null,
user_id VARCHAR(128) not null,
PRIMARY KEY (appl_id, user_id)) $
CREATE TABLE t1 (c1 INT) $
CREATE TABLE audit (c1 varchar(100)) $
CREATE TRIGGER auditT1
AFTER INSERT ON t1
REFERENCING NEW AS newrow
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
DECLARE v_user VARCHAR(128);
SET v_user = (
SELECT user_id FROM login L
WHERE L.appl_id=application_id());
-- note: v_user will be null if application ID not found.
INSERT INTO audit VALUES (
'The value: ' || char(newrow.c1) ||
' was inserted by ' || COALESCE(v_user, 'unknown!'));
END$
|
要测试该触发器，您可以发出：
-- case where insert performed as non-registered user
insert into t1 values (0) $
-- case where insert performed by application user
insert into login values (application_id(), 'John') $
insert into t1 values (1),(2),(3) $
select * from audit $
|
结果：
-----------------------------------------------
The value: 0 was inserted by unknown!
The value: 1 was inserted by John
The value: 2 was inserted by John
The value: 3 was inserted by John
|
结束语
本文中，我们讨论了每个至数据库的连接如何拥有唯一的应用程序标识。接着我们向您展示了如何创建并测试名为 application_id() 的函数来轻松检索这一信息。我们还提供了两个常见的方案，其中应用程序标识可以用于解决实际的数据库应用程序问题。 |
