Home » RDBMS Server » Security » Encrypt & Decrypt
Encrypt & Decrypt [message #479823] Tue, 19 October 2010 10:38 Go to next message
kvignes1
Messages: 33
Registered: September 2007
Member

We are using Ora 11g but don't have option package Oracle Advan Security. So for encrypt/ decrypt, we need to use the dbms_obfuscation_toolkit package.

I have never done encrypt & decrypt and its first time. I Google it and found out using 2 functions(DES-Encrypt & DES-Decrypt) we can encrypt & decrypt the data.

My requirement is, I have a table with column( EMPLID, Year_Salary, Month_Salary,Commission). I want to encrypt Year_Salary, Month_Salary & Commission column alone(NUMBER Column) .. and then expose these data to another database user who access it using decrypt function.... How do I do it using the above two functions.
Re: Encrypt & Decrypt [message #479824 is a reply to message #479823] Tue, 19 October 2010 10:44 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Have a look at fine grained access control
Re: Encrypt & Decrypt [message #479827 is a reply to message #479824] Tue, 19 October 2010 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So for encrypt/ decrypt, we need to use the dbms_obfuscation_toolkit package.

No, use dbms_crypto, surely not the old and obsolete dbms_obfuscation_toolkit.

Quote:
I Google it and found out using 2 functions(DES-Encrypt & DES-Decrypt) we can encrypt & decrypt the data.

These algorithms are no more sure and cracked since about 10 years; use AES.

The best solution depends on the actual requirements:
1/ What do you want to protect: all data, some tables, some columns of some tables... (you answered this some columns of one table)
2/ Against who you want to protect the data: common users, DBA, sysadmin...
3/ Against which kind of access you want to protect: SQL, direct file access, backups, network sniffing...
4/ Which kind of application have you: SQL*Plus, software package, custom application (which language?), 2-tier or 3-tier...

Note that encryption is not a requirement, it is a way to protect data maybe not the best one in your case.

Regards
Michel
Re: Encrypt & Decrypt [message #479830 is a reply to message #479824] Tue, 19 October 2010 11:10 Go to previous messageGo to next message
kvignes1
Messages: 33
Registered: September 2007
Member

Fine Grained Access Control is a row level security.My question is how do i encrypt a number column and store into the oracle database
Re: Encrypt & Decrypt [message #479831 is a reply to message #479827] Tue, 19 October 2010 11:18 Go to previous messageGo to next message
kvignes1
Messages: 33
Registered: September 2007
Member

Michel Cadot wrote on Tue, 19 October 2010 12:02
Quote:
So for encrypt/ decrypt, we need to use the dbms_obfuscation_toolkit package.

No, use dbms_crypto, surely not the old and obsolete dbms_obfuscation_toolkit.

Quote:
I Google it and found out using 2 functions(DES-Encrypt & DES-Decrypt) we can encrypt & decrypt the data.

These algorithms are no more sure and cracked since about 10 years; use AES.

The best solution depends on the actual requirements:
1/ What do you want to protect: all data, some tables, some columns of some tables... (you answered this some columns of one table)
2/ Against who you want to protect the data: common users, DBA, sysadmin...
3/ Against which kind of access you want to protect: SQL, direct file access, backups, network sniffing...
4/ Which kind of application have you: SQL*Plus, software package, custom application (which language?), 2-tier or 3-tier...

Note that encryption is not a requirement, it is a way to protect data maybe not the best one in your case.

Regards
Michel



Thanks a Lot Michel.

Quote:

1/ What do you want to protect: all data, some tables, some columns of some tables... (you answered this some columns of one table)


yes you are right. some columns of one table.

Quote:

2/ Against who you want to protect the data: common users, DBA, sysadmin...


Common Users

Quote:

3/ Against which kind of access you want to protect: SQL, direct file access, backups, network sniffing...


SQL access

Quote:

4/ Which kind of application have you: SQL*Plus, software package, custom application (which language?), 2-tier or 3-tier...


SQL*Plus.


I am not able to encrypt the column with number. Thanks for your reply again.



Re: Encrypt & Decrypt [message #479832 is a reply to message #479831] Tue, 19 October 2010 11:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I am not able to encrypt the column with number.
The statement above is as useful as saying

My car won't go.
Tell me how to make my car go.

I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Encrypt & Decrypt [message #479833 is a reply to message #479831] Tue, 19 October 2010 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If this common users then use a view for these users without the unwanted columns and do not grant them direct access to the table to these users but to the view.
Here's an example.
Using the usual EMP table and assuming that TEST user should not see EMPNO, SAL and COMM columns, you can use:
SQL> create user test identified by test;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> desc emp
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 EMPNO                            NOT NULL NUMBER(4)
 ENAME                                     VARCHAR2(10)
 JOB                                       VARCHAR2(9)
 MGR                                       NUMBER(4)
 HIREDATE                                  DATE
 SAL                                       NUMBER(7,2)
 COMM                                      NUMBER(7,2)
 DEPTNO                                    NUMBER(2)

SQL> create or replace view emp_view as 
  2  select ename, job, mgr, hiredate, deptno 
  3  from emp
  4  /

View created.

SQL> create synonym test.emp for emp_view;

Synonym created.

SQL> grant select on emp_view to test;

Grant succeeded.

SQL> connect test/test
Connected.
TEST> select * from emp;
ENAME      JOB              MGR HIREDATE                DEPTNO
---------- --------- ---------- ------------------- ----------
SMITH      CLERK           7902 17/12/1980 00:00:00         20
ALLEN      SALESMAN        7698 20/02/1981 00:00:00         30
WARD       SALESMAN        7698 22/02/1981 00:00:00         30
JONES      MANAGER         7839 02/04/1981 00:00:00         20
MARTIN     SALESMAN        7698 28/09/1981 00:00:00         30
BLAKE      MANAGER         7839 01/05/1981 00:00:00         30
CLARK      MANAGER         7839 09/06/1981 00:00:00         10
SCOTT      ANALYST         7566 09/12/1982 00:00:00         20
KING       PRESIDENT            17/11/1981 00:00:00         10
TURNER     SALESMAN        7698 08/09/1981 00:00:00         30
ADAMS      CLERK           7788 12/01/1983 00:00:00         20
JAMES      CLERK           7698 03/12/1981 00:00:00         30
FORD       ANALYST         7566 03/12/1981 00:00:00         20
MILLER     CLERK           7782 23/01/1982 00:00:00         10

Regards
Michel


[Updated on: Tue, 19 October 2010 11:29]

Report message to a moderator

Re: Encrypt & Decrypt [message #479940 is a reply to message #479833] Wed, 20 October 2010 06:15 Go to previous message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://forums.oracle.com/forums/thread.jspa?threadID=581699&tstart=0
http://psoug.org/reference/dbms_crypto.html
Previous Topic: Create table others schema
Next Topic: Need info on Inactive Session
Goto Forum:
  


Current Time: Thu Mar 28 11:32:32 CDT 2024