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