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' );
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,
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.
If I had to perform this action, I would take a slightly different route. Each user is assigned a profile and that profile can include a password verification function. There is nothing stopping you from coding your rules in that password verification function. This function can insert rows of data into another table and raise exceptions.
Dig Deeper on Oracle database design and architecture