What privileges do I need to change the password of sys in an Oracle Database?

Question

Our identity management tool wants to change the sys password regularly. It fails with the ORA-01031. What privileges or roles do I need to grant the identity management tool user to be able to change the sys password?

Answer 1

You'll need to be connected as a user with the SYSDBA privilege. Even "alter any user" will not be enough.

SQL> select *
  2  from   session_privs
  3  where  privilege like 'ALTER ANY%';

PRIVILEGE
----------------------------------------
ALTER ANY TABLE
ALTER ANY CLUSTER
ALTER ANY INDEX
ALTER ANY SEQUENCE
ALTER ANY ROLE
ALTER ANY PROCEDURE
ALTER ANY TRIGGER
ALTER ANY MATERIALIZED VIEW
ALTER ANY TYPE
ALTER ANY LIBRARY
ALTER ANY OPERATOR
ALTER ANY INDEXTYPE
ALTER ANY DIMENSION
ALTER ANY OUTLINE
ALTER ANY EVALUATION CONTEXT
ALTER ANY RULE SET
ALTER ANY RULE
ALTER ANY SQL PROFILE
ALTER ANY EDITION
ALTER ANY ASSEMBLY
ALTER ANY MINING MODEL
ALTER ANY CUBE DIMENSION
ALTER ANY CUBE
ALTER ANY SQL TRANSLATION PROFILE
ALTER ANY MEASURE FOLDER
ALTER ANY CUBE BUILD PROCESS
ALTER ANY ATTRIBUTE DIMENSION
ALTER ANY HIERARCHY
ALTER ANY ANALYTIC VIEW

29 rows selected.

SQL> alter user sys identified by newpass;
alter user sys identified by newpass
*
ERROR at line 1:
ORA-01031: insufficient privileges

Answer 2

Our identity management tool wants to change the sys password regularly ...

I would suggest that this is unwise.

The SYS password can be used by more than just Oracle Database.

The DataGuard Broker and Enterprise Manager are two that spring to mind. The latter will have the SYS password stored away in it at some point (given OEM's tendency to "remember" things for you, this is all but inevitable) so regularly changing the SYS password(s) is likely to cause you problems.

I also seem to recall that the OEM Agents can also make use of it (for Automated Failover?) so having this password changed out from under you on a regular basis is a lot Riskier than you might think.

And I'm assuming here that you mean the SYS passwords, plural, because, of course, every Oracle installation must have its own credentials, separate from every other installation, and you must have at least two installations (Test and Live)!

Answer 3

I am using Oracle 11.2:

SQL>select banner
  2  from v$version
  3  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0  Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

To modify a password of a user one needs 'ALTER USER' privilege.

SQL>connect / as sysdba
Connected.
SQL>create user myuser identified by myuser;

User created.

SQL>grant create session to myuser;

Grant succeeded.

SQL>grant alter user to myuser;

Grant succeeded.

SQL>connect myuser/myuser
Connected.
SQL>alter user sys identified by HalloWorld;

User altered.

SQL>

So ALTER USER is a very dangerous privilege. A user can change the SYS password, login as SYS and can modify/read/delete the database. So you should do the following

1. create a schema, e.g. SECURE, and grant ALTER USER to this schema
2. create a procedure, e.g PROCEDURE CHANGE_SYSPW(new_syspw), in this schema that modifies the SYS password
3. grant EXECUTE CHANGE_SYSPW to the user that schould be able to change the SYS password

here is an example

SQL>connect / as sysdba
Connected.
SQL>create user secure identified by secure;

User created.

SQL>grant create procedure to secure;

Grant succeeded.

SQL>grant alter user to secure;

Grant succeeded.

SQL>create or replace procedure secure.change_syspw(new_syspw varchar2)
  2  as
  3  begin
  4  EXECUTE IMMEDIATE 'alter user sys identified by "'||new_syspw||'"';
  5  end;
  6  /

Procedure created.

SQL>create user myuser identified by myuser;

User created.

SQL>grant create session to myuser;

Grant succeeded.

SQL>grant execute on secure.change_syspw to myuser;

Grant succeeded.

SQL>connect myuser/myuser
Connected.
SQL>exec secure.change_syspw('HalloWorld')

PL/SQL procedure successfully completed.

SQL>

Answer 4

This is a documented behavior change in 12.2:

RDBMS 12.2 - Users Being granted The ALTER USER System Privilege Cannot Change SYS Password (Doc ID 2263715.1)

SYMPTOMS

A user who is granted the ALTER USER system privilege is not able to change the SYS password as of Oracle RDBMS 12.2.0.1. This was possible in the earlier versions.

CAUSE

This is the expected behavior.

SOLUTION

As of Oracle RDBMS 12.2 a user with ALTER USER privilege will no longer be able to alter any of the attributes(password, profile, quota, etc.) of the SYS user.

There is also, a workaround to address this issue. We can create a procedure under sys schema that include alter user command to change sys password and grant execute on this procedure to system user.