CREATE OR REPLACE TRIGGER SYS.DDL_MONITOR
AFTER CREATE OR DROP OR TRUNCATE OR ALTER OR GRANT OR REVOKE
ON DATABASE
DECLARE
V_TERMINAL V$SESSION.TERMINAL%TYPE;
V_OSUSER V$SESSION.OSUSER%TYPE;
V_MACHINE V$SESSION.MACHINE%TYPE;
V_IP VARCHAR2 (20);
V_MODULE V$SESSION.MODULE%TYPE;
V_DATABASE V$PARAMETER.VALUE%TYPE;
CRLF VARCHAR2 (2) := CHR (13) || CHR (10);
BEGIN
SELECT VALUE
INTO V_DATABASE
FROM V$PARAMETER
WHERE NAME = 'db_name';
V_OSUSER := SYS_CONTEXT ('USERENV', 'OS_USER');
V_MACHINE := SYS_CONTEXT ('USERENV', 'TERMINAL');
V_IP := SYS_CONTEXT ('USERENV', 'IP_ADDRESS');
INSERT INTO HELPDESK.S_DDL_MONITOR (DATE_TIME,
DDL_EVENT,
OS_USER,
MACHINE)
VALUES (
SYSDATE,
'Username: ('
|| ORA_LOGIN_USER
|| ') '
|| 'Action: ('
|| ORA_SYSEVENT
|| ') '
|| 'Object: ('
|| ORA_DICT_OBJ_OWNER
|| '.'
|| ORA_DICT_OBJ_NAME
|| ') '
|| 'Type: ('
|| ORA_DICT_OBJ_TYPE
|| ')',
V_OSUSER,
V_IP || ' (' || V_MACHINE || ')');
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/The table, in which information is stored is:
SQL> DESC HELPDESK.S_DDL_MONITOR
Name Null? Type
----------------------- ----- -------------
DATE_TIME DATE
DDL_EVENT VARCHAR2(500)
OS_USER VARCHAR2(30)
MACHINE VARCHAR2(150)Some users have the SELECT ANY TABLE system privilege and we cannot revoke it from them.
But we do not want them to read these data. So, we decided to encrypt all columns in this table, except DATE_TIME.
We will use DBMS_CRYPTO package to achieve this. All procedures we will create will be under SYSTEM schema, so, as SYSDBA, grant execute to the package:
SQL> GRANT EXECUTE ON SYS.DBMS_CRYPTO TO SYSTEM;
Grant succeeded.First, we will need an encryption key. Obviously, this key should be stored and not to be given to anyone not trusted.
SQL> SELECT DBMS_CRYPTO.RANDOMBYTES(24) FROM DUAL;
DBMS_CRYPTO.RANDOMBYTES(24)
------------------------------------------------
FCF50295B0A28167FF88218A2EF8575102A20874305C8F2AThe following function converts a string to its encrypted value, using the key we just generated.
The encryption key should be stored in the function's code and not be passed as an input variable.
CREATE OR REPLACE FUNCTION SYSTEM.ENC_VARCHAR (V_VARCHAR IN VARCHAR2)
RETURN RAW
IS
V_ENC_VARCHAR RAW(500);
V_KEY RAW(24) := 'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A';
V_MOD NUMBER
:= SYS.DBMS_CRYPTO.ENCRYPT_AES
+ SYS.DBMS_CRYPTO.CHAIN_CBC
+ SYS.DBMS_CRYPTO.PAD_PKCS5;
BEGIN
V_ENC_VARCHAR :=
SYS.DBMS_CRYPTO.ENCRYPT (UTL_RAW.CAST_TO_RAW (V_VARCHAR), V_MOD, V_KEY);
RETURN V_ENC_VARCHAR;
END;
/The next function takes the encrypted value and the key (in this case, it should be passed as an input variable) and returns the original string:
CREATE OR REPLACE FUNCTION SYSTEM.DEC_VARCHAR (V_RAW IN RAW, V_KEY IN RAW)
RETURN VARCHAR2
IS
V_RET VARCHAR2 (500);
V_DEC_RAW RAW (500);
V_MOD NUMBER
:= DBMS_CRYPTO.ENCRYPT_AES
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
V_DEC_RAW := DBMS_CRYPTO.DECRYPT (V_RAW, V_MOD, V_KEY);
V_RET := UTL_RAW.CAST_TO_VARCHAR2 (V_DEC_RAW);
RETURN V_RET;
END;
/We should wrap these functions, so their code and, most importantly, the encryption key will not be readable by anyone:
SQL> DECLARE
BEGIN
DBMS_DDL.CREATE_WRAPPED('CREATE OR REPLACE FUNCTION SYSTEM.ENC_VARCHAR (V_VARCHAR IN VARCHAR2)
RETURN RAW
IS
V_ENC_VARCHAR RAW(500);
V_KEY RAW(24) := ''FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A'';
V_MOD NUMBER
:= SYS.DBMS_CRYPTO.ENCRYPT_AES
+ SYS.DBMS_CRYPTO.CHAIN_CBC
+ SYS.DBMS_CRYPTO.PAD_PKCS5;
BEGIN
V_ENC_VARCHAR :=
SYS.DBMS_CRYPTO.ENCRYPT (UTL_RAW.CAST_TO_RAW (V_VARCHAR), V_MOD, V_KEY);
RETURN V_ENC_VARCHAR;
END;');
END;
/
PL/SQL procedure successfully completed.
SQL> DECLARE
BEGIN
DBMS_DDL.CREATE_WRAPPED('CREATE OR REPLACE FUNCTION SYSTEM.DEC_VARCHAR (V_RAW IN RAW, V_KEY IN RAW)
RETURN VARCHAR2
IS
V_RET VARCHAR2 (500);
V_DEC_RAW RAW (500);
V_MOD NUMBER
:= DBMS_CRYPTO.ENCRYPT_AES
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
V_DEC_RAW := DBMS_CRYPTO.DECRYPT (V_RAW, V_MOD, V_KEY);
V_RET := UTL_RAW.CAST_TO_VARCHAR2 (V_DEC_RAW);
RETURN V_RET;
END;');
END;
/
PL/SQL procedure successfully completed.Check, if we can see the code:
SQL> SELECT DBMS_METADATA.GET_DDL('FUNCTION','ENC_VARCHAR','SYSTEM') FROM DUAL;
DBMS_METADATA.GET_DDL('FUNCTION','ENC_VARCHAR','SYSTEM')
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION "SYSTEM"."ENC_VARCHAR" wrapped
a000000
25
abcd
ab
SQL> SELECT DBMS_METADATA.GET_DDL('FUNCTION','DEC_VARCHAR','SYSTEM') FROM DUAL;
DBMS_METADATA.GET_DDL('FUNCTION','DEC_VARCHAR','SYSTEM')
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION "SYSTEM"."DEC_VARCHAR" wrapped
a000000
25
abcd
abWe check, if they work, by encrypting and decrypting string "This is a test".
SQL> SELECT SYSTEM.ENC_VARCHAR('This is a test') FROM DUAL;
SYSTEM.ENC_VARCHAR('THISISATEST')
----------------------------------
10009EB5AA82E2D10047A7B662720E29
SQL> SELECT SYSTEM.DEC_VARCHAR (
'10009EB5AA82E2D10047A7B662720E29',
'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2A')
FROM DUAL;
SYSTEM.DEC_VARCHAR(
-------------------
This is a testAnd if we use a wrong key:
SQL> SELECT SYSTEM.DEC_VARCHAR (
'10009EB5AA82E2D10047A7B662720E29',
'FCF50295B0A28167FF88218A2EF8575102A20874305C8F2B')
FROM DUAL;
SELECT SYSTEM.DEC_VARCHAR (
*
ERROR at line 1:
ORA-28817: PL/SQL function returned an error.
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
ORA-06512: at "SYS.DBMS_CRYPTO", line 41
ORA-06512: at "SYSTEM.DEC_VARCHAR", line 11Now, as SYSDBA we change the trigger to use our encryption function:
CREATE OR REPLACE TRIGGER SYS.DDL_MONITOR
AFTER CREATE OR DROP OR TRUNCATE OR ALTER OR GRANT OR REVOKE
ON DATABASE
DECLARE
V_TERMINAL V$SESSION.TERMINAL%TYPE;
V_OSUSER V$SESSION.OSUSER%TYPE;
V_MACHINE V$SESSION.MACHINE%TYPE;
V_IP VARCHAR2 (20);
V_MODULE V$SESSION.MODULE%TYPE;
V_DATABASE V$PARAMETER.VALUE%TYPE;
CRLF VARCHAR2 (2) := CHR (13) || CHR (10);
V_DDL_EVENT VARCHAR2 (500);
V_DDL_EVENT_ENC RAW (500);
V_OSUSER_ENC RAW (500);
V_MACHINE2 VARCHAR2 (150);
V_MACHINE_ENC RAW (500);
BEGIN
SELECT VALUE
INTO V_DATABASE
FROM V$PARAMETER
WHERE NAME = 'db_name';
V_OSUSER := SYS_CONTEXT ('USERENV', 'OS_USER');
V_MACHINE := SYS_CONTEXT ('USERENV', 'TERMINAL');
V_IP := SYS_CONTEXT ('USERENV', 'IP_ADDRESS');
V_DDL_EVENT :=
'Username: ('
|| ORA_LOGIN_USER
|| ') '
|| 'Action: ('
|| ORA_SYSEVENT
|| ') '
|| 'Object: ('
|| ORA_DICT_OBJ_OWNER
|| '.'
|| ORA_DICT_OBJ_NAME
|| ') '
|| 'Type: ('
|| ORA_DICT_OBJ_TYPE
|| ')';
V_MACHINE2 := V_IP || ' (' || V_MACHINE || ')';
V_DDL_EVENT_ENC := SYSTEM.ENC_VARCHAR (V_DDL_EVENT);
V_OSUSER_ENC := SYSTEM.ENC_VARCHAR (V_OSUSER);
V_MACHINE_ENC := SYSTEM.ENC_VARCHAR (V_MACHINE2);
INSERT INTO HELPDESK.S_DDL_MONITOR (DATE_TIME,
DDL_EVENT,
OS_USER,
MACHINE)
VALUES (SYSDATE,
UTL_RAW.CAST_TO_VARCHAR2 (V_DDL_EVENT_ENC),
UTL_RAW.CAST_TO_VARCHAR2 (V_OSUSER_ENC),
UTL_RAW.CAST_TO_VARCHAR2 (V_MACHINE_ENC));
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/We execute a few DDL statements and query the table:
SQL> SELECT DATE_TIME, DDL_EVENT, OS_USER, MACHINE FROM HELPDESK.S_DDL_MONITOR;| DATE_TIME | DDL_EVENT | OS_USER | MACHINE |
| 16-FEB-12 | ξΎΉ‰G« ω―ζ œCXά ’fƒp0CΡηΌE)b¤Ιΐ ¨ψ‰ψ’ ψζˆ,κΣ¤ ί¥L—£pμί¦eΦ&ΚY*<<ό©¶Y /]― C@ˆιCc; | d«>8υRπ`WXΪ,¥ΔL | g…£JmΜ B»βν¬žciβ k|
No comments:
Post a Comment