oracle密码文件文件

oracle密码⽂件⽂件
密码⽂件作⽤：
密码⽂件⽤于dba⽤户的登录认证。

dba⽤户：具备sysdba和sysoper权限的⽤户，即oracle的sys和system⽤户。

本地登录：
1）操作系统认证：
[oracle@localhost ~]$ sqlplus "/as sysdba"
[oracle@localhost ~]$ sqlplus / as sysdba
[oracle@localhost ~]$ sqlplus sys/tiger as sysdba
2）密码⽂件认证：
[oracle@localhost ~]$ sqlplus sys/tiger@rezin as sysdba
远程密码⽂件登录：
[oracle@localhost ~]$ sqlplus sys/tiger@192.168.96.141:1521/orcl as sysdba
密码⽂件位置：
linux/unix：[oracle@localhost ~]$ ls $ORACLE_HOME/dbs/orapw$ORACLE_SID
/u01/oracle/10g/dbs/orapworcl
/u01/oracle/10g/dbs/orapwrezin
windows：$ORACLE_HOME/oradate/orapw$ORACLE_SID
密码⽂件查找顺序：
1）opapw<sid>
2）orapw
以上两个都查找不到，验证失败。

密码⽂件认证还是OS认证：
1）参数⽂件：remote_login_passwordfile=none|exclusive|shared
none:不使⽤密码⽂件认证
exclusive：使⽤密码⽂件认证，⾃⼰独占使⽤（默认）
shared：使⽤密码⽂件认证，不同实例dba⽤户可以共享密码⽂件（asm下必须使⽤）
2）$ORACLE_HOME/network/admin/sqlnet.ora⽂件下：
SQLNET.AUTHENTICATION_SERVICES =none|all|nts（linux下默认没有设置）
none：关闭OS认证，只能密码⽂件认证
all：linux平台关闭本机密码⽂件认证，采⽤操作系统认证，但是远程（异机）可以密码⽂件认证 nts：windows下使⽤（桶linux下all）
练习：
1）配置：remote_login_passwordfile=exclusive
SQLNET.AUTHENTICATION_SERVICES =none
结果：可以密码⽂件认证（本地/远超），不可以操作系统认证
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 14 19:00:39 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved.
:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options ??
[oracle@localhost ~]$ sqlplus / as sysdba（OS认证）
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 14 19:00:51 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
或
[oracle@localhost ~]$ sqlplus "/as sysdba"（OS认证）
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 14 19:01:04 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
2）配置：remote_login_passwordfile=exclusive
SQLNET.AUTHENTICATION_SERVICES =all
结果：本机密码⽂件认证不可⽤，但是远程密码⽂件认证可⽤，本机OS认证可⽤[oracle@localhost ~]$ sqlplus "/as sysdba"（本机OS认证登录成功）
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 14 19:45:35 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@localhost ~]$ sqlplus sys/tiger@orcl as sysdba（本机密码⽂件认证失败）SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 14 19:46:52 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12641: Authentication service failed to initialize
Enter user-name:
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
[oracle@localhost ~]$ sqlplus scott/tiger（普通⽤户本地OS登录成功）
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 14 20:01:57 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
[oracle@localhost ~]$ sqlplus scott/tiger@orcl（登录失败）
[oracle@localhost ~]$ sqlplus scott/t （登录失败）
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 14 20:02:52 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12641: Authentication service failed to initialize
Enter user-name:
密码⽂件管理：
密码⽂件建⽴：orapwd命令⽤法（不建议使⽤）
[oracle@localhost ~]$ orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n>
where
file - name of password file (mand), -->创建密码⽂件名字:orapw<sid>
password - password for SYS (mand), -->sys⽤户密码
entries - maximum number of distinct DBA and -->可以有多少个sysdba和sysoper⽤户可以放到密码⽂件⾥边去（采⽤⼆进制⽅式，即输⼊1表⽰最少存放4个，去除重复的）
force - whether to overwrite existing file (opt), -->oracle 10g后新加的参数,⽤法：force=n或force=y，表⽰密码⽂件存在是否覆盖，10g之前只能删除原有的密码⽂件，再创建。

