Writing a python program to check Oracle Listener

At this note; I will show you how you can write a python program
to check whether an Oracle Listener is running on a server.

First ; I want to give a brief introduction for the program ;

I will write a python function which will take two parameters.
One of them is server Ip adress and the other is server port number.
At the program ; first I will try to open a port connection and then I will
send a special message to this port. If the Oracle listener runs on the server.
The listener gives a special answer to this message. If the Oracle listener does not work on the server, you will not get an answer.

I will not add an exception handling part to this program . If you can add an exception handling part and  you can developed this program to check many port numbers and network segments  find servers which run Oracle databases on your network

Let’s start to write the program.

In python we use socket module to use sockets on the network. Therefore we need to import it like this;

 import socket

Now we can start to create our function oracle_listener_ping with two parameters

def oracle_listener_ping(p_servername,p_port):

At the first part of the program try to open a port connection to the server with below commands

  sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
  result = sock.connect_ex((p_servername, p_port))
  print(‘Connection Result >> ‘ , result)

if the connection result is 0 . It means that this port is used by the server. But It does not mean that this port is used by Oracle Listener.  For this reason we have to send the below message to this port. This is the magic part of  the program. This is the command for Oracle listener to  check it on the server. ( Hint: Wireshark will help you to analyze network packets )

# Message sent: (CONNECT_DATA=(COMMAND=ping))
# to check an Oracle listener whether is  running the server

send_msg= bytearray ([
0x00, 0x57, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00,
0x01, 0x3a, 0x01, 0x2c, 0x00, 0x00, 0x20, 0x00,
0x7f, 0xff, 0xc6, 0x0e, 0x00, 0x00, 0x01, 0x00,
0x00, 0x1d, 0x00, 0x3a, 0x00, 0x00, 0x00, 0x00,
0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
0x00, 0x00, 0x28, 0x43, 0x4f, 0x4e, 0x4e, 0x45,
0x43, 0x54, 0x5f, 0x44, 0x41, 0x54, 0x41, 0x3d,
0x28, 0x43, 0x4f, 0x4d, 0x4d, 0x41, 0x4e, 0x44,
0x3d, 0x70, 0x69, 0x6e, 0x67, 0x29, 0x29 ] )

Now send the message to the server an then close the open connection

sock.send(send_msg)
msg = sock.recv(2048)
sock.close()
print(‘FULL RETURNED MESSAGE’)
print (‘Received >> ‘,msg )
return

As I said I did not add an exception  handling part this program to make it much more readable. But If you want you can add it.

I tried it on my virtual server.

The IP of  My virtual server is  192.200.11.9 and Oracle listener runs on 1521 port like this

Capture

Now let’s check this Oracle Listener

Capture

As you see ; Oracle listener returns a special message for your command and It shows that Oracle listener is running on this server.

Successful return from the program

Connection Result >> 0
FULL RETURNED MESSAGE
Received >> b’\x00A\x00\x00\x04\x00\x00\x00″\x00\x005(DESCRIPTION=(TMP=)(VSNNUM=0)(ERR=0)(ALIAS=LISTENER))’
>>>

Otherwise you will get error messages like this

Capture

You can get the full python program from github .

I hope that this note will help you to understand how Oracle works on the network.

Oracle July 2017 Critical Patch Update

Oracle July 2017 Critical Patch Update (CPU)  has been released on this page. This CPU includes 308 new security fixes across all Oracle products. A Critical Patch Update (CPU) is a collection of patches for  security vulnerabilities and these are released in cumulative manner.

In  Document ID 2282980.1 ( metalink note ) you can find Executive Summary and Analysis for Oracle 2017 july CPU.

At this note; I will give brief information about the critical vulnerabilities which are solved in this CPU.  I especially will give information about very critical vulnerabilities. The importance of the vulnerabilities are scored by  Common Vulnerability Scoring System v3.0 and according to this classification the CVVS score between 9 and 10 is called critical vulnerabilities.  The important point of these vulnerabilities is you can compromise a  system without authentication on the network.

Let’s start with Database CPUs

In this patch there is a solution for CVE-2017-10202;  Vulnerability in the OJVM component of Oracle Database Server.  This vulnerability remotely exploitable without authentication. It is CVVS score is 9.9 and it is very high if you compare this score with other patches in 2017 . This is the maximum score in 2017.

Capture

And if you look at ; Oracle Fusion Middleware patches you will see; CVE-2017-10137 (JINDI)  CVSS Base Score: 10.0 . By HTTP protocol intruder can easily compromise  Oracle WebLogic Server without authentication.

Capture

And another very important patches for MYSQL database is CVE-2016-4436 (Apache Struts 2). It is score is 9.9. An  attacker can compromise MYSQL database via  HHTP over TLS without authentication

Capture

As you see ; there are very important solutions for security vulnerabilities in July-2017 CPU. Therefor I advice you to  apply this CPU in mean time.

Oracle 12c New Security Features – 02 DBA_USERS new columns

