In Oracle 12cR2 a new security enhancement has been introduced, the “alter user” system privilege when granted to an account….this account won’t be able to change “SYS” password anymore.

Let us explore “ALTER USER” system privilege in Oracle 12cR1 first:

 

 

while in Oracle 12cR2:

i have granted SYSTEM account the “ALTER USER” system privilege:

SQL> SELECT * FROM DBA_SYS_PRIVS where grantee=’SYSTEM’;

As a SYSTEM user I can’t alter the SYS user password as shown below:

The oracle error raised is:

ORA-01031: insufficient privileges

Not only you can’t change the password, you can’t change any activity that is related to the “ALTER USER” command such as changing the “profile”, “quota” ….etc

This feature will be very helpful if you have a segregation of duties in your database infrastructure  where account management is maintained by different entity which will definitely be granted “ALTER USER” system privilege for password management and reset….in this case they wont have the power to change the “SYS” user password.

Is there any workaround to go over this security restriction ?

Yes.

Under SYS schema create the following procedure:

SQL> create procedure sys.sys_pass_change(my_pass in varchar2) as

begin

execute immediate ‘alter user sys identified by ‘||my_pass;

end;

/

SQL> grant execute on sys.sys_pass_change to system;

Then, accessing the database as “SYSTEM” user:

SQL> exec sys.sys_pass_change(‘yarn_9ns’);

in this example The SYSTEM account will execute the procedure with “definer’s” right.