Setup needed for access to our database servers for CS630, fall 2019

For security reasons, you cannot login to dbs3. Thus you will need to ssh to ssh to users.cs.umb.edu and then ssh to pe07, or ssh directly to pe07.cs.umb.edu and then use sqlplus to access Oracle on dbs3.
For example, the sample user scott has password tiger1, and a table named emp. Here we use sqlplus running on pe07.cs.umb.edu to access the database on dbs3 by adding "@//dbs3.cs.umb.edu/dbs3" to the usual sqlplus command:

pe07$ sqlplus scott/tiger1@//dbs3.cs.umb.edu/dbs3

        

        SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 22 15:45:19 2016

        

        Copyright (c) 1982, 2014, Oracle.  All rights reserved.

        

        Last Successful login time: Fri Aug 19 2016 14:19:03 -04:00

        

        Connected to:

        Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
        Production

        With the Partitioning, OLAP, Advanced Analytics and Real Application
        Testing options

        

        SQL> select count(*) from emp;

        

          COUNT(*)

        ----------

                14

        

        SQL> exit

        Disconnected from Oracle Database 12c Enterprise Edition Release
        12.1.0.2.0 - 6bit Production

        With the Partitioning, OLAP, Advanced Analytics and Real Application
        Testing options

        pe07$

Example with student user jsmith: Note how the UNIX/Linux username is also the password here:

pe07$ sqlplus jsmith/jsmith@//dbs3.cs.umb.edu/dbs3

To avoid showing your login information on the command line (which is also shown by the "ps al" command to others), do the following:

 pe07$ sqlplus /nolog 

        

        SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 30 13:54:13 2016

         

        Copyright (c) 1982, 2014, Oracle. All rights reserved. 

        

        SQL> connect scott/tiger1@//dbs3.cs.umb.edu/dbs3 

        Connected.

SQL> select count(*) from emp;

        

          COUNT(*)

        ----------

                14

SQL> exit

        Disconnected from Oracle Database 12c Enterprise Edition Release
        12.1.0.2.0 - 6bit Production

        With the Partitioning, OLAP, Advanced Analytics and Real Application
        Testing opions

        pe07$

Oracle Accounts

Once you have run apply for a cs630 account (even if you are in cs430 officially), you are listed for an Oracle account. I will create the accounts and will be the (one and only) Oracle DBA for dbs3. Your Oracle password is the same string as your Oracle username, which is the same as your Linux username. At any time during the term, if you believe the Oracle system is not working properly, please email me (eoneil@cs.umb.edu) right away.

MySQL Accounts

I will also create a mysql database for you using the mysql server on pe07. User joe would get a database named joedb. Note that you can also use Oracle from pe07, so this should be your database access machine for this course. Note that having multiple databases on a system is normal for mysql but unusual for Oracle, where user accounts are typically made all on the same Oracle database. To reach your mysql account from pe07:

pe07$ mysql -u joe -D joedb -p 

        Password:xxxxx

        mysql> exit

        Bye

        pe07$

Note that although sqlplus accesses the same database server (dbs3) from either topcat (used for cs636) or pe07 (used for cs430/630), the mysql program accesses the mysql server on that same machine. Your mysql account is on pe07.

Home machine tunnel setups (not needed until we start using JDBC)

To access the database systems dbs3.cs.umb.edu and pe07.cs.umb.edu, themselves inside the firewall, from a system outside the firewall, we need a “tunnel” that uses the SSH protocol’s ability to provide a secure connection to a port that is not directly accessible for security reasons. For example, we want to access port 1521 on dbs3 to talk to Oracle using JDBC, but this port is blocked by the firewall. So, instead we connect to port 22 (SSH’s port, which is not blocked by the firewall for users.cs.umb.edu or pe07.cs.umb.edu) on pe07.cs.umb.edu and arrange that SSH make a connection for us inside the firewall to port 1521 on dbs3, and then move the data back and forth. For more info, see IBM article on tunneling.

For your home Windows machine

See PuttyTunnels.html for instructions on using putty to set up tunnels from home to our database hosts.

On your home Linux or MacOSX machine (in Terminal):

