Using full redaction for masking the column values in 12c

This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle’s Advanced Security.

 

Why would we need to mask a column’s data? One of the most common uses is to enable us to hide sensitive data from being shown on the screen of any application.

There are multiple ways to do this and one of the ways is to use DBMS_RLS policy. Here is one nice article published by Surya.  In this tutorial, however, we will explore how we can make use of full redaction option from DBMS_REDACT to mask the column values. The actual reference of masking can be taken from Oracle documentation from here.

In this article I am going to explain only one part i.e. full redaction, but there are other features which can also be used based on our business requirement. such as:

Partial redaction:  

Using this option we can mask only certain portions of the column. For example, say if we want to mask all digits except the last 4 numbers from our credit card or our SSN number then we can use partial redaction.

Regular expressions:

This type of option can be used to mask characters based on patterns search.

Random redaction:

We can dynamically generate any dummy number that can be displayed on an application’s screen based on the data type of the column.

No redaction:

This type of option is only used to test the redaction policy. To test the code and verify the application before moving the actual making policy to any production environment.

To use full redaction type from DBMS_REDACT we need to first grant the schema, in my case I am going to take the example from SCOTT schema.  In 10g onward we used “DBMS_RLS.ADD_POLICY” to use to mask the columns now let us take a look at how we can use dbms_redact object in a 12c environment.

First, let’s give the “GRANT” to the schema.

oracle@dev-VirtualBox ~ $ sql 
SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 1 14:28:18 2018 
Copyright (c) 1982, 2014, Oracle. All rights reserved. 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

SQL> GRANT EXECUTE ON sys.dbms_redact TO SCOTT; 
Grant succeeded. 
SQL> 

 
Now let us select the column which we want to mask , in this example I am using existing schema on oracle i.e. SCOTT and object name is EMP table. 

 

SQL> select ename, sal from emp; 
ENAME SAL
---------- ---------- 
SMITH 800 
ALLEN 1600 
WARD 1250 
JONES 2975 
MARTIN 1250 
BLAKE 2850 
CLARK 2450 
SCOTT 3000 
KING 5000 
TURNER 1500 
ADAMS 1100 
JAMES 950 
FORD 3000 
MILLER 1300 

14 rows selected.

Open in new window

Now, let's add the policy to mask the SAL column from the EMP table. To add the policy we can use something like the example given below:
 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 
SQL> 
BEGIN DBMS_REDACT.add_policy( object_schema => 'scott', object_name => 'EMP', column_name => 'SAL', policy_name => 'redact_sal', function_type => DBMS_REDACT.full, expression => '1=1' ); END;
/ 


PL/SQL procedure successfully completed. 

Open in new window


Let's verify our table now.
SQL> select ename, sal from emp; 
ENAME SAL ---------- ---------- 
SMITH 0 
ALLEN 0 
WARD 0 
JONES 0 
MARTIN 0 
BLAKE 0 
CLARK 0 
SCOTT 0 
KING 0 
TURNER 0 
ADAMS 0 
JAMES 0 
FORD 0 
MILLER 0 

14 rows selected. 

Open in new window


So we can now see the masking is successfully implemented.

Now, let’s see how we can use “SYS_CONTEXT” with the DBMS_REDACT package.

At the start of this article, I shared a link to where we can use “DBMS_RLS” but there, the only part we were using is to create a standalone function inside the DBMS_RLS package, but here we will see how we can use SYS_CONTEXT while adding the policy.  To demonstrate this I am going to create some sample tables that were used in the same blog.

So let’s first create the table:

SQL> CREATE TABLE XX_TEMP (EMP_SSN NUMBER , ENAME VARCHAR2(100), HIREDATE DATE); 

Table XX_TEMP created. 

Select allOpen in new window

Now, let’s add some sample data:

 
SQL> begin 
insert into xx_temp values (1234, 'VINAY', SYSDATE) ; 
insert into xx_temp values (1111, 'VENKET', SYSDATE) ; 
insert into xx_temp values (1234, 'SURYA', SYSDATE) ; 
commit; 
end; 

