Oracle 11g Login Analysis in different scenarios

Oracle's account login issues are often tricky for Oracle beginners and even some experienced Oracle DBAs, even if they are successfully logged in to Oracle. Through systematic learning and repeated practice, the spirit of breaking the sand pot to ask the end of the attitude, finally to Oracle's login principle and operational details have a more comprehensive understanding. This article records these experiences and hopes to help Oracle beginners to successfully log in to Oracle with confidence.

1 Learn the prerequisites for this article

There is a lot of knowledge about Oracle, but some basic terminology is something that all DBAs should be familiar with. To better understand the content of this article, readers need to understand the following terms: instance and Database,ip/tcp,sqlplus,oracle accounts and operating system accounts, Oracle DBA.

2 Oracle Login Overview 2.1 Oracle's C/s architecture and communication protocol

The overall architecture of Oracle software is based on C/s and is divided into the client and server side according to function. The client is responsible for accepting the user's input and receiving and showing the result from the server side, the common client has SQL Developer, SqlPlus; the server side is responsible for parsing the SQL request from the client and returning the result to the client.

Any software of c/s architecture, communication part is very important, the most important is the design of communication protocol. Oracle is no exception, and the communication protocol used is known as Oracle Net. It is an application-layer protocol, and Oracle NET can now run on many of the underlying protocols, such as TCP, secure TCP, Named Pipes, SDP, and so on. In addition, for different underlying operating system platforms, Oracle NET also supports the local interprocess communication protocol for the operating system.

No matter what the underlying platform is, no matter what the client software is, where it is running, client and server communication can only be done with a unique Oracle NET protocol, with no other side.

2.2 Oracle Accounts and authentication methods

Permission control through an account is a method that many software takes, for example, each OS has its own account. Oracle is no exception, and in order to get into Oracle, it must be entered in some way, which is Oracle's account. Oracle accounts are broadly divided into privileged and regular accounts according to their privileges. Privileged accounts have great permissions, while the privileges of ordinary users are greatly limited. A typical privileged account is sys,system.

So how does Oracle validate the account? The answer is in many ways, the easiest to understand is the local password verification, and the use of OS account verification, the use of LDAP authentication, and Kerberos and other external authentication. This article only covers the most basic methods of password authentication.

3 Oracle Listening and password storageas the C/S architecture program, the account login process is divided into two parts, one is the network connection, and the other is the account verification.
3.1 Two types of server-side listeners

As mentioned earlier, Oracle NET supports a variety of underlying communication protocols, including inter-process communication protocols that are local to the operating system. Regardless of the underlying protocol, the server-side program must listen in some form to wait for a connection request from the customer.

(1) Network Listener program Tnslsnr

Oracle Tnslsnr provides a listening service when the underlying protocol is a network protocol. This is a separate process, independent of other processes. Here's how the Oracle dedicated server works with the listener.

As shown, the client process first connects with the listener (red wire) and verifies the account and permissions, and if all the validations pass, the listening process is responsible for generating a new server process and having the client process connect directly to the newly generated server process (blue line), called a session, and disconnects itself from the client , and no longer has any relationship with the newly established session. The visible listening process plays a role. After that, even if the listener stops working, the established session is not affected at all.

(2) Local Listener program

The local listener is completely different from the network listener, which does not require a network protocol stack, and the implementation relies heavily on the underlying OS. In Oracle, local listening is completely independent from Tnslsnr. Local listening on the Linux platform does not require any programs to be executed. This means that the local login does not depend on Tnslsnr. This is useful for DBAs, and we can still log on to Oracle with a local connection when the listener fails to work properly.

3.2 Two ways to store your password

This article only cares about authentication of local password methods.

(1) The password is stored in the data dictionary

At this point, to verify the password, the database must be open, most of the ordinary user's password is stored in this way. storing the password in the data dictionary poses the problem that the data dictionary is not available until the database is opened, so that the account password is not known and the user cannot log on. This is a fatal problem for the DBA, imagine that one day the database problems can not be loaded, the DBA must log in to the system for repair, if the DBA account password is stored in the data dictionary, to log in must first open the database, this is not into a dead loop. So Oracle rules that passwords for accounts with special DBA authority are automatically copied and stored in an external file.

(2) The password is stored in an external file

This password file is usually stored in the @oracle_home/dbs/orapw$sid location. It is important to note that only account passwords with DBA authority are stored in external files and are added automatically when special permissions are granted, and external password files cannot be modified manually.

4 Analysis of several scenarios for Oracle loginfor different listening methods and different ways of storing passwords, you can arrange the following types of login scenarios. The client process is based on the connection string to determine the specific use of that situation. 4.1 Local Listen + no password required

Usage Prerequisites:

(1) The client runs on the same machine as the server side;

(2) Log in to the OS using an OS account that installs Oracle;

(3) Set the $ORACLE_SID environment variable.

Connection string:

At this point, the connection string must be Connect/as sysdba.

Usage scenarios:

It is worth noting that, in this case, the only prerequisite for successful login is the need to specify the $ORACLE_SID environment variable. No listener is required, no database loading is required, and no instance startup is required. As shown, the instance does not start. At this point, the Oracle SYS account is used to log on by default.

The use of this scenario is that the DBA starts the database.

4.2 Local listening + external file password

Usage Prerequisites:

(1) The client runs on the same machine as the server side;

(2) Log in to the OS using any account;

(3) Set the $ORACLE_SID environment variable.

Connection string:

At this point, the connection string must be connect privileged account name/password as SYSDBA.

Usage scenarios:

Similar to 4.1, only the OS account at this time can run Sqlplus without the need to be an Oracle installation user.

4.3 local listening + data dictionary passwordApplicable Prerequisites:(1) The client runs on the same machine as the server side; (2) The database has been loaded open. (3) Set the $ORACLE_SID environment variable.



Connection string:Connect Username/password.
Usage Scenarios:this applies to regular users who log on locally. Note that because the password is stored in the data dictionary, the database must be in the open state.
4.4 Network Listening + external file passwordApplicable Prerequisites:(1) Tnslsnr must have been working properly;(2) The data instance starts, and the database is not loaded.

Connection string:Connect Privileged User name/[email Protected]/servicename as Sysdba
Usage Scenarios:DBA remote login for database maintenance. The database is not loaded at this time. Note that there is a service name in the connection string, so the listener must know the service name, but by default only the database is loaded and the service information is dynamically registered with the listener. Now the case is that only the DB instance is started and the database is not loaded. At this point, in order for the listener to know the service information, the service must be statically registered. The method is to modify the server-side Listener.ora configuration file as follows:


4.5 Network listening + data dictionary passwordApplicable Prerequisites:(1) Tnslsnr must have been working properly;(2) The data instance is started, the database is loaded and opened;

Connection string:Connect Common user name/[email protected]/servicename
Usage Scenarios:This is the most common scenario for a common Oracle application, and most Web applications typically use an ordinary account to access the Oracle server remotely to read and write data.
5 handling of special characters in passwordsAs mentioned earlier, the client determines in which way to log on to the server, depending on the connection string, the connection string has a specific syntax format and special characters, such as/,@, ". For Oracle account names, it is generally important to avoid the occurrence of these special characters in the name and prevent conflicts. For the account password, many people like to use special characters, once the password contains/,@, ", it will inevitably lead to the syntax of the login string conflict. Let's look at a few examples.
(1) password [email protected]at this point, if the connection string is connect Username/[email protected], then @ will be treated as a keyword, and ssw0rd is treated as a listening address, resulting in an error. The workaround is to enclose the cipher part with double quotes. Connect username/"[email protected]".
(2) PasswordThe method is the same as above, connect username/"1/2".
(3) password 1 "2The password with double quotation marks, I have never seen, also do not know how to create this password, also please expert advice.
when Oracle parses a connection string, the user name is automatically converted to uppercase characters if it is not surrounded by quotation marks, and the parsing of the cipher part is unchanged.
finally recommend a ORACLEDBA book, the book is a lot of Oracle terminology and principles of the very clear, just a slightly rough translation, it is recommended to download the English version of comparative reading. Download link for English version: http://download.csdn.net/detail/smstong/7534001.