OPERs (opt),
There are no spaces around the equal-to (=) character.
例如：[oracle@localhost ~]$orapwd file=orapworcl password=rezin entries=1 force=y
密码⽂件修改：例如修改sys⽤户密码或授予sysdba、sysoper权限
orapwd重建密码⽂件：不建议使⽤，可能会让其他sys⽤户不能登录
alter user sys identified by <new password>
grant sysdba|sysoper to <user>;
revoke sysdba|sysoper from <user>
]\[Z
ORACLE Remote Password file
INTERNAL
9D9FF9FDAFB17385
E6BAA2164C375C09
sysdba和sysoper具体区别：查看官⽅⽂档
通过system_privilege_map视图查看系统权限：
SQL> select * from system_privilege_map
2 where name like '%SYS%';
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------------------------------------------------------
-3 ALTER SYSTEM 0
-4 AUDIT SYSTEM 0
-83 SYSDBA 0
-84 SYSOPER 0
查看⽤户系统权限通过密码⽂件视图v$pwfile_user查看：
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
通过以上查询可以知道，sys⽤户登录⽅式既可以通过as sysdba登录schema显⽰‘SYS’，也可以通过as sysoper登录schema显⽰‘PUBLIC’。

LAST验证：需要配合参数⽂件知识练习
1、按照组合：
1）remote_login_passwordfile=none sqlnet.authentication_services=none
2）remote_login_passwordfile=exclusive sqlnet.authentication_services=none
3）remote_login_passwordfile=none sqlnet.authentication_services=all
如果是win，请你把all改为nts
4）remote_login_passwordfile=exclusive sqlnet.authentication_services=all
分别测试：
本机：sqlplus / as sysdba
sqlplus sys/<pswd> as sysdba
sqlplus sys/<pswd>@<sid> as sysdba
远程：sqlplus sys/<pswd>@<sid> as sysdba
sqlplus sys/<pswd>@ip:port/<sid> as sysdba
测试哪些组合可以登录成功，哪些不能登录成功。

总结出如果关闭OS验证；如何关闭密码⽂件验证；如何关闭本地密码⽂件验证；如何关闭远程密码⽂件验证。

3、如果sys密码丢失或不对，你如何做？
alter user sys identified by tiger;修改密码
4、sysdba、sysoper区别在哪，普通⽤户如何使⽤密码⽂件已sysdba或sysoper登录。

答案：
1）remote_login_passwordfile=none sqlnet.authentication_services=none 关闭密码⽂件认证，关闭OS认证。

本机：sqlplus / as sysdba
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 15 08:06:22 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
本机：sqlplus sys/tiger as sysdba
[oracle@localhost dbs]$ sqlplus sys/tiger as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 15 08:14:24 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
本机：sqlplus sys/tiger@orcl as sysdba
[oracle@localhost dbs]$ sqlplus sys/tiger@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 15 08:15:39 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
远程：sqlplus sys/tiger@orcl as sysdba
C:\Users\WCWEN>sqlplus sys/tiger@orcl as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期⽇ 3⽉ 15 00:16:11 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS: ⽆法解析指定的连接标识符
请输⼊⽤户名:
远程：sqlplus sys/tiger@192.168.96.141:1521/orcl as sysdba
ORA-01017: invalid username/password; logon denied
请输⼊⽤户名:
2）remote_login_passwordfile=exclusive sqlnet.authentication_services=none 关闭OS认证，只能使⽤密码⽂件认证，⾃⼰独占使⽤。

本机：sqlplus / as sysdba
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 14 22:38:23 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
本机： sqlplus sys/<pswd> as sysdba
[oracle@localhost dbs]$ sqlplus sys/tiger as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 14 22:39:24 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
本机： sqlplus sys/<pswd>@<sid> as sysdba
[oracle@localhost dbs]$ sqlplus sys/tiger@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 14 22:41:00 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
远程：sqlplus sys/<pswd>@<sid> as sysdba
C:\Users\WCWEN>sqlplus sys/tiger@orcl as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期六 3⽉ 14 14:41:52 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS: ⽆法解析指定的连接标识符
请输⼊⽤户名:
远程： sqlplus sys/<pswd>@ip:port/<sid> as sysdba
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
3）remote_login_passwordfile=none sqlnet.authentication_services=all 关闭密码⽂件认证，采⽤OS认证。

本机：sqlplus / as sysdba
[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 15 08:22:24 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
本机：sqlplus sys/tiger as sysdba
[oracle@localhost dbs]$ sqlplus sys/tiger as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 15 08:23:10 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
本机：sqlplus sys/tiger@orcl as sysdba
[oracle@localhost dbs]$ sqlplus sys/tiger@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 15 08:24:01 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12641: Authentication service failed to initialize
Enter user-name:
远程：sqlplus sys/tiger@orcl as sysdba
C:\Users\WCWEN>sqlplus sys/tiger@orcl as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期⽇ 3⽉ 15 00:24:47 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS: ⽆法解析指定的连接标识符
请输⼊⽤户名:
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
请输⼊⽤户名:
4）remote_login_passwordfile=exclusive sqlnet.authentication_services=all
linux平台关闭本机密码⽂件认证，采⽤OS认证，但是远程（异机）可以密码⽂件认证，⾃⼰独占使⽤。

本机：sqlplus / as sysdba
[oracle@localhost dbs]$ sql / as sysdba
[uniread] Loaded history (12 lines)
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 14 22:57:20 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
本机： sqlplus sys/<pswd> as sysdba
[oracle@localhost dbs]$ sqlplus sys/tiger as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 14 22:59:16 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
本机：sqlplus sys/<pswd>@<sid> as sysdba
[oracle@localhost dbs]$ sqlplus sys/tiger@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 14 23:00:44 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12641: Authentication service failed to initialize
Enter user-name:
远程：sqlplus sys/<pswd>@<sid> as sysdba
ERROR:
ORA-12154: TNS: ⽆法解析指定的连接标识符
请输⼊⽤户名:
远程：sqlplus sys/<pswd>@ip:prot/<sid> as sysdba
C:\Users\WCWEN>sqlplus sys/tiger@192.168.96.141:1521/orcl as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期六 3⽉ 14 15:02:56 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SQL>。