Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: