Oracle 11g Login Analysis in different scenarios

For people who have just started learning from Oracle. Even for some experienced Oracle DBAs, Oracle's account login issues are often tricky. Even if you have successfully logged in to Oracle, you know the reason why.

The author after systematic learning and repeated practice, in the spirit of breaking the sand pot to ask the exact attitude. Finally, Oracle's login principle and operational details have a more comprehensive understanding.

This article records these experiences and hopes to help the people who have just started learning 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 of the main terms are familiar to all DBAs. To better understand the content of this article, readers need to understand such terms as the following: instance and database. Ip/tcp,sqlplus,oracle account with operating system account, 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, which is divided into client and server side according to function.

The client is responsible for accepting the user's input and receiving and displaying the results from the server side. The common client has SQL Developer, and the sqlplus;server side is responsible for parsing the SQL requests from the client and returning the results to the client.

No matter what the C/S architecture software. Communication is crucial, and the most important is the design of the communication protocol. Oracle is no exception. The communication protocol used is known as Oracle Net. It is an application-layer protocol, and Oracle NET can now execute on very many 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.

Regardless of the underlying platform, client and server communications are only able to use the unique Oracle NET protocol, regardless of what the client software is and where it is executed. There is no other side.

2.2 Oracle Accounts and authentication methods

Access control through accounts is a very much more software approach, for example, every 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. However, the privileges of the ordinary user are greatly limited. A typical privileged account is sys,system.

So how does Oracle validate the account? The answer is that there are many ways, and the easiest to understand is the local password authentication, plus the use of OS account validation, the use of LDAP authentication, and external authentication such as Kerberos.

This article only deals with the most important password verification methods.

3 Oracle Listening and password storageas the C/S architecture program. Account login process is divided into two parts, one is the network connection, and the second 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.

The following is the listener work under the Oracle dedicated Server mode of operation.

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvc21zdg9uzw==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/center "/>

As you can see, the client process first connects to the listener (red wire). and verify the account and permissions. Assuming that all validation passes, the listening process is responsible for generating a new server process and having the client process directly connected to the newly generated server process (blue line), called a session, which disconnects itself from the client at the same time, 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, it does not require a network protocol stack, and the detailed implementation relies heavily on the underlying OS.

In Oracle. Local listening and Tnslsnr are completely independent. Native listening on the Linux platform does not require any program to be executed. This means that the local login does not depend on Tnslsnr. This is very useful for DBAs. When a problem with the listener does not work, we are still able to log in to Oracle via a local connection.

3.2 Two kinds of password storage methods

This article only cares about the validation of local password methods.

(1) password stored in the data dictionary

To verify password, the database must be open, and most of the ordinary user's password are stored in this way. The problem with storing the password in a data dictionary is that the data dictionary is not available until the database is opened. The account password is not known, causing the user to be unable to log in. This is a fatal problem for the DBA. Imagine a day when the database fails to load, the DBA must log in to the system to repair, assuming that the DBA account password stored in the data dictionary, to log in must first open the database, this is not into a dead loop. Oracle therefore stipulates that password information for an account with special DBA Authority will voluntarily copy and store it in an external file.

(2) password 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 password with DBA authority will be stored in the external file. and is to grant special permissions when the system itself to join, can not manually change the external password file.

4 Analysis of several scenarios for Oracle loginfor different listening methods and different password storage methods. The ability to sort out several login scenarios such as the following. The client process infers the details of the case according to the connection string. 4.1 Local Listen + no password

Usage Prerequisites:

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

(2) Log in to the OS using an OS account with Oracle installed.

(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 such cases, the only prerequisite for successful login is the need to specify the $ORACLE_SID environment variable. No listener required and no database loading required. You don't even need an instance to start. As you can see. 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 Listen + external file password

Usage Prerequisites:

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

(2) Log in to the OS with no account;

(3) Set the $ORACLE_SID environment variable.

Connection string:

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

Usage scenarios:

Similar to 4.1, only the OS account at this time should have permission to execute sqlplus, without having to be an Oracle installation user.

4.3 local listening + data dictionary passwordApplicable Prerequisites:(1) Theclient is executed on the same machine as the server side; (2) The database is already 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 password is stored in the data dictionary, the database must be open.
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 does not need to be loaded at this time. It is important to note that there is a service name in the connection string, so the listener must know the information of the service name, but by default the service information is dynamically registered to the listener only after the database is loaded.

And now the situation is. Only the DB instance is started. Database not loaded.

At this point, to let the listener know the service information, you must register the service statically. The method is to alter the server-side Listener.ora configuration file. For example, see the following:


4.5 Network listening + data dictionary password applicable premise:(1) Tnslsnr must have been working properly; (2) The data instance is started. The database is already loaded and opened;

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvc21zdg9uzw==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/center "/>

connection string: connect Normal user name/[email protected]/ ServiceName
usage scenario:
5 password

For Oracle account names, it is generally important to avoid the occurrence of these special characters in the name and prevent conflicts.

For account password, very many people like to use special characters, once password includes/,@, ". is bound to cause a syntax conflict with the login string. See below for a few examples.

(1) password [email protected] assuming that the connection string is connect Username/[email protected], then @ will be treated as a keyword,ssw0rd as a listening address, resulting in an error. The solution is to enclose the password part with a double-lead.

Connect username/"[email protected]".
(2) Password The same method as above, connect username/"1/2".

(3) password 1 "2 password with double-cited. I really did not see, and do not know how to create such a password, but also please be generous to enlighten.
oracle when parsing a connection string, it is assumed that the user name is not surrounded by an argument. You are actively converting to uppercase characters. The parsing of the password part is intact. (4) Note the meta-character escapes of the platform shell sqlplus supports login with user name and password parameters directly in the Cui interface of the operating system. such as  

Take bash for example, such as the above password [email protected] situation, at this time need to write:sqlplus username/\ "[email protected]\ ] because "itself is a meta-character in bash. To pass "itself as part of a reference to Sqlplus." It must be escaped.
finally recommend a ORACLEDBA book, the book has a lot of Oracle terminology and principles of very clear, just a slightly rough translation, it is recommended to download the English version of reading. Download link for English version: http://download.csdn.net/detail/smstong/7534001.

Oracle 11g Login Analysis in different scenarios