Restore database user history account password
1. 用户状态
select * from user_astatus_map;
select * from user_astatus_map;
STATUS# STATUS
---------- --------------------------------
OPEN
EXPIRED
EXPIRED(GRACE)
4 LOCKED(TIMED)
LOCKED
EXPIRED & LOCKED(TIMED)
EXPIRED(GRACE) & LOCKED(TIMED)
EXPIRED & LOCKED
EXPIRED(GRACE) & LOCKED
2. password_versions
2.1）在oracle 10g， 密码记录在sys.user$.PASSWORD列，其长度为16字符，且不区分大小写；
2.2）在oracle 11g版本后，其复杂度得到了加强，将密码同时写入到sys.user$.spare4列，并且sys.user$.spare4对大小敏感。
2.3）dba_users.password_versions的优先级大于sec_case_sensitive_logon参数，11g默认为true。
3. 下面通过语句进行刷密码操作
3.1）创建序列
start nomaxvalue nocycle nocache;
3.2）创建操作记录表
create table tb_refpwd_log( id number not null, -- 引用seq_refpwd.nextval oper_time date, -- 记录操作时间 oper_command ) -- 记录操作命令内容 );
3.3）执行语句
declare
v_datetime ) := to_char(sysdate, 'yyyymmddHH24MI');
v_tbname ) := trim(concat('tb_userpwd_', v_datetime)); -- 备份表名称
v_pf_sql ) := 'create profile temp_profile limit PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME UNLIMITED'; -- 构造创建profile语句
v_sql );
TYPE RECORD_TYPE_USERS IS RECORD(
v_username sys.user$.name%TYPE,
v_user_pwd ),
v_profile ),
v_status sys.user$.ASTATUS%TYPE);
user_rec RECORD_TYPE_USERS;
cursor c_pwd_cursor is
select t2.name,
case trim(t1.password_versions)
when '10G' then
t2.password
else
nvl(t2.spare4, t2.password)
end,
t1.profile,
t2.astatus
from sys.dba_users t1, sys.user$ t2
where t1.user_id = t2.user#
, , , , , );
invalid_option EXCEPTION;
begin
-- select to_char(sysdate, 'yyyymmddHH24MI') into v_datetime from dual;
-- select trim(concat('tb_userpwd_',v_datetime)) into v_tbname from dual;
select 'create table ' || v_tbname ||
' as select name,type#,password,datats#,tempts#,ctime,ptime,exptime,ltime,resource$,astatus,lcount,spare4 from sys.user$ where astatus <> 9'
into v_sql
from dual;
open c_pwd_cursor;
fetch c_pwd_cursor
into user_rec;
then
-- 1. create unlimited temporary profile
execute immediate v_pf_sql;
IF SQL%NOTFOUND THEN
RAISE invalid_option;
end IF;
-- 2. backup user$ tables
execute immediate v_sql;
IF SQL%NOTFOUND THEN
RAISE invalid_option;
end IF;
end if;
while c_pwd_cursor%FOUND LOOP
-- 3. reflash user password
/*
dbms_output.put_line('alter user ' || user_rec.v_username ||
' profile temp_profile');
dbms_output.put_line('alter user ' || user_rec.v_username ||
' identified by values ' || chr(39) ||
user_rec.v_user_pwd || chr(39));
dbms_output.put_line('alter user ' || user_rec.v_username ||
' profile ' || user_rec.v_profile);
*/
execute immediate 'alter user ' || user_rec.v_username ||
' profile temp_profile';
execute immediate 'alter user ' || user_rec.v_username ||
) ||
user_rec.v_user_pwd );
) ));
execute immediate 'alter user ' || user_rec.v_username || ' profile ' ||
user_rec.v_profile;
fetch c_pwd_cursor
into user_rec;
end loop;
-- 4. delete temporary profile
execute immediate 'drop profile temp_profile cascade';
insert into tb_refpwd_log(id, oper_time, oper_command) values(seq_refpwd.nextval,v_datetime,'drop profile temp_profile cascade');
commit;
close c_pwd_cursor;
EXCEPTION
when invalid_option then
, v_datetime, 'invalid opertaion, please check.');
when others then
null;
end;
/
4. 结果确认
) cnt
from (select t1.name, t1.password, t1.spare4
from sys.user$ t1
minus
select t2.name, t2.password, t2.spare4
from &v_datetime t2
);
select username, account_status, lock_date, expiry_date, created, profile, password_versions,default_tablespace, temporary_tablespace from dba_users;