DB2数据库比较常被客户投诉ID连接不了数据库,碰到SQL30082N,怎么去确认id的密码问题?
现象
DB2数据库比较常被客户投诉ID连接不了数据库,碰到SQL30082N,怎么去确认id的密码问题?
SQL30082N错误现象:
db2inst1:/dbhome/db2inst1$ db2 connect to SAMPLE user db2inst1 using 12345678
SQL30082N Security processing failed with reason "24" ("USERNAME AND/OR
PASSWORD INVALID"). SQLSTATE=08001
db2inst1:/dbhome/db2inst1$
查看ID的登录记录,特别是unsuccessful_login_count
db2inst2@bb10drdb001i:/home/db2inst2$sudo lsuser db2inst1
~省略~
unsuccessful_login_count=100 roles=
db2inst2@bb10drdb001i:/home/db2inst2$
很明显:100次没有登录成功
unsuccessful_login_count=100
连接密码错误
判断ID密码什么时候修改的。(一般大企业是90天需要更新密码)
db2inst2@bb10drdb001i:/home/db2inst2$sudo grep -ip db2inst1 /etc/security/passwd
db2inst1:
password = {smd5}/4cqVEdW$teNTMCjkvaFp4ERjpkNgR1
lastupdate = 1460106888
db2inst2@bb10drdb001i:/home/db2inst2$TIMEPOINT=1460106888
db2inst2@bb10drdb001i:/home/db2inst2$perl -le 'print scalar localtime(shift);' $TIMEPOINT
Fri Apr 8 18:14:48 2016
db2inst2@bb10drdb001i:/home/db2inst2$
查看ID上一次登录情况
可以通过/etc/security/lastlog的内容来查看id的上一次连接记录。time_last_login/time_last_unsuccessful_login的时间转换可以参考之后[重置修改密码时间]的命令去转换
$ls -ltr /etc/security/lastlog
-rw-r----- 1 root security 20449 May 06 16:39 /etc/security/lastlog
$sudo grep -ip db2inst1 /etc/security/lastlog
db2inst1:
unsuccessful_login_count = 0
time_last_login = 1443076616
tty_last_login = /dev/pts/0
host_last_login = 192.168.1.121
time_last_unsuccessful_login = 1441070600
tty_last_unsuccessful_login = ssh
host_last_unsuccessful_login = 10.0.1.11
$
查看数据库里id的权限(connect)
db2 “select * from syscat.dbauth” | tr -s “ “ |
$ db2 "select * from syscat.dbauth" | tr -s " "
GRANTOR GRANTORTYPE GRANTEE GRANTEETYPE BINDADDAUTH CONNECTAUTH CREATETABAUTH DBADMAUTH EXTERNALROUTINEAUTH IMPLSCHEMAAUTH LOADAUTH NOFENCEAUTH QUIESCECONNECTAUTH LIBRARYADMAUTH SECURITYADMAUTH SQLADMAUTH WLMADMAUTH EXPLAINAUTH DATAACCESSAUTH ACCESSCTRLAUTH CREATESECUREAUTH
-------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------- --------- ------------------- -------------- -------- ----------- ------------------ -------------- --------------- ---------- ---------- ----------- -------------- -------------- ----------------
SYSIBM S DB2INST2 U Y Y Y Y Y Y Y Y Y N Y N N N Y Y N
DB2INST2 U WEBSPHR U Y Y Y Y Y Y Y Y Y N N N N N Y Y N
DB2INST2 U B123310 U Y Y Y Y Y Y Y Y Y N N N N N Y Y N
BB101101 U BB20151 U N Y N N N N N N N N N N N N N N N
BB101101 U ONETEAM U N Y N N N N N N N N N N N N N N N
DB2INST2 U C123310 U Y Y Y Y Y Y Y Y Y N N N N N Y Y N
BB101101 U DD201603 U N Y N N N N N N N N N N N N N N N
BB101101 U BB201201 U N Y N N N N N N N N N N N N N N N
SYSIBM S DB2SYSA G N N N Y N N N N N N N N N N Y Y N
DB2INST2 U BBC65281 U N N N Y N N N N N N N N N N Y Y N
查看CONNECTAUTH等权限
系统一般设置6次没有成功被lock,需要reset
sudo smitty user
->
Reset User's Failed Login Count
-> 需要reset的id
F10 退出
不变更密码,重置变更密码时间方法
如重置修改密码时间 db2inst1
grep -ip db2inst1 /etc/security/passwd
db2inst1:
password = po9zWVpue5RWk
lastupdate = ****
当前时间赋值给TIMEPOINT
TIMEPOINT=`perl -le "print scalar time" 2>/dev/null`
echo $TIMEPOINT
pwdadm -c db2inst1
更新修改密码时间
chsec -f /etc/security/passwd -s db2inst1 -a lastupdate=$TIMEPOINT
确认时间
grep -ip db2inst1 /etc/security/passwd
db2inst1:
password = po9zWVpue5RWk
lastupdate = ****
TIMEPOINT=上面lastupdate值
perl -le 'print scalar localtime(shift);' $TIMEPOINT