OCP Blog
Super DBA
How Prevent user with alter user privileges from changing password of sys and system users.
by Vazha Mantua Wednesday, August 17, 2011 10:26 AM

Good Day My friends,

 

Today we will discuss about one small security issue for users which have an alter user privileges. Our Task is avoid from these users privileges changing password of system users.

In metalink we found article about this issue. Note id is 271077.1 , which tell us create system trigger for avoiding this case.

 

SQL> conn  / as sysdba
Connected.

SQL> CREATE or REPLACE TRIGGER prohibit_alter_SYSTEM_SYS_pass
AFTER ALTER on SCOTT.schema
BEGIN
IF ora_sysevent='ALTER' and ora_dict_obj_type = 'USER' and
(ora_dict_obj_name = 'SYSTEM' or ora_dict_obj_name = 'SYS')
THEN
RAISE_APPLICATION_ERROR(-20003,
'You are not allowed to alter SYSTEM/SYS user.');
END IF;
END;
/

Trigger created.

 

Now we can see a result:

SQL> conn scott/tiger
Connected.
SQL>alter user system identified by manager;
alter user system identified by manager
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20003: You are not allowed to alter SYSTEM/SYS user.
ORA-06512: at line 5
SQL> alter user sys identified by manager;
alter user sys identified by manager
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20003: You are not allowed to alter SYSTEM/SYS user.
ORA-06512: at line 5
SQL> alter user dbsnmp identified by dbsnmp;
User altered.

 

 

But There are one mistake , which found my student Mariam Kupatadze.

 

Password of user system changed. trigger works after alter user user, correct version is before alter for prevent changing password!

 

Finally we give you correct version of trigger:

SQL> CREATE or REPLACE TRIGGER prohibit_alter_SYSTEM_SYS_pass 
BEFORE ALTER on SCOTT.schema
BEGIN
IF ora_sysevent='ALTER' and ora_dict_obj_type = 'USER' and
(ora_dict_obj_name = 'SYSTEM' or ora_dict_obj_name = 'SYS')
THEN
RAISE_APPLICATION_ERROR(-20003,
'You are not allowed to alter SYSTEM/SYS user.');
END IF;
END;
/

Trigger created.

 

 

 

Tags: , , , , , , , , ,

Filter by APML

Calendar

<<  November 2017  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar

TextBox