Saturday 27 August 2011

Brief on Oracle Cursors(Implicit,Explicit and Where Current of Feature)


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 PL-SQL 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 the above trigger.

SQL> CONN TEST/TEST
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> 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;



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; ------------------ Why replace is used?? 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

2 comments:

Pradnya said...

nice one...........

kavya said...

Hi,
Thanks for sharing, it was informative. We play a small role in upskilling people providing the latest tech courses. Join us to upgradeNETSUITE FUNCTIONAL ONLINE TRAINING

Post a Comment