Q

Restricting password changes

I would like to revoke the privilege ALTER USER X IDENTIFIED BY Y. Although this privilege has not been granted

explicitly, I understand that this has come along with the connect, resource privileges. Even though I have granted the individual privileges like create session, create table, create type, etc., I find that the user x is allotted the privilege of changing his own password. I agree that being the user he can alter his passwords but when there are many sessions from different terminals how would I restrict the password from getting changed from any one of the terminals. Oracle implicitly allows any user to alter their own password. You cannot revoke that privilege because it is never granted. Therefore, you'll have to approach the problem from a different angle.

In Oracle8i and above, you can create triggers on DDL statements (like ALTER) and can use that feature to create a trigger that will disallow users from modifying their own user account. Here's a sample that works in 10g and should work in other releases as well:

CREATE OR REPLACE TRIGGER no_alter_user
BEFORE ALTER
ON DATABASE
BEGIN
  --we do not need to check the ora_sysevent since the trigger is only
  --  firing for ALTER statements
  IF (    ora_dict_obj_type =  'USER'
      AND ora_dict_obj_name =  ora_login_user
      AND ora_login_user    != 'SYS'
      AND ora_login_user    != 'SYSTEM'
     ) THEN
    DBMS_OUTPUT.PUT_LINE('Altering the user: '||ora_dict_obj_name);
    DBMS_OUTPUT.PUT_LINE('I am logged in as: '||ora_login_user);
    RAISE_APPLICATION_ERROR(-20000,'You are not allowed to alter yourself!');
  END IF;
END NO_ALTER_USER;
/

Be careful to escape the DBA users since they may want to modify their own passwords. This code is purely a starting point and you assume any responsibility associated with deploying it. You may want to check out MetaLink NOTE 199455.1 for some more ideas on how you could use a similar trigger to create a custom audit trail.

This was first published in April 2004

Dig deeper on Oracle database security

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close