Category: Security

ORA-4098 from After Logon Trigger

SQL> connect testuser
Enter password:
ERROR:
ORA-04098: trigger 'SYS.CHECK_USERS_TRIGGER' is invalid and failed
re-validation


Warning: You are no longer connected to ORACLE.

SOLUTION

There is an after logon trigger which is invalid.  To fix the issue by either way of the following :

1)Fix the after logon trigger to make it valid.

2)Disable the invalid after logon trigger.

SQL>ALTER TRIGGER SYS.CHECK_USERS_TRIGGER DISABLE ;

3)Drop the invalid after logon trigger.

SQL>drop trigger SYS.CHECK_USERS_TRIGGER;

We must be careful when using AFTER LOGON triggers, because if after logon trigger becomes invalid or failed to execute due to some other errors, it prevents the users (even DBA users) from logging in. The only way to resolve this error would be to connect with SYSDBA privileges.

Data Encryption and Decryption in Oracle

1)DBMS_OBFUSCATION_TOOLKIT package:

Encryption Function:

SQL> CREATE OR REPLACE FUNCTION encrypt (p_text IN VARCHAR2, p_key VARCHAR2 )
 RETURN RAW
 IS
 lc_text VARCHAR2(32767) := p_text;
 lr_key RAW(255) := UTL_RAW.cast_to_raw(p_key);
 lt_enc_text RAW(32767);
BEGIN
lc_text := RPAD( lc_text, (TRUNC(LENGTH(lc_text)/8)+1)*8, CHR(0) );
DBMS_OBFUSCATION_TOOLKIT.desencrypt(input => UTL_RAW.cast_to_raw(lc_text),
                                      key => lr_key,
                           encrypted_data => lt_enc_text);
RETURN lt_enc_text;
END;
 /

Function created.

SQL> SELECT ENCRYPT('HELLO WORLD !','1234567890') from dual;

ENCRYPT('HELLOWORLD!','1234567890')
----------------------------------------------
F9D853C25CAB740D930D7DCA709D0994

SQL>

Decryption Function:

SQL> CREATE OR REPLACE FUNCTION decrypt (p_raw IN RAW, p_key VARCHAR2 )
RETURN VARCHAR2 IS
 lc_decrypted VARCHAR2(32767);
 lc_return_dec VARCHAR2(32767);
 lr_key RAW(255) := UTL_RAW.cast_to_raw(p_key);
 BEGIN
  DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => p_raw,
                                        key => lr_key,
                             decrypted_data => lc_decrypted);
   lc_return_dec := UTL_RAW.cast_to_varchar2(lc_decrypted);
  RETURN RTRIM( lc_return_dec, CHR(0) );
END; 
/

Function created.


SQL> SELECT decrypt('F9D853C25CAB740D930D7DCA709D0994','1234567890')
     FROM   dual;

DECRYPT('F9D853C25CAB740D930D7DCA709D0994','1234567890')
--------------------------------------------------------------
HELLO WORLD !

2) DBMS_CRYPTO Package:

set serveroutput on;
DECLARE
  l_text VARCHAR2(32767) :='1234-5678-9012-3456';
  l_key RAW(128) := UTL_I18N.STRING_TO_RAW('0123456789', 'AL32UTF8');
  l_encrypted_raw RAW(2048);
  l_decrypted_raw RAW(2048);
BEGIN
  dbms_output.put_line('Original : ' || l_text );
  l_encrypted_raw := dbms_crypto.encrypt(
            src =>  UTL_I18N.STRING_TO_RAW (l_text , 'AL32UTF8'),
            typ =>  dbms_crypto.des_cbc_pkcs5,
            key =>  l_key
                                        );
dbms_output.put_line('Encrypted ( CHAR ) : ' ||
                UTL_I18N.RAW_TO_CHAR(l_encrypted_raw, 'AL32UTF8'));
 
dbms_output.put_line('Encrypted ( HEX ) : ' ||
                RAWTOHEX(l_encrypted_raw));
 
 l_decrypted_raw := dbms_crypto.decrypt(
                                src => l_encrypted_raw,
                                typ => dbms_crypto.des_cbc_pkcs5,
                                key => l_key);

 dbms_output.put_line('Decrypted : '|| 
                UTL_I18N.RAW_to_CHAR(l_decrypted_raw,'AL32UTF8'));
 END;

   /

Original : 1234-5678-9012-3456
Encrypted ( CHAR ) : .n?k.z????L??E??AmdM
Encrypted ( HEX ) : 2E1F6EAE186B2E7A9DBCFA834C3FBD45A6B0C3806D64044D
Decrypted : 1234-5678-9012-3456

PL/SQL procedure successfully completed.

DBMS_CRYPTO Block Cipher Suites

NameDescription
DES_CBC_PKCS5ENCRYPT_DES + CHAIN_CBC+ PAD_PKCS5
DES3_CBC_PKCS5ENCRYPT_3DES + CHAIN_CBC + PAD_PKCS5

Cryptographic Hash Functions in Oracle

1) ORA_HASH() Function.

ORA_HASH( expr, max_bucket, seed_value)

  • The optional max_bucket argument determines the maximum bucket value returned by the hash function. You can specify any value between 0 and 4294967295. The default is 4294967295.
  • The optional seed_value argument enables Oracle to produce many different results for the same set of data. Oracle applies the hash function to the combination of expr and seed_value. You can specify any value between 0 and 4294967295. The default is 0.

ORA_HASH function returns a NUMBER value.

SQL>  select ora_hash('Hello World !') from dual;

ORA_HASH('HELLOWORLD!')
-----------------------
             3256043700

2) STANDARD_HASH ( expr, ‘method’), which is available from 12.1 onward.

Valid method algorithms are SHA1, SHA256, SHA384, SHA512, and MD5. If you omit this argument, then SHA1 is used.

SQL> select STANDARD_HASH ( 'Hello World !') from dual;

STANDARD_HASH('HELLOWORLD!')
----------------------------------------
F95BE4341EDDB92DDE5F14D045978F9A4EC6C1A6

SQL> select STANDARD_HASH ( 'Hello World !', 'SHA1') from dual;

STANDARD_HASH('HELLOWORLD!','SHA1')
----------------------------------------
F95BE4341EDDB92DDE5F14D045978F9A4EC6C1A6

SQL> select STANDARD_HASH ( 'Hello World !', 'MD5') from dual;

STANDARD_HASH('HELLOWORLD!','MD5
--------------------------------
B9BE3EF4018BE19F248F6F8E63B9E006

SQL> select STANDARD_HASH ( 'Hello World !', 'SHA256') from dual;

STANDARD_HASH('HELLOWORLD!','SHA256')
----------------------------------------------------------------
07F2BDEF34ED16E3A1BA0DBB7E47B8FD981CE0CCB3E1BFE564D82C423CBA7E47

SQL> select STANDARD_HASH ( 'Hello World !', 'SHA384') from dual;

STANDARD_HASH('HELLOWORLD!','SHA384')
--------------------------------------------------------------------------------
67E60F9CE837CAA3CA82550F0DFCBDE1B8B8A7C1605FA8D115BCC2314204FD95F5F607306622C38C
0205DE7DF6D426D8


SQL> select STANDARD_HASH ( 'Hello World !', 'SHA512') from dual;

STANDARD_HASH('HELLOWORLD!','SHA512')
--------------------------------------------------------------------------------
FEAB0028F1142D420A1425D1DD5B518225B4523AA1CFF63385ECE3411318819F5EC83042CCB79D81
F20E4A243866886CA3AE3026153ACFF8E126C0E89631502E

3) DBMS_OBFUSCATION_TOOLKIT.MD5() function.

SQL> CREATE OR REPLACE FUNCTION toolkit_md5 (v_input VARCHAR2)
RETURN dbms_obfuscation_toolkit.varchar2_checksum
AS
BEGIN
      RETURN dbms_obfuscation_toolkit.md5(input_string => v_input);
END toolkit_md5;
/

Function created.

SQL> select
RAWTOHEX( UTL_RAW.CAST_TO_RAW( toolkit_md5('Hello World !'))) from dual;

RAWTOHEX(UTL_RAW.CAST_TO_RAW(TOOLKIT_MD5('HELLOWORLD!')))
--------------------------------------------------------------------------------
B9BE3EF4018BE19F248F6F8E63B9E006

4) DBMS_CRYPTO.HASH() function.

 The predefined constants HASH_MD4, HASH_MD5 and HASH_SH1 were already available in previous Oracle releases. In Oracle 12c, three additional constants for different lengths of SHA-2 (256, 384 and 512 bits) were added:
HASH_MD4   CONSTANT PLS_INTEGER := 1;
HASH_MD5   CONSTANT PLS_INTEGER := 2;
HASH_SH1   CONSTANT PLS_INTEGER := 3;
HASH_SH256 CONSTANT PLS_INTEGER := 4;
HASH_SH384 CONSTANT PLS_INTEGER := 5;
HASH_SH512 CONSTANT PLS_INTEGER := 6;
CREATE OR REPLACE 
FUNCTION CRYPTO_HASH (v_input VARCHAR2, v_hash_typ BINARY_INTEGER) 
RETURN RAW DETERMINISTIC
AS
   PRAGMA UDF;
BEGIN
   RETURN dbms_crypto.hash(utl_raw.cast_to_raw(v_input), v_hash_typ);
END CRYPTO_HASH ;
/

Function created.


-- MD4
--
SQL>  select CRYPTO_HASH('Hello World !',1) from dual;

CRYPTO_HASH('HELLOWORLD!',1)
--------------------------------------------------------------------------------
BBA87E44C59377A32F26033CC0A9203B


-- MD5
-- 
SQL>  select CRYPTO_HASH('Hello World !',2) from dual;

CRYPTO_HASH('HELLOWORLD!',2)
--------------------------------------------------------------------------------
B9BE3EF4018BE19F248F6F8E63B9E006

-- SH1
SQL>  select CRYPTO_HASH('Hello World !',3) from dual;

CRYPTO_HASH('HELLOWORLD!',3)
--------------------------------------------------------------------------------
F95BE4341EDDB92DDE5F14D045978F9A4EC6C1A6

-- SH256
--

SQL> select CRYPTO_HASH('Hello World !',4) from dual;

CRYPTO_HASH('HELLOWORLD!',4)
--------------------------------------------------------------------------------
07F2BDEF34ED16E3A1BA0DBB7E47B8FD981CE0CCB3E1BFE564D82C423CBA7E47

-- SH384
--

SQL>  select CRYPTO_HASH('Hello World !',5) from dual;

CRYPTO_HASH('HELLOWORLD!',5)
--------------------------------------------------------------------------------
67E60F9CE837CAA3CA82550F0DFCBDE1B8B8A7C1605FA8D115BCC2314204FD95F5F607306622C38C
0205DE7DF6D426D8

-- SH512
--

SQL>  select CRYPTO_HASH('Hello World !',6) from dual;

CRYPTO_HASH('HELLOWORLD!',6)
--------------------------------------------------------------------------------
FEAB0028F1142D420A1425D1DD5B518225B4523AA1CFF63385ECE3411318819F5EC83042CCB79D81
F20E4A243866886CA3AE3026153ACFF8E126C0E89631502E

How do I change my Oracle user password ?

c:\>
C:\>sqlplus testuser/Password@//ractest-scan.ractest.local:1521/service

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 31 11:44:13 2018
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options


SQL> password

Changing password for TESTUSER
Old password:
New password:
Retype new password:
Password changed

SQL>

ORA-24247: network access denied by access control list (ACL)

While executing PL/SQL procedure to send mails by using UTL_SMTP, it fails with error “ORA-24247” in 11g databases.

SOLUTION

ACL needs to be created with following instructions.

DECLARE
acl_path VARCHAR2(4000);
BEGIN
  SELECT acl INTO acl_path FROM dba_network_acls
  WHERE host = '*' AND lower_port IS NULL AND upper_port IS NULL;
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'SCOTT','connect')
     IS NULL 
 THEN
     DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'SCOTT', TRUE,    'connect');
  END IF;
EXCEPTION
WHEN no_data_found THEN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('utl_mail.xml','ACL description',  'SCOTT', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('utl_mail.xml','*');
END;

COMMIT;
SQL> select ACL,PRINCIPAL,PRIVILEGE,IS_GRANT 
       from DBA_NETWORK_ACL_PRIVILEGES;

ACL                    PRINCIPAL  PRIVILEGE IS_GRANT
---------------------- ---------- --------- --------------------
/sys/acls/utl_mail.xml SCOTT      connect   true

ORA-24247 Trying To Send Email Using UTL_SMTP from 11gR1 (11.1.0.6) or higher (Doc ID 557070.1)