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:
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:
o In a new session, start the name server of the system database by executing the following commands:
o Reset command for SYSTEM user
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:
§ 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:
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:
o Create a new password for the SYSTEM user by executing the following statement:
· 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.
3. Activation & Deactivation of User in HANA
3.1 Deactivation of HANA user
SQL> ALTER USER <UserName> DEACTIVATE USER NOW;
- Run below command to deactivate SYSTEM user.
3.2 Deactivation of HANA user