Home » RDBMS Server » Security » Encrypt & Decrypt
| Encrypt & Decrypt [message #479823] |
Tue, 19 October 2010 10:38  |
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 #479827 is a reply to message #479824] |
Tue, 19 October 2010 11:02  |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #479831 is a reply to message #479827] |
Tue, 19 October 2010 11:18 |
kvignes1
Messages: 33
Registered: September 2007
|
Member |
|
|
Michel Cadot wrote on Tue, 19 October 2010 12:02Quote: 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 #479833 is a reply to message #479831] |
Tue, 19 October 2010 11:28 |
|
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
|
|
Goto Forum:
Current Time: Mon Jan 06 17:16:56 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
