Masking Data according to User roles in Oracle Database with Data Redaction

At this note I will show to you how you can mask data according to the role of users by using Data Redaction. Actually I will not explain Data Redaction in detail , I assume that you already know about Data Redaction. But in the future I will give detailed information about Oracle Advanced Security and Data Redaction.

Now in this note we will use Data Redaction to mask data according to session roles. Virtual Private database can be used instead of Data Redaction. I will show it in the another note.

I will explain this subject with a sample. In the sample we have an user which holds data (rep_user) , application user ( app_user which can see all data ) and inq_user ( data will be masked for this user) . At the example; Only users which have a special role (redact_role) can not be affected by data redaction policy.

Let’s build up the environment and create users.

First ;  Create rep_user

Capture

Create app_user

Capture

Create inq_user and redact_role

Capture

Capture

 

We simple create a table with one  column and we mask this column with data redaction.

Capture

Insert some data to this table and grant this table to app_user and inq_user

Capture

Now app_user and inq_user can select this table as below

Capture

Now create Data Redaction policy to hide data

Capture

And according to our policy only users with redact_role can not be affected by this policy.

To do this grant redact_role to app_user;

Capture

 

And now app_user can see the masked data but other users( inq_user)  can not reach this data.

Lets’s test it

Capture

Masked numeric data can be seen as 0  in Data Redaction ( on default).

As you see we can hide data according to user’s role by using SYS_CONTEXT function. You can change this case according to your needs.

you can reach all scripts from github

Thanks.

Anıl Akduygu

Advertisements

Applying July 2017 Oracle WebLogic Server Security Patch Part – 2

At this note ; I will show you how you can install July 2017 Oracle WebLogic Server Security Patch. At my first note I showed how you can download this security patch.

Now I assume that; you read the first note you downloaded B25A security patch.Now we can install it. Before your operation starts do not forget to take full backup of your system. This backup depends on your system configuration.

First go to Middleware home and set environments with below commands;

cd /oracle/Middleware/wlserver_10.3/server/bin
. setWLSEnv.sh
cd $MW_HOME/utils/bsu

to check environment variables, look at java version with below commands.
java weblogic.version

 WebLogic Server 10.3.6.0.170418 PSU Patch for BUG25388747 WED MAR 21 18:34:42 IST 2017                                                                                                                                              WebLogic Server 10.3.6.0  Tue Nov 15 08:52:36 PST 2011 1441050

As you see;  I  installed April – 2017 security patch , because WebLogic server version is 10.3.6.0.170418 . Before staring upgrade get a detailed information about WebLogic applied patches with below command.

$ ./bsu.sh -prod_dir=/oracle/Middleware/wlserver_10.3  -status=applied -verbose -view

ProductName:       WebLogic Server                                                                             ProductVersion:    10.3 MP6                                                                                         Components:        WebLogic Server/Core Application Server,WebLogic Server/Administration Console,WebLogic Server/Configuration Wizard andUpgrade Framework,WebLogic Server/Web 2.0 HTTP Pub-Sub Server,WebLogic Server/WebLogic SCA,WebLogic Server/WebLogic JDBCDrivers,WebLogic Server/Third Party JDBC Drivers,WebLogic Server/WebLogic Server Clients,WebLogic Server/WebLogic Web S erver Plugins,WebLogic Server/UDDI and Xquery Support,WebLog ic Server/Evaluation Database,WebLogic Server/Workshop CoCompletion Support

BEAHome:           /oracle/Middleware                                                                      ProductHome:       /oracle/Middleware/wlserver_10.3                                          PatchSystemDir:    /oracle/Middleware/utils/bsu                                                          PatchDir:          /oracle/Middleware/patch_wls1036                                                            Profile:           Default                                                                                                  DownloadDir:       /oracle/Middleware/utils/bsu/cache_dir                            JavaVersion:       1.6.0_29                                                                                           JavaVendor:        Sun                                                                                                                        Patch ID:               RVBS                                                                                                    PatchContainer:    RVBS.jar                                                                                              Checksum:          1748595871                                                                                            Severity:          optional                                                                                                      Category:          General                                                                                                        CR/BUG:            25388747                                                                                                    Restart:           true                                                                                                               Description:       WLS PATCH SET UPDATE 10.3.6.0.170418                                                         WLS PATCH SET UPDATE 10.3.6.0.170418

 As you see; Download directory is

DownloadDir:       /oracle/Middleware/utils/bsu/cache_dir 

 And the latest applied patch is

PatchContainer:    RVBS.jar 

First we will deinstall  this patch and then we will install the latest patch.

cd /oracle/Middleware/utils/bsu

 ./bsu.sh -remove -patchlist=RVBS   -prod_dir=/oracle/Middleware/wlserver_10.3

Now we deinstalled the latest patch and check WebLogic server version.

java weblogic.version

WebLogic Server 10.3.6.0  Tue Nov 15 08:52:36 PST 2011 1441050                                         Use ‘weblogic.version -verbose’ to get subsystem information                                                 Use ‘weblogic.utils.Versions’ to get version information for all modules

 Now our version is 10.3.6.0  and this is the base release. Now we can apply July-2017 security patch. Now put the downloaded patch file into

DownloadDir:       /oracle/Middleware/utils/bsu/cache_dir 

And start applying patch with the below command:

./bsu.sh -install -patchlist=B25A -prod_dir=/oracle/Middleware/wlserver_10.3

Checking for conflicts….                                                                                                                     No conflict(s) detected                                                                                                               Installing Patch ID: B25A..                                                                                                             Result: Success

 As you see; Result is success and check WebLogic Server version again.

java weblogic.version

WebLogic Server 10.3.6.0.170718 PSU Patch for BUG25869650 MON JUNE 05 18:34:42 IST 2017                                                                                                                                            WebLogic Server 10.3.6.0  Tue Nov 15 08:52:36 PST 2011 1441050

Now our version is 10.3.6.0.170718 this is correct version.

Thanks for reading this note.

Anil.

Applying July 2017 Oracle WebLogic Server Security Patch Part – 1

In this note I will show you; how you can find and download July-2017 Oracle WebLogic patch. And at the another note; I will show you how you can install this patch ( Part-2). Simply, you can use the same procedure for other Oracle WebLogic patches as well. This note will be a sample for you.

at my environment, I use Oracle Web Logic 10.3.6.0. Therefore at this note, I will focus on this version but the procedure is the same for other versions.

First go to Critical Patch Updates, Security Alerts and Third Party Bulletin

 

Capture

At this page   choose Critical Patch Update – July 2017

from this web page ; choose Oracle WebLogic Server, versions 10.3.6.0, 12.1.3.0, 12.2.1.1, 12.2.1.2

Capture

Now you can see detailed information about Oracle WebLogic security patch.

Capture

 

From this page; choose  My Oracle Support Note 2261562.1. But at this point, you need a valid metalink account to reach Oracle WebLogic security patches.

Simply; you will reach the below document.

Patch Set Update and Critical Patch Update July 2017 Availability Document (Doc ID 2261562.1)

At this document;  choose  3.3 Oracle Fusion Middleware

Capture

From here; choose Section 3.3.57 “Oracle WebLogic Server”

And now you can see all security patches for different versions.

Capture

 

I am using WebLogic Server for 10.3.6.0. Therefore I choose Patch 25869650

At this page, you can download July-2017 security patch for Oracle 10.3.6.0 version. You can choose your operating system to download the correct version.

Capture

At my next note; I will show you how you can apply this patch to your system.

See you.

 

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 in Python. You can use it your projects.