you can grant at the column level for modification purposes - you can let someone see something but not update it with a grant.

Star

ask questions in one place. For the complete answer to this, go to the other place you asked basically the *SAME THING*




In response to:

... Lets try this now.. wallet is open and we created another table (CREATE TABLE
AS SELECT * FROM ENCR) then WALLET IS CLOSED. I AM ABLE TO SEE THE ENCRYPTED
DATA IN THE NEW TABLE... (whats the point for wallet then ? )
...

ummm, you copied the data, into another table, that table was not using encrypted columns.

what did you expect to happen?

what do you expect to happen with:

spool x
select encrypted_column from t;
spool off

??? it is basically the same concept.

... I believe that this is just a password protected. It doesnt encrypt the data. ...

why do you believe that? Stop believing that - it is 100% completely and utterly wrong. The data in the encrypted column is stored in the SGA and on disk *encrypted*.

If you take that data out of the table, it is obviously NOT ENCRYPTED anymore.

if you copy data from point a to point b

and that data WAS encrypted at point a
why do you think, believe, imagine that it would by of course encrypted at point b.


Unless and until you encrypt point b, point b is just a copy of data you were allowed to see. Just like if you:

spool data
select encrypted_data from table;
spool off

the file you produced on your PC, data.lst, would be

a) encrypted?
b) not-encrypted?

it would be (b) of course UNLESS your file system was encrypted....



...
Please share your spool file or internal views which says that data has been
encrypted as we enter into the columns (which are on encrypted and can be seen
on dba_encrypted_columns)
......

I have no idea what you are asking for there.


...
I tried to find the encryption/decryption from explain plan and v$sql but could
not found anything that sort.
......

why would you? It is just like if you define a column to be of type number, when you fetch it and display it - it is converted (TRANSPARENTLY) into a string. When you retrieve data which was stored with TRANSPARENT data encryption, it is TRANSPARENTLY decrypted for you. That is the entire goal here. If you could "see it", it would not be transparent.



....
Even, I feel that its just a password protected and the moment I query those
columns, it checks that whether wallet is open or not... if its open then it
displays the clear text data...
.......

a) stop feeling
b) start reading (the documentation)

then you can make factually informed comments....


Here is a demo I use to demonstrate "transparent data encryption" frequently:

ops$tkyte%ORA11GR1> !cat $ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /home/ora11gr1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/ora11gr1/network/admin/)))


ops$tkyte%ORA11GR1> pause

That shows the minimal set up - I'm using a wallet stored in the file system...

ops$tkyte%ORA11GR1> rem ALTER SYSTEM SET ENCRYPTION KEY identified by foobar;
ops$tkyte%ORA11GR1> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY foobar;

System altered.

ops$tkyte%ORA11GR1> pause

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> clear screen

and we either create the wallet the first time (mine is already there, hence the REM) or we open it. You must open the wallet for each instance - and an instance is a set of processes and memory (it is not a database, that is the datafiles and such). You open the wallet every time you startup.

ops$tkyte%ORA11GR1> create tablespace tde_test
  2  datafile '/tmp/tde_test.dbf' size 15m
  3  autoextend on next 1m;

Tablespace created.

ops$tkyte%ORA11GR1> pause

create a tablespace to demonstrate with...

ops$tkyte%ORA11GR1> create table t
  2  ( c1 varchar2(30),
  3    c2 varchar2(30) ENCRYPT
  4  )
  5  tablespace tde_test;

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> pause

My demo table - one column 'normal', one encrypted....

ops$tkyte%ORA11GR1> insert into t values
  2  ( 'this_is_NOT_encrypted',
  3    'this_is_encrypted' );

1 row created.

ops$tkyte%ORA11GR1> commit;

Commit complete.

ops$tkyte%ORA11GR1> pause

some very simple data.......

ops$tkyte%ORA11GR1> select * from t;

C1                             C2
------------------------------ ------------------------------
this_is_NOT_encrypted          this_is_encrypted

ops$tkyte%ORA11GR1> pause

ta-dah, demo over - we have encrypted data in C2, but since it is transparent data encryption - we can see it since we have ACCESS to it (encryption is never about access control, encryption is protection from accessing the data in the event of THEFT - access control in the form of grant, dbms_rls etc - that is access control)

ops$tkyte%ORA11GR1> alter system set encryption wallet close;

System altered.

ops$tkyte%ORA11GR1> pause

Now, if someone stole our database, they would not have our wallet, or if they did, they would not have the password to release the wallet. So, when they restore the database, open it, and try to access the encrypted data:

ops$tkyte%ORA11GR1> select c1 from t;

C1
------------------------------
this_is_NOT_encrypted

ops$tkyte%ORA11GR1> select c2 from t;
select c2 from t
               *
ERROR at line 1:
ORA-28365: wallet is not open


ops$tkyte%ORA11GR1> pause

that is what they'll get. Oracle is not saying "I won't let you see this data" with this message. Rather Oracle is saying "so sorry, I cannot access this data, you see it is scrambled and I don't have the keys".

ops$tkyte%ORA11GR1> alter system set encryption wallet open identified by foobar;

System altered.

ops$tkyte%ORA11GR1> select * from t;

C1                             C2
------------------------------ ------------------------------
this_is_NOT_encrypted          this_is_encrypted

If they have your wallet and the password that releases the wallet, they'll be able to see the data. Unless and until both things are true (have wallet, have password) the data is encrypted, inaccessible to everything

It is encrypted, and we can see that by dumping a block if you wanted, I prefer this as it is easier:

ops$tkyte%ORA11GR1> alter system checkpoint;

System altered.

flush dirty blocks to disk...

