eMarcel.com

Oracle Database, Fusion Middleware, Linux

Around The World Flight 24, Nairobi to Kalemie

A spectacular flight along the shores of Lake Victoria and Lake Tanganyika.

Kalemie_APPR

Another highlight of the tour is the opportunity to fly close to the shores of both Lake Victoria and Lake Tanganyika as we head to Kalemie in the Democratic Republic of the Congo. Lake Victoria is one of the African Great Lakes. With a surface area of approximately 68,800 km2 (26,600 sq mi), it has a maximum depth of 84 m (276 ft) and an average depth of 40 m (130 ft). Lake Victoria is Africa’s largest lake by area, the world’s largest tropical lake, and the world’s second largest fresh water lake by surface area, after Lake Superior in North America.

NASA http://eol.jsc.nasa.gov/sseop/EFS/photoinfo.pl?PHOTO=STS51G-34-12, Public Domain, https://commons.wikimedia.org/w/index.php?curid=26671324

Lake Tanganyika, Tanzania, Zaire, and Zambia June 1985

Lake Tanganyika is another African Great Lake. The lake covers 32,900 km2 (12,700 sq mi), with a mean depth of 570 m (1,870 ft) and a maximum depth of 1,470 m (4,820 ft). It is estimated to be the second largest freshwater lake in the world by volume, and the second deepest, in both cases, after only Lake Baikal in Siberia. It is also the world’s longest freshwater lake. The lake is divided among four countries – Tanzania, Democratic Republic of the Congo (DRC), Burundi, and Zambia. Kalemie, formerly Albertville or Albertstad, is a town on the western shore of Lake Tanganyika. (source: wikipedia)

Continue reading…

, , , , , ,

Data Redaction in Oracle Database 12c flaws or security gaps?

I’ve been working on proof of concept project for Data Redaction in Oracle Database 12c. Hard to say but POC has proven that data redaction has couple of flaws or according to Oracle “constraints”. Therefore before we could continue with implementation we would have to find solutions to below findings. Any feedback from the readers would be much appreciated.

Test scenario: Database user schema is “APEX_ZION”. A table “DEMO_CUSTOMERS” has data redaction enabled on CUST_POSTAL_CODE column, masking data using randomly generated characters. Here is how to enable data redaction. Another user schema “TEST_USER” has granted SELECT privileges on DEMO_CUSTOMERS table. The goal is to mask sensitive data for test_user only. So when we login to a database as “APEX_ZION” and we run an SQL query we can see true data:

SQL> SELECT CUST_POSTAL_CODE FROM APEX_ZION.DEMO_CUSTOMERS;

CUST_POSTAL_CODE
----------------
20166     
30320     
02128     
60666     
11371     
63145     
06096     

 7 rows selected

Next we run the same SQL query as “test_user” and we can see masked data only:

SQL> SELECT CUST_POSTAL_CODE FROM APEX_ZION.DEMO_CUSTOMERS;

