User management is a critical aspect of database operations. Similarly, managing access and permissions is a fundamental responsibility in database administration and security.
In MySQL and MariaDB environments, you can use the database clients to administer user accounts and assign varied levels of privileges.
In this comprehensive tutorial, you will learn the three steps in creating a user and assigning appropriate permissions.
Table Of Contents
- Create a User in MySQL and MariaDB and Grant Privileges
- Remove a MariaDB User Account
- Conclusion
- FAQs
Create a User in MySQL and MariaDB and Grant Privileges
Let’s start with the steps of creating a user in MySQL and MariaDB.
The Prerequisites
Before trying out the steps of the process, make sure you have the following:
- Access to a MySQL or MariaDB server
- A user account with appropriate root privileges for the database
Note: For the purposes of this demonstration, we will work with a MariaDB instance.
Step #1: Access the MariaDB Server
Enter the following command in the terminal to access the MariaDB client shell:
# sudo mysql -u root
If the root user has a preset password, modify the command as follows so that the command prompts for it:
# sudo mysql -u root -p
Enter the password when indicated to access the MariaDB shell.
If you have a MariaDB database, proceed to the next step.
We will now create a test database to demonstrate the next steps.
Now, create a database with the following statement in the MariaDB client shell:
MariaDB> CREATE DATABASE 'yourDB';
Run the following statement to list all available databases. This is a critical step in verifying that the previous statement was successful:
MariaDB> SHOW DATABASES;
You can see the recently created database in the output:
Step #2: Create a New MariaDB User
In the MariaDB shell, execute the following statement to create a new MariaDB user:
MariaDB> CREATE USER 'user1'@localhost IDENTIFIED BY 'password1';
In this instance, we utilize the localhost hostname instead of the server’s IP address. This is a common practice when intending to SSH into your server or utilizing the local client host to connect to a local MySQL server.
After creating user1, you should verify its status by running the following SELECT statement:
MariaDB> SELECT User FROM mysql.user;
The output displays a list of all existing users.
Step# 3: Grant Privileges to a MariaDB User
Note that the newly created user lacks privileges to manage databases or access the MariaDB shell. This is a critical security consideration that protects the database from unauthorized newly created users and their malicious operations.
We will now discuss two common scenarios of granting privileges to users.
The first scenario is to grant all privileges to user1 for all databases. The statement in this context will be as follows:
MariaDB> GRANT ALL PRIVILEGES ON *.* TO 'user1'@localhost IDENTIFIED BY 'password1';
The *.* in this statement refers to granting privileges for all databases and tables. As a result, This particular statement grants access to all databases on the server. Since this could pose a significant security risk, we strongly recommend replacing the *.* with the name of the specific database or table you wish to grant access to.
In the second scenario, we will directly grant privileges specifically for the yourDB database. For this, we will use this statement:
MariaDB> GRANT ALL PRIVILEGES ON 'yourDB'.* TO 'user1'@localhost;
In both scenarios, after granting new privileges to a user, we recommend refreshing the privileges with the following statement:
MariaDB> FLUSH PRIVILEGES;
After this step, you can verify that the new user user1 has the appropriate permissions by running the following statement:
MariaDB> SHOW GRANTS FOR 'user1'@localhost;
The system will display the information on the terminal.
Remove a MariaDB User Account
Deleting a user is as important as creating new ones. In fact, this step is included in most security checklists because of the possibility of hackers accessing and abusing unused user accounts.
In MariaDB and MySQL environments, we recommend the following DROP statement to delete a user:
MariaDB> DROP USER 'user1'@localhost;
As a good practice, you should always check that the user is no longer present in the active user list. For this, run the following statement:
MariaDB> SELECT User FROM mysql.user;
The output confirms that user1 no longer has access or privileges.
Removing a user from a database is a critical aspect of ensuring the integrity and smooth performance of the environment. We recommend our tutorials on deleting a user from a MySQL database and removing a user account from a Postgres database.
Conclusion
This short tutorial covered the process of creating a MariaDB user and granting them appropriate privileges. This fundamental procedure will soon become second nature to you. With MariaDB’s diverse options for customizing privileges, you can effortlessly tailor them to suit your specific requirements.
At RedSwitches, As the leading provider of dedicated servers, we comprehend the significance of secure and efficient database management.
If you’re looking for a robust server for your Linux projects, we offer the best dedicated server pricing and deliver instant dedicated servers, usually on the same day the order gets approved. Whether you need a dedicated server, a traffic-friendly 10Gbps dedicated server, or a powerful bare metal server, we are your trusted hosting partner.
We invite you to delve deeper into the myriad options available, empowering you to administer your databases with confidence and precision.
FAQs
Q. How can I grant USER privilege during user creation in MariaDB and MySQL?
During user creation in MariaDB and MySQL, you can grant specific privileges using the USER GRANT statements. These statements allow you to assign privileges to user1 or any other user, ensuring respective owners have the necessary access levels for database management.
Q. What security measures should I consider when creating a new user with Grant Privileges in MariaDB and MySQL?
When creating a new user with Grant Privileges in MariaDB and MySQL, prioritize security by using a PASSWORD clause, avoiding plain-text passwords, and utilizing active authentication plugins. These measures strengthen authentication mechanisms and safeguard user accounts. Additionally, secure client connections with optional clauses and INSTALL PLUGIN INSTALL commands in the MySQL command-line client.
Q. What security measures should be taken to address major security issues when creating an admin user in MariaDB and MySQL?
Creating an admin user in MariaDB and MySQL poses potential security concerns, especially in high-risk network environments. To mitigate these risks, consider implementing separate networks or resource limits to restrict access to sensitive server resources. Additionally, ensure secure connections by utilizing TLS-related server system configurations and prefixing SSL options. By adopting these security measures, you can safeguard against malicious actors and maintain the integrity of your database system.
Q. How do I grant UPDATE privileges for database access in MariaDB and MySQL?
Granting UPDATE privileges for database access in MariaDB and MySQL involves using the basic syntax of the GRANT command. By specifying individual options for UPDATE privileges, such as the exact meaning of the permissions, you can tailor access levels to meet compatibility reasons or specific requirements within your LAMP stack environment. This ensures that authorized users can modify data while minimizing the risk of unauthorized access or data manipulation by malicious actors.
Q. Can I substitute user1 for secure successors in MariaDB and MySQL Secure Connections Overview?
In the Secure Connections Overview of MariaDB and MySQL, substituting user1 with secure successors is crucial for enhancing security and addressing security concerns. By ensuring that user1 adheres to TLS-related configurations and prefixing SSL options, you can establish secure connections that protect against security vulnerabilities and potential exploits. This practice strengthens the overall security posture of your database system and minimizes the risk of unauthorized access or data breaches.
Q. How can I monitor database server performance using status variables in MySQL databases?
Monitoring database server performance in MySQL databases involves leveraging status variables to gain insights into various aspects of server operation. By tracking status variables related to database usage, connection activity, and resource consumption, administrators can assess the health of the entire database environment and identify potential performance bottlenecks.
Q. What measures should be taken to secure remote connections to MySQL databases?
Securing remote connections to MySQL databases is essential for safeguarding the integrity of the entire database system. Administrators should configure access controls to restrict remote connections only to authorized users or hosts, mitigating the risk of unauthorized access by anonymous users or malicious actors. Additionally, disabling the anonymous localhost user and implementing custom user account management practices further enhances the security posture of the MySQL database environment.
Q. How can I manage database users effectively in MySQL databases?
Effective management of database users in MySQL databases entails implementing robust user account management practices. Administrators should create base users with appropriate access privileges and customize user accounts as needed to align with specific database requirements. By maintaining control over database users and monitoring user activity, administrators can ensure the security and integrity of the MySQL database environment.
Q. How do I create a new user in MariaDB using SQL statements in Linux?
Connect to the MariaDB server via MySQL command-line client. Execute “CREATE USER” followed by desired username. Optionally, specify host or password. Assign privileges with “GRANT” statement. Use “REVOKE” to revoke privileges if needed. This method enables efficient user management and access control in the MariaDB environment.