ops$tkyte%ORA11GR1> !strings -a /tmp/tde_test.dbf | grep 'this_is'
this_is_NOT_encryptedD

ops$tkyte%ORA11GR1> pause

Look into datafile - you will not find the encrypted data using a search string, it is ENCRYPTED, it is not stored on disk in clear text.



Now if you were to index C2, you'd be able to see that it is 'special'

ops$tkyte%ORA11GR1> create index t_c2_idx on t(c2);
create index t_c2_idx on t(c2)
                           *
ERROR at line 1:
ORA-28338: cannot encrypt indexed column(s) with salt


ops$tkyte%ORA11GR1> alter table t modify c2 encrypt no salt;

Table altered.

ops$tkyte%ORA11GR1> alter table t move;


Table altered.

ops$tkyte%ORA11GR1> create index t_c2_idx on t(c2);

Index created.


ops$tkyte%ORA11GR1> select /*+ index( t t_c2_idx )*/ * from t where c2 like 'ALL_OBJECT%';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  3405 |   159K|   216   (2)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    |  3405 |   159K|   216   (2)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(INTERNAL_FUNCTION("C2") LIKE 'ALL_OBJECT%')

we cannot range scan on that encrypted bit of data using this query - why? because when using column level encryption - you are indexing ENCRYPTED DATA - the data cannot be therefore stored "sorted" in index.

Also note the "internal_function()" - there you go, there is the magic....




And just to be clear here - encryption is entirely, totally inappropriate to meet your stated goal

On the other page where you started asking redundantly basically the same thing, you said:

We need to encrypt columns of Customer table so developers / testing team and
other people can not see the data in actual format. Should we go ahead with Oracle Wallet ? Is it
possible in Oracle 9i ?



And I clearly wrote "stop, you are doing it wrong", I wrote:


... We need to encrypt columns of Customer table so developers
/ testing team and other people can not see the data in actual format ...

Nope, you need to use access control - grant, VPD (row and column security, column masking in particular)

encryption is useful to protect against the THEFT of data
encryption is not what you use to prevent ACCESS to data.



please take that to heart - you cannot use encryption to achieve "your goal" - think about that...

... According to me, I know that Encrypt means the data would be visible but nobody
can guess and it would hard to predict. Also encrypt means scrumbled data.
Right ?
...


Encryption means

a) the data can be encrypted (obscured)
b) the data can subsequently be UNENCRYPTED - retrieved

when you put transparent into the equation, as with TRANSPARENT data encryption, the fact that the data is stored encrypted (to prevent THEFT, that is all encryption is good for - to prevent THEFT) is totally TRANSPARENT. If the application knew the data was encrypted, or had to deal with the fact the data was encrypted - IT WOULD NOT BE TRANSPARENT..


... If yes, then I would like to know the encrypted value. ...

start dumping blocks, that is the only way to see it.


... I would like to see that kind of data in your data dictionary (since you said
that Data Dictionary will store data in encrypted format.. ...

where the HECK did I say that?


... When we do any transaction over web (SSL), it
encrypts data from client end and then it decrypts data at server end... right
? But we can see that scrumbbled data.
...


if and ONLY IF you put a sniffer on the network. Our equivalent of the sniffer is the dumping of a block, go ahead, use a binary editor on the datafile - google around and see how to dump blocks, then you can "see" this "totally useless" stuff.

And you know what, your example is perfect. SSL is to network encryption what TDE is to data encryption.

Anyone allowed to see the data is allowed to see it, period, without having to do anything. Your java program in the middle tier that is a target of a https request - it doesn't get encrypted data, the protocol SSL *transparently decrypts and delivers the data*. Only people that try to STEAL THE DATA OFF OF THE WIRE cannot see it.

Just like with TDE, if you have been granted access to it, it is delivered to you - UNENCRYPTED (just like SSL). Only if you steal the datafile and try to use it would you be thwarted by encryption.


.... Another example, when we login to client VPN, our company network will be
disconnected and we can not store any sort of files / log / screenshots to our
local system unless we remain logged in to the client VPN. After logging out
from that VPN, we can not even see those files. Right ? .....


I don't know what kind of VPN you are using, but mine sure does NOT do that. If I VPN into Oracle and download a file to my hard disk - that file is surely still there afterwards. What you are describing sounds a lot like a B1-Multi Level Secure OS (B1-MLS) - the likes of which I haven't worked on since the early 1990's and haven't really seen many of (due to the fact they are so cumbersome to work on)


... I am aware that the above two examples are different types of Security. But we
need first kind of solution in our database... Possible via TDE ?
....

I'll say this one last time for you - because I have said it over and over

a) you do not want encryption
b) you do however want access control - GRANT, find grained access control (row masking, column masking).

Ok - you want, you need, your requirement demands ACCESS CONTROL. You do not want, you do not need, you do not desire, your requirements will not be met by encryption.



... I also feel that its a debatable topic but in general practice, encryption
means data will be encrypted and can be seen in some random algorithm.
...


it is not debatable, the algorithms would not be random, the data is encrypted but that just means IT CAN BE DECRYPTED. you use encryption to protect against THEFT (over and over I say this...) You use ACCESS CONTROLS to control - ACCESS.

You have clearly defined needs "prevent this data from being seen by these people". THAT IS ACCESS CONTROL.

Encryption is to protect your data in case some bad guy steals it and restores it. They can bypass all access controls, but they cannot decrypt the data.


... --> What if we have cold backup with the encrypted data and we need to restore
to another DB...?
...

you would read the documentation.....

sounds like, if you ask me, you have two attributes (at least)

that is, your model is wrong.


Why isn't this two fields - what is the use case here, what is causing you to do this?

Sounds like data hidden inside of other data

you will have to maintain this data in another field. data masking masks, entirely.