oracledatabase-administrationoracle19c

How to change the password for all Oracle database users?

Oracle database 19c is used for development and testing purposes only.

All users (including sys, system and all others created by default during installation) have the same password.

For example, password123

How do I change the password, for example to password456 for all users?

So that I don't have to change the password for each user separately.

Please advise some steps or script.

Thank you in advance.

Solution

  • One option is to use a cursor FOR loop.

    This is user scott whose password is tiger:

    SQL> connect scott/tiger@orcl
Connected.

    Connect as a privileged user (the one who is capable of modifying other users' passwords):

    SQL> connect mydba/mypwd@orcl
Connected.

    Run such a script; compose alter user statement, display it (so that you'd know all users whose passwords have been changed) and then change the password. You'd modify line #6; I'm restricting it only to scott as I don't want to change any other password in my database. You'd either name all users whose passwords you want to change, or - if you want to change all passwords - omit line #6:

    SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     l_str  VARCHAR2 (200);
  3  BEGIN
  4     FOR cur_R IN (SELECT username
  5                     FROM all_users
  6                    WHERE username = 'SCOTT')
  7     LOOP
  8        l_str := 'alter user ' || cur_r.username || ' identified by password456';
  9        DBMS_OUTPUT.put_line (l_str);
 10
 11        EXECUTE IMMEDIATE l_str;
 12     END LOOP;
 13  END;
 14  /
alter user SCOTT identified by password456

PL/SQL procedure successfully completed.

    Can scott connect using its old password? No:

    SQL> connect scott/tiger@orcl
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

    Using its new password? Yes:

    SQL> connect scott/password456@orcl
Connected.

SQL>