DBMS_CRYPTO is well known package collection that are used in encrypting or hashing values in oracle database. This is true out of the box collection set that really help oracle people in many security tasks.
Regardless there are many nice topics on this subject (one of them is of course Database Security Enhancements in Oracle Database 10g about all important in this area), my intention is to show a little bit different approach for very common problem: encrypting column values and showing them fully, partially or not at all based on custom rule.
The problem
Let us suppose we have a table with some sensitive data we should protect somehow:SQL> DESC SEC_DEMO_TBL Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(38) A_USERNAME NOT NULL VARCHAR2(32) A_CREDIT_CARD_NUMBER NOT NULL VARCHAR2(32) SQL> insert into SEC_DEMO_TBL values (1, 'SMITH', '0123456-789-0001'); 1 row created. SQL> insert into SEC_DEMO_TBL values (2, 'PITT', '0123456-789-0002'); 1 row created. SQL> insert into SEC_DEMO_TBL values (3, 'JOLIE', '0123456-789-0003'); 1 row created. SQL> commit; Commit complete. SQL> select * from SEC_DEMO_TBL; ID A_USERNAME A_CREDIT_CARD_NUMBER ---------- -------------------------------- -------------------------------- 1 SMITH 0123456-789-0001 2 PITT 0123456-789-0002 3 JOLIE 0123456-789-0003 SQL>A_CREDIT_CARD_NUMBER is a column where now exists open credit card numbers. A task is to crypt it and show their values according to 4 types of users:
- ordinary oracle user cannot see any part of A_CREDIT_CARD_NUMBER column at all!
- '***********' to ordinary user (see stars only instead of cc number)
- '******xxxx' to privileged user (see last four numbers of the cc number)
- 'xxxx****xxxx' to power user (see first and last four numbers of cc number)
- 'xxxxxxxxxxxxx' to data admin user (see complete cc number)
The implementation
The implementation is divided in next four parts:- Implement security policy that will allow four different outputs
- Create core functions
- Convert existing data to crypted values
- Ensure that new incoming data are crypted as well
- Test results
Implement security policy that will allow four different outputs
As an owner of the table, create view and public synonym on that view in a way that A_CREDIT_CARD_NUMBER will be masked with incomming functionSQL> create or replace force view SEC_DEMO_VW as ( 2 SELECT id, 3 a_username, 4 cc_security.decrypt_data(A_CREDIT_CARD_NUMBER) CC 5 FROM SEC_DEMO_TBL 6 ); Warning: View created with compilation errors. SQL> show errors; No errors. SQL> create or replace public synonym SEC_DEMO for SEC_DEMO_VW; Synonym created. SQL>Force view is nice oracle feature that allow us to create views based on objects that doesn't exist in the moment of creation. Regardless view is invalid it will become valid on first usage. Beside that masking practice (different public name from real object name) is always a good security feature.
Because I allways like oracle based security solutions, this approach will involve 4 different roles which should be grant to chosen users:
- SEC_CC_0_NUMBERS
- SEC_CC_4_NUMBERS
- SEC_CC_8_NUMBERS
- SEC_CC_ALL_NUMBERS
SQL> CREATE ROLE SEC_CC_0_NUMBERS IDENTIFIED BY VALUES '1F5C90D6079FF8AA';
Role created.
SQL> BEGIN
2 SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
3 SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
4 SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('SEC_CC_0_NUMBERS', 'DEFAULT_CON
SUMER_GROUP', false);
5 SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> CREATE ROLE SEC_CC_4_NUMBERS IDENTIFIED BY VALUES '3B078E03C4E9230E';
Role created.
SQL> BEGIN
2 SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
3 SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
4 SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('SEC_CC_4_NUMBERS', 'DEFAULT_CON
SUMER_GROUP', false);
5 SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> CREATE ROLE SEC_CC_8_NUMBERS IDENTIFIED BY VALUES '3F5493B72C7BFA18';
Role created.
SQL> BEGIN
2 SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
3 SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
4 SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('SEC_CC_8_NUMBERS', 'DEFAULT_CON
SUMER_GROUP', false);
5 SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> CREATE ROLE SEC_CC_ALL_NUMBERS IDENTIFIED BY VALUES 'DE1D7A90367C516C';
Role created.
SQL> BEGIN
2 SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
3 SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
4 SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('SEC_CC_ALL_NUMBERS', 'DEFAULT_C
ONSUMER_GROUP', false);
5 SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
6 END;
7 /
PL/SQL procedure successfully completed.It is very important to create rolas under privileged user who is trusted. In this case I have used owner of the table schema. And of course, secure rolas with password so altering that role can be done only from appropriate user.For test we'll have 4 users:
- A_USER_0 (granted "SEC_CC_0_NUMBERS" rola)
- A_USER_4 (granted "SEC_CC_4_NUMBERS" rola)
- A_USER_8 (granted "SEC_CC_8_NUMBERS" rola)
- A_USER_ALL (granted "SEC_CC_ALL_NUMBERS" rola)
SQL> create user a_user_0 identified by a_user_0; User created. SQL> create user a_user_4 identified by a_user_4; User created. SQL> create user a_user_8 identified by a_user_8; User created. SQL> create user a_user_all identified by a_user_all; User created. SQL> grant create session to a_user_0; Grant succeeded. SQL> grant create session to a_user_4; Grant succeeded. SQL> grant create session to a_user_8; Grant succeeded. SQL> grant create session to a_user_all; Grant succeeded. SQL>
As you can see user has no other privilege except create session. Then, grant appropriate roles to them:
SQL> grant sec_cc_0_numbers to a_user_0; Grant succeeded. SQL> grant sec_cc_4_numbers to a_user_4; Grant succeeded. SQL> grant sec_cc_8_numbers to a_user_8; Grant succeeded. SQL> grant sec_cc_all_numbers to a_user_all; Grant succeeded. SQL>
Create core functions
When users and security part is done, next comes creation of core functions that we'll use. In this example I'll use one package with only two exposed functions encrypt_data and decrypt_data and this is all I need at all:CREATE OR REPLACE PACKAGE cc_security /*-------------------------------------------------------------------------------------------------------------------- NAME : cc_security PURPOSE : Utility which holds sensitive security data. MUST BE WRAPPED! Date : 02.07.2009. Author : Damir Vadas Remarks : owner of package must have grant execute on DBMS_CRYPTO package Changes (DD.MM.YYYY, Name, CR/TR#): -------------------------------------------------------------------------------------------------------------------- */ IS FUNCTION encrypt_data ( p_input_string IN VARCHAR2 ) RETURN VARCHAR2; FUNCTION decrypt_data ( p_input_string IN VARCHAR2 ) RETURN VARCHAR2; END cc_security; /
Package body:
CREATE OR REPLACE PACKAGE BODY cc_security
IS
-- This is most sensitive part because based on this seed all encrypting is done!
-- Use some oracle random generator for it
p_seed VARCHAR2(24) := 'IFDXNJEQETNPTMSBICPSQXJK';
p_seed_length PLS_INTEGER DEFAULT 24;
SQLERRMSG VARCHAR2(255);
SQLERRCDE NUMBER;
-- Defined Encryption Methods:
-- SH1_ECB_ZERO: Secure Hash Algorithm, Electronic Codebook Cipher chaining, pad with zeroes
SH1_ECB_ZERO CONSTANT PLS_INTEGER := DBMS_CRYPTO.HASH_SH1 + DBMS_CRYPTO.CHAIN_OFB + DBMS_CRYPTO.PAD_ZERO;
/*
I allways prefer separate functions because of reusability code. So this function will return:
0 ordinary user - norights at all!
4 privileged user with SEC_CC_4_NUMBERS role
8 power user with SEC_CC_8_NUMBERS role
16 admin user with SEC_CC_ALL_NUMBERS
*/
function level_cc_user_rights (p_username IN DBA_USERS.USERNAME%TYPE
) return INTEGER
IS
l_retval INTEGER;
BEGIN
SELECT decode (granted_role,
'SEC_CC_4_NUMBERS',4,
'SEC_CC_8_NUMBERS',8,
'SEC_CC_ALL_NUMBERS',16,
0
)
INTO l_retval
FROM dba_role_privs
WHERE grantee=p_username;
return l_retval;
EXCEPTION
WHEN no_data_found then
return -1;
WHEN OTHERS THEN
return -1;
END level_cc_user_rights;
FUNCTION encrypt_data (
p_input_string IN VARCHAR2
) RETURN VARCHAR2
IS
converted_seed RAW(128);
converted_string RAW(128);
encrypted_string RAW(128);
BEGIN
-- Convert incoming string and supplied seed to RAW datatype using the
-- new UTLI18N package to convert the string to the AL32UTF8 character set
converted_string := UTL_I18N.STRING_TO_RAW(p_input_string, 'AL32UTF8');
IF length(nvl(p_seed,'1'))<>p_seed_length THEN
RETURN NULL;
END IF;
converted_seed := UTL_I18N.STRING_TO_RAW(p_seed, 'AL32UTF8');
-- Encrypt the RAW value using the ENCRYPT function and the appropriate
-- encryption type
encrypted_string :=
DBMS_CRYPTO.ENCRYPT(
src => converted_string
,typ => SH1_ECB_ZERO
,key => converted_seed
,iv => NULL);
RETURN to_char(encrypted_string);
EXCEPTION
WHEN OTHERS THEN
SQLERRMSG := SQLERRM;
SQLERRCDE := SQLCODE;
RETURN NULL;
END encrypt_data;
FUNCTION decrypt_data (
p_input_string IN VARCHAR2
) RETURN VARCHAR2
IS
converted_string VARCHAR2(128);
decrypted_string VARCHAR2(128);
converted_seed RAW(64);
l_user VARCHAR2(32);
l_cc_level INTEGER;
BEGIN
-- Convert incoming string and supplied seed to RAW datatype using the
-- new UTLI18N package to convert the string to the AL32UTF8 character
-- set
IF length(nvl(p_seed,'1'))<>p_seed_length THEN
RETURN NULL;
END IF;
converted_string := UTL_I18N.STRING_TO_RAW(p_input_string, 'AL32UTF8');
converted_seed := UTL_I18N.STRING_TO_RAW(p_seed, 'AL32UTF8');
-- Encrypt the RAW value using the ENCRYPT function and the appropriate
-- encryption type
decrypted_string :=
DBMS_CRYPTO.DECRYPT(
src => p_input_string
,typ => SH1_ECB_ZERO
,key => converted_seed
,iv => NULL);
-- Convert incoming string to RAW datatype, using the UTLI18N package
-- to convert the string to the AL32UTF8 character set
converted_string := UTL_I18N.RAW_TO_CHAR(decrypted_string, 'AL32UTF8');
-- Check if user is priviledged to decrypt data
SELECT sys_context('USERENV', 'SESSION_USER')
INTO l_user
FROM dual;
l_cc_level :=level_cc_user_rights(l_user);
IF l_cc_level=0 THEN
-- ordinary user-shows only stars
RETURN '***************';
ELSIF l_cc_level=4 THEN
-- privileged user-shows only 4 last digits...
IF LENGTH(converted_string) <= 4 THEN
RETURN NULL;
ELSE
RETURN '***********'|| SUBSTR(converted_string,length(converted_string)-3,4);
END IF;
ELSIF l_cc_level=8 THEN
-- power user-shows only first and last 4 digits...
IF LENGTH(converted_string) <= 8 THEN
RETURN NULL;
ELSE
RETURN SUBSTR(converted_string,1,4) || '*******'|| SUBSTR(converted_string,length(converted_string)-3,4);
END IF;
ELSIF l_cc_level=16 THEN
-- admin user-show all!
RETURN converted_string;
ELSE
-- This is for all "classic oracle users" they see null!
RETURN null;
END IF;
EXCEPTION
WHEN OTHERS THEN
SQLERRMSG := SQLERRM;
SQLERRCDE := SQLCODE;
RETURN NULL;
END decrypt_data;
END cc_security;
/For this part of task I strongly suggest to use ready made oracle functions from DBMS_CRYPTO package because they are fully tested and provide full compatibility to any future version and any kind of database upgrade. Keep in mind that this package body MUST BE WRAPPED because it holds the most sensitive data which should not be available even to DBA's!
As you remember we have created view which now can be completed with grant select for previously created users. So as an table owner perform:
SQL> grant select on SEC_DEMO_VW to a_user_0,a_user_4,a_user_8,a_user_all; Grant succeeded. SQL>With this grant only named users can even select data from table (there is no select on SEC_DEMO_TBL table at all), so first request is applied (ordinary oracle user has no no query right at all).
Convert existing data to crypted value
Existing data in column A_CREDIT_CARD_NUMBER has to be encrypted first. This is done on a very easy way through simple SQL:SQL> update SEC_DEMO_TBL 2 set A_CREDIT_CARD_NUMBER = cc_security.encrypt_data (A_CREDIT_CARD_NUMBER); 3 rows updated. SQL> commit; Commit complete. SQL> select * from SEC_DEMO_TBL; ID A_USERNAME A_CREDIT_CARD_NUMBER ---------- -------------------------------- -------------------------------- 1 SMITH 93B38FD3A694DA2A11988AAE1F0E148D 2 PITT 93B38FD3A694DA2AE7C11AA4994D5B04 3 JOLIE 93B38FD3A694DA2AEB1B968C370095E9 SQL>Keep in mind that in this step you loose original A_CREDIT_CARD_NUMBER values so if you haven't done a backup or proper test your code, you might get in trouble retrieving values back!
Ensure that new incoming data are crypted as well
For any new data (or editing existing ones) a trigger is the best way to ensure validity of future actions. This trigger is for upadate and insert as well.CREATE OR REPLACE TRIGGER SEC_DEMO_TBL$TGBIU BEFORE INSERT OR UPDATE ON SEC_DEMO_TBL FOR EACH ROW BEGIN IF inserting THEN IF :new.A_CREDIT_CARD_NUMBER is not null THEN :new.A_CREDIT_CARD_NUMBER := cc_security.encrypt_data(:new.A_CREDIT_CARD_NUMBER); END IF; END IF; IF updating THEN IF :new.A_CREDIT_CARD_NUMBER is not null AND :new.A_CREDIT_CARD_NUMBER != :old.A_CREDIT_CARD_NUMBER THEN :new.A_CREDIT_CARD_NUMBER := cc_security.encrypt_data(:new.A_CREDIT_CARD_NUMBER); END IF; END IF; END SEC_DEMO_TBL$TGBIU; /
Do small test and insert data as table owner:
SQL> conn damirv/***** Connected. SQL> insert into SEC_DEMO_TBL values (4,'RAMBO', '0123456-789-0004'); 1 row created. SQL> commit; Commit complete. SQL>Insert seems to be proper!
Test results
Here is the result for all 4 users...Results are obvious!SQL> conn a_user_0/a_user_0 Connected. SQL> select * from SEC_DEMO; ID A_USERNAME CC ---------- -------------------------------- ---------------- 1 SMITH *************** 2 PITT *************** 3 JOLIE *************** 4 RAMBO *************** SQL> SQL> conn a_user_4/a_user_4 Connected. SQL> select * from SEC_DEMO; ID A_USERNAME CC ---------- -------------------------------- --------------- 1 SMITH ***********0001 2 PITT ***********0002 3 JOLIE ***********0003 4 RAMBO ***********0004 SQL> SQL> conn a_user_8/a_user_8 Connected. SQL> select * from SEC_DEMO; ID A_USERNAME CC ---------- -------------------------------- --------------- 1 SMITH 0123*******0001 2 PITT 0123*******0002 3 JOLIE 0123*******0003 4 RAMBO 0123*******0004 SQL> SQL> conn a_user_all/a_user_all Connected. SQL> select * from SEC_DEMO; ID A_USERNAME CC ---------- -------------------------------- ---------------- 1 SMITH 0123456-789-0001 2 PITT 0123456-789-0002 3 JOLIE 0123456-789-0003 4 RAMBO 0123456-789-0004 SQL>All looks OK!
The end
The beauty of this solution is that it is bullet proof because it is implemented through Oracle roles-admin tool for user's right administration. In a moment roles can be revoked from user or granted to some other user.If you connect as owner of table, who own package, table and view but have no proper rola granted, you are unable to see data:
SQL> conn damirv/***** Connected. SQL> select * from SEC_DEMO; ID A_USERNAME CC ---------- -------------------------------- ---------------- 1 SMITH 2 PITT 3 JOLIE 4 RAMBO SQL>As you see even as an owner of procedure and table cannot see data in it's own schema and you are treated as ordinary user! While in the same time, trigger in owner's schema is updating/inserting data without problems! Of course, as owner you can see plain data (with crypted column) as shown at the beggining but if this is a problem, lock this user!
And for the end, the whole administration of this solution is centralized in one package and one trigger what allows easy ugrade path in the future.
Cheers!
Sorry for my bad english. Thank you so much for your good post. Your post helped me in my college assignment, If you can provide me more details please email me.
ReplyDeleteSorry for my bad english. Thank you so much for your good post. Your post helped me in my college assignment, If you can provide me more details please email me.
ReplyDelete