1 Reply Latest reply: Feb 7, 2013 5:46 AM by Robert Geier RSS

    AFTER ALTER ON DATABASE gives the old password value in Oracle

    989415
      I am writing a trigger to fetch the 'password' value when 'sys.users$' table gets updated.

      CREATE OR REPLACE
      TRIGGER Sys_User_Nm_Trg AFTER ALTER ON DATABASE
      WHEN (ora_dict_obj_type = 'USER')
      DECLARE

      CURSOR get_pw IS
      SELECT password
      FROM sys.user$
      WHERE name = ora_dict_obj_name;

      user_pw_ VARCHAR2(100);

      BEGIN

      IF (ora_dict_obj_name != 'SYS' AND ora_dict_obj_name != 'SYSTEM') THEN
      IF (ora_des_encrypted_password IS NOT NULL) THEN

      OPEN get_pw;
      FETCH get_pw INTO user_pw_;
      CLOSE get_pw;
      END IF;
      END IF;

      END;
      /

      But this cursor gives the old 'password' not the changed 'password' value. But if I run

      SELECT password
      FROM sys.user$
      WHERE name = ora_dict_obj_name;

      in a separate transaction it gives the correct changed value. Is there something that I should change in the trigger to fetch the updated value for the password?

      Thanks in advance!

      Edited by: 986412 on Feb 7, 2013 12:11 AM

      Edited by: 986412 on Feb 7, 2013 3:17 AM