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

不知道老密码的情况下替换密码 今天客户的环境有个新需求，需要更新一下用户的密码（密码 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;