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 sysdbaConnected.

SYS@PROD4 >set linesize 200 pagesize 200SYS@PROD4 >col username for a10SYS@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 sysdbaConnected.

4）查看ora 28000报错SYS@PROD4 >!oerr ora 2800028000, 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 sysdbaConnected.SYS@PROD4 >select name,password from user$ where name='SCOTT';

NAME                   PASSWORD------------------------------ ------------------------------SCOTT                   F894844C34402B67

2）查看scott用户的过期时间SYS@PROD4 >set linesize 200 pagesize 200SYS@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"

结论：可以在不知道用户密码的情况下，重置用户密码有效期