Database Logon Trigger to Block Sessions from Some Users using Toad,SQL Developer….

Here is an example of  database logon trigger for blocking specific users from accessing database through tools like  Toad or SQL Developer tools, etc.

Subscribe to get access

Read more of this content when you subscribe today.

Any database users with “ADMINISTER DATABASE TRIGGER” will bypass the database logon trigger.

SQL> grant ADMINISTER DATABASE TRIGGER to testuser;

Grant succeeded.

SQL> connect testuser
Enter password:
Connected.

Schema Logon Trigger to Block Logon Sessions from Toad, SQL Developer, Sqlplus….

In production environment, sessions from developers using tools like Toad, SQL Developer, Sqlplus, etc can be blocked by after schema logon trigger.

Subscribe to get access

Read more of this content when you subscribe today.

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