Oracle Security Issues: "Prevent User from Altering Password of Sys/System and Own"

In today’s article I will discuss two securities issues:

A) How to prevent user with ALTER USER privileges from changing password of sys and system.

B) How to prevent the user to alter his/her password.

I'll start with discussing “How to prevent user with alter user privileges from changing password of sys and system”

If suppose one of the users in Db has “ALTER USER” privilege and he is misusing it by changing the passwords of another USER then, is it possible to stop a user from changing SYS/SYTEM password. This is an issue as the system privilege ALTER USER allows password changes of any user.

One way is to revoke “ALTER USER” grant. But it's always better to take preventive measures.

For this we have a native way. Here our PLSQL code will come in handy.

I came up with the idea of a DDL trigger that fires on the use of an ALTER USER command and then blocks the user.

First I need to create my test user.

Login as SYS user:

SQL> create user test identified by test;

User created.

SQL> grant connect to test;

Grant succeeded.

SQL> grant alter user to test; 

Grant succeeded.

SQL> alter user system identified by manager; 

User altered.

SQL> show user

USER is "TEST"

Now I will create a trigger “STOP_ALTER_PASS” in SYS schema on test user (TEST schema has “ALTER USER” privilege)

Login as SYS user:

SQL> CREATE or REPLACE TRIGGER STOP_ALTER_PASS

BEFORE ALTER on test.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;

/

Note: “ora_sysevent” is the way you can handle the “SYSTEM EVENT”. When a trigger is fired, you can retrieve certain attributes about the event that fired the trigger. Each attribute is retrieved by a function call. To make these attributes available, you must first run the CATPROC.SQL script.

Let’s check the robustness of above trigger.

SQL> CONN TEST/TEST

Connected.

SQL> 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> show user

USER is "TEST"

So now it is possible to stop a user from changing SYS/SYTEM password using our native code.

 But,

B) What about if someone asks you to prevent user to alter his/her own password?

For this activity I will use Oracle’s Profile and PASSWORD_VERIFY_FUNCTION.

PASSWORD_VERIFY_FUNCTION: There is a default password verify function under $ORACLE_HOME/rdbms/admin with filename utlpwdmg.sql. This script creates a password verify function named "verify_function"

PASSWORD_VERIFY_FUNCTION verify_function; (Usage of Function)  

I will create my own function and refer it to PASSWORD_VERIFY_FUNCTION profile as a parameter.

So going ahead with above info, first I will revoke the alter user privilege from TEST schema.

SQL> revoke alter user from test;

Revoke succeeded.

Then I create password verify funtion

CREATE OR REPLACE FUNCTION verify_passwd (

username VARCHAR2

, password VARCHAR2

, old_password VARCHAR2) RETURN boolean IS

BEGIN

raise_application_error(-20009, 'ERROR: Password cannot be changed');

END;

/

SQL> CREATE PROFILE testpwd LIMIT PASSWORD_VERIFY_FUNCTION verify_passwd; -----àHere I created a profile and passed my function to PASSWORD_VERIFY_FUNCTION as a parameter.



SQL> alter user test profile testpwd;

SQL> conn test/test

I will try to change my own password as a test user

SQL> alter user test identified by test;

alter user test identified by test

*

ERROR at line 1:

ORA-28221: REPLACE not specified

SQL> alter user test identified by test replace test; ----------------- If you don’t have alter user privilege then you have to use 'replace' keyword to change your password(In case password verify function is on).

SQL> alter user test identified by test replace test;  

alter user test identified by test replace test
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20009: ERROR: Password cannot be changed

SQL> show user

USER is "TEST"

This brings us to end of the topic .Hope you enjoy it. What I have written is purely my understanding so if you find any mistakes please correct me through your comments.