Trigger to track user password changes in table
I need to create a trigger which tracks user password changes into a table my_table. I have a database user who has alter user privileges and would like him to change users who belong to his group. That is, when the user changes the password for a user, the trigger fires and inserts info into my_table. In case this user tries to change another user's password, the trigger should fire again telling him 'cannot alter the user.'
I'm enclosing the trigger code I wrote:
create or replace trigger LOGG_TRAIL before alter on database when (ora_dict_obj_type = 'USER' and ora_dict_obj_name != user and sys_context('USERENV','ISDBA') = 'FALSE' ); declare by_user varchar2(30); to_user varchar2(30); vprofile varchar2(30); begin select to.PROFILE into vprofile from DBA_USERS by, DBA_USERS to where by.USERNAME = user and to.USER_NAME = ora_dict_obj_name and by.PROFILE = to.PROFILE; insert into TRAIL_TEMP (USER_ALTERED, PASSWORD_MODIFIED_BY, PASSWORD_MODIFIED_DATE, PROFILE) values (ora_dict_obj_name, user, sysdate, vprofile); exception when others then raise_application_error (-20001,'can''t alter user'); end; /This gives errors. Can you please check it? Your help will be highly appreciated.