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.


412 comments:

«Oldest   ‹Older   401 – 412 of 412
Anonymous said...

Hii

Thank you for the sharing this informative. A great blog consistently offers fresh and captivating content, and as I read this blog, I can genuinely sense that it embodies these qualities that define an exceptional blog. Here is sharing some Kofax Totalagility Training course journey information may be its helpful to you.

Kofax Totalagility Training

Import Globals said...

Get the best Import Export Data services for Indonesia Imports, Russia Imports, Mexico Imports, and Turkey Import Data by Import Globals. Visit our website for more information in details.
Indonesia Import Data

exam help online - get exam help online said...

Encrypting data with Oracle wallet and TDE master key is a crucial step in securing sensitive information. The discussion hints at a step-by-step guide for encrypting both tablespaces and column tables. The author seems to be emphasizing practical implementation, offering a hands-on approach to the encryption process. This tutorial-style content is likely to be valuable for readers seeking to enhance data security within an Oracle environment.





Muskan said...

I like your blog! The step-by-step explanation of configuring the Oracle Wallet, creating a Software Keystore, and encrypting data is very clear and insightful.
Also Read: Voice User Interface (VUI) Testing Quality Assurance for Voice-Activated System

Decentralized Exchange script said...

Thanks for sharing

Seekware Global Ottawa said...

Welcome to Seekware Global, your gateway to unparalleled digital marketing solutions in Ottawa! Our team of seasoned experts specializes in elevating brands, enhancing online visibility, and driving unprecedented growth in the digital landscape. With a focus on cutting-edge strategies tailored to your unique needs, we deploy innovative techniques across SEO, social media, content marketing, PPC, and more. Trust Seekware Global to navigate the complexities of the digital realm, propelling your brand to new heights in Ottawa and beyond. Join forces with us and unlock the full potential of your online presence today!

Anonymous said...

Great post updated with the enriched information. Worth spending time reading the post. Nevertheless, for anyone wishing to learn, unlearn, and relearn digital marketing, digital marketing course in Bangalore will be the best option as their efficiency in providing training online and offline to anyone across the country at the reasonable course fee.

vcube said...

Nice Blog Keep Posting.
Selenium training institute in kukatpally

Ududjdjfjjf said...

Considerations before Buying YouTube Views
However, buying views is not without its risks and drawbacks, and here’s what you should consider before making the purchase.
Quality and Source of Views
Many services provide views at a low cost, but these may not be from genuine users, which can be identified by YouTube’s algorithms and negatively impact your channel. Ensure you're purchasing from a reputable service that offers views from real users and not bots.
Risks and Potential Consequences
YouTube’s terms of service discourage manipulation of engagement metrics. There's always a risk that the platform may flag your content, leading to demonetisation, reduced visibility, or worse, channel suspension.
Ethical and Moral Implications
The authenticity of your growth is essential for long-term success. Buying views can be seen as deceptive and may harm your reputation with actual followers if discovered.
https://www.buyyoutubesubscribers.in/youtube-video-views/

Supreme Hospital said...

Keep doing this work it really helps me to understand new things and contains some interesting topics with knowledgeable stuff also have a look at this Best Neurologist in Faridabad

Supreme Hospital said...

What a wonderful blog it contains some interesting topics and helped me to understand new things. It also contains some knowledgeable stuff so keep doing this work also have a look at this Best treatment for fibroids in Delhi and thank you for this informative post.

Supreme Hospital said...

Thank you for this informative post it really helped me and contains some interesting topics so keep writing this kind of blog also have a look at this Treatment of Cervical Cancer

«Oldest ‹Older   401 – 412 of 412   Newer› Newest»