不知道老密码的情况下替换密码

今天客户的环境有个新需求，需要更新一下用户的密码（密码 Expired 并且不知道老密码的情况下），之前在网上看到过相关的内容，现在进行测试。

根据user$查出原密码的密文，然后再改回去

19c测试：

SELECT ' alter user ' || NAME || ' identified by values ''' || B.SPARE4 ||

       ''';' AS reset_password

  FROM SYS.USER$ B

 INNER JOIN DBA_USERS A

    ON B.NAME = A.USERNAME

 WHERE ACCOUNT_STATUS = 'OPEN'

   and a.username <> 'SYS'

   and b.spare4 is not null;

我的测试用户zc 密码也是 zc 

SQL> conn zc/zc

Connected.

获取密文密码：

SQL> SELECT ' alter user ' || NAME || ' identified by values ''' || B.SPARE4 ||

       ''';' AS reset_password

  FROM SYS.USER$ B

 INNER JOIN DBA_USERS A

    ON B.NAME = A.USERNAME

 WHERE ACCOUNT_STATUS = 'OPEN'

   and a.username <> 'SYS'

   and b.spare4 is not  2    3    4    5    6    7    8   null;

RESET_PASSWORD

--------------------------------------------------------------------------------

 alter user SYSTEM identified by values 'S:BD9A2B952386A0985052478F5CD5940041B1E6430F7C9FA875025D29F49A;T:930D003DEB27720E9DC079F52E888D7A7849DF6B52923F7314D271C722505B500787367FD52886C48A2769B6EA4182E7FD044624C52606FB9EA07E4F32829C5FAF1646112C45E7B24E1A490A0AFB6F49';

 alter user ZC identified by values 'S:023E3DF9300645928DC1A64F64E5C82C46A225BC9E30DE75D715BD4438F3;T:B1AFEFCB334B67F4702173B050DE44555276CCEFC28E12D7D644A97708038DCEB1E0E4667304B0C4355C9DF561E83CA3B81C820DFC05F936C3E9D6D4C548275B5B88A367E045692DA2D583A7968F9B2C';

我们根据密文修改下然后再登录测试：

 alter user ZC identified by values 'S:023E3DF9300645928DC1A64F64E5C82C46A225BC9E30DE75D715BD4438F3;T:B1AFEFCB334B67F4702173B050DE44555276CCEFC28E12D7D644A97708038DCEB1E0E4667304B0C4355C9DF561E83CA3B81C820DFC05F936C3E9D6D4C548275B5B88A367E045692DA2D583A7968F9B2C';

User altered.

SQL> conn zc/zc

Connected.

SQL> conn zc/zz

ERROR:

ORA-01017: invalid username/password; logon denied

可以发现我们的测试方法是可以的，下面11g测试

SELECT ' alter user ' || NAME || ' identified by values ''' || B.SPARE4 ||

       ''';' AS reset_password

  FROM SYS.USER$ B

 INNER JOIN DBA_USERS A

    ON B.NAME = A.USERNAME

 WHERE ACCOUNT_STATUS = 'OPEN'

   and a.username <> 'SYS'

   and b.spare4 is not null;

 alter user SYSTEM identified by values 'S:867B7B97F7DF128C207F29D00B220A94E751309670BB7D0083C484781CE0';

 alter user TEST identified by values 'S:16F0E68865ECF7A8DEC8CC2B4F17F69C794BEBA61D7A6A50C1C05A2DB96B';

 alter user LINKS identified by values 'S:66F0701A10D129ACA4EC49DCFEB9CDFE960F1F11AB21629A2C83DD7FEDAE';

 alter user ZC identified by values 'S:39CE38AF290FCB8421DC85180DC43A3433AEEC6D90D79470B35FDE86052A';

我们根据密文修改下然后再登录测试：

 alter user ZC identified by values 'S:39CE38AF290FCB8421DC85180DC43A3433AEEC6D90D79470B35FDE86052A';

User altered.

SQL> conn zc/zc

Connected.

SQL> conn zc/zz

ERROR:

ORA-01017: invalid username/password; logon denied

后面发现用19c的密文也是可以的

SQL>  alter user ZC identified by values 'S:023E3DF9300645928DC1A64F64E5C82C46A225BC9E30DE75D715BD4438F3;T:B1AFEFCB334B67F4702173B050DE44555276CCEFC28E12D7D644A97708038DCEB1E0E4667304B0C4355C9DF561E83CA3B81C820DFC05F936C3E9D6D4C548275B5B88A367E045692DA2D583A7968F9B2C';

User altered.

SQL> conn zc/zc

Connected.

select ' alter user ' || name || ' identified by values ''' || password ||

       ''';'

  from sys.user$

 where name in (select username

                  from dba_users

                 where ACCOUNT_STATUS <> 'LOCK'

                   and ACCOUNT_STATUS not like 'EXPIRED%LOCKED');

(11G以这一句为标准)

select ' alter user ' || username || ' identified by values ''' || password ||

       ''';'

  from dba_users

 where ACCOUNT_STATUS <> 'LOCK'

   and ACCOUNT_STATUS not like 'EXPIRED%LOCKED';

(10G以这一句为标准)

密码 Expired 后处理(不知道密码情况)

1、直接修改状态，刷新 shared_pool 生效

UPDATE USER$ SET ASTATUS=0 WHERE NAME = 'SCOTT'; -- sys

COMMIT;

ALTER SYSTEM FLUSH SHARED_POOL; --清空缓存池，负载高环境不应该在线执行；不执行该命令上面设置不生效(虽说查看状态会变)

-- ASTATUS 状态码：

select * from user_astatus_map;

   STATUS# STATUS

---------- --------------------------------

         0 OPEN

         1 EXPIRED

         2 EXPIRED(GRACE)

         4 LOCKED(TIMED)

         8 LOCKED

         5 EXPIRED & LOCKED(TIMED)

         6 EXPIRED(GRACE) & LOCKED(TIMED)

         9 EXPIRED & LOCKED

        10 EXPIRED(GRACE) & LOCKED

2、使用密文字段更新

-- 获取密码密文方式1

select NAME,PASSWORD,SPARE4 from user$ where name='SCOTT';

NAME     PASSWORD             SPARE4

-------- -------------------- ------------------------------------------------------------------------------------------------------------------------

SCOTT                           S:6BC1DB96CE62D9468922A6DD7E0C8CA904EEBB7AE7B79C5EF257E9DEB0C3;T:DA86551199D7BC8338CBCF23C40EF2F027DCE57030008B6E0F23D992502173614CCD85A40806FDACF5CCB9A6713E05C1E8DDD81D01DFD16D6BE28374A4DD8F345B04D15DA853AA467EBD4CF5916CD6A8

-- 获取密码密文方式2

select dbms_metadata.get_ddl('USER','SCOTT') from dual;

   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:6BC1DB96CE62D9468922A6DD7E0C8CA904EEBB7AE7B79C5EF257E9DEB0C3;T:DA86551199D7BC8338CBCF23C40EF2F027DCE57030008B6E0F23D992502173614CCD85A40806FDACF5CCB9A6713E05C1E8DDD81D01DFD16D6BE28374A4DD8F345B04D15DA853AA467EBD4CF5916CD6A8'

      DEFAULT TABLESPACE "USERS"

      TEMPORARY TABLESPACE "TEMP"

      PROFILE "DBAMGMT"

-- 使用密文修改

alter user scott identified by VALUES 'S:6BC1DB96CE62D9468922A6DD7E0C8CA904EEBB7AE7B79C5EF257E9DEB0C3;T:DA86551199D7BC8338CBCF23C40EF2F027DCE57030008B6E0F23D992502173614CCD85A40806FDACF5CCB9A6713E05C1E8DDD81D01DFD16D6BE28374A4DD8F345B04D15DA853AA467EBD4CF5916CD6A8';

commit;

select NAME,PASSWORD,SPARE4 from user$ where name='SYS';

select dbms_metadata.get_ddl('USER','SYS') from dual;