Pages

Saturday, October 01, 2011

Restricting user to change the password

If for some reason you want to restrict the user to change the password ,
Use the event trigger AFTER ALTER with the attribute function ora_des_encrypted_password 
specific for ALTER USER events:
CREATE or REPLACE TRIGGER pass_change 
    AFTER ALTER on database
          BEGIN
               IF ora_sysevent='ALTER' and ora_dict_obj_type = 'USER' and ora_des_encrypted_password is not null
               THEN
                  RAISE_APPLICATION_ERROR(-20003,  'You are not allowed to alter password user.');
               END IF;
          END;
/
Note:
In the trigger, instead of raising the error, you may want to insert a row into a custom audit table.
 

No comments:

Post a Comment