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

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

View posts in large calendar

TextBox