SQL Commands used for HANA database user

By | 01/11/2018

SQL Commands used for HANA database user

The following sql command will show details for all sap hana users with deactivated status.

Query:

select USER_NAME, USER_DEACTIVATED from “SYS”.”USERS”;

clip_image001

After executing the command, we will get below message:

Statement ‘select USER_NAME, USER_DEACTIVATED from “SYS”.”USERS”‘

successfully executed in 247 ms 698 µs (server processing time: 0 ms 326 µs)

Fetched 13 row(s) in 0 ms 211 µs (server processing time: 0 ms 0 µs)

We will get the result as below:

clip_image002

In the above screenshot

· The value true means user is deactivated.

· The value false means user is active and we can use it.

The following sql command will show details for all sap hana users with activated status. An activated status does not mean a user is capable to connect to a database.

Query:

Select USER_NAME, VALID_FROM, VALID_UNTIL, INVALID_CONNECT_ATTEMPTS, PASSWORD_CHANGE_NEEDED, USER_DEACTIVATED, IS_RESTRICTED from “SYS”.”USERS” WHERE USER_DEACTIVATED=’FALSE’;

clip_image003

We will get below message:

Statement ‘Select USER_NAME , VALID_FROM, VALID_UNTIL, INVALID_CONNECT_ATTEMPTS, PASSWORD_CHANGE_NEEDED, …’

successfully executed in 157 ms 555 µs (server processing time: 0 ms 347 µs)

Fetched 5 row(s) in 0 ms 187 µs (server processing time: 0 ms 0 µs)

 

Result:

clip_image005

Hana Studio will give restricted details for a specific user. However, you can see rapidly whether a user is de-activated or not. For more details, it is advised to use SQL queries, which can run from the Hana SQL console.

clip_image007

The following sql command will show details for all sap hana users having “USER ADMIN” privilege

SELECT DISTINCT object_type, privilege, grantee FROM SYS.GRANTED_PRIVILEGES where privilege = ‘USER ADMIN’

We will get below message:

Statement ‘SELECT DISTINCT object_type, privilege, grantee FROM SYS.GRANTED_PRIVILEGES where privilege = ‘USER …’

successfully executed in 38 ms 542 µs (server processing time: 7 ms 637 µs)

Fetched 4 row(s) in 0 ms 71 µs (server processing time: 0 ms 0 µs)

Result:

clip_image009

The following sql command will show details for invalid connection attempts made for SYSTEM user

SELECT * from SYS.INVALID_CONNECT_ATTEMPTS WHERE user_name=’SYSTEM’

We will get command successful message

Statement ‘SELECT * from SYS.INVALID_CONNECT_ATTEMPTS WHERE user_name=’SYSTEM”

successfully executed in 18 ms 578 µs (server processing time: 0 ms 329 µs)

Fetched 0 row(s) in 0 ms 0 µs (server processing time: 0 ms 0 µs)

Result:

SQL Commands used for HANA database user

In the above screen we don’t get any result because there were no invalid connect attempts made for user SYSTEM

The procedure can be done with hdbsql or via studio. Hdbsql is a better method as error messages are more meaningful at that level than at hana studio level.

How does the SYSTEM user gets locked?

The system user can be locked if too many wrong attempts have been done. It is very easily done when the SYSTEM user is commonly used by everyone. In an emergency, you want the system used to be available as soon as possible. When the system user is not needed anymore it has to be locked for security purposes.

First pitfall: Admins have the passwords stored in the HANA studio. If it is stored within the configuration the user became immediately locked after a password change if different admins startup their studio afterward.

Second pitfall: If the password is changed but not clearly communicated (assume complex passwords!) the invalid login attempts counter is increasing fastly and leads also to a locked user.

How to unlock the HANA SYSTEM user?

It is highly recommended that you do not use the SYSTEM for day-to-day activities in production environments. Instead, use it to create database users with the minimum privilege set required for their duties (for example, user administration, system administration). Then deactivate SYSTEM.

