oracle passwordfile作用 &11g改进

文件位置在
主要用作sysdba远程登陆
[oracle@vm11g ~]$ sqlplus / as sysdba 这个是操作系统验证 不需要password file

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Apr 14 06:58:57 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

cd $ORACLE_HOME/dbs

[oracle@vm11g dbs]$ ls orapw*
orapwxh

[oracle@vm11g dbs]$ more orapwxh

ORACLE Remote Password file

文件中存放
sysdba/sysoper 用户的用户名和密码

与之有关系的数据库参数
remote_login_passwordfile   
说明: 指定操作系统或一个文件是否检查具有权限的用户的口令。如果设置为 NONE, Oracle 将忽略口令文件。如果设置为EXCLUSIVE, 将使用数据库的口令文件对每个具有权限的用户进行验证。如果设置为 SHARED, 多个数据库将共享
SYS 和 INTERNAL 口令文件用户。
简单理解就是空驶sysdba/sysoper远程登陆的（他们有down,startup  database的最高权利）
NODE就是不允许远程sysdba/sysoper 登陆

 

远程sysdba 登陆
SQL> show parameter remote_login_pass

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL>

SQL> conn / as sysdba
Connected.
SQL> grant sysdba to xh;

Grant succeeded.

 

sqlplus xh/a123@test as sysdba（远程登陆 就需要password file了）
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 25 22:00:06 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"

 

SQL> alter system set remote_login_passwordfile=NONE scope=spfile;

System altered.

SQL> startup force

SQL> show parameter remote_log

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      NONE

 

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options (with complications)
[oracle@ora10g ~]$ sqlplus xh/a123@test as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 25 21:56:48 2010

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

ERROR:
ORA-01017: invalid username/password; logon denied

none就不允许远程sysdba登陆了

 

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

SQL> startup force

[oracle@vm11g dbs]$ rm -rf orapwxh
[oracle@vm11g dbs]$

SQL> select * from v$pwfile_users;（11g server）

no rows selected

[oracle@ora10g ~]$ sqlplus xh/a123@test as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 25 22:02:43 2010

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

ERROR:
ORA-01031: insufficient privileges

Enter user-name:

重新建立passwordfile
[oracle@vm11g dbs]$ orapwd
Usage: orapwd file= password= entries= force= ignorecase= nosysdba=

  where
    file - name of password file (required),
    password - password for SYS (optional),~~user sys password
    entries - maximum number of distinct DBA (required),
    force - whether to overwrite existing file (optional),~~~~~~~~~覆盖原有的passwd file
    ignorecase - passwords are case-insensitive (optional),~~~是否区分密码中大小写(11g新增,默认区分大小写，=y后不区分了)
    nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only). 与DATABASE VAULT 有关系若为true 就等于VAULT中禁止了SYSDBA权限通过密码文件验证方式登陆数据库
   There must be no spaces around the equal-to (=) character.
[oracle@vm11g dbs]$

  entries： passwordfile中可以存放的最大用户数，对应于允许以SYSDBA/SYSOPER权限登录数据库的最大用户数，如果用户数超过这个值只能重建口令文件，增大entries

[oracle@vm11g dbs]$ orapwd file=orapwxh password=a123456 ignorecase=y

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

 

[oracle@ora10g ~]$ sqlplus xh/a123@test as sysdba（client）

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 25 22:23:25 2010

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

ERROR:
ORA-01031: insufficient privileges

 

SQL> grant sysdba to xh;（server 由于新建立的里面只有用户sys,其他的还得手工grant sysdba添加进去）

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XH                             TRUE  FALSE FALSE

[oracle@ora10g ~]$ sqlplus xh/A123@test as sysdba    (不区分大小写了)

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 25 22:24:36 2010

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> show user
USER is "SYS"

 

关于remote_login_passwordfile = shared 意思就是同一个机器上的db可以共享同一个 password file,不允许grant sysdba to xx了）另外刚才的orapwxh已经无法共享受因为格式是orapwd
所以要建立的名字为orapw就可以共享了（另外orapw也可以被其他exclusive的db共享，但密码文件里除去sys用户不能有其他user）

SQL> alter system set remote_login_passwordfile=shared scope=spfile;

System altered.

SQL> startup force

SQL> grant sysdba to zz;
grant sysdba to zz
*
ERROR at line 1:
ORA-01999: password file cannot be updated in SHARED mode

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
XH                             TRUE  FALSE FALSE

 

 

#另外补充在rac中修改拥有sys用户密码，都会修改orapw 但这个是在本地disk上，所以不会修改其它node，解决就是每个node都修改,或者password放在共享存储上,ln从本地到共享存储

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-661623/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-661623/