1 --============================== 2 -- Oracle密码文件 3 --============================== 4 /* 5 一、密码文件 6 作用：主要进行DBA权限的身份认证 7 DBA用户：具有sysdba，sysoper权限的用户被称为dba用户。默认情况下sysdba角色中存在sys用户，sysoper角色中存在system用户 8 9 二、Oracle的两种认证方式； 10 1.使用与操作系统集成的身份验证 11 2.使用Oracle数据库的密码文件进行身份认证 12 13 三、密码文件的位置 14 Linux下的存放位置：$ORACLE_HOME/dbs/orapw$ORACLE_SID 15 即：ORACLE_HOME/dbs/orapw<sid> 16 Windows下的存放位置:$ORACLE_HOME/database/PWD%ORACLE_SID%.ora 17 18 密码文件查找的顺序 19 --->orapw<sid>--->orapw--->Failure 20 21 两种认证方式：类似于SQL server中的windows认证和SQL server认证 22 决定在两个参数中 23 1.remote_login_passwordfile = none | exclusive |shared 位于$ORACLE_HOME/dbs/spfile$ORACLE_SID.ora参数文件中 24 none : 不使用密码文件认证 25 exclusive :要密码文件认证，自己独占使用(默认值) 26 shared ：要密码文件认证，不同实例dba用户可以共享密码文件 27 28 2. $ORACLE_HOME/network/admin/sqlnet.ora 29 SQLNET.AUTHENTICATION_SERVICES = none | all | ntf(windows) 30 none : 表示关闭操作系统认证，只能密码认证 31 all : 用于linux或unix平台，关闭本机密码文件认证，采用操作系统认证，但远程<异机>可以使用密码文件认证 32 nts : 用于windows平台 33 34 不同的组合 35 1 2 36 none none sys用户无论是本机还是远程均不可用 37 38 判断当前使用的是操作系统认证还是密码认证 39 40 四、演示： 41 1.在sqlnet.ora中追加SQLNET.AUTHENTICATION_SERVICES = none */ 42 [oracle@robinson ~]$ sqlplus / as sysdba /*登陆失败*/ 43 44 SQL*Plus: Release 10.2.0.1.0- Production on Fri Apr 9 10:41:28 2010 45 46 Copyright (c) 1982, 2005, Oracle. All rights reserved. 47 48 ERROR: 49 ORA-01031: insufficient privileges 50 Enter user-name: 51 -------------------------------------------------------------------------------- 52 [oracle@robinson ~]$ sqlplus sys/redhat as sysdba /*使用密码文件认证，登陆成功*/ 53 54 SQL*Plus: Release 10.2.0.1.0- Production on Fri Apr 9 10:42:35 2010 55 56 Copyright (c) 1982, 2005, Oracle. All rights reserved. 57 58 59 Connected to: 60 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0- Production 61 With the Partitioning, OLAPand Data Mining options 62 63 SQL> 64 --================================================================================= 65 66 2.将SQLNET.AUTHENTICATION_SERVICES的值改为all 67 68 [oracle@robinson admin]$ sqlplus / as sysdba /*采用本机认证可以登陆*/ 69 70 SQL*Plus: Release 10.2.0.1.0- Production on Fri Apr 9 10:46:55 2010 71 72 Copyright (c) 1982, 2005, Oracle. All rights reserved. 73 74 75 Connected to: 76 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0- Production 77 With the Partitioning, OLAPand Data Mining options 78 79 SQL> 80 -------------------------------------------------------------------------------------- 81 [oracle@robinson admin]$ sqlplus sys/redhat@orclas sysdba /*使用密码文件登陆认证失败*/ 82 83 SQL*Plus: Release 10.2.0.1.0- Production on Fri Apr 9 10:48:35 2010 84 85 Copyright (c) 1982, 2005, Oracle. All rights reserved. 86 87 ERROR: 88 ORA-12641: Authenticationservice failed to initialize 89 90 Enter user-name: 91 92 --注：此时可以使用远程登陆。 93 94 --使用#符号将新增的SQLNET.AUTHENTICATION_SERVICES行注释掉恢复到缺省值 95 96 /* 97 五、密码文件的建立：orapwd */ 98 [oracle@robinson ~]$ orapwd 99 Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n> 100 101 where 102 file - name of password file (mand), /*密码文件的名字orapw<sid>*/ 103 password - password for SYS (mand), /*sys用户的密码*/ 104 entries - maximum number of distinct DBA and /*可以有多少个sysdba,sysoper权限用户放到密码文件中去，去掉重复记录*/ 105 /*注意entries中存放的个数但不是实际个数，这个是二进制数据*/ 106 force - whether to overwrite existingfile (opt),/*10g新增的参数，默认值为n ,y表示允许覆盖*/ 107 OPERs (opt), 108 There are no spaces around the equal-to(=) character. 109 110 --修改密码： 111 [oracle@robinson ~]$ cd $ORACLE_HOME/dbs 112 [oracle@robinson dbs]$ ll orapworcl 113 -rw-r----- 1 oracle oinstall 1536 Apr 7 15:50 orapworcl 114 [oracle@robinson dbs]$ orapwd file=orapworcl password=oracle force=y 115 [oracle@robinson dbs]$ sqlplus sys/oracle@orclas sysdba 116 117 SQL*Plus: Release 10.2.0.1.0- Production on Fri Apr 9 11:34:09 2010 118 119 Copyright (c) 1982, 2005, Oracle. All rights reserved. 120 121 122 Connected to: 123 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0- Production 124 With the Partitioning, OLAPand Data Mining options 125 126 SQL> 127 --将密码改回原来的密码 128 [oracle@robinson dbs]$ orapwd file=orapworcl password=redhat 129 130 OPW-00005:File with same name exists - pleasedelete or rename 131 [oracle@robinson dbs]$ orapwd file=orapworcl password=redhat force=y 132 [oracle@robinson dbs]$ rm orapworcl /*删除密码文件*/ 133 [oracle@robinson dbs]$ orapwd file=orapworcl password=redhat/*重建密码文件*/ 134 135 --演示将entries改为，然后将多个用户设置为sysdba或sysoper 136 [oracle@robinson dbs]$ orapwd file=orapworcl password=redhat entries=1 137 [oracle@robinson dbs]$ strings orapworcl 138 ]/[Z 139 ORACLE Remote Password file 140 INTERNAL 141 F7AC0C5E9C3C37AB 142 E100B964899CDDDF 143 144 --创建PL/SQL增加个新用户 145 SQL> begin 146 2 for i in 1..20 loop 147 3 execute immediate'create user u'||i||' identified by u'||i||''; 148 4 end loop; 149 5 end; 150 6 / 151 --将新用户赋予sysdba角色 152 PL/SQL procedure successfully completed. 153 154 SQL> begin 155 2 for i in 1..20 loop 156 3 execute immediate'grant sysdba to u'||i||''; 157 4 end loop; 158 5 end; 159 6 / 160 begin /*得到和密码文件相关的错误提示*/ 161 * 162 ERROR at line 1: 163 ORA-01996:GRANT failed: passwordfile '' is full 164 ORA-06512: at line 3 165 166 --再次查看orapworcl发现多出了行，即当设置为的时候多出了个用户。原因是该密码文件是二进制文件，按矩阵计算可存放多少 167 [oracle@robinson dbs]$ strings orapworcl 168 ]/[Z 169 ORACLE Remote Password file 170 INTERNAL 171 F7AC0C5E9C3C37AB 172 E100B964899CDDDF 173 3E81B724A296E296 174 668509DF9DD36B43 175 9CE6AF1E3F609FFC 176 7E19965085C9ED47 177 178 179 --注意不要轻易删掉密码文件，这样会将其他账户的信息也删除 180 181 /* 182 六、导致密码文件内容修改的几种方式： 183 1.使用orapwd建立，修改密码文件，不建议使用 184 2.使用alter user sys identified by <> 185 3.使用grant sysdba to <>或grant sysoper to <>或revoke sysdba |sysoper from <> 186 187 七、查看密码文件内容 */ 188 189 [oracle@robinson dbs]$ strings orapworcl 190 ]/[Z 191 ORACLE Remote Password file 192 INTERNAL 193 F7AC0C5E9C3C37AB 194 E100B964899CDDDF 195 196 --当sys密码不记得可以使用OS系统身份认证登陆到sqlplus,再使用alter user修改密码 197 SQL> alteruser sys identified by oracle; 198 199 User altered 200 --再次查看密码文件与上一次对比，已经发生变化 201 SQL> ho strings orapworcl 202 ]/[Z 203 ORACLE Remote Password file 204 INTERNAL 205 AB27B53EDC5FEF41 206 8A8F025737A9097A 207 208 209 --通过授予权限来修改密码，密码文件中多出了scott的信息 210 SQL> grant sysdbato scott; 211 212 Grant succeeded. 213 214 SQL> ho strings orapworcl 215 ]/[Z 216 ORACLE Remote Password file 217 INTERNAL 218 AB27B53EDC5FEF41 219 8A8F025737A9097A 220 SCOTT 221 F894844C34402B67 222 223 224 --注意此处中登陆后，显示的账户信息还是sys,而不是scott,但此时的scott已经具备了sys权限 225 [oracle@robinson dbs]$ sqlplus scott/tiger@orclas sysdba 226 227 SQL*Plus: Release 10.2.0.1.0- Production on Fri Apr 9 11:56:09 2010 228 229 Copyright (c) 1982, 2005, Oracle. All rights reserved. 230 231 232 Connected to: 233 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0- Production 234 With the Partitioning, OLAPand Data Mining options 235 236 SQL> show user 237 USER is "SYS" 238 239 /* 240 八、sysdba与sysoper的区别*/ 241 SQL> select* from system_privilege_mapwhere name like '%SYS%'; 242 243 PRIVILEGE NAME PROPERTY 244 ---------- ---------------------------------------- ---------- 245 -3 ALTER SYSTEM 0 246 -4 AUDIT SYSTEM 0 247 -83 SYSDBA 0 248 -84 SYSOPER 0 249 250 --下面的链接是两者不同的权限说明 251 http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dba.htm#sthref137 252 The manner in which you are authorized to use these privileges depends upon the methodof authentication that you use. 253 254 When you connect with SYSDBA or SYSOPER privileges, you connectwith a default schema, not with theschema that is 255 generally associatedwith your username. 256 For SYSDBA this schemais SYS; for SYSOPER the schema is PUBLIC. 257 --两者的schema不同 258 SQL> show user 259 USER is "SYS" 260 SQL> conn /as sysoper 261 Connected. 262 SQL> show user 263 USER is "PUBLIC" 264 265 --查看密码文件视图，可以得到哪些用户为sysdba,哪些用户为sysoper 266 SQL> select* from v$pwfile_users; 267 268 USERNAME SYSDB SYSOP 269 ------------------------------ ----- ----- 270 SYS TRUE TRUE 271 SCOTT TRUE FALSE 272 USER1 FALSE TRUE 273 --下面演示了使用不同的角色来登陆 274 SQL> conn scott/tiger@orclas sysdba 275 Connected. 276 SQL> conn scott/tiger@orclas sysoper /*scott的sysop列为false*/ 277 ERROR: 278 ORA-01031: insufficient privileges 279 280 281 Warning: You are no longer connected to ORACLE. 282 SQL> conn user1/user1@orclas sysdba 283 ERROR: 284 ORA-01031: insufficient privileges 285 286 287 SQL> conn user1/user1as sysoper 288 Connected. 289 /*