I would like to revoke the privilege ALTER USER X IDENTIFIED BY Y. Although this privilege has not been granted explicitly, I understand that this has come along with the connect, resource privileges. Even though I have granted the individual privileges like create session, create table, create type, etc., I find that the user x is allotted the privilege of changing his own password. I agree that being the user he can alter his passwords but when there are many sessions from different terminals how would I restrict the password from getting changed from any one of the terminals.
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.