Using Wrap Utility to Hide the SQL Code in Oracle

Posted on July 15, 2019 by Ajitabh

At times we may need to encrypt sensitive information in certain columns in Oracle Database. Oracle provides a DBMS_CRYPTO package for the purpose. More details about this package can be found in Oracle Documentation here.

The approach I followed is as follows –

  • Create a custom package using features provided in DBMS_CRYPTO, which will consists for functions to encrypt / decrypt varchar and blob type columns.
  • Use this package to encrypt / decrypt the data in the fields.

For a reference implementation of such a code, you can refer to the Oracle Documentation mentioned above.

One key aspect is the use of a ‘Key’ in the encryption logic. In the sample code given on the documentation, a random key has been chosen. This random key is fine as long as you do not recompile the package. If the package is recompiled then the key changes and hence invalidates all the encrypted data which can not be decrypted in the absence of the old key.

If we keep the key as fixed, then anyone looking at the source code of the package can see the key and thus potentially compromising on the security aspects.

To resolve this, fortunately Oracle provides a wrap utility to hide the source code.

$ wrap iname=enc_dec.sql oname=enc_dec.sql.wrp

SQL> @enc_dec.sql.wrp;

Now anyone looking at the source code of the package will find gibberish understood by Oracle only. However, the original source with the key has to be kept safe, just incase you need it back again.

Posted in Databases | Leave a comment

Apt Pinning in Raspbian

Posted on June 17, 2018 by Ajitabh

Quite sometime back I wrote a blog entry on apt-pinning to mix multiple repositories in debian and prioritize them. Recently, I felt the need to do the same on my raspberry pi.

I use rsnapshot to backup remote systems. Rsnapshot has an associated perl script which is meant to send beautiful reports via email at the end of the backup. The script in the version which came with raspbian was broken (1.3.1-4+deb8u1) and I needed 1.4.2-1, which is available in Debian Stretch.

Following my earlier post, I performed the following steps to perform the installation of the required version without impacting the rest of the system. As you can see that the priority of Jessie is higher than that of Stretch, which will ensure that the system does not get messed up when you do an upgrade.

# Create the preferences file for jessie and stretch as shown below
$ sudo vi /etc/apt/preferences.d/jessie.pref
Package: *
Pin: release n=jessie
Pin-Priority: 900

$ sudo vi /etc/apt/preferences.d/stretch.pref
Package: *
Pin: release a=stretch
Pin-Priority: 750

# Define the package sources for both jessie and stretch
$ sudo vi /etc/apt/sources.list.d/jessie.list
deb http://mirrordirector.raspbian.org/raspbian/ jessie main contrib non-free rpi

$ sudo vi /etc/apt/sources.list.d/stretch.list
deb http://mirrordirector.raspbian.org/raspbian/ stretch main contrib non-free rpi

# Refresh the cache and sources list
$ sudo apt-get update

# Install the desired package by specifying the repository from which 
# it has to be installed
$ sudo apt-get install rsnapshot -t stretch

Please be careful before performing these steps in a production system.

Posted in FLOSS, RaspberryPi | Tagged , , | Leave a comment

Ansible Quirks – 4

Posted on June 16, 2018 by Ajitabh

I was installing ansible on OpenSuSE Leap 42.3 machine and faced a problem with multiple python versions. I have both python 2.7 and python 3.4 installed on this machine. Python 2.7 is the default one.

I tried installed ansible with the usual pip install and was faced with an error related to setuptools (could not capture the error message). I upgraded my pip version to the latest one 10.0.1 and then installed ansible.

$ sudo pip install --upgrade pip
$ sudo pip install ansible

After that I ran an ansible-galaxy init command to create one of the roles and I was welcome with the error stating “Error: Ansible requires a minimum of Python2 version 2.6 or Python3 version 3.5. Current version: 3.4.6”. And I was wondering what happened because my default python version was 2.7.

The usual google search did not help me go anywhere. So I looked at the /usr/bin/ansible file and found that it was using python3 interpreter.

$ head -1 /usr/bin/ansible
#!/usr/bin/python3

I then checked the source of pip command and found the same thing.

$ head -1 /usr/bin/pip
#!/usr/bin/python3

So I looked at pip documentation on installing pip for python 2.7 and found this link which helped me with the command to install pip for the default version of python installed –

$ sudo python -m ensurepip --default-pip
Requirement already satisfied: setuptools in /usr/lib/python2.7/site-packages
Collecting pip
Installing collected packages: pip
Successfully installed pip-9.0.1

Now when I reinstalled ansible, it installed the correct python interpreter for my system.

$ sudo pip install ansible
Collecting ansible
  Cache entry deserialization failed, entry ignored
.....
.....
You are using pip version 9.0.1, however version 10.0.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
$ head -1 /usr/bin/pip /usr/bin/ansible
==> /usr/bin/pip <==
#!/usr/bin/python

==> /usr/bin/ansible <==
#!/usr/bin/python

Now when I upgraded pip it was for the default python version

