Configure Oracle SQLDeveloper SSH connection to Oracle Public Cloud Database

Oracle SQLDeveloper SSH Configuration

Challenge

After provisioning an Oracle SOA Cloud Service and its related Oracle Database 12c Pluggable Database Cloud Service Instance, I then needed to connect to the Database in the Oracle Public Cloud in order to run some scripts for my demo tables. Obviously mixing customer data into the SOA Meta Data-Store (MDS) Database is not best practice but it was fine for my demo purposes.

In order to run the SQL scripts I had I was presented with a couple of choices (at least).

1. Work out how to transfer the SQL files (via sftp, or scp) to the OPC Database VM, or
   
2. Use Oracle SQLDeveloper to connect to the already running Oracle Database in the cloud over SSH.

Solution

Firstly the disclaimer, even though I got this to work it may not constitute best practice for security etc but it worked for me and got me to my end goal in support of my customer demonstration quite quickly.

Connecting to the Oracle Database in the cloud should be straightforward but I did have a couple of lessons to learn first, as is often said, there is no gain without some pain. Fortunately for me it wasn’t too painful as others had climbed the mountain before me and I had a trail of breadcrumbs too follow. I discovered an Oracle blog which proved useful in getting me started, please refer https://blogs.oracle.com/dbaas/entry/connecting_to_a_database_cloud

The above blog identifies the steps involved which I have summarised below;

1. Get the Database Host IP address using the Oracle Cloud Database Service Console
   
2. Get the SSH Private Key that was used when the Database Service was provisioned
   
3. Launch Oracle SQL Developer (I used version 4.1.5)
   
4. Create an SSH Host (refer figure 1 below) and specify
   
   1. the Public IP address of the Database Cloud Service Instance, and
      
   2. the Private Key
      
5. Create a local Port Forward for port 1521
   
6. Modify the database connection to use SSH
   
7. Save and Test
   

Once the above steps are complete you should be able to connect to the Cloud Database instance using Oracle SQL Developer.
Interactions between the Oracle SQL Developer client and the cloud database will be over the SSH tunnel.
Typically in the above steps you will be connecting ( at least initially ) as a privileged user eg system or sys with the sysdba role.

Figure 1- SSH Host Referencing the IP Address of the Cloud Database Host

Figure 2 – SSH Host Local Port Forward Setup for Port 1521

Figure 3 – SQL*Net Connection over SSH as Common User to Oracle Database

Due to the fact that the Oracle Database that I provisioned in the Oracle Cloud was a Pluggable Database I still had some additional steps to do.
The following section is designed to briefly highlight a change to Oracle Database Users that was introduced as part of the multi-tenanted capability of the Oracle Database 12c.
Understanding Users in the context of the Container Database is necessary before we create the User that we need and secondly to connect SQL Developer to the Pluggable Database as that user.
It is certainly neither complete nor exhaustive and there is a wealth of detailed product documentation and published blogs for those who want to explore further.

Pluggable Databases

Oracle introduced the concept of Pluggable Databases in the 12c release. According to the Oracle Product Documentation https://docs.oracle.com/database/121/ADMIN/cdb_intro.htm#ADMIN13507

“A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB.
All Oracle databases before Oracle Database 12c were non-CDBs.”

For an overview of the Oracle Database Multi-Tenant Architecture please refer https://docs.oracle.com/database/121/CNCPT/cdblogic.htm#CNCPT89248

There are some differences with Users in a Container Database which is slightly different to that which I was used to in releases of the Oracle Database prior to 12c.
With the introduction of the Container Database,, Oracle has also introduced the concept of a Common User and a Local User.
Every PDB is owned by SYS, which is a common user in the CDB, regardless of which user created the PDB.

The common users are typically privileged users that also may have access to the pluggable databases. The local user is local to a Pluggable Database.

Common User:

• A common user is a database user that has the same identity in the root and in every existing and future PDB.
  
• Every common user can connect to and perform operations within the root, and within any PDB in which it has privileges.
  
• Every common user is either Oracle-supplied or user-created.
  Examples of Oracle-supplied common users are SYS and SYSTEM.
  
• The name of every user-created common user must begin with the characters c## or C##.
  (Oracle-supplied common user names do not have this restriction.)
  

Local User:

• A local user is a database user that is not common and can operate only within a single PDB.
  
• A local user is specific to a particular PDB and may own a schema in this PDB.
  
• The name of a local user must not begin with the characters c## or C##.
  

Now back to the task at hand …. My objective was now to create a Local User and grant privileges. Once I had created the user and granted privileges then I would configure a new connection within Oracle SQL Developer. This connection would be to the same Cloud Database (IP address) but this time targeting my specific pluggable database ‘PDB1’ as the user ‘E2EDEMO’. Once I had established the connection I could then then run my SQL script inside SQL Developer in order to create tables, indexes, sample data in support of my SOA demonstration. Once again I found a useful blog post to get me started http://dba.stackexchange.com/questions/60806/oracle-12c-create-user – also refer https://dbasolved.com/2013/06/29/common-user-vs-local-user-12c-edition/ for some common errors you may encounter – don’t you just love the Internet.

The steps I had to do were as follows;

1. Connect to the database as a Common User (eg System) using SQL Developer over an SSH tunnel
   
2. Once connected, execute the following command over the SQL*Net connection
   
   1. alter session set container = PDB1;
      
3. Once the session is altered to reference the PDB1 container then execute the following
   
   1. create user E2EDEMO identified by mypassword;
      
   2. grant create session, resource, dba to E2EDEMO; (or your specific grants !!)
      
4. Finally I had to create a new connection inside SQL Developer referencing my specific Pluggable database (PDB1) and user (E2EDEMO) as per the following screenshot.
   

Here is a screenshot of the user definition of my user ‘E2EDEMO’ within the my pluggable Database ‘PDB1’ in the Oracle Public Cloud.

Figure 4 – SQL*Net Connection over SSH as Local User

Hope you found this useful.