One of the the security improvement  in Oracle 12c version is the new columns at DBA_USERS view.
These new added columns are very uesfull for security administrators.
At the below picture you can see the definition of DBA_USERS in Oracle 11g version

Capt02

And the definition of DBA_USERS in Oracle 12c version is given below.

Capt01As you see; four new colums are added. These are

PROXY_ONLY_CONNECT
COMMON
LAST_LOGIN
ORACLE_MAINTAINED

Now we work on these new columns

PROXY_ONLY_CONNECT

This column shows  you  whether a user can connect directly ( if it is value N ) or can only be   proxied (if it is value Y) by users.
Let’s query this column;

Capture

You can enable or disable this attribute of a user by alter user command

Capture.JPG

COMMON

This column shows whether the user  is a COMMON user.

COMMON users   are used in Multitenant databases which are introduced in Oracle 12c version. In another note ; I will explain multitenant databases in Oracle 12c version.

This column can have two values ; YES or NO

YES means this user is a COMMON user.

NO means this user is a local user.

Let’s query this column

Capture

 

LAST_LOGIN

Last_login column is very useful column for database security administrator and it solves very important problem in Oracle 11g version. This column shows  the user’s last logon time. In Oracle 11g version we have create a logon trigger  and a special table to find and keep user’s last logon time. Now in Oracle 12c version you have nothing to do ; just you need the query this column to find the time of the users logon.

To query Last_login column  you use the below query. If the LAST_LOGIN column is null It means that this user has not been connected to the database yet.

Capture.JPG

ORACLE_MAINTAINED

This is another very important new column at Oracle 12c database.

If the value of this column is ‘Y’. It means that this  user was  created and could only managed by Oracle-supplied script ( Scripts are given by Oracle company) . You must not change  any properties of these users. This column is very important when running security control scripts.  At some security controls  you would like to exclude  Oracle pre-defined application users. In Oracle 11g version , you have to know these Oracle usernames (  for example DBSNMP, MDSYS,CTXSYS,OUTLN…) . In Oracle 11g version , if you want to exclude Oracle managed users  from your selection in any security control, you have to write a very big condition like below;

USERNAME NOT IN

(

‘ANONYMOUS’,’CTXSYS’,’DBSNMP’,’EXFSYS’,’LBACSYS’,’MDSYS’,’MGMT_VIEW’,’OLAPSYS’,

‘OWBSYS’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’SI_INFORMTN_SCHEMA’,’SYS’,’SYSMAN’,

‘SYSTEM’,’TSMSYS’,’WK_TEST’,’WKSYS’,’WKPROXY’,’WMSYS’,’XDB’,’APEX_PUBLIC_USER’,

‘DIP’,’FLOWS_30000′,’FLOWS_FILES’,’MDDATA’,’ORACLE_OCM’,

‘SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’XS$NULL’,

‘OWBSYS_AUDIT’,’ORDDATA’,’APEX_030200′,’APPQOSSYS’,’DVSYS’,’DVF’

)

But in Oracle 12c version you can add a condition like

ORACLE_MAINTAINED <> ‘Y’

to exclude Oracle  managed users.

Capture

I downloaded  all scripts which are given on this note  to  github

https://github.com/yusufanilakduygu/Wordpress-Posts/blob/master/DBA_USERS%20new%20columns

 

Have a good day.

Anıl

Starting to Pyton – Python Installation on Windows

In this note; I will show you how you can install Python to your Windows system.

If you have any Linux, Unix or Mac machines. You will see that ; Python is already installed. For example at my virtual Linux system Python is already installed as you see on the below picture.

Capture

 

But this is a Python 2 . In this note I will show you how you can install Python 3.

Actually There is no to many differences between Python 2 and Python 3 .  Now the  most widely used libraries have been ported to Python 3 and for new projects I advice you to start with Python 3.

Before starting to install Python 3 , Please check that Python is already installed to your windows. Now  we can download Python 3 from this web site.

https://www.python.org/downloads/

Capture

And run python-3.6.1 from downloaded directory. At my computer I already download Python 3 therefore when I start python-3.6.1.exe below screen will appear.

 

Capture

If your first installation please check Custom installation . Because If you choose default installation ; The installation directory will be under your user directory.  The best way is to choose default installation and install Python 3 to a directory named like c:\Python361

Go on Custom installation; Choose all optional Features

Capture

Then Press Next; At Advanced Options choose a directory to install Python

Capture

wait for a while; and Python is installed and start it with a command “python” in installed directory

Capture

I can  put the python directory in PATH variable. By this way you can call python from any directory at your system

Capture

Now you can play with your python.

 

Connect Oracle from Python in Windows

Python is a  very popular programming language that can be used for general purposes. It is an interpreted language with object-oriented features. At my blog I will give some information about Python how you can use it for database security subjects. at this note I will explain how you can connect Oracle from Python . I assume that you already installed Python to your PC. In the future at another note I will explain how you can install and run Python at your Windows Client.

In order to connect Oracle from Python in Windows  ; you need to downloads and install Python interface to Oracle from this website

