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.

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.

 

 

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;

From this page choose Advanced system settings

choose Environment Variables;

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

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

 

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.

Now you installed and configured Oracle package in 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;

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’;

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’;

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

 

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

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;

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.

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

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

 

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

 

And then just double click on it.

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

 

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

 

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.

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.

 

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;

And the last one DATAPUMP_EXP_FULL_DATABASE ;

 

Now look the the same SQLs in Multitenant Architecture ;

EXP_FULL_DATABASE Role for Multitenant Architecture

 

And the last one DATAPUMP_EXP_FULL_DATABASE for Multitenant Architecture

 

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;

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;

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

 

FROM

dvsys.DBA_DV_REALM_OBJECT

WHERE

REALM_NAME = ‘Oracle Default Schema Protection Realm’;

 

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’;

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

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

Oracle DB Vault New Features in Oracle 12c R1 – Part1 : Changes at DB Vault Installation

There are many changes at Oracle DB Vault in Oracle 12c version. At this note I will give you information about  the change at DB  Vault Installation .

1. DB Vault Installation

At Oracle 11g version you need to  relink Oracle binary before installing Oracle DB Vault. You do not need this operation in Oracle 12c.

At Oracle 11g version you need to relink binary with chopt command like below

$ chopt enable lbac

$ chopt enable dv

And You do not need in Oracl 12c vesion

In order to install and configure DB Vault In Oracle 11g version, you have to use dbca. At Oracle 12c you only need dbca to install Oracle Label Security and Oracle DB vault component . You can use DVSYS.CONFIGURE_DV packet to configure DB vault. Actually still you can do this configuration with dbca but it is optional.

DB Vault component installation is made with this page in  dbca in Oracle 12c version.

And DB Vault configuration can be made by dbca but It is optional

This gives us flexibility ; During database installation DBAs can install Oracle DB Vault without making any configuration on it. After the DB Vault installation is completed, you can make DB Vault configuration as a security officer without DBA intervention.

The below  query shows that DB Vault component is installed. But It does not mean that It is enabled.

SQL> select comp_id,status from dba_registry where comp_id in (‘OLS’,’DV’);

COMP_ID STATUS
—————————— ———–
DV VALID
OLS VALID

With DVSYS.CONFIGURE_DV; you can mention which user is DB Vault admin and which user database account manager.

SQL> BEGIN
2 DVSYS.CONFIGURE_DV (
3 dvowner_uname => ‘dvowner’,
4 dvacctmgr_uname => ‘dvacctmngr’);
5 END;
6 /

PL/SQL procedure successfully completed.

This packet is new in Oracle 12c and It gives us flexibility to security officer to configure DB vault alone. After DB Vault component installation tou do not need to rebound the database bu you need to run utlrp.sql to compile all invalid objects.

 

 

 

 

 

A SIMPLE SOLUTION FOR ORACLE TNS REMOTE VERSION DISCLOSURE

A SIMPLE SOLUTION FOR ORACLE LISTENER REMOTE VERSION DISCLOSURE

At this note I will give you some brief information about how you can hide Oracle listener version information from hackers and network scanning tools. This vulnerability is called tns remote version disclosure.

First we know that ; there is no parameters at Oracle listeners to hide version information.

And the legal solution for this problem is to filter network traffic by any means. But this solution can be very expensive to hide version information, If you think about you have many listeners.

Therefore the simple solution is to change the default listener configuration.

The main point is that ; hackers assume that you are using factory setting at your database listener. At the factory settings the name of the listener is LISTENER and the default port is 1521 or 1522

Now ; After installing Oracle binary if you do not change default listener parameters. Hackers can get your installed binary version name  by sending version command to 1521 port. The output will be liket that.

After getting this information; Hackers will try the known vulnerabilities to hack your database . Therefore it is important to hide Oracle binary version.

You can do it very easily way. Change default listener name to any complicated name. Like on the following picture.

And then restart the listener.

After that ; when any scanning tools or hackers attempt to find Oracle binary version. They will get the below answers.

After changing Listener Name, if you change default port. It will be much more difficult for hackers to hack Oracle listener. But do not forget that changing listener port could be difficult job.

I will work  on listener security topics on the next notes.

Installing Oracle DB Vault to Oracle 11g Database

Installing Oracle DB Vault to Oracle 11g Database

At this document; I will show you how you can install Oracle DB vault to Oracle 11g Database.

Step 1: Check If DB Vault installed before

We use GV$OPTION view to check this. I checked DB vault in two nodes RAC database.

SQL> column parameter format a25

SQL> column value format a25

SQL> SELECT * FROM gV$OPTION WHERE PARAMETER in ( ‘Oracle Database Vault’,’Oracle Label Security’);

INST_ID|PARAMETER |VALUE

———-|————————-|————————-

1|Oracle Label Security |FALSE

1|Oracle Database Vault |FALSE

2|Oracle Label Security |FALSE

2|Oracle Database Vault |FALSE

If Oracle database Vault and Oracle Label Security are already installed ( It means all TRUE returned from this query ) goto step 3;

Step 2: Enable Oracle Label Security and Oracle DB Vault

2.1 Close the Database

Shutdown Oracle database , stop listener ( If you opened a listener with this binary ) and stop enterprise manager ( If Enterprise manager uses this binary )

SQL> SHUTDOWN IMMEDIATE

$ lsnrctl stop listener

$ emctl stop dbconsole