CUST_POSTAL_CODE
----------------
]2x#( 
_UJX/ 
\Bzy# 
z:*Qr 
L`!<I 
oBE&5 
N"2G] 

 7 rows selected 

So far so good. Here comes the funny part…

Using Oracle SQL Developer we are logged as “test_user” (1), We run SQL query (2) and as a result we can see masked data. Next, in the same session navigate to Other Users (3) -> APEX_ZION (4) -> Tables (5) -> DEMO_CUSTOMERS (6) and click on Data tab (7) to view table’s content. Surprise, the data is not being redacted anymore.

In addition please read David Litchfield’s white paper “Oracle Data Redaction is Broken” here (PDF). I checked all three methods in my labs in September 2016 and in result we see that using “RETURNING INTO” and “XMLQUERY()” methods appear to be fixed. However one gap still persist in a Database 12c version 12.1.0.2 – “an iterative inference attack”. It is possible to be executed disclosing redacted data even to a regular test_user, with “create procedure” and “select” privileges on a target table! According to Oracle documentation it is a constraint in data redaction.

Below test result based on example 3 from David Litchfield’s white paper:

select cc from APEX_ZION.REDACTIONTEST;
CC 
-------
XXXXXXXXXXXXXXXX

--an iterative inference attack
exec p_undoredaction;
PL/SQL procedure successfully completed.
CC: 4111222233334444

All in all Oracle Data Redaction seems to be very handy feature next to well known Oracle Data Masking option. However facing above flaws it is a bit pointless to implement it in any of the environments. And the sad part of it is that David Litchfield has reported his findings to Oracle in 2013 and Oracle did not “fix” it yet (2016). I’m not sure whether bug from SQL Developer has been already reported to Oracle, this is something that I’ve discovered today. Please leave your comments if you know more similar flaws or how to tackle existing ones.

EDIT: Even though Oracle Data Redaction is not designed to prevent data exposure to database users who run ad hoc queries directly against the database, it can provide an additional layer to reduce the chances of accidental data exposure. Because such users may have rights to change data, alter the database schema, and circumvent the SQL query interface entirely, it is possible for a malicious user to bypass Data Redaction policies in certain circumstances. Source: Oracle Documentation

More can be read here Security Considerations for Using Oracle Data Redaction:

Oracle Data Redaction is not intended to protect against users who run ad hoc SQL queries that attempt to determine the actual values by inference.

Now I’m asking myself, what’s the point of using data redaction whatsoever? It’s like asking my boy not to watch certain TV channels securing them wit default security pin 0000. Do you know any use cases of using data redaction? Please leave your comments down below.

Cheers!!

, , , ,

Oracle Data Redaction in Oracle Database 12c

Data Redaction provides a way to define masking policies for an application. Oracle Data Redaction provides functionality to mask (redact) data that is returned from user SELECT queries. The masking takes place in real time. The difference between Oracle Data Masking and Data Redaction is that Data Redaction doesn’t alter underlying data in the database; it redacts the data only when it is being displayed. Data Redaction can be applied conditionally, based on different factors such as user, application identifiers, or client IP addresses. Data Redaction is available in Oracle Database 12c and now also in 11g Release 2, patch set 11.2.0.4. Data Redaction is licensed as part of Oracle Advanced Security.

EDIT: Be aware of Oracle’s data redaction “constraints”. Read David Litchfield’s white paper “Oracle Data Redaction is Broken” here (PDF). I checked all three described methods in my labs in September 2016 and using “RETURNING INTO” and “XMLQUERY()” methods appears to be fixed. However one gap still persist in a Database 12c version 12.1.0.2 – “an iterative inference attack”. It is still possible to be executed disclosing redacted data even to a regular test_user schema!

Below TEST 3 result based on David’s white paper example:

select cc from APEX_ZION.REDACTIONTEST;
CC 
-------
XXXXXXXXXXXXXXXX

--an iterative inference attack
exec p_undoredaction;
PL/SQL procedure successfully completed.
CC: 4111222233334444

data _redaction_concept

Data redaction doesn’t prevent application logic, operations like inserting, updating or deleting data  are perfectly consistent with original data. If the application user creates a view on redacted table, the view will also contain the redacted data.

The following methods are available in data redaction:

data _redaction_methods

Oracle Data Redaction in Oracle Enterprise Manager 12c:

In our demo we will be using sample table created in “APEX_ZION” schema running in Oracle Database 12c pluggable database. Customer data resides in CUSTOMERS table in APEX_ZION schema. We’ve got ~50k of records in the table. We generated our sample data at fakenamegenerator.com and imported it using Oracle SQL Developer.

Here is an sql script to create a demo table with several rows and test user account:

as APEX_ZION user
create table customers (
	customer_id		number,
	cust_first_name		varchar2(20),
	cust_last_name		varchar2(20),
	cust_phone_nr		varchar2(25),
	CUST_STREET_ADDRESS	varchar2(60),
	cust_postal_code	varchar2(10),
	cust_city		varchar2(30),
	cust_email		varchar2(60),
	cust_url		varchar2(100),
	cust_national_id	varchar2(20)
	
Insert into CUSTOMERS values ('124','George','Haynes','478-226-7323','4145 Oakridge Lane','30901','Augusta','GeorgeAHaynes@dayrep.com','closedloopcolor.com','670-09-4251');
Insert into CUSTOMERS values ('125','William','Bill','205-968-8067','3553 Wright Court','35243','Cahaba Heights','WilliamCBill@gustr.com','jswimshop.com','419-92-3203');
Insert into CUSTOMERS values ('126','Rhonda','Finley','217-615-9990','1405 University Hill Road','62701','Springfield','RhondaNFinley@cuvox.de','istanbulchekup.com','345-40-5460');
Insert into CUSTOMERS values ('127','Rosana','Solis','630-912-3504','747 Lewis Street','60606','Chicago','RosanaASolis@teleworm.us','germanfacials.com','342-30-5422');
Insert into CUSTOMERS values ('128','Craig','Montz','407-296-6024','1326 Barnes Street','32808','Orlando','CraigAMontz@dayrep.com','moocroot.com','768-32-5412');
Insert into CUSTOMERS values ('129','Todd','Barrios','325-282-5551','2445 Felosa Drive','79506','Blackwell','ToddJBarrios@teleworm.us','rekzai.com','636-84-3118');
Insert into CUSTOMERS values ('130','Ida','Cochran','334-566-0879','3410 Turkey Pen Lane','36081','Troy','IdaJCochran@gustr.com','onwebinsurance.com','417-04-4580');
Insert into CUSTOMERS values ('131','Lewis','Anderson','812-442-2562','709 Lucy Lane','47834','Brazil','LewisBAnderson@armyspy.com','oreauction.com','309-98-5662');
Insert into CUSTOMERS values ('132','Cheryl','Winters','559-501-6054','2155 Chicago Avenue','93721','Fresno','CherylGWinters@jourrapide.com','mcdube.com','573-54-1811');
Insert into CUSTOMERS values ('133','Leann','Sullivan','773-396-7992','1060 Oakmound Drive','60605','Chicago','LeannDSullivan@cuvox.de','localvim.com','356-78-9654');

commit;

connect sys / as sysdba
create user test_user identified by *******;
grant insert, select, delete, update on APEX_ZION.customers to test_user;
grant create session to test_user;

Our goal is to redact the following columns for all other users than apex_zion:

Column Name Data Redaction method
cust_phone_nr Partial
cust_postal_code Random
cust_email RegExp
cust_national_id Partial

Login to the Oracle Enterprise Manager 12c, next login to a target database and create data redaction policy:

New policies are effective immediately, let’s test it in Oracle SQL Developer by selecting the same data from two different user accounts:

Data Redaction policies can also be managed in Oracle SQL Developer.

First we have to grant the following privileges to user APEX_ZION

--as sysdba user:
grant select on sys.redaction_policies to apex_zion;
grant select on sys.redaction_columns to apex_zion;
grant execute on dbms_redact to apex_zion;

Data Redaction in SQL Developer:

That’s all folks. For more information please check DBMS_REDACT at Oracle Docs.

Please leave your comments down below.

, , ,

Configure SSL in WebLogic Server Domain

Quick guide on how to implement SSL in WebLogic Server domain. Custom Identity and Custom Trust with self-signed certificate.

First, let’s create custom directory to store self-signed certificate, custom keystore and custom trust store files:

mkdir -p /u01/app/oracle/config/domains/wls12c_domain/security/SSL

Modify input variables according to your requirements and run below script on WebLogic Server host. This script will automate entire procedure and does the following:

  • create keystore
  • create self-signed certificate
  • export the server certificate
  • create Trust Store
#Script: Generate Keystore
DOMAIN_HOME=/u01/app/oracle/config/domains/wls12c_domain
cd ${DOMAIN_HOME}/security/SSL

JDK_HOME="/bin"
ALIAS="zion"
DNAME="CN=example.local.net, OU=ZION Support, O=ZION Inc, L=Gotham, ST=StateOfMind, C=EU"
KEYPASS="password"
IDENTITY_JKS="identity.jks"
STOREPASS="password"
CERT_CER="cert.cer"
TRUST_JKS="trust.jks"

#create-keystore
echo "Creating keystore"
${JDK_HOME}/keytool -genkey -alias ${ALIAS} -keyalg RSA -keysize 2048 -sigalg SHA256withRSA -dname "${DNAME}" -keypass ${KEYPASS} -keystore ${IDENTITY_JKS} -storepass ${STOREPASS}

#Self Signing the Certificate
echo "Self Signing the Certificate"
${JDK_HOME}/keytool -selfcert -alias ${ALIAS} -dname "${DNAME}" -keypass ${KEYPASS} -keystore ${IDENTITY_JKS} -storepass ${STOREPASS}

#Exporting the Server certificate
echo "Exporting the Server certificate"
${JDK_HOME}/keytool -export -alias ${ALIAS} -file ${CERT_CER} -keystore ${IDENTITY_JKS} -storepass ${STOREPASS}

#Creating Trust Store
echo "Creating Trust Store"
${JDK_HOME}/keytool -import -alias ${ALIAS} -file ${CERT_CER} -keystore ${TRUST_JKS} -sigalg SHA256withRSA -storepass ${STOREPASS} -noprompt

echo ""
echo "Done"
echo ""

To check the content of the keystore

keytool -v -list -keystore identity.jks

Next, Login to Weblogic Server Admin Console, go to Servers and select the managed server you want to update.

  1. Go to Configuration -> Keystore tab
  2. Click the Change button under Keystore Configuration and select Custom Identity and Custom Trust
  3. For the Custom Identity Keystore, enter the path to identity.jks file, next type in Keystore type: JKS
  4. Enter your Custom Identity Keystore Passphrase as the password
  5. For the Custom Trust Keystore: enter the path to trust.jks file
  6. Keystore Type: JKS. Click Save button
  7. Go to Configuration -> Keystore -> SSL tab
  8. Enter the server certificate key alias (in our example: zion), and the keystore password
  9. Click Save to apply the changes.

Finally go to Configuration -> General tab and enable managed server to be listening on SSL port. Save changes.

You need to reboot WebLogic managed servers for the changes to take effect.

Extra tip: You may like the following WLST script which will automate the WebLogic Server configuration part – WLS_configure_SSL

Cheers!!

, , , , ,

Oracle Live SQL is live. Code SQL in a Web Browser

Oracle Live SQL is a free online tool to learn and code SQL & PL/SQL in Oracle Database. Learn and share SQL, for free.

source: oracle.com

Get instant access to the Oracle Database and learn from a collection of community scripts and structured tutorials. Save and share SQL sessions with others!

Oracle Live SQL

Oracle Live SQL exists to provide the Oracle database community with an easy online way to test and share SQL and PL/SQL application development concepts.

  • Browser based SQL worksheet access to an Oracle database schema
  • Ability to save and share SQL script
  • Schema browser to view and extend database objects
  • Interactive educational tutorials
  • Customized data access examples for PL/SQL, Java, PHP, C
  • Learn more by using Live SQL yourself. Login to OTN and get started.

Live SQL Content

SQL Queries, PL/SQL Procedures, Functions, Packages and more!

Browse the collection of SQL scripts and tutorials: View ALL

source: oracle.com

, , , , ,

How to enable Oracle Enterprise Manager Express 12c

Oracle Enterprise Manager Express is a Web-based interface for managing an Oracle database 12c. It enables users to perform basic administrative tasks such as managing users, managing database initialization parameters, memory or storage. You can also view performance and SQL Tuning Advisor information, check status information about your database and pluggable databases.

In Oracle Database 12c Release 1, the concept of multi-tenant environment has been introduced. The multi-tenant architecture enables an Oracle database to function as a multi-tenant container database (CDB) that includes zero, one, or many customer-created pluggable databases (PDBs).

A CDB includes the following components:

Root named CDB$ROOT, stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages. A common user is a database user known in every container.

A PDB appears to users and applications as if it were a non-CDB. For example, a PDB can contain the data and code required to support a specific application (e.g., APEX).

Each of these components is called a container. Therefore, the root is a container, the seed is a container, and each PDB is a container.

In this tutorial we will show two different types of configurations of Enterprise Manager Express one for CDB and the second for PDBs only. Imagine yourself as a dba who has full access to non-CDB/CDB/PDB, OEM Express 12c will allow you to manage CDB and all PDB containers from one central console. On the other hand you would like to allow regular users to login to OEM Express 12c as well, but grant them access to their PDBs only.

multitenant_db12c_01

Continue reading…

, , , , , ,

Installing Oracle Linux 7 for Oracle software deployments

This demo shows installation process of Oracle Linux 7 in virtual machine (VirtualBox) and basic OS configuration for further Oracle software deployments e.g., Oracle Databases, WebLogic Servers.

oracle_linux-7

1. Download Oracle Linux ISO image from Oracle Software Delivery Cloud

Continue reading…

, ,

Shell script: Copy files from source to target directory

Simple shell script to search and copy files from one folder to another.

Pretty useful if there is a need to copy/move files from one folder to another (e.g., Dropbox) in case that running program cannot be configured to write output files to multiple locations. The existing files in the destination folder are skipped. However bear in mind that this script doesn’t cover currently opened files, or it will not transfer the files again if the size of the source file has changed – for such scenario it would be useful to run rsync utility.

#!/bin/sh
#This script will search for files defined by SUFFIX_ARG in SPATH
#directory and copy them to DPATH directory
#The files that already exist in destination will be skipped

#Script Variables
SPATH="/home/user/source"
DPATH="/home/user/target"
SUFFIX_ARG="*.jpeg"
ACTION_TIME=`date +"[%d-%m-%y %T]:"`
COPY_LOG=COPY-`date +"%d-%m-%y_%T"`.log

echo "${ACTION_TIME} Starting copy task" >> ${COPY_LOG}
find ${SPATH} -type f -name "${SUFFIX_ARG}" -print | while read path
do
   FN="${path##*/}" #extract the file name from the path
   ACTION_TIME=`date +"[%d-%m-%y %T]:"`
  
   if [ -e "${DPATH}/${FN}" ]   #if the destination file exists, skip it
   then
      echo "${ACTION_TIME} Skipped: File ${DPATH}/${FN} already exist" >> ${COPY_LOG}
   else
       echo "${ACTION_TIME} Trying to copy ${FN}" >> ${COPY_LOG}
	   cp "$path" "${DPATH}/${FN}" >> ${COPY_LOG}
	   ACTION_TIME=`date +"[%d-%m-%y %T]:"`
	   echo "${ACTION_TIME} Copy ${FN} to ${DPATH} Complete" >> ${COPY_LOG}
   fi
done

As an alternative we could use rsync utility in order to synchronize multiple folders in the system. This is useful when we run rsync and there are still opened files. Once the particular file has been written, next rsync run will also synchronize this file, regardless whether it exists in the target folder or not.

rsync -q -axr --delete --exclude "DIR1" --exclude "DIR2" --include "*/" --include="*.jpeg" --exclude "*" /home/user/source /home/user/target

What we do in rsync is the following:

-q, --quiet		suppress non-error messages
-a, --archive		archive mode
-x, --one-file-system	don't cross filesystem boundaries
-r, --recursive		recurse into directories
--delete		delete extraneous files from destination
--exclude=PATTERN	exclude files matching PATTERN
--include=PATTERN	don't exclude files matching PATTERN

Cheers!!

,

Oracle SOA Suite 11g: a workaround for long running SQL queries

Oracle SOA Suite 11g (11.1.1.7) environment is connected to a database which is its dehydration store database, used to store process status data, especially for asynchronous BPEL processes. Production environments tend to produce millions of records in tables of that database. This, when not well maintained, can become a true performance bottleneck in the database and SOA Suite engine itself. Some of the performance issues can be caused by long running queries being queued in a database. Here is the example. Three SQL queries were executed in a database in parallel, multiple times per minute but it required 1-4 minutes for database in order to resolve each of them. Next to that there were more than 150M of records in CUBE_INSTANCE table, because historical composites’ instances were not being purged properly. The entire environment was monitored by Oracle Enterprise Manager 12c.

oem12c_active_sessions_1

Here are the 2 of 3 SQL queries (3rd query has different solution):

SELECT ci.DOMAIN_NAME ,
 ci.COMPOSITE_NAME ,
 ci.COMPOSITE_REVISION,
 ci.COMPONENT_NAME ,
 ci.STATE ,
 COUNT(*)
FROM CUBE_INSTANCE ci
WHERE ci.COMPONENTTYPE = :1
GROUP BY ci.DOMAIN_NAME ,
 ci.COMPOSITE_NAME ,
 ci.COMPOSITE_REVISION,
 ci.COMPONENT_NAME ,
 ci.STATE
ORDER BY ci.DOMAIN_NAME ,
 ci.COMPOSITE_NAME ,
 ci.COMPOSITE_REVISION,
 ci.COMPONENT_NAME ,
 ci.STATE

Continue reading…

, ,

Fix for ORA-03113: end-of-file on communication channel

Oracle Database Server 12c Here is how to fix ORA-03113: end-of-file on communication channel

Cause: I issued shutdown immediate command in a running database instance. The database was busy and it took ages to shut it down. I was impatient and I did kill a virtual machine server. After virtual machine restart, the database did not start any more, throwing ORA-03113 error.

[oracle@host ~]$ sqlplus / as sysdba
...
...
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4903
Session ID: 237 Serial number: 26032

Solution:

SQL> exit
Disconnected from Oracle Database 12c 
Enterprise Edition Release 12.1.0.2.0 - 64bit Production

[oracle@zeus ~]$ sqlplus / as sysdba
...
...
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes
SQL> alter database mount;

Database altered.

SQL> alter database clear unarchived logfile group 1;
Database altered.

SQL> alter database clear unarchived logfile group 2;
Database altered.

SQL> alter database clear unarchived logfile group 3;
Database altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2926472 bytes
Variable Size 1224738936 bytes
Database Buffers 905969664 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL>

Uff… Database opened!

Cheers!

, ,

Previous Posts

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close