Oracle's password file and remote SYSDBA login

A password file (password files) 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 the password. Oracle uses the operating system to perform this authentication when Oracle is started on the local system. When you install Oracle, you are asked to specify the administrator "group" for the person who completed the completion. On Unix/linux, this group typically defaults to DBA, which defaults to OSDBA on Windows, but can also be any legitimate group name on the platform. This group is special because any user in this group can connect to Oracle as SYSDBA without specifying a user name or password.

[[email protected] ~]# id mysqluid=496 (MySQL)  gid=495 (MySQL)  groups=495 (MySQL), (Oinstall) [[Email protected] ~]# 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: [[email protected] bin]# usermod -g dba mysql[ [email protected] bin]# id mysqluid=496 (MySQL)  gid=495 (MySQL)  groups=495 (MySQL), 501 (dba) [[Email protected] 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 options[email protected]>show useruser is   "SYS"

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

c:\users\victor>sqlplus/@orcl as Sysdbasql*plus:release 12.1.0.1.0 Production on Thu December 21:42:04 2016Copyright (c  ) 1982, Oracle. All rights reserved. Error:ora-01017:invalid Username/password; Logon denied

On the network, the operating system certification for SYSDBA no longer works, even if the very insecure remote_os_authent parameter is set to True. Therefore, the operating system authentication is not OK. So the password file came into being.

The password file holds a list of user names and passwords that correspond to users who are remotely authenticated as SYSDBA over the network. Oracle must use this file to authenticate the user, not the list of normal passwords stored in the database.

The following verifies this situation. First, set Remote_login_passwordfile, there are three values: none, meaning no password file, there is no "remote SYSDBA login", SHARED, multiple databases can use the same password file, EXCLUSIVE, Only one database uses a given password file. This is set to exclusive.

Alter system set remote_login_passwordfile=exclusive Scope=spfile;

Modifying this parameter requires restarting the database.

The

uses ORAPWD to create and fill out this initial password file, which is located in the $oracle_home/dbs directory.

[[Email protected] dbs]$ which orapwd/u02/app/oracle/product/11.2.4/db1/bin/orapwd[[email  protected] dbs]$ orapwdUsage: orapwd file=<fname> entries=<users>  force=<y/n> ignorecase=<y/n> nosysdba=<y/n>  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. [[email protected] dbs]$ pwd/u02/app/oracle/product/11.2.4/db1/dbs[[email protected]  dbs]$ orapwd file=orapw$oracle_sid password=oracle entries=20[[email protected]  Dbs]$ ls -l orapw$oracle_sid-rw-r----- 1 oracle oinstall 3584 dec  15 21:55 orapworcl

There is currently only one user in the file, the user sys, although there are other SYSDBA accounts on the database, but they are not yet in the password file. However, based on the above setup we can connect Oracle as a SYSDBA over the network, even if Oracle is not booting and can remotely start Oracle.

c:\users\victor>sqlplus sys/[email protected] as  sysdbasql*plus: release 12.1.0.1.0 production on  Thursday  12 Month  15 22:00:24  2016Copyright  (c)  1982, 2013, Oracle.  All rights  Reserved. connected to the idle routine. The sql> startuporacle  routine has been started. total system global area  784998400 bytesfixed size                   2257352 bytesvariable  Size             754978360  bytesdatabase buffers           20971520  bytesredo buffers               The   6791168 bytes database is loaded. The database is already open. 

Note: If this step is encountered ORA-12505 "Tns:listener does not currently know of the SID given in Connect descriptor" indicates that there is no static listener configured for the DB instance.

Create a password file, then we can look at the password file in the end what is recorded in it, will we leak the password?

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

[Email protected] dbs]$ strings orapworcl]\[zoracle Remote Password fileinternalab27b53edc5fef418a8f025737a9097amhd2

From the output it can be seen that the password file does not use clear text to record our password, but some serial code is recorded.

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 ()  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) [email protected]>select * from v$pwfile_users; username   sysdba   sysoper    Sysasm------------------------------------------------------------------------------------------ -------- ------- --------------- ---------------sys   true    true     false--to the user ZX give SYSDBA permissions can see v$pwfile_users more a record, and password file ORAPWORCL also a line of string code. [email protected]>grant sysdba to zx; Grant succeeded. [email protected]>select * from v$pwfile_users; username   sysdba   sysoper    Sysasm------------------------------------------------------------------------------------------ -------- ------- --------------- ---------------sys   true    true     falsezx   true    false   false[email  protected]>!strings /u02/app/oracle/product/11.2.4/db1/dbs/orapworcl]\[zoracle  remote password fileinternalab27b53edc5fef418a8f025737a9097amhd27b06550956254585--gives the user the Sysoper permission to ZX, You can see that V$pwfile_users's ZX Line state has changed, but ORAPWORCL has not changed [email protected]>grant sysoper to zx; Grant succeeded. [email protected]>select * from v$pwfile_users; username   sysdba   sysoper    Sysasm------------------------------------------------------------------------------------------ -------- ------- --------------- ---------------sys   true    true     falsezx   true    true    false[email  protected]>!strings /u02/app/oracle/product/11.2.4/db1/dbs/orapworcl]\[ZORACLE Remote  password fileinternalab27b53edc5fef418a8f025737a9097amhd27b06550956254585--Remove the password file and move back, remove the password file V$pwfile_ Users become empty, and v$pwfile_users are logged after they are moved back. [email protected]>! mv /u02/app/oracle/product/11.2.4/db1/dbs/orapworcl /u02/app/oracle/product/11.2.4/db1/dbs/orapworcl_orcl[ email protected]>select * from v$pwfile_users;no rows selected[email  protected]>! mv /u02/app/oracle/product/11.2.4/db1/dbs/orapworcl_orcl /u02/app/oracle/ product/11.2.4/db1/dbs/orapworcl[email protected]>select * from v$pwfile_users; username   sysdba   sysoper    Sysasm------------------------------------------------------------------------------------------ -------- ------- --------------- ---------------sys   true    true     FALSEZX   TRUE    TRUE     false--Test ZX User remotely with SYSDBA login C:\users\victor>sqlplus zx/[email protected] as sysdbasql*plus:  Release 12.1.0.1.0 Production on  Thursday  12 Month  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  for   "SYS"--Reclaim user ZX Sysdba and Sysoper permissions, V$pwfile_users in the ZX Record Line no, password file ORAPWORCL no change [email  protected]>revoke sysdba,sysoper from zx; Revoke succeeded. [email protected]>select * from v$pwfile_users; username   sysdba   sysoper    Sysasm------------------------------------------------------------------------------------------ -------- ------- --------------- ---------------sys   true    true     false[email protected]>!strings /u02/app/oracle/product/11.2.4/db1/dbs/orapworcl]\[zoracle remote password  fileinternalab27b53edc5fef418a8f025737a9097amhd27b06550956254585--re-test the ZX user to log in remotely with SYSDBA and cannot log in now C:\Users\victor >sqlplus zx/[email protected] as sysdbaSQL*Plus: Release 12.1.0.1.0  production on  Thursday  12 Month  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

"9I10G11G programming art in-depth database architecture"

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1883180

Oracle's password file and remote SYSDBA login