Oracle不知道用户密码情况下，如何在不更改密码的前提下解锁用户或者延期密码有效期
1.问题描述：
生产环境，zabbix告警业务用户密码即将过期，但是如何不知道业务用户密码的情况下来解决该问题？
2.实验一：
1）创建新的用户test，并授予test resource角色和connect权限，并测试连接test用户
SYS@PROD4 >create user test identified by test;
User created.
SYS@PROD4 >grant resource to test;
Grant succeeded.
SYS@PROD4 >grant connect to test;
Grant succeeded.
SYS@PROD4 >conn test/test;
Connected.
2）查看test用户的状态（open），
TEST@PROD4 >conn / as sysdba
Connected.
SYS@PROD4 >set linesize 200 pagesize 200
SYS@PROD4 >col username for a10
SYS@PROD4 >select username,password,account_status,lock_date,expiry_date,created,password_versions from dba_users where username='SCOTT';
USERNAME PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA CREATED PASSWORD
---------- --- --------------------------- -------------------------------- --------- --------- --------- --------
TEST OPEN 20-AUG-20 22-FEB-20 10G 11G
3）锁定test用户，并验证
SYS@PROD4 >alter user test account lock;
User altered.
SYS@PROD4 >select username,password,account_status,lock_date,expiry_date,created,password_versions from dba_users where username='TEST';
USERNAME PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA CREATED PASSWORD
---------- ------------------------------ -------------------------------- --------- --------- --------- --------
TEST LOCKED 22-FEB-20 20-AUG-20 22-FEB-20 10G 11G
SYS@PROD4 >conn test/test;
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
@ >conn / as sysdba
Connected.
4）查看ora 28000报错
SYS@PROD4 >!oerr ora 28000
28000, 00000, "the account is locked"
// *Cause: The user has entered wrong password consequently for maximum
// number of times specified by the user's profile parameter
// FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
// *Action: Wait for PASSWORD_LOCK_TIME or contact DBA
5）查看test用户的密码在user$中的hash值
SYS@PROD4 >select name,password from user$ where name='TEST';
NAME PASSWORD
------------------------------ ------------------------------
TEST 7A0F2B316C212D67
5）利用该hash值解锁scott用户
SYS@PROD4 >alter user test identified by values '7A0F2B316C212D67' account unlock;
User altered.
6）用原密码测试连接scott用户
SYS@PROD4 >conn test/test;
Connected.
TEST@PROD4 >show user;
USER is "TEST"
结论：在不知道用户密码的情况下，可以通过查询用户密码的hash值，在不更改密码的情况下解锁用户
测试二：
1）查看scott用户密码的hash值
TEST@PROD4 >conn / as sysdba
Connected.
SYS@PROD4 >select name,password from user$ where name='SCOTT';
NAME PASSWORD
------------------------------ ------------------------------
SCOTT F894844C34402B67
2）查看scott用户的过期时间
SYS@PROD4 >set linesize 200 pagesize 200
SYS@PROD4 >select username,password,account_status,lock_date,expiry_date,created,password_versions from dba_users where username='SCOTT';
USERNAME PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA CREATED PASSWORD
---------- ------------------------------ -------------------------------- --------- --------- --------- --------
SCOTT OPEN 07-AUG-20 18-SEP-11 10G 11G
3）利用scott用户密码的hash值重置用户的过期时间
SYS@PROD4 >alter user SCOTT identified by values 'F894844C34402B67';
User altered.
4）查看scott用户的密码过期时间（发现已经被重置）
SYS@PROD4 >select username,password,account_status,lock_date,expiry_date,created,password_versions from dba_users where username='SCOTT';
USERNAME PASSWORD ACCOUNT_STATUS LOCK_DATE EXPIRY_DA CREATED PASSWORD
---------- ------------------------------ -------------------------------- --------- --------- --------- --------
SCOTT OPEN 20-AUG-20 18-SEP-11 10G
5）验证scott用户用原密码是否可登陆
@ >conn scott/tiger;
Connected.
SCOTT@PROD4 >show user;
USER is "SCOTT"
结论：可以在不知道用户密码的情况下，重置用户密码有效期
