Tips for Create MySQL / MariaDB User Database

In this post I will give a few tips to create a good mysql or mariadb database user. For a developer, of course, you need a user to access the database on the server. So, as a SysAdmin, my job is to make user database access for developers. In order to maintain access to the database properly, here are some tips for create mysql / mariadb user database that I can give:

1. Create individual users for each developer – the goal is so that we, as SysAdmin or DatabaseAdministrator, can trace the query activity per-developer has access to. So if there is an error by the developer, we can trace it through the log database. Can you imagine if many developers access the database database with the same user, then one developer makes a mistake, how to trace it ?.
2. give access in combination with SSH – if your database server is installed in a cloud server, it would be nice if you also make SSH access to the developer. So that we can determine the static IP on the user database based on the IP Local cloud server that we have. So, before a developer can log into the database server, the developer must log in to CloudServer first. I think this method provides an additional layer of security for access to our database server.
3. set the database user privileges for each developer – lastly, we have to determine what access commands we will give to each database user. for example, full access is only given to senior developers, junior developers usually will not be given full access (for example, they are not given access to drop queries, delete). for the privilegs rule on mysql / mariadb, you can read it in MySQL / MariaDB User Privileges Settings.

Those are the tips I can give in making a good mysql or mariadb database user. Next, we just go to the user database creation query, here is the query:

#1 Create a user without a password and can only be accessed locally
CREATE USER 'username';

#2 Create a user with a password and can only be accessed locally
CREATE USER 'username' IDENTIFIED BY 'password';

#3 Create a user with a password and determine which IP can access
CREATE USER 'username'@'IpAddress' IDENTIFIED BY 'password';

#4 Create a user that can be accessed globally, fill in the IP with%
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

#5 Creating a specific user (ip is just an example)
CREATE USER 'username'@'192.168.10.10' IDENTIFIED BY 'password';

I usually create a user in # 5, where I specify that only certain IPs can access the database server. The IP is the Local IP belonging to my CloudServer, this is for the application of the 2nd tip that I mentioned above.

So that’s how to create mysql / mariadb user database that I usually use, I hope this information is useful, Thank you ^^.