Friday, June 13, 2014

How to encrypt the data (tablespace or column's table) using a software keystore previously known as Oracle Wallet

To create a standard Oracle wallet and then add a master key to it you have to follow few basic steps:
1) Configure the sqlnet.ora file to define a file system location for the Software Keystore
2) Create the Software Keystore 
3) Opening a Software Keystore 
4) Setting the TDE Master Encryption Key in the Software Keystore 
5) Encrypt the Data 

The first four steps are already described here. Now it's time to encrypt your data.

5) Encrypt the Data 
Once you have created an Oracle Wallet and set a TDE master key in it, you can proceed to encrypt your data. Let's start creating a new encrypted tablespace first and then a column's table. My current data files:
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/app/oracle/oradata/CDB001/system01.dbf
/app/oracle/oradata/CDB001/sysaux01.dbf
/app/oracle/oradata/CDB001/undotbs01.dbf
/app/oracle/oradata/CDB001/users01.dbf
The statement to create an encrypted tablespace:
SQL> create tablespace ts_encrypted datafile '/app/oracle/oradata/CDB001/ts_encrypted01.dbf' size 10M encryption default storage(encrypt);

Tablespace created.
Information about the encrypted tablespace available in the database.
SQL> select a.name, b.TS#, b.ENCRYPTEDTS, b.ENCRYPTIONALG, b.CON_ID from V$TABLESPACE A, V$ENCRYPTED_TABLESPACES B                     
  2  where A.ts# = B.ts#;

NAME      TS# ENCRYPTEDTS ENCRYPTIONALG CON_ID
------------ --- ----------- ------------- ------
TS_ENCRYPTED 5   AES128      YES           1
How is it possible to test if the data is encrypted or not ? I'm going to create a table on the USERS (unencrypted) tablespace and another on the TS_ENCRYPTED tablespace. Because the Oracle Wallet is already open I can create on the encrypted tablespace the t1_encrypted table and insert some rows in it.
SQL> create table t1_not_encrypted (text varchar2(255)) tablespace USERS;

Table created.

SQL> create table t1_encrypted (text varchar2(255)) tablespace TS_ENCRYPTED;

Table created.

SQL> insert into t1_not_encrypted values ('my name is marcov');

1 row created.

SQL> insert into t1_encrypted values ('the secrets of marcov');

1 row created.

SQL> commit;

Commit complete.
Flush the buffer cache to be sure all data is written to the datafiles.
SQL> alter system flush buffer_cache;

System altered.
I'm able to grep and see the text on the USERS tablespace, but not that one on the TS_ENCRYPTED tablespace.
[oracle@localhost oracle]$ strings /app/oracle/oradata/CDB001/users01.dbf|grep "my name is"
my name is marcov
[oracle@localhost oracle]$ strings /app/oracle/oradata/CDB001/ts_encrypted01.dbf|grep "secrets"
[oracle@localhost oracle]$
Let's see what happens when the Oracle Wallet is closed. The following command closes an open Oracle Wallet.
SQL> administer key management set keystore close identified by "0racl30racle3";

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                                            STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
-------- -------------------------------------------------------- ------ ----------- --------- --------- ------
FILE     /app/oracle/product/12.1.0/dbhome_1/admin/CDB001/wallet  CLOSED UNKNOWN     SINGLE    UNDEFINED 0
When the Oracle Wallet is closed you can query every tables but those based on an encrypted tablespace.
SQL> select * from t1_not_encrypted;

TEXT
-----------------
my name is marcov

SQL> select * from t1_encrypted;
select * from t1_encrypted
              *
ERROR at line 1:
ORA-28365: wallet is not open
You have to open again the Oracle Wallet to successfully execute the query
SQL> administer key management set keystore open identified by "0racl30racle3";

keystore altered.

SQL> select * from t1_encrypted;

TEXT
---------------------
the secrets of marcov
Let's see how a closed Oracle Wallet affects an encrypted column of a table. I'm going to create a new table with two columns: one is encrypted and the other is not encrypted.
SQL> create table c##marcov.t2_column_encrypted (text varchar2(255), text_encrypted varchar2(255) encrypt) tablespace USERS;

Table created.

SQL> insert into c##marcov.t2_column_encrypted values ('this column is not encrypted', 'the secrets of marcov');

1 row created.

SQL> commit;

Commit complete.
The Oracle Wallet is closed.
SQL> administer key management set keystore close identified by "0racl30racle3";

keystore altered.
When the Oracle Wallet is closed I can able to query the non-encrypted column.
SQL> select text from c##marcov.t2_column_encrypted;

TEXT
----------------------------
this column is not encrypted
But when I try to query the encrypted column it fails:
SQL> select * from c##marcov.t2_column_encrypted;
select * from c##marcov.t2_column_encrypted
                        *
ERROR at line 1:
ORA-28365: wallet is not open
I need first to open the Oracle Wallet
SQL> administer key management set keystore open identified by "0racl30racle3";

keystore altered.
Now I can query again the encrypted column of my table.
SQL> select * from c##marcov.t2_column_encrypted;

TEXT                         TEXT_ENCRYPTED
---------------------------- ---------------------   
this column is not encrypted the secrets of marcov

That's all.


70 comments:

Path Infotech said...

Path Infotech is in the field of oracle training program from past several years.

For more info : Ocp Certification in Noida

Sruthi veni said...

After coming this blog, I have learned so many things. Thank you friend keep posting like this.
Oracle DBA Online Training

Tec-Tist Texas said...

Really something Grate in this article Thanks for sharing this. We are providing DATABASES courses training online. After reading this slightly am changed my way of introduction about my training to people. And also refer my website for DATABASES Training and solutions of DATABASES applications. Please Visit Us @ DATABASES training courses online

Mindmajix Trainings said...

Good Article keep continuing the blog, Encryption is an important component of several of these solutions. While there are many good reasons to encrypt data Read more..

Check this site Mindmajix for indepth Oracle DB Tutorials

Go here if you’re looking for information on Oracle DB Blogs

web lol said...

wtf

Geet choudhary said...

Nice Article...We have All India Database like Indian mobile and Email Database in all Categories, Students Database , Teachers Database, Employees Database, HNI, frequent flyers Database, Business Database and many more...
Feel free Contact us : +91 9620541463

Friv4 Games Online said...

Thank you for your post, I look for such article along time, today i find it finally. this post give me lots of advise it is very useful for me
Signature:
i like play games friv 4 online and play games 2 girls 2 and juego frozen

sss lokesh said...

Excellent information about oracle.....


For Best training on Oracle Apps Technical Training In Hyderabad . This is the one providing 24x7 job assistance for all.... Many professionals are placed in the MNC's......... here you can check the details more......


Oracle Apps Technical Training In Hyderabad

mine craft said...

http://www.boombu.com --- fitness you x

http://www.sextamina.com --- sex xx
http://www.aceofbeauty.com --- fat loss sexy

http://www.bestbuyx.com --- sex xx

http://www.theorchidx.com --- orchids orchidaceae phalaenopsis care

http://www.babxyz.com --- dog food xx

http://www.babysolve.com --- baby potty xx
http://www.suwandri.com --- options trading xx
http://www.googleclickbank.com --- forex fx xx

http://www.jelema.com --- fat loss xx

http://www.bestbuygameguide.com --- betting money xx

mesothelioma master xx

pubsquash John said...

Thanks for sharing the information. It is very useful for my future. keep sharing
Kizi 123|
Friv 234|
Friv

bibikafu said...

Hello to every body, it's my first pay a quick visit of this weblog; this weblog carries amazing and really excellent data in favor of readers.
Friv 4
Kizi 3

Raju Kumar said...


Spot on with this write-up, I absolutely believe this web site needs a lot more attention. I’ll probably be back again to read through more, thanks for the information!
SAP APO Online Training

david said...

Really something Grate in this article Thanks for sharing this. We are providing Online Training Classes. After reading this slightly I am changed my way of introduction about my training to people.
ORACLE DBA ONLINE TRAINING

Yepi said...

You need to kill time, you need entertainment. Refer to our website. hope you get the most comfort.
Thanks you for sharing!
Yepi
Kizi
Friv
Friv Games

Kamran said...

http://tinyurl.com/jxwagtb

Aileen Smith said...

Thank you very much for posting and sharing this great Blog And Good Information.carry on your posting.

oracle database management

Jessica Meri said...

Thanks for sharing these information. It’s a very nice topic. We are providing online training classesOracleRACOnlineTraining

Friv 10 said...

you'd have time to look these kids active. Please visit our website and let us play the game interesting.
Thanks for sharing !
Friv 10
Kizi 10
Yepi 2

tranbaongoc said...

You are stressed and tired, you need entertainment. Refer to our website. hope you get the most comfortable.
Kizi 4

Herry jonson said...

ARINET DBA Services is a Chicago, IL based association set up in 2013. Uniting over 100+ years of consolidated contribution in giving quality Oracle database Support organizations to American associations, we've given the assorted alternatives about remote dba aces, remote dba associations, remote dba strengthen, database administration, prophet remote, dba remote, database affiliation, prophet dba fortify, Oracle sponsorship and Oracle Consultants.

Le Bens said...

What a wonderfull post! thank you... read with pleasure!
Friv 100

Friv5 said...

You need to kill time, you need entertainment. Refer to our website. hope you get the most comfort.
Thanks for sharing !
Friv 5
Kizi 1
Yepi 3

tranthuphuong said...

thank you for your interest in welcome visit
Friv 4

Srinu Vasu said...

nice article
SAP GTS Training In Hyderabad

Praveen Kumar said...

Valuable information thanks for sharing Oracle DBA Online Training

oakleyses said...

jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet

oakleyses said...

sac vanessa bruno, new balance, vans pas cher, ray ban uk, nike blazer pas cher, true religion outlet, michael kors outlet, true religion outlet, replica handbags, polo lacoste, oakley pas cher, coach purses, hollister uk, abercrombie and fitch uk, nike free uk, north face uk, louboutin pas cher, polo ralph lauren, hollister pas cher, nike air max uk, michael kors pas cher, nike air max, true religion jeans, timberland pas cher, nike air max uk, coach outlet, air max, michael kors, jordan pas cher, sac hermes, north face, lululemon canada, coach outlet store online, nike roshe, sac longchamp pas cher, nike air force, mulberry uk, hogan outlet, ralph lauren uk, longchamp pas cher, michael kors, converse pas cher, burberry pas cher, nike roshe run uk, true religion outlet, kate spade, nike free run, nike tn, ray ban pas cher, guess pas cher

oakleyses said...

asics running shoes, babyliss, soccer jerseys, hermes belt, reebok outlet, ipad cases, oakley, iphone cases, soccer shoes, iphone 5s cases, nfl jerseys, north face outlet, abercrombie and fitch, ghd hair, vans outlet, iphone 6 cases, hollister, nike roshe run, wedding dresses, mac cosmetics, lululemon, new balance shoes, jimmy choo outlet, instyler, giuseppe zanotti outlet, p90x workout, s6 case, chi flat iron, iphone 6s cases, longchamp uk, baseball bats, mcm handbags, iphone 6 plus cases, bottega veneta, ferragamo shoes, timberland boots, mont blanc pens, insanity workout, nike air max, nike trainers uk, herve leger, nike huaraches, celine handbags, north face outlet, beats by dre, iphone 6s plus cases, valentino shoes, ralph lauren, hollister clothing, louboutin

Anonymous said...

New attractive game . I will play it every day
Friv|
Friv 2

Dhivya Shree said...

Database means to maintain and organize all the files in a systematic format where the data can be easily accessible when needed.
Oracle DBA training in chennai | Oracle training in chennai | Oracle course in Chennai

John Nash said...

Thanks for sharing this information. Wonderful tips, very helpful well explained.
Oracle Database SQL Workshop

Oracle Database Integration 11g Online Training
Oracle RAC DBA Online Training

Dhiya L said...

Wow amazing i saw the article with execution models you had posted. It was such informative. Really its a wonderful article. Thank you for sharing and please keep update like this type of article because i want to learn more relevant to this topic.

Hadoop Training in Chennai

Ann Lily said...

I enjoyed over read your blog post. This was actually what i was looking for, and i am glad to came here!
Happy Wheels | Happy Wheels Game | happywheels | happywheels game | Slitherio | Wingsio

Raju Kumar said...


Thanks for sharing with us that awesome article you have amazing blog.....

Informatica training in Hyderabad

Trúc Phương said...

Chúng tôi xin chân thành cảm ơn in kỷ yếu giá rẻ
in phong bì giá rẻ các tập đoàn, công ty, cơ quan và các doanh nghiệp mẫu thẻ nhân viên đẹp
In thẻ VIP giá rẻ đã hợp tác với Công ty cổ phần in Hồng Đăng mau ky yeu dep
mau phong bi dep Nhờ đó mà Công ty chúng tôi có cơ hội khẳng định mình cũng như ngày càng hoàn thiện hơn báo giá in kẹp file
in giấy khen phục vụ khách hàng một cách chuyên nghiệp in giấy khen
in bao li xi và chất lượng hơn nữa in thiep chuc mung nam moi
in catalogue

Yoko Hao said...

kickfit |
boxing | vo tu ve | kick boxing | Quyen Thai | muay thái | võ gậy arnis

Pradeep Reddy said...

very nice and useful post thank you... Oracle DBA Training
Oracle DBA Online Training

Friv100 said...

You want to relax after a stressful working hours. Refer to our website. hope you get the most comfort.
Thanks for sharing !
Friv 100
Friv 8
Friv 2

Sathya G said...


the blog is very useful, interesting and informative. thank you for sharing the blog with us. keep on updating.
Linux Training in Chennai

lee anh nuooi said...

Play games we reward Corolla Altis car

Friv Games
Kizi 100
Games2Win

GirlsGoGames Dressup said...

You want to relax after a stressful working hours. Refer to our website. Hope you get the most comfort.
Thanks for sharing !
GirlsGoGames
Kizi 100
Frvi

Jenny Daring said...


Fireboy and Watergirl arrived again to the temple in the forest. 2 players together can help them to find their way out.
happy wheels | strike force heroes | tank trouble 2 | fireboy and watergirl |fireboyandwatergirl | fireboy and watergirl 2 | goodgame empire | slitherio | Tank trouble | happy wheels | Strike Force Heroes
Log in to your account or sign up to create a new account
gmail sign in | create a Gmail account

Praveen Kumar said...

Valuable information thanks for sharing Oracle DBA Online Training

Praveen Kumar said...

Valuable information thanks for sharing Oracle DBA Online Training

شركة زهرة العلا said...

شركة مكافحة حشرات بالدمام
مكافحة حشرات بالطائف
شركة مكافحة حشرات ببريدة
شركة مكافحة حشرات بنجران
مكافحة النمل الابيض بالدمام
شركة مكافحة حشرات بجازان
شركة مكافحة حشرات بالرياض
مكافحة حشرات بالمدينة
شركة تركيب باركية بالرياض
شركة تركيب غرف نوم بالرياض
مستودعات تخزين الاثاث بالرياض
شركة تنظيف مساجد بالرياض

games2girls said...

You need to have time to take care of the kids active. Please visit our website and play exciting flash games.
Thanks you for sharing!
Games2Girls
Kizi 1000
Frin
Friv 3

Online Training said...


Ya that's great to have blog like this it may clear the many thought from this


Pega Training
PlSQL Training
AWS Training
Teradata Training
Qlikview Training
Informatica MDM Training

Team NCT said...

Tỏi đen học viện quân y
Trà giảm cân học viện quân y
In thẻ nhựa
In lịch tết
In lịch tết 2017
Phục hồi tóc hư tổn
Máy làm cám
Máy ép cám viên
Cách sử dụng tỏi đen
Tác dụng của tỏi đen
in thẻ bảo hành
In thẻ học sinh, sinh viên
In thẻ gửi xe, vé xe
In thẻ VIP
Phôi thẻ nhựa
In lịch để bàn
In lịch để bàn chữ A
In lịch để bàn chữ M
In lịch lò xo 7 tờ
In lịch nẹp thiếc 1 tờ

Team NCT said...

In lịch nẹp thiếc 5 tờ
Báo giá in lịch tết
In lịch để bàn giá rẻ
Báo giá in lịch tết 2017
Dầu gội GATASE
Phục hồi tóc cháy
Phục hồi tóc tại nhà
Phục hồi tóc yếu
Phục hồi tóc chẻ ngọn
Phục hồi tóc nát
Máy ép cám viên
Máy đùn cám viên
Máy băm nghiền đa năng
Máy nghiền vỡ hạt ngô
Máy thái rau
Van uống nước cho thỏ
Tấm nhựa đa năng
Lồng công nghiệp
Tam nhua da nang
Máy thái cây chuối
Mang an cho tho
Van uong nuoc cho tho
May dun cam vien
Máy thái cỏ
Máy nghiền thức ăn
Máy đùn cám viên
Video máy ép cám viên
Video hướng dẫn làm cám cho thỏ
Máy ép cám viên kiểu đứng

gaballh al-kattib said...

شركة كشف تسربات المياه بالرياض
كشف تسربات المياه بالرياض شركة كشف تسربات المياه بالدمام
كشف تسربات المياه بالدمام شركة كشف تسربات المياه بالاحساء
كشف تسربات المياه بالاحساء
شركة كشف تسربات المياه بالجبيل
كشف تسربات المياه بالجبيل
شركة كشف تسربات المياه بالخبر
كشف تسربات المياه بالخبر
شركة كشف تسربات المياه بالقصيم
كشف تسربات المياه بالقصيم
شركة كشف تسربات المياه بالقطيف
كشف تسربات المياه بالقطيف

gaballh al-kattib said...

شركة كشف تسربات المياه بالرياض
كشف تسربات المياه بالرياض شركة كشف تسربات المياه بالدمام
كشف تسربات المياه بالدمام شركة كشف تسربات المياه بالاحساء
كشف تسربات المياه بالاحساء
شركة كشف تسربات المياه بالجبيل
كشف تسربات المياه بالجبيل
شركة كشف تسربات المياه بالخبر
كشف تسربات المياه بالخبر
شركة كشف تسربات المياه بالقصيم
كشف تسربات المياه بالقصيم
شركة كشف تسربات المياه بالقطيف
كشف تسربات المياه بالقطيف

gaballh al-kattib said...

شركة كشف تسربات المياه بالرياض
كشف تسربات المياه بالرياض شركة كشف تسربات المياه بالدمام
كشف تسربات المياه بالدمام شركة كشف تسربات المياه بالاحساء
كشف تسربات المياه بالاحساء
شركة كشف تسربات المياه بالجبيل
كشف تسربات المياه بالجبيل
شركة كشف تسربات المياه بالخبر
كشف تسربات المياه بالخبر
شركة كشف تسربات المياه بالقصيم
كشف تسربات المياه بالقصيم
شركة كشف تسربات المياه بالقطيف
كشف تسربات المياه بالقطيف

Pham Dinh said...

You want to relax after a stressful working hours. Refer to our website. Hope you get the most comfort.
Thanks for sharing !Y8 arcade

Praveen Kumar said...

Valuable information thanks for sharing Oracle DBA Online Training

apttree said...

very useful information...

RRB Exam Questions and Answers

Vao exam questions and answers

Iqbal Dlk said...


Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing

dot net training in chennai

php training in chennai

java training in chennai

apttree said...

for preparing bank exam and group exam , we are providing an online test model questions papers

Group Exam Questions and Answers
Bank Exam Questions and Answers

gun mayhem said...

You go to our Web page you can play online games for free.
Our Web page selection is the biggest collection so you can play entirely for free
gun mayhem | age of war
learn to fly | happy wheels game
tank trouble

YooB said...

Posts shared useful information and meaningful life, I must appreciate the way you have express your feelings through your blog!.
Thank you for sharing!
Yoob Games
JuegosFriv2
Kizi 2
Yepi 100

Praveen Kumar said...

Valuable information thanks for sharing Oracle DBA Online Training

Reena S said...

Thanks for sharing these information. It’s a very nice topic. got to learn more new things..

Selenium Training in chennai

Praveen Kumar said...

Valuable information thanks for sharing Oracle DBA Online Training

Kits Online Training said...

nice blog, thanks for sharing
Dataguard Online Training Institute

Best Etl Testing Online Training

Best Oracle Golden Gate Online Training institute from india

Best Vmware Online Training institute from Hyderabad india

حسام داود said...

شركة تسليك مجاري بالرياض
شركة تسليك مجارى بالرياض
level تسليك المجاري بالرياض
افضل شركة تنظيف بالرياض
تنظيف شقق بالرياض
شركة تنظيف منازل بالرياض
شركة غسيل خزنات بالرياض
افضل شركة مكافحة حشرات بالرياض
رش مبيدات بالرياض
شركة تخزين عفش بالرياض
شركة تنظيف مجالس بالرياض
تنظيف فلل بالرياض
ابى شركة تنظيف بالرياض

Lo Lo said...

Jordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan UniversityJordan University
http://www.ju.edu.jo/home.aspx
Jordan University
[url]http://www.ju.edu.jo[/url]

Movers Packers said...

Packers and movers in Pune
Packers and Movers in Bangalore

tirupati raja said...

thankyou for valueble information in oracle dba , excellent blog great thinking questions sovle eassy
best learning in online trainings oracle dba

hường lê said...

Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.

Discover our website bounty of free online games now!
Our website has the biggest collection of free online games. Totally new games are added every day!

age of war 2| gold Miner 2| unfair Mario 2 | cubefield 2 |tanki Online 2

nguyen thanh truc said...

Oh my preparations and entertainment
Friv Games
Y2
Friv 200

Eric Trump said...

I’m glad you enjoyed it. Those are great habits! Thank you for sharing.
Friv 100