PL/SQL procedure successfully completed. 

Select allOpen in new window

So now our table will look something like this:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

SQL> conn scott 
Enter password: 
Connected. 
SQL> select * from XX_TEMP; 

EMP_SSN ENAME HIREDATE
 ---------- ---------------------------------------- 
1234             VINAY          02-FEB-18 
1111              VENKET       02-FEB-18 
1234             SURYA          02-FEB-18 

SQL> 

Open in new window

 

Now, let’s add the policy using SYS_CONTEXT:

 
BEGIN DBMS_REDACT.add_policy ( 
object_schema => 'SCOTT', 
object_name => 'XX_TEMP', 
column_name => 'EMP_SSN', 
policy_name => 'redact_syscontext', 
function_type => DBMS_REDACT.full, 
expression => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''SCOTT''' ); 
END;
/ 

PL/SQL procedure successfully completed. 

Open in new window

Now let’s grant the same table to other schema and see how the table will look:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 
SQL> conn scott 
Enter password: 
Connected. 

SQL>GRANT SELECT ON SCOTT.XX_TEMP TO sloba; 

Grant succeeded.  

Open in new window

Now, let’s login with sloba and verify the table.

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 2 09:51:19 2018 
Copyright (c) 1982, 2014, Oracle. All rights reserved. 
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

SQL> conn sloba 
Enter password: 
Connected. 
SQL> select * from scott.xx_temp; 

EMP_SSN ENAME HIREDATE 
---------- --------------------- --------- 
0 VINAY 02-FEB-18 
0 VENKET 02-FEB-18 
0 SURYA 02-FEB-18 

SQL> conn scott 
Enter password: 
Connected. 

SQL> select * from xx_temp; 

EMP_SSN ENAME HIREDATE 
-------------------------------- --------- 
1234 VINAY 02-FEB-18 
1111 VENKET 02-FEB-18 
1234 SURYA 02-FEB-18 

SQL> 

Open in new window

From the above, we can see that the policy is applied for other users, other than scott user.

If we don’t want to use the policy we can drop it by using the following command:

SQL> 
BEGIN 
DBMS_REDACT.drop_policy ( 
object_schema => 'SCOTT', 
object_name => 'XX_TEMP', 
policy_name => 'redact_syscontext' );
END;
 / 

Open in new window

Now, let’s verify from sloba user after dropping the policy:

 
SQL> conn sloba 
Enter password: 
Connected. 

SQL> select * from scott.xx_temp; 

EMP_SSN ENAME HIREDATE 
-------------------------------- --------- 
1234 VINAY 02-FEB-18 
1111 VENKET 02-FEB-18 
1234 SURYA 02-FEB-18 
SQL> 

Open in new window

So now you can see that after dropping the policy we can view all the data from the object that we masked earlier.

Thank you for reading my blog , the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Advertisements

This article will show the step by step guide to install Tableau Business intelligence tool on Windows operating system.

Tableau is another powerful business intelligence application or tool which is used by many business users to represent the date virtually to get more inside on it.

This tool can be connected to any type of data source, it can be live or an extract like CSV, Excel, text files etc… There are other tools which used to view the reports developed using Tableau or it can be just deployed on any server so that the required end user can access the reports to view online as per the business need.

This article will show the step by step guide to install Tableau Desktop public edition  Business intelligence tool on Windows operating system.

To start the installation we have to get the setup from Tableau website.

URL:  https://public.tableau.com/s/download?source=cta

Go to this URL and download the app by proving your email address as like below:

I have downloaded the software and kept it on my desktop as shown below.

Now double click on the setup to start the installation.

Once you start the installation you will be prompt to accept the license agreement to show the install button, i am going to use the simple basic setup to move further.

Click “Yes” to proceed further.

Once the setup is completed you will see the options as below:

You can see the left side of the panel shows the option to connect to any data source if we want to use or we can also use the Tableau Public to get the source if we have any.

One of the good things about this tool is it has a lot of online videos in free to explain and shows the steps on how to use this tool for reporting purpose. But yes if we want to use within an organization then we have to get them the professional editions. But to learn on how to develop the reports we can still use the public edition.

Thank you for reading my article. Please feel free to leave me some feedback or to suggest any future topics. Please give my article a thumbs up if you liked it.

Looking forward to hearing from you – Swadhin Ray (Sloba) – ( LinkedIn) ( Twitter)

Install JDK9 on Linux Mint

In this article we will see how we can install JDK9 i.e. Oracle Java 9 on Linux mint version.

Execute the below commands in sequence on Linux Mint machine:

sudo add-apt-repository ppa:webupd8team/java
sudo apt-get update
sudo apt-get install oracle-java9-installer

While executing the above you will see the get the aggrement options where we need to click ok and proceed. After the above steps setup Java environment using PPA repository

Exeucte the below command to install the package :

sudo apt-get install oracle-java9-set-default

Now let us check our Java version by exeucting the below commands:

oracle@dev-VirtualBox ~ $ java -version
java version "9.0.4"
Java(TM) SE Runtime Environment (build 9.0.4+11)
Java HotSpot(TM) 64-Bit Server VM (build 9.0.4+11, mixed mode)
oracle@dev-VirtualBox ~ $ 

Thank you for reading my blog , please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

 

0% [Connecting to archive.ubuntu.com ] Error

I encountered this error while using “apt-get” command on  my Linux mint server and it got stuck for 12-3 mins.

Here is the details on my error that I faced :


dev@dev-VirtualBox /etc $ sudo apt-get install alien
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
autoconf automake autopoint autotools-dev debhelper debugedit dh-autoreconf
dh-strip-nondeterminism libfile-stripnondeterminism-perl librpm3
librpmbuild3 librpmio3 librpmsign3 libtool po-debconf rpm rpm-common
rpm2cpio

.........

......

.........

0% [Connecting to archive.ubuntu.com (2001:67c:1560:8001::14)] [Connecting to

The issue is on the IPv6 taking more time to execute. Now let us check if my IPv6 is enabled or disabled. Based on the output we will see if we need to disable.


dev@dev-VirtualBox /etc $ cat /proc/sys/net/ipv6/conf/all/disable_ipv6
0
dev@dev-VirtualBox /etc $

If we get the output “0” then my IPv6 is enabled and if we get “1” then it is disabled.  So in my case I have to disable it. Let us see how we can disable it . And try to rerun my command where I got stuck.

To disable please execute the below commands and after that reboot the machine:


dev@dev-VirtualBox /etc $ echo "#disable ipv6" | sudo tee -a /etc/sysctl.conf
#disable ipv6
dev@dev-VirtualBox /etc $ echo "net.ipv6.conf.all.disable_ipv6 = 1" | sudo tee -a /etc/sysctl.conf
net.ipv6.conf.all.disable_ipv6 = 1
dev@dev-VirtualBox /etc $ echo "net.ipv6.conf.default.disable_ipv6 = 1" | sudo tee -a /etc/sysctl.conf
net.ipv6.conf.default.disable_ipv6 = 1
dev@dev-VirtualBox /etc $ echo "net.ipv6.conf.lo.disable_ipv6 = 1" | sudo tee -a /etc/sysctl.conf
net.ipv6.conf.lo.disable_ipv6 = 1
dev@dev-VirtualBox /etc $

I have rebooted my system now let us check what is the output I am getting :


oracle@dev-VirtualBox ~ $ cat /proc/sys/net/ipv6/conf/all/disable_ipv6
1
oracle@dev-VirtualBox ~ $

Now we can see that my IPv6 is disabled , so let us try to execute my initial “apt-get” command


dev@dev-VirtualBox ~ $ sudo apt-get update
Hit:1 http://archive.canonical.com/ubuntu xenial InRelease
Ign:2 http://packages.linuxmint.com sylvia InRelease
Hit:3 http://packages.linuxmint.com sylvia Release
Hit:4 http://archive.ubuntu.com/ubuntu xenial InRelea

...

...

..

Get:22 http://archive.ubuntu.com/ubuntu xenial-updates/universe Translation-en [233 kB]
Get:23 http://archive.ubuntu.com/ubuntu xenial-updates/multiverse amd64 Packages [16.2 kB]
Get:24 http://archive.ubuntu.com/ubuntu xenial-updates/multiverse i386 Packages [15.3 kB]
Fetched 4,825 kB in 1s (2,621 kB/s)
Reading package lists... Done
dev@dev-VirtualBox ~ $

So we can see that we are now able to install the packages without any issues.

 

Thank you for reading my blog , please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

 

 

Append row into a CSV file on SFTP

In this post I am going to show how we can append a row into CSV file using simple bash command.

To demonstrate this I am using Linux virtual machine, the OS is on Mint 18.3 64bit.  First me create a sample file on my Linux Machine as like below.

dev@dev-VirtualBox ~/Desktop/CSV $ pwd
/home/dev/Desktop/CSV
dev@dev-VirtualBox ~/Desktop/CSV $ ls
dev@dev-VirtualBox ~/Desktop/CSV $ cat <<EOF >sample.csv
> COL1,COL2,COL3
> 1,FIRST_LINE,18-JAN-2018
> EOF
dev@dev-VirtualBox ~/Desktop/CSV $ ls
sample.csv
dev@dev-VirtualBox ~/Desktop/CSV $

Once the file is created it will look something like below:

image

Now let us think that this file is on a SFTP server where we need to connect and append another line without renaming or changing the file format.

Login to the server


login as: dev
dev@10.0.0.203's password:
Welcome to Linux Mint 18.3 Sylvia (GNU/Linux 4.10.0-38-generic x86_64)

* Documentation:  <a href="https://www.linuxmint.com/">https://www.linuxmint.com</a>
Last login: Tue Jan 23 09:55:52 2018 from 10.0.0.203
dev@dev-VirtualBox ~ $ ls
Desktop  Documents  Downloads  Music  Pictures  Public  Templates  Videos
dev@dev-VirtualBox ~ $ cd Desktop/
dev@dev-VirtualBox ~/Desktop $ ls
CSV
dev@dev-VirtualBox ~/Desktop $ cd CSV/
dev@dev-VirtualBox ~/Desktop/CSV $ ls
sample.csv

So now we can login to the server.

Method 1: Now let us execute the below line to insert or append a new row to the existing CSV file i.e. sample.csv:


dev@dev-VirtualBox ~/Desktop/CSV $ sed -i.bak 1i"2,Second_Line,18-JAN-2018" sample.csv
dev@dev-VirtualBox ~/Desktop/CSV $

Once the above line is executed we will see the row is inserted but not at the end but at the beginning.

image

Now how we can append the line at the end .


dev@dev-VirtualBox ~/Desktop/CSV $ pwd
/home/dev/Desktop/CSV
dev@dev-VirtualBox ~/Desktop/CSV $ ls
sample.csv
dev@dev-VirtualBox ~/Desktop/CSV $ cat sample.csv
COL1,COL2,COL3
1,FIRST_LINE,18-JAN-2018
dev@dev-VirtualBox ~/Desktop/CSV $ sed -i -e "\$a2,Second_Line,18-JAN-2018" sample.csv
dev@dev-VirtualBox ~/Desktop/CSV $ cat sample.csv
COL1,COL2,COL3
1,FIRST_LINE,18-JAN-2018
2,Second_Line,18-JAN-2018
dev@dev-VirtualBox ~/Desktop/CSV $

 

The line used to insert the row/ append the row at the end is :

sed -i -e "\$a2,Second_Line,18-JAN-2018" sample.csv

Method 2: Now let us check how we can use echo to insert the line at the end.


dev@dev-VirtualBox ~/Desktop/CSV $ echo "3,Third_Line,18-JAN-2018" >> sample.csvdev@dev-VirtualBox ~/Desktop/CSV $ cat sample.csv
COL1,COL2,COL3
1,FIRST_LINE,18-JAN-2018
2,Second_Line,18-JAN-2018
3,Third_Line,18-JAN-2018
dev@dev-VirtualBox ~/Desktop/CSV $

Method 3: Append using a simple python programming


dev@dev-VirtualBox ~/Desktop/CSV $ python
Python 2.7.12 (default, Nov 19 2016, 06:48:10)
[GCC 5.4.0 20160609] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> with open('sample.csv', 'a') as file:
...     file.write('4,Fourth_Line,18-JAN-2018')
... ;
   File "<stdin>", line 3
     ;
     ^
SyntaxError: invalid syntax
>>> with open('sample.csv', 'a') as file:
...     file.write('4,Fourth_Line,18-JAN-2018\n');
...
>>> exit()

Finally, the file will look like as below:


dev@dev-VirtualBox ~/Desktop/CSV $ cat sample.csv
COL1,COL2,COL3
1,FIRST_LINE,18-JAN-2018
2,Second_Line,18-JAN-2018
3,Third_Line,18-JAN-2018
4,Fourth_Line,18-JAN-2018

dev@dev-VirtualBox ~/Desktop/CSV $



 

image

 

To automate the process you can create a script and try to pass the row as a input to the file while calling the script.

 

Thank you for reading my blog , please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

 

Reorder the columns in CSV file using Python

Many time we work on files to load or process the flat files. In this post, I am showing if we receive the file but the columns are not in order then how to rearrange them in the right format. There are multiple ways to handle this but here I am using a simple python code which will reorder my columns.

Say for example my right order of my file should be as below:

 empno, ename, sal 

But say I receive a file with

 sal, ename, empno 

image

Now here is the simple python script which will change the file into right order :

import csv

with open('file2.csv', 'r') as infile, open('file2_modified.csv', 'a') as outfile:
fieldnames = ['empno','ename','sal']
writer = csv.DictWriter(outfile, fieldnames=fieldnames)
writer.writeheader()
for row in csv.DictReader(infile):
writer.writerow(row)

Save the above code as “target_file_format.py” or anyname you want.

Execute the python script , and check the result.

image

You can modify the code in various ways as per your need.

Thank you for reading my blog , please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Access Windows shared directory from Ubuntu 16.04.3 LTS VirtualBox Guest

In this post I am going to show how to access windows shared directory from Ubuntu 16.04.3 LTS virtual machine.

First create a directory on the host. For example I am creating one directory called on my E drive [ My host is Windows 7 ] .

Now open VirtualBox go to settings of your Ubuntu guest, under shared section add your directory that was created with full permission. Once this is completed login to Ubuntu virtual machine and type the below:

 sudo usermod -aG vboxsf $(whoami) 

Executed on my machine as like below:

sloba@sloba-VirtualBox:~$ sudo usermod -aG vboxsf $(whoami)
[sudo] password for sloba:
sloba@sloba-VirtualBox:~$ reboot

After that reboot your Ubuntu machine you can access the shared drive as shown below.


sloba@sloba-VirtualBox:~$ ls
Desktop Downloads Music Pictures Templates
Documents examples.desktop oraInventory Public Videos
sloba@sloba-VirtualBox:~$ cd /media/
sloba@sloba-VirtualBox:/media$ ls -lrt
total 12
drwxrwx--- 1 root vboxsf 0 Jan 22 21:12 sf_vmshare

sloba@sloba-VirtualBox:/media$ cd sf_vmshare/
sloba@sloba-VirtualBox:/media/sf_vmshare$

 

Thank you for reading my blog , please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )