Using Wrap Procedure ONLOGN Trigger

1. Make a script file with following code
CREATE OR REPLACE PROCEDURE V_AUTH_PROG
IS
V_PROGR_NAME VARCHAR2(200);
BEGIN
 SELECT UPPER(sys_context('USERENV', 'MODULE')) INTO V_PROGR_NAME
 FROM DUAL;
 IF V_PROGR_NAME <> 'SQLPLUS.EXE' THEN
  RAISE_APPLICATION_ERROR(-20121,'THIRD PARTY TOOL NOT ALLOWED');
 END IF;
END;

2. save the file with v_auth_prog.sql

3. Switch to Command Prompt

4. write the following command on command prompt

D:\WRAP C:\V_AUTH_PROG.SQL TO C:\V_AUTH_PROG_W.SQL

5. Now open the SQLPLUS session and connect with SYS user as Sysdba

6. Run the above scritp

    SQL>@c:\v_auth_prog_w.sql

7.Now create the following code  for onlogon Trigger

   CREATE OR REPLACE TRIGGER DB_LOGON AFTER LOGON ON DATABASE

   CALL V_AUTH_PROG

  /

do not need to add ; or other things, its available for all users who are trying to accesss database with third parties tools. You can inculde formbuilder (builder and runing) program name in procedure.

Best of Luck and remember me in your prayer.

Changes the Default Profile.

As you can seee the default behaviour for Oracle User to change the password after limiting time, here is the following solution to use Oracle user without changes a password.

sql>conn sys/oracle as sysdba

sql>select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE from  dba_users where USERNAME in ('HR');

-- Chech the PASSWORD_LIFE_TIME LIMIT in dba_profiles tables for 'DEFAULT' profile..

sql>select * from dba_profiles where PROFILE='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME' ;

-- set 'PASSWORD_LIFE_TIME' limit to 'UNLIMITED'
sql>alter profile DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;

sql>select * from dba_profiles where PROFILE='DEFAULT' and 
RESOURCE_NAME='PASSWORD_LIFE_TIME' ;

sql>select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,PROFILE from  dba_users where USERNAME in ('HR');