HANA 101 Series - SYSTEM User Reset Procedures

 Hello All,

Many-a-times we would have faced this issue as a BASIS consultant, where SYSTEM user is either deactivated or locked or the password needed to be reset.

To ease things out, Below are the steps that need to be followed to reset password of SYSTEM user in systemdb and tenant db respectively. Also, Activation and deactivation of HANA User commands are listed below..

Keep in mind, to run activation and deactivation commands, user should have useradmin system privileges.

!IMPORTANT! - We Highly recommend not to use SYSTEM user for daily activities in production environment, below procedures are for only rare cases of urgency of using SYSTEM user by resetting the user.

1.                   Reset the SYSTEM User Password in the System Database

            1.1                If you can log on as the SYSTEM user

                ·       Log onto the system database, and execute below ALTER USER SQL statement:

    SQL>  ALTER USER SYSTEM PASSWORD <new_password>

            1.2               If you cannot log on as the SYSTEM user

                ·       you can reset the password as the operating system administrator by starting the name server in emergency mode.

                ·       The steps to do above are described below :

    o   Log on to DB server with <sid>adm on which nameserver is running.

    o   Shutdown the instance by executing below command:

    SQL> /usr/sap/<SID>/HDB<instance>/exe/sapcontrol -nr <instance> -function StopSystem HDB

    o   In a new session, start the name server of the system database by executing the following commands:

    SQL> /usr/sap/<SID>/HDB<instance>/hdbenv.sh

   o   Reset command for SYSTEM user

    SQL> /usr/sap/<SID>/HDB<instance>/exe/hdbnameserver -resetUserSystem


       o   After the last notification appears, enter a new password for the SYSTEM user.

       o   The password for SYSTEM user of the system database is reset and nameserver stops.


New session, start the instance by executing the following command:

SQL> /usr/sap/<SID>/HDB<instance>/exe/sapcontrol -nr <instance> -function StartSystem HDB

Outcome:

§  The password of the SYSTEM user in the system database is reset.

§  The next time you log on with this user, you have to change the password in accordance with the password policy of the system database.


2.               Reset the SYSTEM User Password in a Tenant Database

               2.1                If you can log on as the SYSTEM user

                ·       Log onto the system database, and execute the ALTER USER SQL statement:

    SQL>  ALTER USER SYSTEM PASSWORD <new_password>

            2.2               If you cannot log on as the SYSTEM user

            ·       you can reset the password using the SQL statement ALTER DATABASE from the system database.

                ·       The steps to do this are described below :

    o   Login to system database using system user & Stop the Tenant Database by Executing the below command:

    SQL>  ALTER SYSTEM STOP DATABASE <database_name>

    o   Create a new password for the SYSTEM user by executing the following statement:

    SQL>  ALTER DATABASE <database_name> SYSTEM USER PASSWORD <new_password>

Outcome:

·        The password of the SYSTEM user in the Tenant database is reset.

·        The next time you log on with this user, you have to change the password in accordance with the password policy of the system database.

  1.  

      1. 3.               Activation & Deactivation of User in HANA

                       3.1                Deactivation of HANA user

            • Run below command to deactivate SYSTEM user.


        SQL>       ALTER USER <UserName> DEACTIVATE USER NOW; 

                     3.2                Deactivation of HANA user

      • Run below command to deactivate SYSTEM user.

SQL>       ALTER USER <UserName> ACTIVATE USER NOW; 





References:

HANA User Reset
Activation of HANA User






Comments

  1. Nice one!
    One observation:
    Section 2 outcome should be related to Tenant DB

    ReplyDelete
    Replies
    1. Thanks for the correction Mani, Typo error.. corrected it now :)

      Delete

Post a Comment