MariaDB, Mysql, Mysql Workbench and multiple users

Question

We have a new Dell computer and want to work with mysql databases on it. In the past, we used Mysql workbench, Mysql-server and unique users on each computer, but now we want to give access to multiple users.

However, on our new computer, Fedora 40 Scientific is installed as operating system and this includes MariaDB instead of MysqlDB. How can we configure our computer and software such that more than one user can work with mysql databases on it?

Answer 1

Make sure the MariaDB server is started up by running systemctl start mariadb.service. You can then access the MariaDB instance on the local server as the root user using sudo mariadb. If you need remote access, make sure you set bind-address=0.0.0.0 in a configuration file (usually in /etc/my.cnf.d/ for Fedora).

For each user who you want to give access to, create a new user using the CREATE USER SQL command. This gives them access to the database.

If you want to just isolate users to their own subsets of tables, you can use the CREATE SCHEMA command to create a schema for each user and then grant them permission to do what they want using the GRANT command.

For example, if I have users bob and alice and I want bob to have access to the bob_db schema and alice to have access to the alice_db schema, I'd execute the following SQL:

CREATE USER bob IDENTIFIED BY 'bob_secret';
CREATE SCHEMA bob_db;
GRANT ALL ON bob_db.* TO bob;

CREATE USER alice IDENTIFIED BY 'alice_secret';
CREATE SCHEMA alice_db;
GRANT ALL ON alice_db.* TO alice;