First get a Terminal window for Mac or a shell window for Linux to do the following commands. Use ssh to set up the needed tunnels to dbs3, port 1521, and pe07, port 3306.

Replace ‘user’ here with your cs.umb.edu Linux username and answer the password prompt with your cs.umb.edu Linux password. These commands will “hang”, so open another shell/Terminal window to continue working.

      ssh -N -L1521:dbs3.cs.umb.edu:1521 user@pe07.cs.umb.edu
      ssh -N -L3333:pe07.cs.umb.edu:3306 user@pe07.cs.umb.edu
      or both at once:
      ssh -N -L1521:dbs3.cs.umb.edu:1521 -L3333:pe07.cs.umb.edu:3306 user@pe07.cs.umb.edu

These work on Mac OSX v 10.9.5 on a desktop system. If you are still having trouble, please report your OSX version number and type of Mac.

Testing the tunnels with a browser

Browse to localhost:1521 with Chrome and see "No Data Received". This means it did connect, just didn't get any data from the server.

Try localhost:12345 or another random port to see "This webpage is not available", meaning it couldn't connect.

Try localhost:3333 with Chrome and see a small download happen. It had to connect to do that.

Testing the tunnels with telnet (or similarly, putty or ssh)

telnet is an classic tool that was used before ssh was needed: it just connects your keyboard and screen to a TCP stream connection, plain and simple. It can be used to test to see if a port on a host is open, among other things. Try “telnet” at the command line to see if it’s available on your system, and if not:

Windows: telnet is usually there, but if not, see Install telnet client.
Linux: telnet should be available in the shell window.
Mac: telnet should be there in Terminal’s command line.

Test mysql tunnel: telnet localhost 3333, or ssh localhost:3333, or putty to host localhost, port 3333

Test Oracle tunnel: telnet localhost 1531, or ssh localhost:1531, or putty to host localhost, port 1531

If successful, telnet or ssh or putty will take over the window and show you anything sent by the server. Anything you type will be sent to the server, except for the telnet escape sequence control-]. We don’t want to confuse the server with garbage, so type the escape sequence immediately, followed by quit, or kill the window. To see what happens with a port that’s not in active use, try “telnet localhost 12345” (or ssh localhost:12345 or putty ...) or some other random number.

Testing the tunnels with JdbcCheckup: from Windows, Linux or MacOSX

Once we have the tunnels working, the database ports show up as localhost:1521 and localhost:3333. We are using a local port 3333 to avoid any locally running mysql databases, which would normally use the local 3306 port. Similarly, if you have a local Oracle server running, you will need to use a different local port for tunneled Oracle.

java -classpath ojdbc6.jar;. JdbcCheckup    (use colon instead of semicolon on Linux/MacOSX)
Please enter information to test connection to the database
Using Oracle (o), MySql (m) or H2 (h)? o
user: xxxxxx
password: xxxxxx
host: localhost
port (often 1521): 1521
sid (site id): dbs3
using connection string:
jdbc:oracle:thin:@localhost:1521:dbs3
Connecting to the database...connected.
Hello World!
Your JDBC installation is correct.

java -classpath mysql-connector-java-8.0.4.jar;. JdbcCheckup
Please enter information to test connection to the database
Using Oracle (o), MySql (m) or HSQLDB (h)? m
user: xxxxx 
password: xxxx
host: localhost
port (often 3306): 3333
using connection string:
jdbc:mysql://localhost:3333/xxxxxdb
Connecting to the database...connected.
Hello World!
Your JDBC installation is correct.

Sqlplus on your home machine (very optional)

You can download and install the same "instant client" software we are using on pe07 to access dbs3's Oracle database right from your own system.

Visit http://www.oracle.com/technetwork/database/features/instant-client/index-097480.htm and choose your platform, and obtain an Oracle web account if necessary (following its prompt to do so)
Find the list of downloads, and download and unzip one of the first two, plus the third (jdbc) and fourth (sqlplus).
Follow the instructions on the above-linked page.
Make sure your tunnels are working, so Oracle's port appears at localhost:1521.
In a new shell/command window, try sqlplus scott/tiger1@//localhost:1521/dbs3
Since 1521 is the default, you can omit it.