2.2 Enable DB Vault Binaries

Now enable Oracle Label Security and Oracle database vault consequently with the following commands

$ chopt enable lbac

$ chopt enable dv

2.3 Open the Database

After enabling Oracle Label security and DB vault you have to open database and other closed applications.

SQL> startup

$ lsnrctl start listener

$ emctl start dbconsole

2.4 Check if Binaries linked properly

Now Check DB vault and Oracle Label Security.

SQL> column parameter format a25

SQL> column value format a25

SQL> SELECT * FROM gV$OPTION WHERE PARAMETER in ( ‘Oracle Database Vault’,’Oracle Label Security’);

INST_ID|PARAMETER |VALUE

———-|————————-|————————-

1|Oracle Label Security |TRUE

1|Oracle Database Vault |TRUE

2|Oracle Label Security |TRUE

2|Oracle Database Vault |TRUE

All returned values have to be TRUE

Step 3 : Install DB Vault

Now It is time  to install DB vault. Start installation with dbca

$ dbca

Choose Configure database option.

Chose the correct DB name to install DB vault.( If there are multiple instances at the same ORACLE_HOME)

Skip Enterprise Manager Configuration

Now Chose Oracle LAbel Security and Oracle Database Vault to install them

Now enter the usernames and passwords for Database Vault Owner and Account Manager separately.

Chose Finish to start the Installation

Now the installation will start

After this windows and the installation window will appear and shows the status of installation. After the installation finishes you can close dbca.

Now you installed Oracle DB Vault to your database. Just connect with  DB Owner and Account Manager usernames to the database to check the installation

At the following notes I will show you how you can manage Oracle DB Vault.

Security Control on Default Oracle Database Users

When you install Oracle databases , some predefined default  users are created.The name of the default users are known by hackers and these users are a attack surface for a database.  These default user passwords are the first passwords which are tried by hackers.Therefore the passwords of these users should be changed after the database installation and at the same time;  these users should be in EXPIRED & LOCKED status.

How we control the password of these passwords. Oracle database includes a view which control the password of these users;

SELECT * FROM DBA_USERS_WITH_DEFPWD;

This view shows the default users which have default passwords. Normally zero records should return from this query.

Let’s check at my database;

 

As you see nearly all the default users have default passwords at my test database.  Before changing their passwords we should  check the status of these users. If these users are in EXPIRED & LOCKED status. It is acceptable , although they have default passwords.  Zero record should return from this query, otherwise it is a big finding.

SELECT
A.USERNAME ,
B.ACCOUNT_STATUS
FROM
SYS.DBA_USERS_WITH_DEFPWD A,
DBA_USERS B
WHERE
A.USERNAME = B.USERNAME
AND B.ACCOUNT_STATUS <> ‘EXPIRED & LOCKED’;

Let’s run it at my test database;

Gotcha ; At my database there are two default users which are on OPEN mode . This  is a  finding what I have to  do is;  I have to  change these  passwords and then I have to change their status to EXPIRED & LOCKED too.

SQL> Alter user Adams identified by complexpasswd01
2         account lock password expire;

User altered.

SQL> alter user Orddata identified by complexpasswd02
2 account lock password expire;

User altered.

Now check all default user status;

 

Good  ; All default users are on EXPIRED & LOCKED status.  But still some of them have default passwords ( except Orddata and adams ). We have to change all default passwords and make them Expired & Locked with the below query

SELECT
‘Alter User ‘||USERNAME||’ identified by ‘
||dbms_random.string(‘U’, 6)
||trunc(dbms_random.value(1000,9999))
||’ account lock password expire;’
FROM
SYS.DBA_USERS_WITH_DEFPWD ;

 

Now run the output of the query;

Alter User DIP identified by JACYDY9781 account lock password expire;
Alter User MDSYS identified by KPIJES7846 account lock password expire;
Alter User SPATIAL_WFS_ADMIN_USR identified by VQOAHQ7579 account lock password expire;
Alter User CTXSYS identified by AQOXGV7508 account lock password expire;
Alter User OLAPSYS identified by RWQIOP7224 account lock password expire;
Alter User OUTLN identified by WZMAQB2175 account lock password expire;
Alter User SPATIAL_CSW_ADMIN_USR identified by YYLLQH7066 account lock password expire;
Alter User EXFSYS identified by CXDMCS3349 account lock password expire;
Alter User ORACLE_OCM identified by GXRUUP7532 account lock password expire;
Alter User DBSNMP identified by DXBASG8552 account lock password expire;
Alter User MDDATA identified by HPEFPE5098 account lock password expire;
Alter User ORDPLUGINS identified by GWOITV2439 account lock password expire;
Alter User ORDSYS identified by ZIPVNJ6941 account lock password expire;
Alter User APPQOSSYS identified by TCTUYF9776 account lock password expire;
Alter User XDB identified by QRGXXV3781 account lock password expire;
Alter User SI_INFORMTN_SCHEMA identified by MHYJOV6216 account lock password expire;
Alter User WMSYS identified by RMYTXH9752 account lock password expire;

With this query we changed default passwords and we made all users EXPIRED&LOCKED again.

Now If you query SYS.DBA_USERS_WITH_DEFPWD , zero record will return. It means that all default passwords have been changed.

SQL> SELECT * FROM DBA_USERS_WITH_DEFPWD;

no rows selected

Have a good day.

Now your default username passwords are secure.

Have a good day.

Anıl Akduygu