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

January 5, 2009

Oracle OS Authentication

Filed under: [OS related topics] — Tags: — zhefeng @ 5:24 pm

Step1: create Operation system User account.
In this testing, we used NIS user called: guest_os
So it’s reachable for all the NIS memeber machines.

Step2: Make sure the os prefix in Oracle database was not null
#turn on local OS authentication
SQL> show parameter os_auth

NAME TYPE VALUE
———————————— ———– ——————————
os_authent_prefix string ops$

Step3: Make sure the remote OS authentication parameter was turned on
#turn on remote OS authenciation
sql>alter system set remote_os_authent=true scope=spfile;
SQL> show parameter remote_os

NAME TYPE VALUE
———————————— ———– ——————————
remote_os_authent boolean TRUE
remote_os_roles boolean FALSE

Step4: create a mapping user in Oracle database to map the OS username
sql>create user OPS$GUEST_OS identified externally default tablespace users temporary tablespace temp;
sql>grant connect to OPS$GUEST_OS;

Step5: testing on another linux client machine
bash-3.00$ id
uid=63371(guest_os) gid=1000(rd) groups=1000(rd)
bash-3.00$ sqlplus /@fun11u03

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jan 5 16:19:56 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Note: if you are configure a windows user account for remote OS auth, then then mapped username in Oracle should be like this:
“OPS$domainname\username” and in sqlnet.ora has to have this line: SQLNET.AUTHENTICATION_SERVICES= (NTS)

Reference:
1. http://www.dba-oracle.com/security/local_os_authentication.htm
2. http://www.oracle-base.com/articles/misc/OsAuthentication.php

Create a free website or blog at WordPress.com.