1

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.

Sagar
  • 1,115
  • 2
  • 11
  • 23
  • We lack information about how your schema looks like. What did you try already? Is your table name maybe case sensitivy in any way? You can also use "show" queries in order to discover existing tables (https://www.tutorialspoint.com/h2_database/h2_database_show.htm). What does "show tables" show? Also this question is as - you ask it - not related to Spring or Spring Boot in any way, actually. – edean Dec 02 '21 at 10:38
  • I have updated my question. Show tables list tables created by application. It doesn't list h2 internal tables. – Sagar Dec 02 '21 at 10:45
  • see https://stackoverflow.com/a/34551665/3728901 – Vy Do Dec 02 '21 at 10:48
  • 1
    I actually kinda misunderstood your question. Sorry, did not realize, it's about the databases users... Did you have a look at https://h2database.com/html/systemtables.html There is an INFORMATION_SCHEMA. So maybe it's select * from INFORMATION_SCHEMA.USERS – edean Dec 02 '21 at 10:52
  • Following the link you shared, I can see Users tables in h2 database, but it doesn't exist for me in memory h2 database. – Sagar Dec 02 '21 at 10:54
  • but it's under the schema INFORMATION_SCHEMA schema – Just as it is in mysql under the mysql schema. Did you notice that? There should also be tables shown, when querying "SHOW TABLES FROM INFORMATION_SCHEMA" – edean Dec 02 '21 at 11:19
  • 1
    Awesome. It is indeed in INFORMATION_SCHEMA schema under USERS table. Interesting mysql INFORMATION_SCHEMA schema doesn't contain USERS table. Thank you anyways :) – Sagar Dec 02 '21 at 11:53
  • Wonderful! As it works out for you, I formulated an answer for you to accept. Glad, you can work on now! – edean Dec 02 '21 at 13:44

1 Answers1

2

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.

edean
  • 498
  • 3
  • 11