Oracle OS authentication and password file Authentication

Environment
Windows2000 + SP4, oracle9.201
Oracle software is installed with an oracle account

1. sysdba Permissions
Sysdba (sysoper and normal) is the identity for logging on to the database, which is different from role (dBA, resource), object permission, and system permission, the logon identity indicates which operations can be performed on the database as a whole. sysdba is similar to the root account in UNIX.
Sysdba permissions or database operations:
Startup, shutdown
Alter database open | Mount
Alter Database Backup controlfile
Alter tablespace begin/end backup
Recover Database
Alter database archivelog, restricted session
Create Database
Recover database

Ii. OS authentication and password file Authentication

1. The essence of OS authentication and password file authentication is the management method for Oracle databases, whether it is local management or unified management through a management server.
Local management adopts OS authentication, while unified management adopts Password File authentication.
2. Implementation of two authentication methods
The Oracle database uses the parameters sqlnet. authentication_services in the sqlnet. ora file, remote_login_passwordfile in the pfile (or spfile) file, to implement identity authentication.
 
Sqlnet. authentication_services = (CNT) | (none)
Sqlnet. authentication_services = (CNT): operating system authentication method, without using a password file
Sqlnet. authentication_services = (none): Password File authentication method
 
Remote_login_passwordfile = (none) | (exclusive) | (shared)
Remote_login_passwordfile = (none): the password file is not used for operating system authentication.
Remote_login_passwordfile = (exclusive): Password File authentication method, but only one database instance can use this file,
The system allows sysoper/sysdba to be granted to other users except internal/sys, And it is valid to log on to other users with such identities.
Remote_login_passwordfile = (shared): Password File authentication method. This file can be used by multiple database instances, but under this setting
Only the internal/sys account can be identified. Even if the file contains information from other users, they are not allowed to log on with sysoper/sysdba.
 
1) sqlnet. authentication_services = (ETS) remote_login_passwordfile = (none) at the same time, which is the operating system authentication method.
After logging on to the local Windows2000 as a user in the oracle_dba group, perform the following operations:
Sqlplus/nolog
SQL> Conn/As sysdba
Or
Sqlplus/nolog
SQL> conn any user name/password as sysdba
You can log on successfully as sysdba to perform database operations.
Run
Sqlplus/nolog
SQL> Conn/As sysdba
Or
Sqlplus/nolog
SQL> conn sys/password as sysdba
Both display
"Error:
ORA-01031: insufficient privileges
"
That is, remote logon to the system as sysdba is not allowed. This is also the reason why OS authentication is also called local authentication.
 
2) sqlnet. authentication_services = (none) remote_login_passwordfile = (exclusive) or (shared) at the same time, with the password file
Pwdsid. ora, which is the password file authentication method.
After logging on to Windows2000 as a user in the oracle_dba group locally, perform the following operations:
Sqlplus/nolog
SQL> Conn/As sysdba
Display
"Error:
ORA-01031: insufficient privileges
"
In essence, the user name and password with sysdba identity are required.
Perform the following operations locally or remotely
Sqlplus "sys/password @ service name as sysdba"
Accessible System
That is to say, the password file authentication method allows users to log on as sysdba locally or remotely, but the password must be provided
 
3) sqlnet. authentication_services = (ETS) remote_login_passwordfile = (exclusive) or (shared) at the same time, combined with the password file pwdsid. ora, operating system authentication and password file authentication work at the same time
After logging on to Windows2000 as a user in the oracle_dba group locally, perform the following operations:
Sqlplus/nolog
SQL> Conn/As sysdba
Accessible System
When executed remotely
Sqlplus "sys/password @ service name as sysdba"
You can also log on to the database system normally.
The preceding parameter configurations are confusing and confusing, resulting in an illusion. I guess some friends on the Internet may be confused about identity authentication.
Configure the parameter!

Iii. Others
 
From the previous discussions, we can know that we can assign sysdba identities to users other than SYS. The specific method is
Sqlnet. authentication_services = (none)
Remote_login_passwordfile = (exclusive)
Password File pwdsid. ora
SQL> grant sysdba to user name
In this way, other users with sysdba identities are added to pwdsid. ora, and can be pwdsid. ora identification, we can use this user granted sysdba identity to log on and perform operations similar to those performed by sys users.