You need a USER with ‘USER ADMIN’ privilege to unlock SYSTEM user.

To unlock the SYSTEM user that was locked after <maximum_invalid_connect_attempts> (default 5 times),

run this SQL statement: ALTER USER SYSTEM RESET CONNECT ATTEMPTS

So let us assume you find yourself in this situation:

· SYSTEM user is locked

· You have no other database users available with the USER ADMIN privilege

Below procedure requires Database Restart

1. Log on to the server on which the master index server is running as the operating system user (that is, <sid>adm user).

2. Open a command line interface.

3. Shut down the instance by executing the following command:
/usr/sap/<SID>/HDB<instance>/exe/sapcontrol -nr <instance> -function StopSystem HDB

4. In a new session, start the name server by executing the following commands:

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

/usr/sap/<SID>/HDB<instance>/exe/hdbnameserver

5. In a new session, start the compile server by executing the following commands:

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

/usr/sap/<SID>/HDB<instance>/exe/hdbcompileserver

6. In a new session, start the index server by executing the following commands:

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

/usr/sap/<SID>/HDB<instance>/exe/hdbindexserver -resetUserSystem

Note: In a scale-out system, you only need to execute the commands on the master index server.
After some start-up notifications, the prompt resetting of user SYSTEM – new password appears:

SQL Commands used for HANA database user

7. Enter a new password for the SYSTEM user.
You must enter a password that complies with the password policy configured for the system.
The password for the SYSTEM user is reset and the index server stops.

8. In the terminals in which they are running, end the name server and compile server processes by pressing CTRL+C.

9. In a new session, start the instance by executing the following command:
/usr/sap/<SID>/HDB<instance>/exe/sapcontrol -nr <instance> -function StartSystem HDB

Results

The SYSTEM user’s password is reset. You do not have to change this new password the next time you log on with this user regardless of your password policy configuration.

If you previously deactivated the SYSTEM user, it is now also reactivated. This means you will need to deactivate it again.

Deactivate SYSTEM user

Prerequisites to deactivate SYSTEM user:

You have the system privilege USER ADMIN. We can create new user and assign him USER_ADMIN

It is just one simple line command. Execute below command. The SYSTEM user can no longer be connect to the SAP HANA database.

Execute the following statement:

ALTER USER SYSTEM DEACTIVATE USER NOW

The SYSTEM user is deactivated and can no longer connect to the SAP HANA database.

SQL Commands used for HANA database user

Now we can see USER_DEACTIVATED status as TRUE

SQL Query:

select USER_NAME, USER_DEACTIVATED from “SYS”.”USERS”;

SQL Commands used for HANA database user

You can verify that this is the case in the USERS system view. For user SYSTEM, check the values in the columns USER_DEACTIVATED, DEACTIVATION_TIME, and LAST_SUCCESSFUL_CONNECT.

we have already performed this task above.

Activate SYSTEM User:

In the event of SAP support package upgrade (Enhancement, SUM usage), you may require to use SYSTEM user. You may need to re-activate the SYSTEM user for the temporary use. Execute below command.

ALTER USER SYSTEM ACTIVATE USER NOW

SQL Commands used for HANA database user

Now we can see USER_DEACTIVATED status as FALSE

SQL Query:

select USER_NAME, USER_DEACTIVATED from “SYS”.”USERS”;

SQL Commands used for HANA database user

DEACTIVATE SYSTEM user from SYSTEM user itself.

Let’s try to deactivate SYSTEM user from SYSTEM user itself

We will get below message:

Could not execute ‘ALTER USER SYSTEM DEACTIVATE USER NOW’

SAP DBTech JDBC: [258]: insufficient privilege: User is not allowed to alter this for himself

SQL Commands used for HANA database user

So it has been proved that we can’t deactivate SYSTEM user from its own ID.


Click on the links below to read the following blog posts:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.