How to Keep Administrator Passwords Synchronized with the Data Dictionary

To synchronize the passwords for non-SYS administrative users, such as SYSDBA, SYSOPER, SYSBACKUP, SYSDG, and SYSKM users, you must first revoke and then regrant the privileges to these users as follows:

1.Find all users who have been granted the SYSDBA privilege.

SQL> show user
USER is "SYS"
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
        12 PDB3                           MOUNTED
SQL> alter session set container=PDB1;

Session altered.

SQL> SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';

no rows selected

SQL> desc v$PWFILE_USERS
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 USERNAME                                                                                                                   VARCHAR2(128)
 SYSDBA                                                                                                                     VARCHAR2(5)
 SYSOPER                                                                                                                    VARCHAR2(5)
 SYSASM                                                                                                                     VARCHAR2(5)
 SYSBACKUP                                                                                                                  VARCHAR2(5)
 SYSDG                                                                                                                      VARCHAR2(5)
 SYSKM                                                                                                                      VARCHAR2(5)
 ACCOUNT_STATUS                                                                                                             VARCHAR2(30)
 PASSWORD_PROFILE                                                                                                           VARCHAR2(128)
 LAST_LOGIN                                                                                                                 TIMESTAMP(9) WITH TIME ZONE
 LOCK_DATE                                                                                                                  DATE
 EXPIRY_DATE                                                                                                                DATE
 EXTERNAL_NAME                                                                                                              VARCHAR2(1024)
 AUTHENTICATION_TYPE                                                                                                        VARCHAR2(8)
 COMMON                                                                                                                     VARCHAR2(3)
 CON_ID                                                                                                                     NUMBER

SQL> SELECT USERNAME FROM V$PWFILE_USERS;

USERNAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS
C##MAXWELL

SQL>

2.Revoke and then re-grant the SYSDBA privilege to these users.

SQL> create user pandasthree identified by pandasthree;

User created.

SQL> grant sysdba to pandasthree;

Grant succeeded.

SQL> revoke sysdba from pandasthree;

Revoke succeeded.

SQL> 

3.Find all users who have been granted the SYSOPER privilege

SQL> SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSOPER='TRUE';

USERNAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C##MAXWELL

SQL> 

4.Revoke and regrant the SYSOPER privilege to these users

REVOKE SYSOPER FROM non-SYS-user;
GRANT SYSOPER TO non-SYS-user;

5.Find all users who have been granted the SYSBACKUP privilege.

SQL> SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSBACKUP ='TRUE';

no rows selected

SQL>

6.Revoke and regrant the SYSBACKUP privilege to these users.

REVOKE SYSBACKUP FROM non-SYS-user;
GRANT SYSBACKUP TO non-SYS-user;

7.Find all users who have been granted the SYSDG privilege.

SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDG='TRUE';

8.Revoke and regrant the SYSDG privilege to these users.

REVOKE SYSDG FROM non-SYS-user;
GRANT SYSDG TO non-SYS-user;

9.Find all users who have been granted the SYSKM privilege.

SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSKM='TRUE';

10.Revoke and regrant the SYSKM privilege to these users.

REVOKE SYSKM FROM non-SYS-user;
GRANT SYSKM TO non-SYS-user;