Home
Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups
General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement |
| Working with INSTEAD-OF Triggers, DBMS_CRYPTO, and WRAP |
One of the big issues confronting organizations requiring PCI compliance is securing credit card numbers. On one hand we need to have the PAN available for billing and on the other we must fully comply. For more information on PCI [Click
Here]. The following demonstration is based on three levels. The lowest, most secure, level that holds the encrypted credit card and has the ability to engage in encryption and decryption is named FIREWALL. This schema should be a mech_id: In other words an application account not associated with any person or person. After
the objects it owns are created it should be locked and expired. For the client level, the end-user entering or accessing credit card information this demo uses the well known, and least privileged, scott/tiger schema. Between these two is the application level which is contained in a schema named APPLEVEL.
Many other solutions to the problem are possible to create but are impossible to implement without substantial changes to existing application code. This solution assumes that a table exists and hides the table behind a view with an instead-of trigger.
The following solution utilizes dbms_crypto.randombytes to create random keys. In a real-world implementation the key would be hard coded into the wrapped trigger code and changed annually.
First lets create the required users and grant them their minimum required system and object privileges. |
CREATE USER firewall IDENTIFIED BY securedpwd
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON uwdata;
GRANT create session, create table TO firewall;
CREATE USER applevel IDENTIFIED BY apppwd
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON uwdata;
GRANT create session, create view, create trigger TO applevel;
GRANT execute ON dbms_crypto TO applevel; |
| The next step is to create the most secure level that will be owned by the FIREWALL schema. Pay special attention to the grant WITH GRANT OPTION. This is the key to passing along carefully crafted access to SCOTT that makes access to the underlying table available without letting SCOTT ever know
that it exists. |
conn firewall/securedpwd
CREATE TABLE cc_source (
member_id NUMBER(3),
ccno VARCHAR2(19),
pan RAW(100));
GRANT select, insert, update ON cc_source TO applevel WITH GRANT OPTION;
conn / as sysdba
REVOKE create session, create table FROM firewall;
ALTER USER firewall ACCOUNT LOCK; |
|
Next we will build the component owned by the application; defined here as the APPLEVEL schema. |
conn applevel/apppwd
desc firewall.cc_source
CREATE OR REPLACE VIEW cc_source AS
SELECT member_id, ccno
FROM firewall.cc_source;
desc cc_source
INSERT INTO cc_source VALUES (1, '7890-1234-5678-9012');
col pan format a65
SELECT * FROM cc_source;
SELECT * FROM firewall.cc_source;
-- this trigger, to make this secure, should be created using wrapped code
CREATE OR REPLACE TRIGGER cc_secure
INSTEAD OF INSERT OR UPDATE
ON cc_source
FOR EACH ROW
DECLARE
enc_val RAW(200);
l_key RAW(200);
l_key_len NUMBER := 256/8; -- convert bits to bytes
l_mod NUMBER := dbms_crypto.encrypt_aes256+dbms_crypto.chain_cbc+dbms_crypto.pad_zero;
BEGIN
l_key := dbms_crypto.randombytes(l_key_len);
enc_val := dbms_crypto.encrypt(utl_raw.cast_to_raw(convert(:NEW.ccno,'AL32UTF8')), l_mod, l_key);
IF INSERTING THEN
INSERT INTO firewall.cc_source
VALUES
(:NEW.member_id, SUBSTR(:NEW.ccno,16,4), enc_val);
END IF;
END cc_secure;
/
INSERT INTO cc_source VALUES (2, '8901-2345-6789-0123');
SELECT * FROM cc_source;
SELECT * FROM firewall.cc_source;
GRANT select, insert, update ON cc_source TO scott; |
|
Finally lets connect as the application user and verify that we have both the required functionality and, also, an inability to access the underlying PAN. |
conn scott/tiger
desc applevel.cc_source
desc firewall.cc_source
SELECT * FROM applevel.cc_source;
SELECT * FROM firewall.cc_source;
INSERT INTO applevel.cc_source VALUES (3, '9012-3456-7890-1234');
SELECT * FROM applevel.cc_source;
-- two weaknesses
SELECT text
FROM all_views
WHERE owner = 'APPLEVEL'
AND view_name = 'CC_SOURCE';
SELECT referenced_owner, referenced_name
FROM all_dependencies
WHERE owner = 'APPLEVEL'
AND name = 'CC_SOURCE'; |
This solution is fully compliant as if someone gains access to either the FIREWALL schema, or the backup tapes, the PAN need never exists in an unencrypted form.
A better solution can be crafted using native dynamic SQL in a PL/SQL package as not only can the code be wrapped but it is possible to totally obscure the dependency. A demo of how to do this will be crafted in the near future. |
|
