In Oracle8i and above, you can create triggers on DDL statements (like ALTER) and can use that feature to create a trigger that will disallow users from modifying their own user account. Here's a sample that works in 10g and should work in other releases as well:
CREATE OR REPLACE TRIGGER no_alter_user BEFORE ALTER ON DATABASE BEGIN --we do not need to check the ora_sysevent since the trigger is only -- firing for ALTER statements IF ( ora_dict_obj_type = 'USER' AND ora_dict_obj_name = ora_login_user AND ora_login_user != 'SYS' AND ora_login_user != 'SYSTEM' ) THEN DBMS_OUTPUT.PUT_LINE('Altering the user: '||ora_dict_obj_name); DBMS_OUTPUT.PUT_LINE('I am logged in as: '||ora_login_user); RAISE_APPLICATION_ERROR(-20000,'You are not allowed to alter yourself!'); END IF; END NO_ALTER_USER; /
Be careful to escape the DBA users since they may want to modify their own passwords. This code is purely a starting point and you assume any responsibility associated with deploying it. You may want to check out MetaLink NOTE 199455.1 for some more ideas on how you could use a similar trigger to create a custom audit trail.
This was first published in April 2004