Table of Contents
- Connect with password
- Connect with key
- Connect with both key and password (Two-factor authentication)
- HTTP proxy
- Connection timeout
- SSH Keys
Connecting
The first thing to do when initialing SFTP communication session is connecting to the remote machine. Connecting is done with ORA_SFTP.CONNECT_HOST.
The CONNECT_HOST method returns a connection identifier (PL/SQL datatype NUMBER) which is required by all the other functions of the package.
Connecting with username and password
In the most simple scenario, a connection is authenticated with a username and password, we shall know in advance those details and use them when invoking the CONNECT_HOST method:
1 2 3 4 5 6 7 8 9 10 11 | DECLARE connection_id NUMBER; remote_ssh_port NUMBER := 22; username VARCHAR2(200); password VARCHAR2(200); BEGIN username:= 'myuser'; password:= 'mypass'; connection_id := ORA_SFTP.CONNECT_HOST('localhost', remote_ssh_port, username, password);END; / |
For FTPS connections, username/password is the only supported authentication method.
Connecting with a username and SSH private key.
The more secure connection option is when the connection is signed with a private key and a corresponding public key is already installed on the SFTP server instance.
The SSH private key must be located in a BLOB field. A full PL/SQL example can be found in [orasftp.zip]/Examples/CONNECT_WITH_SSH_KEY.sql script
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | DECLARE connection_id NUMBER; private_key BLOB; private_key_handle BFILE; PRIVATE_KEY_PASSWORD VARCHAR2(500); BEGIN -- load the key data into private_key DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE); private_key_handle := BFILENAME('MY_KEYS_DIR', 'test_putty_private.ppk'); -- directory name must be Upper case DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY); DBMS_LOB.LoadFromFile( private_key, private_key_handle, DBMS_LOB.GETLENGTH(private_key_handle) ); PRIVATE_KEY_PASSWORD := 'changeit'; connection_id := ORA_SFTP.CONNECT_HOST('sftp host name or IP', 22, 'my sftp user name', private_key, private_key_password); -- Do SFTP stuff here ... ORA_SFTP.DISCONNECT_HOST(connection_id); END; / |
Connecting with both password and SSH private key.
Although not very common it is possible for an SFTP server to be configured to require both username/password and private key for authentication. In order to establish a connection, we must use a dedicated method CONNECT_WITH_KEY_AND_PASSWORD
A full PL/SQL example can be found in [orasftp.zip]/Examples/CONNECT_WITH_SSH_KEY_AND_PASSWORD.sql script
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | DECLARE connection_id NUMBER; private_key BLOB; private_key_handle BFILE; private_key_password VARCHAR2(500); BEGIN -- load the key data into private_key DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE); private_key_handle := BFILENAME('MY_KEYS_DIR', 'test_putty_private.ppk'); -- directory name must be Upper case DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY); DBMS_LOB.LoadFromFile( private_key, private_key_handle, DBMS_LOB.GETLENGTH(private_key_handle) ); private_key_password := 'changeit'; connection_id := ORA_SFTP.CONNECT_WITH_KEY_AND_PASSWORD('host', 22, 'user', 'password', private_key, private_key_password); -- Do SFTP stuff here ... ORA_SFTP.DISCONNECT_HOST(connection_id); END; / |
HTTP Proxy support
The method used for connecting through HTTP proxy is ORA_SFTP.CONNECT_HOST_PROXY. It expects the same key or password parameters as the CONNECT_HOST method and an additional proxy parameter of type ORA_SFTP_HTTP_PROXY.
Below is an example code block where the ORA_SFTP_HTTP_PROXY structure is initialized. We have to set Host, IP address, username/password, and additional HTTP headers for the HTTP proxy server connection, like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | DECLARE connection_id NUMBER; proxy ORA_SFTP_HTTP_PROXY; private_key_handle BFILE; private_key BLOB; private_key_password VARCHAR2(500); BEGIN ... -- load key -- OK, this is just a test, we run a local HTTP Proxy on port 8000 and our test SFTP server is on port 22 -- in a real world scenario your Proxy and SFTP servers probably won't be on the same machine proxy := ORA_SFTP_HTTP_PROXY(Address => '127.0.0.1', Port => 8000, Username => NULL, Password => NULL, AdditionalHeaders => NULL); connection_id := ORA_SFTP.CONNECT_HOST_PROXY('localhost', 22, 'nasko', private_key, '', proxy => proxy); ORA_SFTP.DISCONNECT_HOST(connection_id); END; / |
The Username and Password fields of ORA_SFTP_HTTP_PROXY must be filled if the HTTP Proxy requires authentication.
The AdditionalHeaders field can hold HTTP headers needed by the HTTP proxy. We can pass several header lines separated with “|” like
AdditionalHeaders => 'Accept-Language: en-US|From: johndoe@acm.com'
A complete example is available in \Examples\CONNECT_WITH_SSH_KEY_PROXY.sql file
Connection timeout
In network communications, a timeout is a maximum period that we are eager to wait for an action to complete.
ORA_SFTP accepts two last parameters in the CONNECT_HOST method: connection_timeout and read_timeout, of type PLS_INTEGER and representing the timeout periods in milliseconds (1000 milliseconds = 1 second). By default, both have a value of 0 (zero) which means wait infinitely. There are situations when we may prefer the operations to fail (with SFTP exception) after a certain amount of time. In that case, we have to provide initial values when connecting to a remote host.
The example below will wait for 0.5 (half) second for connection and a maximum of 1 (one) second for an operation to complete.
1 2 3 4 5 6 7 8 9 10 | DECLARE connection_id NUMBER; connection_timeout PLS_INTEGER; read_timeout PLS_INTEGER; BEGIN connection_timeout:= 500; -- 0.5 seconds read_timeout:= 1000; -- 1 second connection_id := ORA_SFTP.CONNECT_HOST('localhost', 'myusername', 'mypassword', connection_timeout, read_timeout);END; / |
SSH Keys
When using the more secure way of identifying with an SSH key, the public SSH key must be installed (e.g. copied) on the remote SSH/SFTP server machine and associated with the username that we are going to use for the connection. When we start the connection we use our Private SSH key to sign the initial SSH handshake and thus authenticate to the remote machine.
SSH keys can be met in various formats, the most common being OpenSSH, PuTTY, and ssh.com format. OraSFTP recognizes them automatically and uses them transparently. The keys must be put into a BLOB field and then passed to the package.
You can create SSH key pair (public and private key) with the free utility PuttyGen.
The example below illustrates how to load an SSH private key test_putty_private.ppk from a local machine folder into a BLOB filed, which afterward can be passed to ORA_SFTP.CONNECT_HOST.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DECLARE private_key_handle BFILE; private_key BLOB; -- the SSH key will be loaded here BEGIN -- initialize the key storage DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE); -- the direcory name must be created with CREATE DIRECTORY MY_KEYS_DIR AS '/my_folder_with_ssh_keys'; private_key_handle := BFILENAME('MY_KEYS_DIR', 'test_putty_private.ppk'); -- directory name must be Upper case -- load the data into a BLOB DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY); DBMS_LOB.LoadFromFile( DEST_LOB => private_key, SRC_LOB => private_key_handle, AMOUNT => DBMS_LOB.GETLENGTH(private_key_handle) ); DBMS_LOB.CLOSE(private_key_handle); END; |
Summary
This chapter is an introduction to how to connect to an SFTP server with PL/SQL from inside the Oracle(r) Database version 11 and above.
The next step that you may want to check is how to disconnect an already established connection.