Hola!! A Successful IDCS Integration with EBS!!!

Configuring On-Prem Identity and Access Management solution and Integration with EBS is really cumbersome. Spending time for building infrastructure and Investing on manpower is really a tough job.

Now this challenge is addressed in IDCS integration with EBS. It has simplified the process of integration and reduce the manpower for this integration.


Coming soon... 

       Keep an eye on it...

How To Create Individual Wallet for databases sharing the same home on the same host

TDE PoC -- self test

Background:
===========

Two Databases on the same server sharing same ORACLE_HOME. How can we configure two Individual wallet for each database

two database: test1 , test2

hostname : db01.oracle.com
==============

Create wallet directories for both instances.

mkdir -p /u01/app/WALLET/test1  /u01/app/WALLET/test2

Change the sqlnet.ora as below (1 single entry for all database but the path will be translated on the basis of ORACLE_SID value)


pENCRYPTION_WALLET_LOCATION=
  (SOURCE=
   (METHOD=FILE)
    (METHOD_DATA=
     (DIRECTORY=/u01/app/WALLET/$ORACLE_SID)))


SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/WALLET/$ORACLE_SID' IDENTIFIED BY "Oracle123"; -- Keystore Created for test1 instance

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "Oracle123"; -- Keystore opened for test1 instance

Now check wallet status on both the instance

open two terminal and set test1 on tty1 and test2 on tty2

on tty1 : instance test1

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN_NO_MASTER_KEY

on tty2 : instance test2

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
NOT_AVAILABLE


Inference:As we didnt create any Keystore for test2 instance hence keystore status is NOT_AVAILABLE


Now create a Keystore for test2  instance


SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/WALLET/$ORACLE_SID' IDENTIFIED BY "Oracle123"; -- Keystore Created for test2 instance

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "Oracle123"; -- Keystore opened for test2 instance

check the status of keystore in test2 :

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN_NO_MASTER_KEY



Now create master key in both the instance :

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "Oracle123" WITH BACKUP;  -- run this on both test1 and test2

keystore altered.

SQL> select status from v$encryption_wallet;

STATUS
------------------------------
OPEN

On test1 Instance:
==================
SQL> select  utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64

 FROM (select RAWTOHEX(mkid) mkeyid from x$kcbdbk);

MASTERKEYID_BASE64
--------------------------------------------------------------------------------
AVYo2uBozE/DvzpLWTiu/4o=


SQL> !mkstore  -wrl /u01/app/WALLET/$ORACLE_SID -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = AVYo2uBozE/DvzpLWTiu/4oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


on test2 Instance:
=================

SQL> select  utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64

 FROM (select RAWTOHEX(mkid) mkeyid from x$kcbdbk);

MASTERKEYID_BASE64
--------------------------------------------------------------------------------
Ab1JjU1zS0+Xv91R1bigDHM=

mkstore  -wrl /u01/app/WALLET/$ORACLE_SID -viewEntry ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

Enter wallet password:
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY = Ab1JjU1zS0+Xv91R1bigDHMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


SQL> create tablespace enc_test1 datafile '/u01/app/test1/TEST1/datafile/test1.dbf' SIZE 10M ENCRYPTION USING 'AES256'  DEFAULT STORAGE (ENCRYPT);

SQL> create tablespace enc_test2 datafile '/u01/app/test1/TEST1/datafile/test2.dbf' SIZE 10M ENCRYPTION USING 'AES256'  DEFAULT STORAGE (ENCRYPT);

SQL> create user test1 identified by Oracle123 default tablespace enc_test1;

SQL> create user test2  identified by Oracle123 default tablespace enc_test2;

SQL> grant connect,resource, unlimited tablespace to test1;

SQL> grant connect,resource, unlimited tablespace to test2;

CREATE TABLE TEST1.appdata(name varchar2(20),roll number);
insert into TEST1.appdata values ('Manoj',1);
insert into TEST1.appdata values ('John',2);
insert into TEST1.appdata values ('Scott',3);

CREATE TABLE TEST2.appdata(name varchar2(20),roll number);
insert into TEST2.appdata values ('Sajan',1);
insert into TEST2.appdata values ('Johnson',2);
insert into TEST2.appdata values ('Tiger',3);

Now from external client(Sql Developer) we loged into test1 and test2 instances and checked

select * from test1.appdata

NAME                       ROLL
-------------------- ----------
Manoj                         1
John                          2
Scott                         3

select * from test2.appdata
NAME                       ROLL
-------------------- ----------
Sajan                         1
Johnson                       2
Tiger                         3



Inference: From this PoC it is proved that we can access Individual database wallet from any application.

Oracle Database Security Assessment

Security, an unavoidable technology these days if you see the technology trend and the number of innovations in IT. Securing your Database is equally important as securing applications and network. Attacks can happen from inside or outside but the portion of damage that can do is unexpected. If attackers can gain access to database from any vulnerable points(say it from Network or Application or From OS)  it can lead to disaster. Hence, it is vital to understand the database vulnerabilities and how to fill the gap.

DBSAT (Database Security Assessment Tool) essentially an Oracle Database security Vulnerability assessment tool which tells what are all security gaps available in the database and it also suggests with recommendations. Roles, privileges, Security configurations, OS level permissions, options and features etc. are the major part of the DBSA report which tells if they are configured as per security standards with the database or not.

A comprehensive and formatted report can be easily generated using DBSA tool. DBSA tool is packaged and provided in support.oracle.com (DOC ID: 2138254.1).

There are two options available in dbsat tool.


$dbsat collect <connect_string> <destination>  -- for collecting data from the database 


$dbsat report collectedFile

