I am running some unit tests using in memory h2 database in spring boot application. Meanwhile interacting with in memory h2 database, I have created some users and grant them some permissions. I can see users are being created successfully (I am assuming it because command gets executed successfully without any issue and grant privileges command also gets executed successfully.). I want to list all existing users that exist in database currently and want to assert results with those users that are created by me.

I know we list all users in mySql database using database query:

select * from mysql.user

I just want to achieve same thing but with in memory h2 database. Does anyone know how can I get list of all users in h2 in memory database.

I am trying this query select * from users but system throws exceptions indicating users table doesn't exist. Is there any way to get all users in h2 database?

I have tried show command but show tables list only those tables that are created by my application. I am not creating any user table myself, I am assuming h2 in memory will have any table persisting users information just like mysql.user table. show schemas also show my database is already created in h2 memory database.

Thank you.

CodePudding user response：

As worked out in comments: you do find the USERS table under the INFORMATION_SCHEMA schema. Therefore you must query: select * from INFORMATION_SCHEMA.USERS;

The way, DBMS organize their internal configuration is quite different from DBMS to DBMS. In mysql as you stated, there is the users table in mysql.users (at least for MariaDB as far as I know) and for H2 the same is at INFORMATION_SCHEMA.USERS.