如何修改Oracle用户的密码
有的人回答说这个不是很简单吗 alter user identified by +密码
如果不知道用户的密码情况下，不能改变原始密码呢(适用于用户密码过期)
测试如下
第一种是user$中的password 值去替换密码 但是根据资料上说12.2以后就不让用了(不知道确定还是不确定)
SQL> alter user dbmt identified by dbmt;
User altered.
SQL> select NAME,PASSWORD,SPARE4 from user$ where name=‘DBMT’;
NAME PASSWORD SPARE4
DBMT 16D08086ED861FDD S:383BD1F3A12E427EFA3C918C74E463F46BD4E51612E6B564ECC0226C3352
SQL> alter user dbmt identified by values ‘16D08086ED861FDD’;
User altered.
SQL> conn dbmt/dbmt
Connected.
SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;
USERNAME PASSWORD_VERSIONS
DBMT 11G
ITPUX02 10G 11G
ITPUX01 10G 11G
LJC 10G 11G
ITPUX 10G 11G
SPATIAL_WFS_ADMIN_USR 10G 11G
SPATIAL_CSW_ADMIN_USR 10G 11G
该PASSWORD_VERSIONS列显示该帐户存在的密码版本列表。10G是指早期不区分大小写的Oracle密码版本，11G是指基于SHA-1的密码版本，12C是指基于SHA-2的SHA-512密码版本。
第二种是user$中的SPARE4 值去替换密码
前提需要确定这个参数sec_case_sensitive_logon 是不是TRUE
先模拟一下 sec_case_sensitive_logon 是false的情况
SQL> alter system set sec_case_sensitive_logon=false;
System altered.
SQL> @p sec_case_sensitive_logon
NAME VALUE
sec_case_sensitive_logon FALSE
SQL> select NAME,PASSWORD,SPARE4 from user$ where name=‘DBMT’;
NAME PASSWORD SPARE4
DBMT 16D08086ED861FDD S:383BD1F3A12E427EFA3C918C74E463F46BD4E51612E6B564ECC0226C3352
SQL>
SQL>
SQL> alter user dbmt identified by dbmt;
User altered.
SQL> conn dbmt/dbmt
Connected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@water ~]$ ora
SQL*Plus: Release 11.2.0.4.0 Production on 星期一 7月 27 14:39:45 2020
Copyright © 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select NAME,PASSWORD,SPARE4 from user$ where name=‘DBMT’;
NAME PASSWORD SPARE4
DBMT 16D08086ED861FDD S:7992E46A3A041A36EE601EF1B0024377F55BCCBD80188D5F41A6FD5C5760
SQL> alter user dbmt identified by values ‘S:7992E46A3A041A36EE601EF1B0024377F55BCCBD80188D5F41A6FD5C5760’;
User altered.
SQL> conn dbmt/dbmt
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to O
当把sec_case_sensitive_logon 改TRUE
SQL> conn dbmt/dbmt
Connected.
SQL> select NAME,PASSWORD,SPARE4 from user$ where name=‘DBMT’;
NAME PASSWORD SPARE4
DBMT 16D08086ED861FDD S:7992E46A3A041A36EE601EF1B0024377F55BCCBD80188D5F41A6FD5C5760
但是在实验的过程中发现这二种改密码的方式混着用 发现个问题
SQL> select NAME,PASSWORD,SPARE4 from user$ where name=‘DBMT’;
NAME PASSWORD SPARE4
DBMT 16D08086ED861FDD S:7992E46A3A041A36EE601EF1B0024377F55BCCBD80188D5F41A6FD5C5760
SQL> alter user dbmt identified by values ‘S:7992E46A3A041A36EE601EF1B0024377F55BCCBD80188D5F41A6FD5C5760’;
User altered.
SQL> alter user dbmt identified by values ‘16D08086ED861FDD’;
User altered.
SQL> select NAME,PASSWORD,SPARE4 from user$ where name=‘DBMT’;
NAME PASSWORD SPARE4
DBMT 16D08086ED861FDD
神奇的发现SPARE4 这个值消失 反过来也是先去修改passwd 后去修改SPARE4 也会发现password的值也会消失