在不修改用户密码的情况下，让用户的过期密码重新生效
首先，查看当前数据库中的用户情况
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
SQL> desc dba_users; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME NOT NULL VARCHAR2(128) USER_ID NOT NULL NUMBER PASSWORD VARCHAR2(4000) ACCOUNT_STATUS NOT NULL VARCHAR2(32) LOCK_DATE DATE EXPIRY_DATE DATE DEFAULT_TABLESPACE NOT NULL VARCHAR2(30) TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30) CREATED NOT NULL DATE PROFILE NOT NULL VARCHAR2(128) INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128) EXTERNAL_NAME VARCHAR2(4000) PASSWORD_VERSIONS VARCHAR2(12) EDITIONS_ENABLED VARCHAR2(1) AUTHENTICATION_TYPE VARCHAR2(8) PROXY_ONLY_CONNECT VARCHAR2(1) COMMON VARCHAR2(3) LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE ORACLE_MAINTAINED VARCHAR2(1) SQL> SQL> col username for a12 SQL> col password for a32 SQL> col account_status for a28 SQL> col profile for a12 SQL> set linesize 400 SQL> SQL> select username,user_id,account_status,password,profile,to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') "Expiry Date",to_char(last_login,'yyyy-mm-dd hh24:mi:ss') "Last Login" from dba_users where username='ORDDATA'; USERNAME USER_ID ACCOUNT_STATUS PASSWORD PROFILE Expiry Date Last Login ------------ ---------- ---------------------------- -------------------------------- ------------ ------------------- ------------------- ORDDATA 76 EXPIRED & LOCKED DEFAULT 2014-11-02 18:01:18 SQL> SQL> col name for a12 SQL> col password for a18 SQL> set linesize 400 SQL> select name,password from user$ where name='ORDDATA'; NAME PASSWORD ------------ ------------------ ORDDATA 2661DB47C3E1EFDA SQL> |
在正式开始操作前，现将用户“orddata”的状态设为我们希望的。
并尝试登陆。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> alter user orddata identified by abcd1234 account unlock; User altered. SQL> grant connect to orddata; Grant succeeded. SQL> SQL> show user USER is "SYS" SQL> connect orddata/abcd1234; Connected. SQL> show user USER is "ORDDATA" SQL> |
如上所示，在开始时，用户：orddata，的密码为：abcd1234。
现在，先将该账户的密码置为过期：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
SQL> col name for a12 SQL> col password for a18 SQL> col username for a12 SQL> col account_status for a22 SQL> col profile for a12 SQL> SQL> set linesize 400 SQL> set pagesize 400 SQL> SQL> conn / as sysdba Connected. SQL> SQL> select username,user_id,account_status,password,profile,to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') "Expiry Date",to_char(last_login,'yyyy-mm-dd hh24:mi:ss') "Last Login" from dba_users where username='ORDDATA'; USERNAME USER_ID ACCOUNT_STATUS PASSWORD PROFILE Expiry Date Last Login ------------ ---------- ---------------------- ------------------ ------------ ------------------- ------------------- ORDDATA 76 OPEN DEFAULT 2015-05-13 22:37:26 2014-11-14 22:37:57 SQL> select name,password from user$ where name='ORDDATA'; NAME PASSWORD ------------ ------------------ ORDDATA 2661DB47C3E1EFDA SQL> SQL> alter user orddata password expire; User altered. SQL> select username,user_id,account_status,password,profile,to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') "Expiry Date",to_char(last_login,'yyyy-mm-dd hh24:mi:ss') "Last Login" from dba_users where username='ORDDATA'; USERNAME USER_ID ACCOUNT_STATUS PASSWORD PROFILE Expiry Date Last Login ------------ ---------- ---------------------- ------------------ ------------ ------------------- ------------------- ORDDATA 76 EXPIRED DEFAULT 2014-11-14 22:41:40 2014-11-14 22:37:57 SQL> select name,password from user$ where name='ORDDATA'; NAME PASSWORD ------------ ------------------ ORDDATA 2661DB47C3E1EFDA SQL> |
用户ORDDATA的密码过期后，尝试登陆：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> show user USER is "SYS" SQL> conn orddata/abcd1234 ERROR: ORA-28001: the password has expired Changing password for orddata New password: Password unchanged Warning: You are no longer connected to ORACLE. SQL> |
可以看到，密码过期后，登陆目标账户会要求立即修改密码，成功后，方能登入。
本文呈现如何不修改密码恢复账户的过期密码的技巧。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
SQL> conn / as sysdba Connected. SQL> show user USER is "SYS" SQL> SQL> col profile for a23 SQL> col limit for a30 SQL> set linesize 300 SQL> SQL> select count(*) from dba_profiles; COUNT(*) ---------- 32 SQL> set pagesize 300 SQL> SQL> select * from dba_profiles; PROFILE RESOURCE_NAME RESOURCE LIMIT COM ----------------------- -------------------------------- -------- ------------------------------ --- DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED NO DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED NO DEFAULT CPU_PER_SESSION KERNEL UNLIMITED NO DEFAULT CPU_PER_CALL KERNEL UNLIMITED NO DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED NO DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED NO DEFAULT IDLE_TIME KERNEL UNLIMITED NO DEFAULT CONNECT_TIME KERNEL UNLIMITED NO DEFAULT PRIVATE_SGA KERNEL UNLIMITED NO DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 NO DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 NO DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED NO DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED NO DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL NO DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 NO DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 NO ORA_STIG_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT NO ORA_STIG_PROFILE SESSIONS_PER_USER KERNEL DEFAULT NO ORA_STIG_PROFILE CPU_PER_SESSION KERNEL DEFAULT NO ORA_STIG_PROFILE CPU_PER_CALL KERNEL DEFAULT NO ORA_STIG_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT NO ORA_STIG_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT NO ORA_STIG_PROFILE IDLE_TIME KERNEL 15 NO ORA_STIG_PROFILE CONNECT_TIME KERNEL DEFAULT NO ORA_STIG_PROFILE PRIVATE_SGA KERNEL DEFAULT NO ORA_STIG_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD 3 NO ORA_STIG_PROFILE PASSWORD_LIFE_TIME PASSWORD 60 NO ORA_STIG_PROFILE PASSWORD_REUSE_TIME PASSWORD 365 NO ORA_STIG_PROFILE PASSWORD_REUSE_MAX PASSWORD 10 NO ORA_STIG_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD ORA12C_STRONG_VERIFY_FUNCTION NO ORA_STIG_PROFILE PASSWORD_LOCK_TIME PASSWORD UNLIMITED NO ORA_STIG_PROFILE PASSWORD_GRACE_TIME PASSWORD 5 NO 32 rows selected. SQL> |
注意其中的：
PASSWORD_REUSE_TIME
PASSWORD_REUSE_MAX
这两个配置文件的限制，有可能会导致无法重用旧的密码。（ORA-28007: the password cannot be reused.）
修改用户：orddata的PROFILE：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> select username,profile from dba_users where username='ORDDATA'; USERNAME PROFILE ------------ ----------------------- ORDDATA DEFAULT SQL> SQL> alter user ORDDATA profile ORA_STIG_PROFILE; User altered. SQL> select username,profile from dba_users where username='ORDDATA'; USERNAME PROFILE ------------ ----------------------- ORDDATA ORA_STIG_PROFILE SQL> |
查看用户：ORDDATA的密码：
|
1 2 3 4 5 6 7 |
SQL> select name,password from user$ where name='ORDDATA'; NAME PASSWORD ------------ ------------------ ORDDATA 2661DB47C3E1EFDA SQL> |
设定密码：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> select username,user_id,account_status,password,profile,to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') "Expiry Date",to_char(last_login,'yyyy-mm-dd hh24:mi:ss') "Last Login" from dba_users where username='ORDDATA'; USERNAME USER_ID ACCOUNT_STATUS PASSWORD PROFILE Expiry Date Last Login ------------ ---------- ---------------------- ------------------ ----------------------- ------------------- ------------------- ORDDATA 76 EXPIRED ORA_STIG_PROFILE 2014-11-14 22:41:40 2014-11-14 22:37:57 SQL> SQL> alter user orddata identified by values '2661DB47C3E1EFDA'; User altered. SQL> select username,user_id,account_status,password,profile,to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') "Expiry Date",to_char(last_login,'yyyy-mm-dd hh24:mi:ss') "Last Login" from dba_users where username='ORDDATA'; USERNAME USER_ID ACCOUNT_STATUS PASSWORD PROFILE Expiry Date Last Login ------------ ---------- ---------------------- ------------------ ----------------------- ------------------- ------------------- ORDDATA 76 OPEN ORA_STIG_PROFILE 2015-01-13 23:01:23 2014-11-14 22:37:57 SQL> |
还原用户：orddata的PROFILE设定。
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> alter user orddata profile default; User altered. SQL> select username,user_id,account_status,password,profile,to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss') "Expiry Date",to_char(last_login,'yyyy-mm-dd hh24:mi:ss') "Last Login" from dba_users where username='ORDDATA'; USERNAME USER_ID ACCOUNT_STATUS PASSWORD PROFILE Expiry Date Last Login ------------ ---------- ---------------------- ------------------ ----------------------- ------------------- ------------------- ORDDATA 76 OPEN DEFAULT 2015-05-13 23:01:23 2014-11-14 22:37:57 SQL> |
旧的密码恢复后，再次尝试登入ORDDATA：
|
1 2 3 4 5 6 7 |
SQL> show user USER is "SYS" SQL> conn orddata/abcd1234 Connected. SQL> show user USER is "ORDDATA" SQL> |
————————————————————————————————
Ending。
