Prevent to change SYS/SYSTEM password

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!!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: