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

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 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. 

Oracle DB Vault New Features in Oracle 12c R1 – Part 2 : Enabling DB Vault

In this article I will continue to describe the changes in Oracle DB Vault in  Oracle 12c version.

At the below note I explained the changes at DB Vault installation . At this note I will show you what has been changed to enable and disable DB Vault in Oracle 12c version.

https://yusufanilakduygu.wordpress.com/2017/04/16/oracle-db-vault-new-features-in-oracle-12c-changes-at-db-vault-installation/

The major changes is you have to connect to database as DB vault owner to disable and enable DB Vault in Oracle 12c. But in Oracle 11g version ,  oracle operating system user can enable and disable Oracle DB Vault. It means that ;  Oracle DBA can change DB vault status in Oracle 11g . But in version Oracle 12c only DB Vault owner can do this.

This is a big change and It makes DB Vault much more secure in Oracle 12c.

In Oracle 11g version

In Oracle 11g , you can disable and enable DB Vault bu only chopt command. Only oracle user ( operation system user ) can run this command from operating system. DBAs can disable Oracle DB Vault in Oracle 11g version and then after making changes at the Database DBAs can enable Oracle DB Vault without asking the Database Security officer. This is an insecure situation and Oracle changed it in Oracle 12c version.

Enable DB Vault in Oracle 11g

Shutdown the database
CONNECT SYS AS SYSOPER
Enter password: password

SHUTDOWN IMMEDIATE

Enable Oracle DB Vault

$ chopt enable lbac

$ chopt enable dv

And then startup the database

CONNECT SYS AS SYSOPER
Enter password: password

STARTUP

DISABLE DB Vault in Oracle 11g

Shutdown the database
CONNECT SYS AS SYSOPER
Enter password: password

SHUTDOWN IMMEDIATE

Disable  Oracle DB Vault

$ chopt disable dv

$ chopt disable lbac

And then startup the database

CONNECT SYS AS SYSOPER
Enter password: password

STARTUP

In Oracle 12c version

In Oracle 12c version you have to connect the database with an account which is a database owner. Simple; database owner can enable and disable Oracle DB vault in Oracle 12c version. And this is much more secure if you compare it with Oracle 11g version.

Enable DB Vault in Oracle 12c

connect as the Oracle Database Owner (DV_OWNER) account, and then enable Oracle Database Vault.

SQL> CONNECT dvowner
Enter password:
Connected.
SQL> EXEC DBMS_MACADM.ENABLE_DV;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

Note ; If Oracle Label security is not enabled before , You should enable it

CONNECT SYS AS SYSDBA
Enter password: password

EXEC LBACSYS.CONFIGURE_OLS;
EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;

and then restart the database;

CONNECT SYS AS SYSOPER
Enter password: password

SHUTDOWN IMMEDIATE

STARTUP 

Disable  DB Vault in Oracle 12c

connect as the Oracle Database Owner (DV_OWNER) account, and then disable Oracle Database Vault.

SQL> CONNECT dvowner
Enter password:
Connected.
SQL> EXEC DBMS_MACADM.DISABLE_DV;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

and then restart the database;

CONNECT SYS AS SYSOPER
Enter password: password

SHUTDOWN IMMEDIATE

STARTUP 

 

Finding Oracle Users with Import – Export Privileges – 2

Oracle Users with  IMPORT/EXPORT  role -2  DATAPUMP_IMP_FULL_DATABASE

Export utilities are used for extracting database objects and data from database to a file, and Import utilities are used for importing these extracted files into databases. In order to run IMPORT/EXPORT utilities you would have to have system roles which are given below.

  • IMP_FULL_DATABASE
  • DATAPUMP_IMP_FULL_DATABASE
  • EXP_FULL_DATABASE
  • DATAPUMP_EXP_FULL_DATABASE

These privileges should only be granted to authorized users. Normally database administrators should perform export and import operations. Therefore during our database assessment; we should find that these grants would only be given to DBA users.

If you want to list Oracle users  ( or roles ) which have DATAPUMP_IMP_FULL_DATABASE system role,  we could use the below query.  This query is developed by hierarchical query technique. Same query can be used on Oracle 11g and Oracle 12c versions.

Capture

The text version of the SQL are given below
SELECT
DISTINCT A.GRANTEE,
A.GRANTED_ROLE,
‘DATAPUMP_IMP_FULL_DATABASE’ GRANTED_CRITIC_ROLE
FROM
(
SELECT
DISTINCT LEVEL LEVEL_DEEP,
GRANTEE,
GRANTED_ROLE
FROM
DBA_ROLE_PRIVS
START WITH GRANTED_ROLE = ‘DATAPUMP_IMP_FULL_DATABASE’
CONNECT BY PRIOR GRANTEE = GRANTED_ROLE
) A,
DBA_USERS B
WHERE
A.GRANTEE = B.USERNAME
AND B.USERNAME NOT IN(
‘SYSTEM’,
‘SYS’
)
AND B.ACCOUNT_STATUS = ‘OPEN’;

 

In order to list users with DATAPUMP_IMP_FULL_DATABASE   in the multitenant architecture, we use the below query.

 

Capture

The text version of this query is given below.

SELECT
DISTINCT A.GRANTEE,
A.GRANTED_ROLE,
B.COMMON,
C.NAME,
‘DATAPUMP_IMP_FULL_DATABASE’ GRANTED_CRITIC_ROLE
FROM
(
SELECT
DISTINCT LEVEL LEVEL_DEEP,
GRANTEE,
GRANTED_ROLE,
CON_ID
FROM
CDB_ROLE_PRIVS
START WITH GRANTED_ROLE = ‘DATAPUMP_IMP_FULL_DATABASE’
CONNECT BY PRIOR GRANTEE = GRANTED_ROLE
AND PRIOR CON_ID = CON_ID
) A,
CDB_USERS B,
V$CONTAINERS C
WHERE
A.GRANTEE = B.USERNAME
AND B.USERNAME NOT IN(
‘SYSTEM’,
‘SYS’
)
AND B.ACCOUNT_STATUS = ‘OPEN’
AND A.CON_ID = C.CON_ID
AND B.CON_ID = C.CON_ID ;

 

Finding  users with  EXP_FULL_DATABASE Role;

Capture

And the last one DATAPUMP_EXP_FULL_DATABASE ;

Capture.JPG

 

Now look the the same SQLs in Multitenant Architecture ;

EXP_FULL_DATABASE Role for Multitenant Architecture

Capture

 

And the last one DATAPUMP_EXP_FULL_DATABASE for Multitenant Architecture

Capture

 

If we want to revoke IMPORT/EXPORT privileges from a user;  we could use below commands

 

REVOKE IMP_FULL_DATABASE FROM UserName;

REVOKE DATAPUMP_ FULL_DATABASE FROM UserName;

REVOKE EXP_FULL_DATABASE FROM UserName;

REVOKE DATAPUMP_EXP_FULL_DATABASE FROM UserName;