Prevent to change SYS/SYSTEM password
October 3, 2011 Leave a comment
Problem: How to Prevent a User Granted the ALTER USER Privilege From Changing SYS/SYSTEM password ?
You should write system event and here it is:
Assuming that you are preventing HR user from altering SYS/SYSTEM user.
–Connect as a sys user and run the following:
CREATE or REPLACE TRIGGER prohibit_alter_SYSTEM_SYS_pass BEFORE ALTER on HR.schema BEGIN IF SYSEVENT='ALTER' and DICTIONARY_OBJ_TYPE = 'USER' and (DICTIONARY_OBJ_NAME = 'SYSTEM' or DICTIONARY_OBJ_NAME = 'SYS') THEN RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to alter SYSTEM/SYS user.'); END IF; END;
I highlighted BEFORE keyword because, on metalink there is wrongly written AFTER TRIGGER. Because, if you write AFTER trigger this actually means that trigger will arise after action will be performed and this is wrong.
More specifically, if we write AFTER trigger, as metalink advices, HR user will actually change SYS/SYSTEM user password and then see the error message!!!!