ANBOB™

之前已经记录过多篇关于用户密码的问题， 今天又看到一则19c 在XTTS升级时，用户创建拼密码SQL脚本失败的问题，对于拼接user$.password和user$.spare$列时值错误，导致密码不能登录问题，对于19c中2个字段值哪种情况下没有值，简单的测试记录。

ORA-28007: the password cannot be reused 如何破

Troubleshooting ‘ORA-28041: Authentication protocol internal error’ change password 12c R2 DB

Alert: SEC_CASE_SENSITIVE_LOGON and ORA-1017 after upgrade to 12.2 、18c、19c

升级12C注意事项： 连接失败 ORA-28040 ORA-1017

Oracle 12c 关于密码(password)的几个新特性小结

— 测试环境oracle 19.3 —

默认情况下

-- 未配置sqlnet.ora中的客户端、服务端兼容性密码相关参数
SQL> create user u1000 identified by u1123456;
User created.

SQL> select password_versionS from dba_users where username='U1000';
PASSWORD_VERSIONS
-----------------
11G 12C

SQL> select password,spare4 from user$ where name='U1000';
PASSWORD                           SPARE4
---------------------------------- ----------------------------------------------------------------
                                    S:0AC2C9EF2988076E4C1873912B26BA0F2325A0C18D045FA75C1C4C918C5F;T:FEBAB39E8280C02772B5290392605D5A86CD0D419921BFBEA0B21B5838E508977633716BAE6F26CE01849E7ADAFC5639AE0C88AF4C0D1D57679F0AA97450B7BE27D9C11EF2F6BE1871E55A41619B570F

只增加SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

-- 注意每次改完sqlnet.ora 文件，要退出重新登录sqlplus 
[oracle@oel7db1 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/19.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

SQL> create user u1002 identified by u1123456;
User created.

SQL> select password_versionS from dba_users where username='U1002';
PASSWORD_VERSIONS
-----------------
10G 11G 12C

SQL> select password,spare4 from user$ where name='U1002';
PASSWORD                                     SPARE4
-------------------------------------------  -------------------------------------------
C47315722F4E4704                             S:7A933852D0A9C96E27339AA8824BF9AEE11C373ED6D495F310C9AEC1937D;T:A617E2E33CFFF74E95D058EC0FE46DFDBE22F7F5D5463C295486EB721963C17CE9C92F2824D791DC31F788BD0E1F187431CACC7D0B79AE8399645075549EBA5E160B3293C9CBAF0A7BEF692EE3190FDE

使用identified by values修改密码

SQL> alter user u1002 identified by values 'C47315722F4E4704';
User altered.

SQL> alter user u1002 identified by values ';C47315722F4E4704';
alter user u1002 identified by values ';C47315722F4E4704'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string

SQL> alter user u1002 identified by values 'C47315722F4E4704;';
alter user u1002 identified by values 'C47315722F4E4704;'
*
ERROR at line 1:
ORA-28007: the password cannot be reused

SQL> delete user_history$ where user#=129;
2 rows deleted.

SQL> commit;
Commit complete.

SQL> alter user u1002 identified by values 'C47315722F4E4704;';
User altered.

SQL> select password,spare4 from user$ where name='U1000';

PASSWORD                                     SPARE4
-------------------------------------------  -------------------------------------------
C47315722F4E4704

SQL> alter user u1002 identified by values 'S:7A933852D0A9C96E27339AA8824BF9AEE11C373ED6D495F310C9AEC1937D;T:A617E2E33CFFF74E95D058EC0FE46DFDBE22F7F5D5463C295486EB721963C17CE9C92F2824D791DC31F788BD0E1F187431CACC7D0B79AE8399645075549EBA5E160B3293C9CBAF0A7BEF692EE3190FDE';

SQL> select password,spare4 from user$ where name='U1002';
PASSWORD                                     SPARE4
-------------------------------------------  -------------------------------------------
                                             S:7A933852D0A9C96E27339AA8824BF9AEE11C373ED6D495F310C9AEC1937D;T:A617E2E33CFFF74E95D058EC0FE46DFDBE22F7F5D5463C295486EB721963C17CE9C92F2824D791DC31F788BD0E1F187431CACC7D0B79AE8399645075549EBA5E160B3293C9CBAF0A7BEF692EE3190FDE

SQL> alter user u1002 identified by values 'C47315722F4E4704;S:7A933852D0A9C96E27339AA8824BF9AEE11C373ED6D495F310C9AEC1937D;T:A617E2E33CFFF74E95D058EC0FE46DFDBE22F7F5D5463C295486EB721963C17CE9C92F2824D791DC31F788BD0E1F187431CACC7D0B79AE8399645075549EBA5E160B3293C9CBAF0A7BEF692EE3190FDE';
User altered.

SQL> select password,spare4 from user$ where name='U1002';
PASSWORD                                     SPARE4
-------------------------------------------  -------------------------------------------
C47315722F4E4704                             S:7A933852D0A9C96E27339AA8824BF9AEE11C373ED6D495F310C9AEC1937D;T:A617E2E33CFFF74E95D058EC0FE46DFDBE22F7F5D5463C295486EB721963C17CE9C92F2824D791DC31F788BD0E1F187431CACC7D0B79AE8399645075549EBA5E160B3293C9CBAF0A7BEF692EE3190FDE
-- 在拼接SQL时无论是spare4||';'||password，还是先后顺序颠倒，都会自动拆分到不同的列中。

— over —

打赏

微信扫一扫，打赏作者吧～