https://pypi.python.org/pypi/cx_Oracle

According to your installation choose 32 bit or 64 bit module.

Capture

Put this interface into Scripts directory  ( C:\Python361\Scripts )  and run pip program with install option

pip install cx_Oracle-6.0rc1-cp36-cp36m-win32.whl

Now you installed Oracle interface for Python. The second operation is to make reachable oci.dll from Python to call Oracle libraries.

For this reason you need to install Oracle instant client. You can download Oracle instant client from below websites.

http://www.oracle.com/technetwork/topics/winx64soft-089540.html  ( 64 bit )

or

http://www.oracle.com/technetwork/topics/winsoft-085727.html  ( 32 bit)

At this website you can see many packages to download but only Oracle Instant Client package is enough for Python.

I download both of them and I put them in different directories. You have to just unzip these packages like below.

Capture

 

Capture

 

Now you have to add the directory of the instant package into PATH environment variable like below. To change PATH variable follow below steps

In Control  Panels choose System;

Capture

From this page choose Advanced system settings

Capture

choose Environment Variables;

In Environment Variables; choose PATH and add Oracle Instant Client path.

Capture

After changing PATH variable I advice to restart your client.

Now you can use Python module cx_Oracle to connect  Oracle database. I wrote below program to check cx_Oracle module. This program connects to an Oracle database and shows its version. At the same time It executes a small query to get database name

 

Capture

you can get the source of this code from github

https://github.com/yusufanilakduygu/Wordpress-Posts/blob/master/connect-oracle-from-python-in-windows

Check the program. Simply run it. You should get below result.

Capture

Now you installed and configured Oracle package ay Python. You can use it your projects.

 

Oracle DB Vault New Features in Oracle 12c Release 1 – Part 4 : New Realms

Now we continue to work on changes in  Oracle Database Vault  in 12c Release 1 version. Three new Realms are added in this release . These are;

  • Oracle Default Schema Protection Realm
  • Oracle System Privilege and Role Management Realm
  • Oracle Default Component Protection Realm

 

Let’s check these new Realms with the query.

 

SELECT

*

FROM

DVSYS.DBA_DV_REALM;

null

What objects are protected in  these new Realms ? Let’s start with Oracle Default Schema Protection Realm;

SELECT

*

FROM

DVSYS.DBA_DV_REALM_OBJECT

WHERE

REALM_NAME = ‘Oracle Default Schema Protection Realm’;

null

This realm protects roles and schemas in Oracle OLAP, Oracle Spatial, and Oracle Text. (CTXSYS, MDDATA and MDSYS)

 

SELECT

*

FROM

DVSYS.DBA_DV_REALM_AUTH

WHERE

REALM_NAME = ‘Oracle Default Schema Protection Realm’;

null

Oracle System Privilege and Role Management realm. This realm protects sensitive roles which are given below. Some import and export roles , Java roles , audit management roles, catalog operation roles..

null

 

Oracle Default Component Protection Realm ;  This realm protects the SYSTEM and OUTLN schemas.

null

You can get the SQL queries in this note at github

 

Oracle DB Vault New Features in Oracle 12c Release 1 – Part 3 :  Oracle Enterprise Manager Cloud Control to Manage DB Vault.

After Oracle 12c Release 1 version you can use all DB Vault functionality with Oracle Enterprise Manager Cloud Control . DBMS_MACADM PL/SQL package procedures and functions have included in Oracle Enterprise Manager. For each operation you can see the running script by pressing Show SQL button.

You should connect to Enterprise Manager to run  Database Vault  Administrator with  a user who has DV_OWNER role. After logon  Database Vault home page appears like this;

db-vault01

At this page ;  you can see

  • Violations that were made against to DB Vault rules
  • Database Vault Alerts
  • Audit Reports

In order to make operations on DB Vault objects; you have to go Administration section . In the below picture ; you can see Command Rules page in Administration  section.

db-vault02

If you want to see all default rules ; you should check Show Oracle defined Command Rules box.If you want to create a new command rule;  just click Create button

db-vault03

You can enter all necessary parameter to create a new Command Rule at this page. After clicking Show SQL button you can see the necessary script to create a new command rule

db-vault04

 

For example ; If you want to see all details about a Command Rule; Choose the Command rule 

db-vault05

 

And then just double click on it.

db-vault06

Another functionality of DB Vault is you can control the authorization on some database operations . For example ; you can identify which user can make data pump operations , Goldengate operations or database patching. You can define all these users  from Oracle Enterprise Manager by choosing ; Database Operation Authorization section

db-vault08

 

After choosing Database Operation Authorization section ; you can see all database operations which are controlled by DB Vault

db-vault09

 

You can add , edit or delete usernames from this page for each database operations.

As you see ; In Oracle 12c Release 1 version you can use all functionality of Oracle DB Vault and the most important thing is Oracle does not support old Oracle DB Vault Console after Oracle 12c version.  Therefore if you are a Database Vault administrator you should learn Oracle Enterprise Manager DB Vault functionality.