[原创]关于系统和数据库ID的一些事

现象

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