SQL Developer provides a variety of options when making database connections by supporting connections to both Oracle and non-Oracle databases. For all of these connections, you'll need the required JDBC drivers. Within the Oracle connections, there are a variety of connection types (such as TNS or LDAP connections) and authentication methods (such as OS or Kerberos authentication).
In this chapter, we'll review the basic connection type used in the examples earlier in the book. We'll see how to add these additional Oracle connection types and review the connections for the supported authentication methods. We'll also look at how to add the required drivers and set up the connections for non-Oracle databases.
We'll start by reviewing the various Oracle connection types available. We'll then look at the authentication methods supported by SQL Developer.
SQL Developer focuses on ease of use right from the start. With a mantra of Download, unzip to install and you're ready to start, this intended ease of use means that you do not need any additional Oracle clients on your machine to connect to an Oracle database. However, in many Oracle environments, having an Oracle Home is standard. Therefore, you can access the alias in a tnsnames.ora file.
To create a new connection, navigate to File | New… | Database Connection or right-click on the Connections node and select New Connection. By now, you should be familiar with the default settings in the Connections dialog, as shown in the following screenshot:
The databases listed in the default Database Connection dialog are Oracle and Microsoft Access. Access, which is displayed only for SQL Developer on Microsoft Windows, is available by default as it uses the JDBC-ODBC bridge and requires no additional drivers. For all other database support, you'll need to add the required drivers, which will be discussed later in the chapter.
For the Oracle connections, the different options are in the Connection Type drop-down list. As you make each selection, the dialog and required input values change, as shown in the following screenshot:
The default connection is the Basic connection. It uses a thin JDBC driver that's shipped with SQL Developer and which allows you to connect to the Oracle database without other Oracle clients on your machine. The default SID is set for XE, which is the default SID for Oracle Express Edition, the free Oracle 11g database. When using the Basic connection type, all that you need to know is the location of your database, the port, and the SID or the Service name.
When creating an Oracle Agent, there are two types of connection. The differences between these two connections are explained as follows:
- SID: This is the unique name of your database
- SERVICE_NAME: This is the alias used when connecting
Tip
Selecting the Save Password checkbox saves the password to an encrypted file. This makes it secure. However, a better level of security is to not save the password at all. That way, there is no file that can be compromised. Disable the Save Password option.
To disable the checkbox to save the password, add the following entry to the sqldeveloper.conf file:
AddVMOption -Dsqldev.savepasswd=false
The New Connection dialog provides two options when you switch to the TNS connection type. These are the Network Alias drop-down list, which is read from the tnsnames.ora file, or the Connect Identifier.
By default, the tnsnames.ora file is defined in the $ORACLE_HOME/network/admin directory. However, you can set the TNS_ADMIN environment variable to specify an alternate location.
The following code is an example of what an entry in the tnsnames.ora file looks like:
ajith11grac= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=localhost) (PORT=1521) ) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=ajith1) ) )
In this example, ajith11grac can be used as the network alias and the remaining lines can be used as the connect identifier to make the connection.
By default, the tnsnames.ora file is defined in the $ORACLE_HOME/network/admin directory. However, you can set the TNS_ADMIN environment variable to specify an alternate location.
You may have multiple tnsnames.ora files set up on your machine. SQL Developer only reads one of these, searching for the file in the following order of priority:
$HOME/.tnsnames.ora$TNS_ADMIN/tnsnames.ora/etc/tnsnames.ora(for non-Microsoft Windows environments)$ORACLE_HOME/network/admin/tnsnames.ora- Registry Key accessing the correct TNS Names file
If SQL Developer does not locate the required tnsnames.ora file, you can set the directory location using the TNS_ADMIN system environment variable.
In Microsoft Windows, you can create the TNS_ADMIN environment variable using the Control Panel. Navigate to System | Advanced | Environment Variables and add or update the value for the TNS_ADMIN variable.
Although not a recommended approach, you can also directly set the registry variable. To set the Registry Key, open the registry editor and find the HKEY_LOCAL_MACHINESOFTWAREORACLE entry. Locate or create the TNS_ADMIN entry and set it to the required path.
SQL Developer provides LDAP authentication to access database service names in the Oracle Internet Directory (OID). Lightweight Directory Access Protocol (LDAP) is an application protocol that's used to query and modify directory services. The OID is a directory service that stores and manages net service names in a central location. In order to use or access an LDAP server, you need to have the LDAP parameter set in the sqlnet.ora file, as follows:
NAMES.DIRECTORY_PATH=(LDAP)
The ldap.ora file should have an entry that's similar to the one shown in the following sample:
DIRECTORY_SERVERS= (localhost:389:636) DEFAULT_ADMIN_CONTEXT = "" DIRECTORY_SERVER_TYPE = OID
SQL Developer reads the ldap.ora file in the same way that it reads the tnsnames.ora file and populates the LDAP Server drop-down list. Alternatively, you can type in the details for the LDAP server, as shown in the previous screenshot. Once you have entered the LDAP server details, click on the Load button to populate the Context and DB Service.
Instead of using the preconfigured connection types that provide the appropriate drop-down lists based on the information that's given, you can use the Advanced connection type, which connects directly to the database using Java. Selecting this provides an empty field where you can enter the full connection string and specify the driver type in the URL. For example, the connection string can be jdbc:oracle:thin:@localhost:1521:orcl. In the following example, there is no Username and Password provided. Therefore, we can't use Test or Connect in the dialog (you can of course include these in the URL and then test the connection). If you save and close the dialog, when you try to connect, you are prompted for the connection username and password, as shown in the following screenshot, and can then connect:
You can connect to an Oracle TimesTen In-Memory Database using SQL Developer. The connection details are available through a separate tab, which only appears alongside the Oracle connections tab if you have TimesTen set up in your environment. The ability to work with the TimesTen In-Memory database is integrated into SQL Developer 1.5 and above. For more details on setting up Oracle TimesTen, refer to the documentation on the Oracle Technology Network, which is available at https://docs.oracle.com/cd/E11882_01/timesten.112/e21632/install.htm#TTINS113.
Once you have created a connection, you can connect to and work with the objects as you do for other database connections:
We have now reviewed connections that seamlessly use either the thin or thick JDBC drivers. Before we look at further connections that use these or when you should force the use of the thick driver, we'll provide a brief explanation of the two driver types and when you need additional client software. We'll also highlight a few points that you need to watch out for.
The JDBC thin driver, also referred to as the Type IV driver, is platform independent and as such does not need an Oracle client, Oracle Call Interface (OCI), or SQL*Net on the machine. This is the driver that SQL Developer uses by default and which you access when using the Basic connection type.
Installing Oracle Database or any other Oracle tools sets up an Oracle Home. In examples like this, you are likely to have a tnsnames.ora file in your environment, which can be used when connecting to the database using SQL Developer. In this case, you can generally use the "thick" JDBC OCI (Type II) driver. If you do not have an Oracle Home and need to use the Type II driver, you'll need to install the Oracle Instant Client.
SQL Developer 4.1 ships with the Oracle Database 11g JDBC driver, ojdbc6.jar.
The Oracle JDBC drivers are certified for the respective currently supported databases. For example, the JDBC drivers shipped with Oracle Database 12c Release 1 are certified for Oracle Database 12c Release 1, Oracle Databases 11.1.x, 11.2.x, 10.2.x, 10.1.x, 9.2.x, and 9.0.1. As SQL Developer is certified for Oracle Databases 9.2.0.1 and above, this driver is compatible with all the current SQL Developer releases. These drivers are not certified for older, unsupported databases such as 8.0.x and 7.x.
When installing Oracle JDBC drivers, the installer installs the JDBC drivers in the $ORACLE_HOME/jdbc directory. If you have an issue with the drivers, verify that you have the correct path in the CLASSPATH.
It is also important to ensure that your connections are compatible with the client. If they are not, you need to update your client installation. Updating your client is not related to and does not affect your database or other Oracle software installations.
In addition to providing a choice of connection types, you also have a choice of authentication methods. As a rule, users use the standard database authentication, which is enforced with a username, a password, roles, and privileges. With security being of such importance in almost all systems today, stronger authentication methods are necessary. In many applications that work across multiple tiers, each tier is also required to provide authentication. If these authentication methods exist, SQL Developer allows users to use them as a part of the connection settings. It is not in the scope of this book to define or describe each of the authentication approaches in detail, but where possible, we will provide some background.
Used more often in the past, operating system (OS) Authentication is employed to avoid logging in to both the operating system and then the database. For example, when this was first introduced, users had a secure profile to connect to Unix and then immediately connect to the database using SQL*Plus. Using OS Authentication means that a user is not required to provide the additional authentication. The database is aware of the OS connection but does not need to manage the user passwords. The action is that you'll connect to the OS and then connect to the database in SQL*Plus using connect / or even sqlplus / in the command prompt.
To set up this environment in the database, you need to create a database user using the same details as that of the operating system's user. First, you should find the OS username and then, you need to create a user in the database. If you don't know the username, then enter the following code in the SQL Worksheet:
SELECT UPPER(SYS_CONTEXT('USERENV','OS_USER'))
FROM DUAL;You can use the Create User… dialog on the Other Users node for your SYSTEM connection, but it's easy to enter the syntax in the SQL Worksheet:
CREATE USER "OPS$AJNARAYA-LAPAJNARAYA" IDENTIFIED EXTERNALLY; GRANT CREATE SESSION TO "OPS$AJNARAYA-LAPAJNARAYA";
Note the use of the OPS$ variable in front of the username. The OPS$ value is historical and can be changed or omitted by setting the OS_AUTHENT_PREFIX database initialization parameter that controls this value. In Microsoft Windows, the username also includes the machine name.
You can use OS Authentication when you connect to a database that is local to your machine. Therefore, you're connecting and authenticated on your machine and then connecting to your local database. You can use OS Authentication to connect to a remote database. To do this, you also need to set the REMOTE_OS_AUTHENT = TRUE.
Finally, in SQL Developer, OS authentication needs to use the thick OCI driver. So, if you use OS Authentication with the Basic connection type, you must ensure that the thick OCI driver is used. To force the use of this driver, set the preference to Use OCI/Thick driver, which can be found by navigating to Database | Advanced Parameters.
Once you have created a user in the database and set the required database initialization parameters and the OCI driver preference, you can create a new connection, shown in the following screenshot:
Without OS Authentication selected, you are required to provide the Username and Password. Once it's selected, you no longer require these values. Therefore, the fields are unavailable.
Typically used in a three-tier environment, proxy authentication allows one connection to act as a proxy for the next connection. This can eliminate the overhead of additional authentication between clients.
To illustrate this, we need to have two users: one of them will be the actual or destination database user and the other one will be the proxy user. If we assume that HR is the actual user in this example, we need to create an additional proxy for HR. We'll allow the new HR proxy to connect to the HR schema and details without needing to know the HR schema password. To start, you need to create the new proxy user and grant the correct proxy authentication, as follows:
GRANT CONNECT, RESOURCE TO HR_PROXY IDENTIFIED BY ORACLE; ALTER USER HR GRANT CONNECT THROUGH HR_PROXY;
SQL Developer supports the ability to use:
In the first instance, we can use the proxy connection syntax. This is also available in a command line such as SQL*Plus in the Connections dialog. This syntax is proxy_user_name[user_name]/proxy_user_password, and this is illustrated in the following screenshot:
When creating this single-session connection, select the proxy Username and Password with the destination user, HR.
You can also use two sessions with the connection. In this case, provide the proxy user and password in the Connections dialog and then click on the Advanced option checkbox. This action invokes a new dialog for the destination user, as shown in preceding screenshot.
The preceding screenshot shows both the Connections dialogs, with the Proxy Connection checkbox selected, and the Oracle Proxy Connection dialog. Here, you need to only supply the Username; the Password is optional. The optional password is driven by the way you create the proxy user, as illustrated in the following two statements:
ALTER USER HR GRANT CONNECT THROUGH HR_PROXY AUTHENTICATED USING PASSWORD; ALTER USER HR GRANT CONNECT THROUGH HR_PROXY;
If you omit the authentication clause, then it is sufficient to use the name without the password.
In the example displayed in the previous screenshot, the HR_PROXY proxy user is connected to the HR user account and can review objects in the HR schema.
Kerberos is one of the strong authentication methods that are available for companies today to ensure identity security in a networked environment. It uses secret-key cryptography to store the username and password, which means that a client is required to prove its identity to the server and the server to prove its identity to the client. For more details regarding Kerberos, refer to http://web.mit.edu/kerberos/www/ and http://www.kerberos.org.
SQL Developer 1.5.3 introduced the ability to connect to the Oracle database using Kerberos authentication. It is beyond the scope of this book to provide the details related to the setting up and configuration of a Kerberos server for authentication. However, this process is fully documented in the Oracle Advanced Security manual. For this section, we'll assume that you have a Kerberos server setup.
SQL Developer provides support for Kerberos authentication using either the thin or the thick JDBC driver. Once the Kerberos server is set up, you need to set up and access two files on the client: the Kerberos configuration file and the credential cache file. If you use the thick JDBC driver, you'll need to provide the location of these files in the sqlnet.ora, while for the thin driver, we'll set this up in SQL Developer.
We'll start by looking at the preferences that influence Kerberos authentication. Navigate to Database | Advanced Parameters. Selecting the Use OCI/Thick driver preference directs SQL Developer to use the thick driver, which in turn reads the sqlnet.ora file.
The easier of the two when it comes to using them and setting them up is the thin JDBC driver, which can be done using the Preferences dialog, as follows:
- Keep the Use OCI/Thick driver preference deselected.
- Provide the path for the Kerberos configuration and credential cache files, as shown in the following screenshot:
Once you have set the preference, you can create the connection. When using the thin driver, you'll need to provide a Username and Password in the Connections dialog, as shown in the following screenshot:
In the preceding screenshot, we selected the Kerberos Authentication option, which requires the username and password in this case due to the preferences that have been set.