同事请求我为她自己用的测试用户改登陆密码,这种小case都help我,显然她对数据库的知识比较欠缺.

于是有了下面的操作案例:

SQL> conn / as sysdba
Connected.
SQL> alter user shiyihai identified by abc;

User altered.

SQL> conn shiyihai/abc
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user shiyihai identified by shiyihai;

User altered.

SQL> conn shiyihai/shiyihai
Connected.
SQL> conn / as sysdba
Connected.
SQL> select username,password from dba_users where username='SHIYIHAI';

USERNAME PASSWORD
------------------------------ ------------------------------
SHIYIHAI 010CD1914E5AB20C

SQL> alter user shiyihai identified by abc;

User altered.

SQL> conn shiyihai/abc
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user shiyihai identified by values '010CD1914E5AB20C';

User altered.

SQL> conn shiyihai/shiyihai
Connected.
SQL>

SQL> conn shiyihai/shiyihai
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user shiyihai identified by abc;

User altered.

SQL> conn shiyihai/abc
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user shiyihai identified by shiyihai;

User altered.

SQL> conn shiyihai/shiyihai
Connected.
SQL> conn / as sysdba
Connected.
SQL> select username,password from dba_users where username='SHIYIHAI';

USERNAME PASSWORD
------------------------------ ------------------------------
SHIYIHAI 010CD1914E5AB20C

SQL> alter user shiyihai identified by abc;

User altered.

SQL> conn shiyihai/abc
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user shiyihai identified by values '010CD1914E5AB20C';

User altered.

SQL> conn shiyihai/shiyihai
Connected.
SQL>
SQL> conn shiyihai/shiyihai
Connected.
SQL> alter user shiyihai identified by abc;

User altered.

SQL> conn shiyihai/abc
Connected.
SQL> conn / as sysdba
Connected.
SQL> alter user shiyihai identified by values '010CD1914E5AB20C';

User altered.

SQL> conn shiyihai/shiyihai
Connected.
SQL>

结论:

1.每个用户均可改自己的密码(alter user xxx identified by yyy),具有dba身份的用户可改其它用户的密码;

2.dba_users字典表中的password是用户保存在数据库中加过密的密码值(这个加密算法我一直都没弄明白采用的是什么算法),结合alter user xxx identified by values 'password'可还原用户的旧密码值.这个功能完全可以用在按用户迁移数据的实际操作中.