Description of SQLNET. AUTHENTICATION_SERVICES authentication method, sqlnet. ora
Today, I went to the customer's site for inspection. The customer proposed that the sys/system password should be modified to improve database security, and the default Operating system mode should be replaced by database verification, now I will summarize the two verification methods.
Operating system verification means that the database is accessed through the permissions of the operating system account. For example, if you already have the permissions of the system administrator in windows, if you use this authentication method, you can access the database without entering the user/password, for example, sqlplus/as sysdba. It doesn't matter if you enter any user name or password, for example, sqlplus abc/efg as sysdba;
C: \ Documents ents and Settings \ Administrator> sqlplus/as sysdba
SQL * Plus: Release 10.2.0.1.0-Production on Thursday August 28 21:02:25 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
From Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options disconnected
C: \ Documents ents and Settings \ Administrator> sqlplus abc/efg as sysdba
SQL * Plus: Release 10.2.0.1.0-Production on Thursday August 28 21:02:33 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
SQL>
Of course, this is only for sys users. For other users, even system users, you must enter the user name and password to access the database.
C: \ Documents ents and Settings \ Administrator> sqlplus system/123456
SQL * Plus: Release 10.2.0.1.0-Production on Thursday August 28 21:05:07 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; login denied
Enter the User Name:
In my environment, the passwords of sys and system users are all "oracle" by default. The passwords of Database sys users are stored in PWD <SID>. in ora (oraPW in Unix/Linux <SID>), you can use the following statement to check whether the sys user has a password:
SQL> select * from v $ pwfile_users;
USERNAME SYSDB SYSOP
----------------------------------------
SYS TRUE
Obviously, there are still some security risks in accessing the database through operating system verification. Generally, in large companies, both SA and DBA are managed by authority, if the database adopts the operating system authentication method, and the SA can access the database at will, it is not appropriate. Based on this scenario, we can use the database verification method to restrict sys users to access the database only by entering the correct user name and password. In this way, when SA does not have the DBA's sys password, it will be unable to access the database.
In windows, the authentication mechanism is controlled through the sqlnet. ora file. When you have installed the database and used netca to create a listener, the file is generated. The following content is usually used by default:
# This file is actually generated by netca. But if MERs choose
# Install "Software Only", this file won't exist and without the native
# Authentication, they will not be able to connect to the database on NT.
SQLNET. AUTHENTICATION_SERVICES = (CNT)
That is to say, operating system verification is adopted by default. Oracle may think that the system has the highest permission if it has the SA permission. Therefore, the database DBA's sys user does not need to verify the password any more.
If it is set to (NONE), it indicates that database authentication is used, and you can also set it to (NONE, CNT). In fact, this setting does not make sense, as long as there is an ETS in the brackets, the operating system verification will be adopted, and NONE will be useless.
It should be pointed out that some articles can also be set to (ALL), but after I design and test it, if it is set to (ALL), an ERROR will be prompted:
C: \ Documents ents and Settings \ Administrator> sqlplus/as sysdba
SQL * Plus: Release 10.2.0.1.0-Production on Thursday August 28 21:26:06 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
ERROR:
ORA-12641: authentication service cannot be initialized
Enter the User Name:
For example, the following section describes:
Use the parameter SQLNET. AUTHENTICATION_SERVICES to enable one or more authentication services.
If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.
NONE for no authentication methods. A valid username and password can be used to access the database.
ALL for all authentication methods
ETS for Windows NT native authentication (An authentication method that enables
A client single login access to a Windows NT server and a database running on the server)
Although ALL is mentioned here, I personally tested it. It does not work. It may be related to the version. My version is 10.2.0.1 windows 32bit.
-- Change to (NONE) and then log on
C: \ Documents ents and Settings \ Administrator> sqlplus/as sysdba
SQL * Plus: Release 10.2.0.1.0-Production on Thursday August 28 21:50:49 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient Permissions
Enter the User name: sys as sysdba
Enter Password: -- enter the correct password "oracle" here"
Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
SQL>
-- Disconnect and change (NONE) to (CNT) for direct connection.
SQL> disc
From Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options disconnected
SQL & gt; conn abc/123 as sysdba
ERROR:
ORA-01031: insufficient Permissions
Because SQLPLUS is not exited, the modified (CNT) has not yet taken effect. It can be seen that the sqlnet. ora file is read only when it enters SQLPLUS.
-- Keep (ETS), log out of SQLPLUS and log on again
SQL> exit
C: \ Documents ents and Settings \ Administrator> sqlplus/as sysdba
SQL * Plus: Release 10.2.0.1.0-Production on Thursday August 28 21:54:52 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
SQL>
Now it is verified by the operating system.
NOTE: If it is changed to (NONE), when RMAN is used, the system user cannot operate and enter the correct password. Only the sys user can enter the password. The test is as follows:
-- Log on to RMAN when set to (ETS)
C: \ Documents ents and Settings \ Administrator> rman target/
Recovery MANAGER: Release 10.2.0.1.0-Production on Thursday August 28 22:02:07 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connect to the target database: ORCL10G (DBID = 1041171248)
RMAN> exit
The recovery manager is complete.
C: \ Documents ents and Settings \ Administrator> rman target system/oracle
Recovery MANAGER: Release 10.2.0.1.0-Production on Thursday August 28 22:02:33 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connect to the target database: ORCL10G (DBID = 1041171248)
RMAN> exit
The recovery manager is complete.
-- After changing to (NONE), log on to RMAN with the password
C: \ Documents ents and Settings \ Administrator> rman target system/oracle -- note that the password here is correct
Recovery MANAGER: Release 10.2.0.1.0-Production on Thursday August 28 22:03:06 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================
RMAN-00554: Internal recovery manager package initialization failed
Error in RMAN-04005: target database:
ORA-01031: insufficient Permissions
C: \ Documents ents and Settings \ Administrator> rman target/
Recovery MANAGER: Release 10.2.0.1.0-Production on Thursday August 28 22:08:47 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================
RMAN-00554: Internal recovery manager package initialization failed
Error in RMAN-04005: target database:
ORA-01031: insufficient Permissions
C: \ Documents ents and Settings \ Administrator> rman target sys/oracle
Recovery MANAGER: Release 10.2.0.1.0-Production on Thursday August 28 22:03:16 2014
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connect to the target database: ORCL10G (DBID = 1041171248)
RMAN>
Obviously, the system user is forbidden to use RMAN after database authentication. Only sys users with the highest database permissions can log on to RMAN, And the password must be entered for login.
Summary:
In windows, SQLNET. AUTHENTICATION_SERVICES can use OS authentication only when it is set to the ETS. If it is not set (for example, add the # annotation symbol to the statement in this row) or if it is set to any other value, OS authentication cannot be used.
It mainly involves the following situations:
1. The sqlnet. ora file is empty, or you can use # To comment out the file-based on the Oracle password file for verification.
2. SQLNET. AUTHENTICATION_SERVICES = (SCN) -- Based on operating system verification
3. SQLNET. AUTHENTICATION_SERVICES = (NONE) -- Authentication Based on the Oracle Password File
4. SQLNET. AUTHENTICATION_SERVICES = (NONE, CNT) -- Based on operating system verification (the same is true for Reverse Order)
Note: by default, sqlnet. the ora file does not have SQLNET. the AUTHENTICATION_SERVICES parameter seems to be connected to sqlnet. ora does not have this file either, which is very different from windows.
In this case, the operating system authentication and Oracle Password Authentication coexist. If SQLNET is added. after the AUTHENTICATION_SERVICES parameter is set, whether it is set to (NONE), (ETS), or (NONE, ETS), it is based on the Oracle password verification.
GMP verification; a series of activities proving that any operating specification (or method) production process or system can achieve the expected results, giving examples of the concept of verification
To put it simply, you have already made all the definitions. If you want to explain it in detail, you can verify how many books are written.
Why can't SQL developer log on to oracle SQL plus?
Oracle login authentication method
There are two methods for Oracle logon authentication: operating system-based logon authentication and oracle-based authentication.
1. Operating System-based logon authentication in Windows
1. In Windows, set oracle logon verification as the operating system authentication method:
1: Add OS users to the ora_dba Group
2: Set sqlnet. ora SQLNET. AUTHENTICATION_SERVICES = (ETS)
SQLNET. AUTHENTICATION_SERVICES = (SCN) is based on operating system verification;
SQLNET. AUTHENTICATION_SERVICES = (NONE) is based on Oracle verification;
SQLNET. AUTHENTICATION_SERVICES = (NONE, CNT) is the coexistence of the two.
2. operating system authentication principles for Logon in windows:
1: Type compmgmt. msc in the command line to enter computer management.
2: select the local user and group-> group (there is a group named ORA_DBA)
3: Double-click to check whether the administrator user exists.
4: Go to the Oracle Installation Directory (that is, $ ORACLE_HOME is generally D: "oracle)" ora92 "network" admin
Find the sqlnet. ora file and check whether SQLNET. AUTHENTICATION_SERVICES = (ETS) exists)
5: if these are correct, you can log on to Oracle using the operating system authentication method (connect/as sysdba ).
3. How to disable operating system authentication in windows:
1: Change SQLNET. AUTHENTICATION_SERVICES = (CNT) to SQLNET. AUTHENTICATION_SERVICES = none.
