Oracle rdbms Brush password
发布时间:2020-12-12 13:46:13 所属栏目:百科 来源:网络整理
导读:Restore database user history account password 1. 用户状态 select * from user_astatus_map; select * from user_astatus_map; STATUS# STATUS -- -------- -------------------------------- 0 OPEN 1 EXPIRED 2 EXPIRED(GRACE) 4 LOCKED(TIMED) 8 LOCK
Restore database user history account password1. 用户状态select * from user_astatus_map; 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.? password_versions2.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)创建序列CREATE SEQUENCE seq_refpwd increment by 1 start with 1 nomaxvalue nocycle nocache; 3.2)创建操作记录表create table tb_refpwd_log( id number not null,-- 引用seq_refpwd.nextval oper_time date,-- 记录操作时间 oper_command varchar2(1000) -- 记录操作命令内容 ); 3.3)执行语句declare v_datetime varchar2(12) := to_char(sysdate,‘yyyymmddHH24MI‘); v_tbname varchar2(32) := trim(concat(‘tb_userpwd_‘,v_datetime)); -- 备份表名称 v_pf_sql varchar2(1000) := ‘create profile temp_profile limit PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME UNLIMITED‘; -- 构造创建profile语句 v_sql varchar2(1000); TYPE RECORD_TYPE_USERS IS RECORD( v_username sys.user$.name%TYPE,v_user_pwd VARCHAR2(1000),v_profile VARCHAR2(30),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# and t2.astatus in (0,1,2,5,6,10); 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; if c_pwd_cursor%ROWCOUNT > 0 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 || ‘ identified by values ‘ || chr(39) || user_rec.v_user_pwd || chr(39); insert into tb_refpwd_log(id,oper_time,oper_command) values(seq_refpwd.nextval,v_datetime,‘alter user ‘ || user_rec.v_username || ‘ identified by values ‘ || chr(39) || user_rec.v_user_pwd || chr(39)); 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,‘drop profile temp_profile cascade‘); commit; close c_pwd_cursor; EXCEPTION when invalid_option then insert into tb_refpwd_log(id,oper_command) values(-1,‘invalid opertaion,please check.‘); when others then null; end; / ? 4. 结果确认select count(1) cnt from (select t1.name,t1.password,t1.spare4 from sys.user$ t1 where t1.type# = 1 minus select t2.name,t2.password,t2.spare4 from &v_datetime t2 where t2.type# = 1); select username,account_status,lock_date,expiry_date,created,profile,password_versions,default_tablespace,temporary_tablespace from dba_users; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |