Shulou(Shulou.com)06/01 Report--

The password file (password file) is an optional file that allows remote SYSDBA or administrators to access the database.

When you start Oracle, there is no database available to validate passwords. When Oracle is started on the local system, Oracle leverages the operating system to perform this authentication. When you install Oracle, the person who finishes it is asked to specify an administrator "group". On Unix/Linux, this group generally defaults to dba, and on Windows it defaults to OSDBA, but it can also be any legal group name on the platform. This group is special because any user in this group can connect to Oracle as a SYSDBA without specifying a user name or password.

[root@rhel6 ~] # id mysqluid=496 (mysql) gid=495 (mysql) groups=495 (mysql), 500 (oinstall) [root@rhel6 ~] # su-mysql-bash-4.1$ export ORACLE_HOME=/u02/app/oracle/product/11.2.4/db1-bash-4.1$ export ORACLE_SID=orcl-bash-4.1$ cd $ORACLE_HOME/bin-bash-4.1 $. / sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 15 21:32:05 2016Copyright (c) 1982, 2013, Oracle. All rights reserved.ERROR:ORA-01017: invalid username/password; logon deniedEnter user-name: ^ C-bash-4.1$ suPassword: [root@rhel6 bin] # usermod-G dba mysql [root@rhel6 bin] # id mysqluid=496 (mysql) gid=495 (mysql) groups=495 (mysql), 501 (dba) [root@rhel6 bin] # exitexit-bash-4.1 $. / sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 15 21:32:36 2016Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionssys@ORCL > show userUSER is "SYS"

You can now connect to the database for administrative work, or start and shut down the database. What if you want to do this over the network from another machine? Next I use the @ connection string to connect:

C:\ Users\ victor > sqlplus / @ orcl as sysdbaSQL*Plus: Release 12.1.0.1.0 Production on Thursday December 15 21:42:04 2016Copyright (c) 1982, 2013, Oracle. All rights reserved.ERROR:ORA-01017: invalid username/password; logon denied

Operating system authentication for SYSDBA no longer works on the network, even if the insecure REMOTE_OS_AUTHENT parameter is set to TRUE. So operating system authentication is not feasible. So the password file came into being.

The password file holds a list of usernames and passwords that correspond to users who have been remotely authenticated as SYSDBA over the network. Oracle must use this file to authenticate users instead of the list of normal passwords stored in the database.

Let's verify this situation. First, set REMOTE_LOGIN_PASSWORDFILE with three values: NONE, which means there is no password file, no remote SYSDBA login, SHARED, multiple databases can use the same password file, EXCLUSIVE, and only one database uses a given password file. This is set to EXCLUSIVE.

Alter system set remote_login_passwordfile=exclusive scope=spfile

Changing this parameter requires a database restart.

Create and fill in this initial password file using orapwd, which is located in the $ORACLE_HOME/dbs directory.

[oracle@rhel6 dbs] $which orapwd/u02/app/oracle/product/11.2.4/db1/bin/orapwd [oracle@rhel6 dbs] $orapwdUsage: orapwd file= entries= force= ignorecase= nosysdba= where file-name of password file (required), password-password for SYS will be prompted if not specified at command line, entries-maximum number of distinct DBA (optional), force-whether to overwrite existing file (optional), ignorecase-passwords are case-insensitive (optional), nosysdba-whether to shut out the SYSDBA logon (optional Database Vault only). There must be no spaces around the equal-to (=) character. [oracle@rhel6 dbs] $pwd/u02/app/oracle/product/11.2.4/db1/dbs [oracle@rhel6 dbs] $orapwd file=orapw$ORACLE_SID password=oracle entries=20 [oracle@rhel6 dbs] $ls-l orapw$ORACLE_SID-rw-r- 1 oracle oinstall 3584 Dec 15 21:55 orapworcl

Currently, there is only one user in the file, user SYS, and although there are other SYSDBA accounts in the database, they are not in the password file. However, based on the above settings, we can connect to Oracle over the network as SYSDBA, even if Oracle is not started and Oracle can be started remotely.

C:\ Users\ victor > sqlplus sys/oracle@orcl as sysdbaSQL*Plus: Release 12.1.0.1.0 Production on Thursday December 15 22:00:24 2016Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle routine. The SQL > startupORACLE routine has been started. The Total System Global Area 784998400 bytesFixed Size 2257352 bytesVariable Size 754978360 bytesDatabase Buffers 20971520 bytesRedo Buffers 6791168 bytes database is loaded. The database is already open.

Note: if ORA-12505 "TNS:listener does not currently know of SID given in connect descriptor" is encountered in this step above, static listening for the database instance is not configured.

After creating the password file, can we see what is recorded in the password file and whether our password will be leaked?

The password file is a binary file that cannot be viewed directly, and can be viewed using the strings command on Linux

[oracle@rhel6 dbs] $strings orapworcl]\ [ZORACLE Remote Password fileINTERNALAB27B53EDC5FEF418A8F025737A9097AmHD2

From the output, we can see that the password file did not record our password in clear text, but recorded some string codes.

In fact, this password file also has some relationship with a view v$pwfile_users in the database.

V$PWFILE_USERS lists all users in the password file, and indicates whether the user has been granted the SYSDBA, SYSOPER, and SYSASM privileges.USERNAME VARCHAR2 (30) Name of the user that is contained in the password fileSYSDBA VARCHAR2 (5) Indicates whether the user can connect with SYSDBA privileges (TRUE) or not (FALSE) SYSOPER VARCHAR2 (5) Indicates whether the user can connect with SYSOPER privileges (TRUE) or not (FALSE) SYSASM VARCHAR2 (5) Indicates whether the user can connect with SYSASM privileges (TRUE) or not (FALSE) sys@ORCL > select * from v$pwfile_users USERNAME SYSDBA SYSOPER SYSASM- -SYS TRUE TRUE FALSE-- gives SYSDBA permission to user zx to see that v$pwfile_users has one more record. And the password file orapworcl also has an extra line of string code. Sys@ORCL > grant sysdba to zx;Grant succeeded.sys@ORCL > select * from v$pwfile_users USERNAME SYSDBA SYSOPER SYSASM- -SYS TRUE TRUE FALSEZX TRUE FALSE FALSEsys@ORCL >! strings / u02/app/oracle/product/11.2.4/db1/dbs/orapworcl]\ [ZORACLE Remote Password fileINTERNALAB27B53EDC5FEF418A8F025737A9097AmHD27B06550956254585-- assigns SYSOPER permissions to user zx You can see that the zx line state of v$pwfile_users has changed, but orapworcl has not changed sys@ORCL > grant sysoper to zx Grant succeeded.sys@ORCL > select * from v$pwfile_users USERNAME SYSDBA SYSOPER SYSASM- -SYS TRUE TRUE FALSEZX TRUE TRUE FALSEsys@ORCL >! strings / u02/app/oracle/product/11.2.4/db1/dbs/orapworcl]\ [ZORACLE Remote Password fileINTERNALAB27B53EDC5FEF418A8F025737A9097AmHD27B06550956254585-- removes the password file and moves it back The v$pwfile_users becomes empty after the password file is removed, and the v$pwfile_users has a record after it is moved back. Sys@ORCL >! Mv / u02/app/oracle/product/11.2.4/db1/dbs/orapworcl / u02/app/oracle/product/11.2.4/db1/dbs/orapworcl_orclsys@ORCL > select * from vaulted pwfilededicated userswitting no rows selectedsys@ORCL >! Mv / u02/app/oracle/product/11.2.4/db1/dbs/orapworcl_orcl / u02/app/oracle/product/11.2.4/db1/dbs/orapworclsys@ORCL > select * from v$pwfile_users USERNAME SYSDBA SYSOPER SYSASM- -SYS TRUE TRUE FALSEZX TRUE TRUE FALSE-- Test zx users remotely log in to C:\ Users\ victor > sqlplus zx/zx@orcl as sysdbaSQL*Plus: Release 12.1.0.1.0 Production on Thursday December 15 22:34:09 2016Copyright (c) 1982 2013, Oracle. All rights reserved. Connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL > show user;USER is "SYS"-reclaim the SYSDBA and SYSOPER permissions of user zx, the zx record line in v$pwfile_users is gone, and the password file orapworcl remains unchanged sys@ORCL > revoke sysdba,sysoper from zx;Revoke succeeded.sys@ORCL > select * from v$pwfile_users USERNAME SYSDBA SYSOPER SYSASM- -SYS TRUE TRUE FALSEsys@ORCL >! strings / u02/app/oracle/product/11.2.4/db1/dbs/orapworcl]\ [ZORACLE Remote Password fileINTERNALAB27B53EDC5FEF418A8F025737A9097AmHD27B06550956254585-- tests zx users to log in remotely as SYSDBA Unable to log in to C:\ Users\ victor > sqlplus zx/zx@orcl as sysdbaSQL*Plus: Release 12.1.0.1.0 Production on Thursday December 15 22:35:17 2016Copyright (c) 1982, 2013, Oracle. All rights reserved.ERROR:ORA-01017: invalid username/password; logon denied

Reference: http://www.xifenfei.com/2011/12/vpwfile_users%E5%92%8C%E5%AF%86%E7%A0%81%E6%96%87%E4%BB%B6%E5%85%B3%E7%B3%BB.html

"the Art of 9i10g11g programming goes deep into database architecture"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.