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: , , , , , , , , ,

Comments (7) -

8/18/2011 11:47:43 PM #

Mariam Kupatadze

Smile Great post! Smile

Mariam Kupatadze Georgia

10/1/2011 3:31:31 AM #

rubber shoes

Hola!!! This is a reallylvery cool webpage.

rubber shoes United States

11/8/2011 9:45:47 AM #

Revue de casino> Dendera casino

This will be pretty amazing site. Basically Google and google manufactured browsing related to tips straightforward upon any type of topic. Successfully keep composing and ad extra amazing websites

Revue de casino> Dendera casino United States

11/20/2011 12:22:47 AM #

cheap HTC phones

Beside that I means T-mobile, three, 02 and Vodafone UK based mobile phone shopping has many sides which impressed me so much.

cheap HTC phones United Kingdom

1/10/2012 11:08:42 PM #

Aesthetic training

Are you a nurse? Or have a dream  to be a successful nurse to serve the people? So don’t think, just come and get admitted and enjoy a reasonable price package.

Aesthetic training United Kingdom

2/5/2012 3:28:21 AM #

Hair loss treatments for women

This is my very first time i go to here. I identified numerous entertaining things with your blog page, specially its discussion. Through the tons of comments on your articles, I guess I’m not the only one having all of the enjoyment here! Hold up the good function.it is heair treatment website

Hair loss treatments for women United States

3/13/2012 3:36:52 PM #

top internet casino

Reading a blog on the internet not difficult as reading magazine. Keep your reading through the blog on the internet.

top internet casino United States

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Filter by APML

Calendar

<<  January 2018  >>
MoTuWeThFrSaSu
25262728293031
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar

TextBox