DBA Sensation

January 6, 2009

DB2 OS authentication

Filed under: 2. IBM DB2 — Tags: — zhefeng @ 1:58 pm

DB2 natively supports OS user authentication, unlike Oracle external users you don’t need to create mapping user inside the database.
However, if you want to pass the authentication without provide username and password (some called single signon), you still need to do sth at db2 instance level.
Here are steps how to do:

Step0: have a OS user, in our case we setup a NIS user “guest” then it’s reachable from any of the server within NIS range.
check the current authentication mode:
[db295@vanpgrepdb02 sqllib]$ db2 get dbm cfg|grep -i auth
GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
Database manager authentication (AUTHENTICATION) = SERVER
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Bypass federated authentication (FED_NOAUTH) = NO

Step1: switch the authentication mode to “client”
By default after you installed a new db2 database, the authentication mode for instance is “server” which means authentication occurs on the server using local operating system security.
In this mode, for local connections, a user ID and password are not required for authentication; for remote connections, a user ID and password(belongs to local server) must be provided for authentication.
By using this command we switch the authencation mode(instance owner login):
[db295@vanpgrepdb02 sqllib]$ db2 update dbm cfg using authentication client immediate

Step2: check the auth mode again:
[db295@vanpgrepdb02 sqllib]$ db2 get dbm cfg|grep -i auth
GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
Database manager authentication (AUTHENTICATION) = CLIENT
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Bypass federated authentication (FED_NOAUTH) = NO

Step3: test the sso:
$ id
uid=100(guest) gid=100(usr)
$ db2 connect to fun95u02

Database Connection Information

Database server = DB2/LINUXX8664 9.5.0
SQL authorization ID = GUEST
Local database alias = FUN95U02

Step4: test the traditional login
$ db2 connect to fun95u02 user guest using password
SQL30082N Security processing failed with reason “42” (“ROOT CAPABILITY
REQUIRED”). SQLSTATE=08001

Here is the tricky part, after you enable the client authentication, the traditional login mode (user xxx using xxxx) doesn’t work. In order to make it work, you also need to turn trusted client authentication from default “client” to “server” (note: only when authentication=server, this parameter works)
[db295@vanpgrepdb02 sqllib]$ db2 update dbm cfg using trust_clntauth server immediate

Step5: test both methods
$id
uid=100(guest) gid=100(usr)

$ db2 connect to fun95u02

Database Connection Information

Database server = DB2/LINUXX8664 9.5.0
SQL authorization ID = GUEST
Local database alias = FUN95U02

$ db2 connect to fun95u02 user guest using password

Database Connection Information

Database server = DB2/LINUXX8664 9.5.0
SQL authorization ID = GUEST
Local database alias = FUN95U02

Done! now both of them are working. For more information about parameter “authentication”, “trust_clntauth”,”trust_allclnts”, refer to the reference doc below.

Reference:
1. “Authentication methods for your server” http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.doc/doc/c0005598.htm

Advertisements

Create a free website or blog at WordPress.com.