Detailed explanation of Oracle login authentication

Oracle has three login authentication methods

1. OS certification

Log in to oracle database operating system and must belong to dba group. If the user logs in as sysdba, no password is required;

`[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 1 22:28:46 2018

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user;

USER is "SYS"

SQL> **2. Password file authentication ** is a remote authentication method through the network. Only users with sysdba permission can use password files; password files: [oracle@oracle11g dbs]$ ll

total 28

-rw-rw---- 1 oracle oinstall 1544 Jan 1 22:28 hc_orcl.dat

-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-r--r-- 1 oracle oinstall 1062 Dec 31 23:12 initorcl.ora

-rw-r----- 1 oracle oinstall 24 Sep 17 20:25 lkORCL

-rw-r---- 1 oracle oinstall 1536 Dec 31 13:44 orapworcl--password file

-rw-r----- 1 oracle oinstall 1536 Oct 15 12:07 orapworcl.bak

-rw-r----- 1 oracle oinstall 3584 Jan 1 22:28 spfileorcl.ora

[oracle@oracle11g dbs]$ `

Login as sysdba: sqlplus sys/oracle@orcl as sysdba

3. Password authentication

It refers to an ordinary user who is not a sysdba user logging in to the database. It must be logged in when the database is open, because the password of an ordinary user is saved in the data dictionary.

If you forget the sys user password, you can log in to the operating system directly and modify sqlplus / as sysdba.

SQL> alter user sys identified by "niceman";User altered.

Forcing password file regeneration

orapwd file=orapw+sid password=oracle force=y

spfile/pfile parameter file remote_login_passwordfile parameter description

Three setting modes: (You can view the current mode by using the show parameter remote command)

1)remote_login_passwordfile = EXCLUSIVE, To authenticate the password file, use it exclusively (default)

2)remote_login_passwordfile = SHARE, To authenticate password files, different instances of dba users can share password files

3)remote_login_passwordfile = NONE, do not make password file authentication, need to pass the operating system authentication, that is, privileged users of the database can only log in from the operating system

remote_login_passwordfile is a static parameter. After modification, it needs to be restarted to take effect.

Related settings in sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES = none | all | ntf(windows)

none: indicates that operating system authentication is turned off and only password authentication can be performed

all : for linux or unix platforms, turn off local password file authentication, use operating system authentication, but remote can use password file authentication

nts : for windows platforms