$ sudo pip install --upgrade pip
Collecting pip
Using cached https://files.pythonhosted.org/packages/0f/74/ecd13431bcc456ed390b44c8a6e917c1820365cbebcb6a8974d1cd045ab4/pip-10.0.1-py2.py3-none-any.whl
Installing collected packages: pip
Found existing installation: pip 9.0.1
Uninstalling pip-9.0.1:
    Successfully uninstalled pip-9.0.1
    Successfully installed pip-10.0.1
$ head -1 /usr/bin/pip /usr/bin/ansible
==> /usr/bin/pip  <==
#!/usr/bin/python

==> /usr/bin/ansible <==
#!/usr/bin/python</p>

In all probability all this was my fault and I think the pip which I upgraded in the beginning of this process may have been installed for python3 by me, otherwise I do not see any reason for all these issues.

Still I thought to share this entry, just in case someone can be helped.

 

Posted in Tips/Code Snippets | Tagged , | 1 Comment

Running Oracle 11g Release 2 (11.2.0.2) XE in Docker Container

Posted on February 9, 2018 by Ajitabh

In DockerCon2017 Oracle announced the availability of Oracle containers in the docker hub. This was a great step forward. I have been thinking of trying Oracle in docker from quite a while for some personal projects, but was not finding time to experiment. The availability of docker build files on github made this task easier. You would still need to download the Oracle Database files from OTN and place them in the prespecified directory.

I tried 11g R2 XE and it was successful. The instructions provided are quite decent so I am not going to explain everything here again.

I am running Docker 17.04.0-ce on OpenSuSe Leap 42.2. While building both 12c and 11gR2 containers I was getting “HEALTHCHECK Unknown flag error”. This is because  HEALTHCHECK option was made available in Docker 17.06 onwards. As suggested in this issue, I simply removed the following line from the Dockerfile.xe and then the image built flawlessly.

HEALTHCHECK --interval=1m --start-period=5m \
CMD [ "su", "-p", "oracle", "-c", "$ORACLE_BASE/$CHECK_DB_FILE > /dev/null || exit 1" ]

Following is the quick sequence of steps I followed to build the image and to test the connectivity using sqlplus from the container itself.

user01@mylaptop > ./buildDockerImage.sh -v 11.2.0.2 -x
user01@mylaptop > docker run --name oracle --shm-size=1g -p 1521:1521 -p 8080:8080 -e ORACLE_PWD=Passw0rd123 -v /home/user01/Documents/oradata:/u01/app/oracle/oradata oracle/database:11.2.0.2-xe
user01@mylaptop > docker stop oracle
user01@mylaptop > docker start oracle
user01@mylaptop > docker exec -it --user oracle oracle bash
bash-4.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 9 01:02:42 2018

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


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select sysdate from dual;

SYSDATE
---------
09-FEB-18

SQL>

Next I am planning to get a container running tomcat with ORDS on it. Stay tuned.

Posted in Containers, Databases | Tagged , | Leave a comment

Connecting to Oracle Database over SSH using SQLDeveloper 4.1

Posted on May 30, 2017 by Ajitabh

As a system administrator, I try to follow the principle of least privilege (minimal privilege). Therefore, in the systems I install, I block all incoming and outgoing ports by default and open only when needed.

While setting up Oracle XE on some machines, port 1521 usually needs to be open so that engineers can connect to the database on that port and perform their developmental work. If this port is blocked and only SSH port is opened in the local firewall, then SQL Developer 4.1 provides a facility to create SSH tunnel and allows connections over that secure tunnel.

Get Started

  • Launching SQL Developer 4.1, click on  the View > SSH menu to open up the SSH Connections panel.
  • In the SSH panel, right-click on the SSH Hosts and choose New SSH Host
  • Complete the SSH connections dialog –
    • Name – This is the connection name and can be anything which identifies the connection.
    • Host – This is the host name or IP address of the host where we are trying to establish the SSH connectivity.
    • Port – This is the port on which your SSH daemon listens to. By default the port is 22, but if your SysAdmin has changed this to something else, please put that value here.
    • If you use a SSH Key File to connect to the server, select the check box “Use key file” and then Browse and select the private key file which needs to be used to establish connection. In case you use password, the you can ignore this check box and during connection, you will be prompted for your password.
    • In order to tunnel the Oracle connections inside an SSH tunnel, you need to select the check box “Add a Local Port Forward”. I normally leave the values in the Name, Host and Port fields to default, but you can change them. If you change these values from Default, localhost and 1521 respectively, then please note it down as you will need to specify this host name and port number in the Oracle Database connections dialog box.

 

  • You can now right click on the SSH Host which you created and click on “Test” to test the connection.
  • After you see the success message, create a new connection for your Oracle database by providing your connection details as usual.
    • If you select the “Connection Type” as “Basic” from the drop-down, the only change would be you host name and port number in case you changed those while creating the SSH connection.
    • If you select the “Connection Type” as “SSH” from the drop down, you just select the Connection name which you created from the “Port Forward” drop down.

I hope this helps.

Posted in Databases | Tagged , | Leave a comment