ORA-01017：invalid username/password; logon denied

  

现象：

客户应用的开发人员反映使用plsql developer登录test用户的时候报错：ORA-01017：invalid username/password; logon denied，使用system和sys在pl/sql developer登录就没问题。


在服务器本机测试：

$ sqlplus /nolog


SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 24 10:30:50 2014


Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


SQL> conn test/test
ERROR:
ORA-01017: invalid username/password; logon denied




SQL> show parameter remote_login_passwordfile


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE   ------------------此处为Oracle默认的值，没问题。



尝试：




$ export ORACLE_SID=test
$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 24 11:28:23 2014


Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.




Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select open_mode from v$database;


OPEN_MODE
----------
READ WRITE


其中还怀疑是因为开发人员未给用户赋权限：

SQL> GRANT CREATE SESSION TO test；


Grant succeeded.


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ sqlplus /nolog


SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 24 11:28:48 2014


Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


SQL> conn test/test
ERROR:
ORA-01017: invalid username/password; logon denied   ------------看来不是权限问题。




SQL> conn / as sysdba
Connected.
SQL> show parameter service;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      test


SQL> alter user test identified by test;


User altered.

该方案成功！


SQL> conn test/test
Connected.


最后找开发人员获取建用户的脚本，发现确实是创建用户时设定密码有问题：



CREATE USER test
  IDENTIFIED BY VALUES 'test' -----------------------此行导致的，一般使用 identified by 直接加密码即可！！,
  DEFAULT TABLESPACE  TEST_DATA                      identied by values适用于加密方式指定密码的，一般为
  TEMPORARY TABLESPACE TEMP                          一串16禁止无可读性的字符，如果明文指定密码的话，使用
  PROFILE DEFAULT                                    identied by password即可。
  ACCOUNT UNLOCK; 
  -- 3 Roles for test 
  GRANT CONNECT TO test;
  GRANT DBA TO test;
  GRANT RESOURCE TO test;
  ALTER USER test DEFAULT ROLE ALL;
  -- 2 System Privileges for test
  GRANT CREATE ANY TABLE TO test;
  GRANT UNLIMITED TABLESPACE TO test;