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).

Work out how to transfer the SQL files (via sftp, or scp) to the OPC Database VM, or

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;

Get the Database Host IP address using the Oracle Cloud Database Service Console

Get the SSH Private Key that was used when the Database Service was provisioned

Launch Oracle SQL Developer (I used version 4.1.5)

Create an SSH Host (refer figure 1 below) and specify

the Public IP address of the Database Cloud Service Instance, and

the Private Key

Create a local Port Forward for port 1521

Modify the database connection to use SSH

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 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.

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;

Connect to the database as a Common User (eg System) using SQL Developer over an SSH tunnel

Once connected, execute the following command over the SQL*Net connection

alter session set container = PDB1;

Once the session is altered to reference the PDB1 container then execute the following

Blogroll

RedThunder.blog and contributors. All Rights Reserved. The views expressed in this blog are our own and do not necessarily reflect the views of Oracle Corporation. All content is provided on an ‘as is’ basis, without warranties or conditions of any kind, either express or implied, including, without limitation, any warranties or conditions of title, non-infringement, merchantability, or fitness for a particular purpose. You are solely responsible for determining the appropriateness of using or redistributing and assume any risks.

Follow Blog via Email

Enter your email address to follow this blog and receive notifications of new posts by email.