一、环境: linux redhat 5.8
oracle 11.2.0.4.0
二、问题描述:
有个用户密码忘记了,又怕改了该用户密码会影响应用,因为改密码很容易,如果影响的话该何所做好回退方案呢。以下就是我的回退方案。
三、实验证明:
1. 建立测试用户并赋权限
[oracle@tora01 ~]$ sqlplus / as sysddba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 28 11:48:33 2015
Copyright (c) 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> create user sam identified by sam;
User created.
SQL> grant connect to sam;
Grant succeeded.
|
2. 记录用户密码与SPARE4字段的值,通过惜分飞的博客得知SPARE4字段是密码大小写敏感控制《
http://www.xifenfei.com/4396.html》。此处我的实验是要求记录此值,为了后面恢复密码使用。
SQL> set lines 200
SQL> col passwrod for a20
SQL> col spare4 for a70
SQL> select password,spare4 from user$ where name='SAM';
PASSWORD SPARE4
-------------------- ----------------------------------------------------------------------
4B95138CB6A4DB94 S:DD85639AB2542CB45E1138F8570E837C95555A337D60752453848A22F1E9
|
3. 使用命令方式修改密码
SQL> alter user sam identified by Oracle;
User altered.
|
4. 再次检查基表情况
SQL> select password,spare4 from user$ where name='SAM';
PASSWORD SPARE4
-------------------- ----------------------------------------------------------------------
25CA928B1DB71583 S:CF85AA4FA2DA01D1FC0B0C7C540C9A49A966A638302DC007AF1B2481CD5B
|
5. 测试用户用新密码登录
SQL> conn sam/Oracle;
Connected.
|
6. 修改基表,使得变更回改前密码
SQL> conn / as sysdba
Connected.
SQL> update user$ set password='
4B95138CB6A4DB94' where name='SAM';
1 row updated.
SQL> update user$ set spare4='
S:DD85639AB2542CB45E1138F8570E837C95555A337D60752453848A22F1E9' where name='SAM';
1 row updated.
SQL> commit;
Commit complete.
|
7. 清理shared_pool
SQL> alter system flush shared_pool;
System altered.
|
8. 测试用原来的密码连接,成功
SQL> conn sam/sam;
Connected.
|
四、总结
该方法由于对基表进行修改,不建议轻易使用,由其是生产环境,最好的方法还是要在文档中记录用户密码。在忘记时可以通过文档查找。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26148431/viewspace-1754165/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26148431/viewspace-1754165/