EXPERT RESPONSE
Oracle implicitly allows any user to alter their own password. You cannot revoke that privilege because it is never granted. Therefore, you'll have to approach the problem from a different angle.
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.
|