Check the Oracle reference below for more details and architecture diagram. 

Reference: https://docs.oracle.com/cd/E76178_01/SATUG/toc.htm#SATUG-GUID-7FFFEC95-6D54-4299-95FA-FDFA2F8835B0


If you want more information you can contact viewssharings@gmail.com

Oracle Database Security Products covering 360 degree Security

We often see customers are looking for various security products available to provide comprehensive security solutions. But most of them we see are segmented that means no single solutions can cover everything(360 degree security solution). We saw bunch of Oracle Security products are available ranging from Database to Middleware. I will brief about Database Security products in this article.

Oracle has a bunch of Security Products, combined them a comprehensive database security solutions can be availed to provide 360 degree security protection.


Broadly these products are categorized under EVALUATE, PREVENT, DETECT, DATA DRIVEN SECURITY

Let us see what are the products coming under which category.

1. EVALUATE: Privilege Analysis, DataBase Security Assessment
2. PREVENT: Database Vault, Data Redaction, Data Encryption, Data Masking and Subsetting, Key Management
3. DETECT: Database SQL Firewall, Audit Vault
4. DATA DRIVEN SECURITY: Label Security, Real Application Security, Row Level Security



DBSAT-(Database Security Assessment Tool): It captures all security parameters and their configurations, security features and options being used and recommendations.

Privilege Analysis: It comes along with Oracle Database Vault license. It evaluates the privileges and roles and filters out necessary and unnecessary privileges.

Database Vault: Privilege account management, Multi-factor authorizations etc. Separation of duties

Data Encryption: Encrypts data at rest in column or tablespace levels.

Data Redaction: Also known as dynamic masking, it mask data on the basis of policy and applies on the basis of factors.(Users, roles, Ip address etc) ex: Credit card number XXXX-XXXX-XXXX-1234

Data Masking and Subsetting: It masks data completely in the underlying table hence reduce the sensitive data exposure to the test and dev like non-prod environments.

Oracle Key vault: Protects keys, certificates, wallet contents etc. and manage key life cycle.
It acts like HSM. TDE keys can be access directly from OKV.

Audit Vault and Database Firewall: DB Firewall can be deployed as first line of defense. It analyses incoming sqls and detects anomaly. Audit vault is a central auditing and monitoring system which can be integrated with DB firewall to work together in detecting anomaly and alert to the security admin.


If you are interested in these technologies, Kindly contact viewssharings@gmail.com.

Sending and receiving file through sftp in non-interactive mode

Here is a case where sometime we need to send the files or receive files using sftp non-interactive mode.

Here lftp has been used to pass the credentials to sftp. agent.jar is a file locally available and We wanted it to copy to remote host 192.168.56.153.

Here is the code.

--------------
#!/bin/sh

HOST=192.168.56.153
USER=oracle
PASS=oracle
PORT=22

lftp -u ${USER},${PASS} sftp://${HOST}:${PORT} <<EOF
cd /home/oracle
put agent.jar
bye
EOF

echo "done"

-------------

put is for sending a file from local to remote and get is to receive a file from remote to local server. you need to change directory using cd and use put or get accordingly.


Hope this will help.

How to hide the default bash prompt and use desire prompt

When you login into a linx or unix user, you will be falling under the home directory and the home directory sometime be in a path which is lengthy. You will find it difficult in a small terminal display to type your command.

for example:
  home directory would be like below
      username@hostname format>

  or username:pathOftheUserHome>
   

We can change the prompt as desired. For example

username>

hostname>

anyName>

here is the example how we can change the prompt.

export PS1='\u>'   outpiut: oracle>

export PS1='\h>'  output:   example>

export PS1='\u@\h>'  output: oracle@example

export PS1='Manoj>'  output Manoj>


You can put this in .bash_profile to load this prompt when you login to the user.


Hope this article will help.


  

Data masking for EBusiness suite 12.2 is on high demand

"I found something interesting these days when experts are talking about data security, this topic is hot now and in fact hottest. There are some sort of chaos going around in the organisations due to insider threat, hackers, cyber attack etc. There are hail lot of incidents happenings every day around the world, Organizations are loosing their stocks, many have been fined an enormous amount as they could not comply to the governments standards for data protection even many have failed to sustain their business due to frequent hacking and not able to protect their business sensitive information." 


Technologies are evolving time to time to prevent hackers or from cyber attack etc. which is similar to fencing around a house to protect against intruders. Still there is a large surface of vulnerability left inside the organisation which is called insider theft. It is really difficult to enforce processes and policies to restrict insider theft. So organizations needs some way to avoid such inadvertent situations.

Non-Prod environments are most vulnerable for such attacks, Below figure can demonstrate how proliferation of sensitive data increases security risks.

when you are using ERPs which holds business sensitive information it is really important to  de-identify these sensitive information before taking it to any non-prod environment. But it is extremely difficult to de-identify the whole database sensitive information as application data model contains thousands of sensitive columns of different formats.

Oracle ERP is one of the complex application system, to such goal Oracle has introduced Data masking template for  EBusiness Suite 12.2  which works perfect with em13c.


I have done a PoC to check how it works. I prepared an EBS environment R12.2 with database 12.1.0.2 and Enterprise Manager cloud control 13.1.

Followed Oracle support document
Using Oracle E-Business Suite Data Masking Pack Release 12.2 with Oracle Enterprise Manager Cloud Control 13c (Doc ID 2076834.1)

This works perfectly fine for me. After a completing masking process I was able to start all application services and could see the data were scrambled. It was not at all possible to identify the original form or to reference them.

I would urge EBS dbas to test this out and suggest respective organisation to use this.