Introduction
When setting up a MySQL database server, it is important to understand the different types of user accounts and privileges. This is because each user account can have specific permissions and roles that allow them to access and modify the data stored on the server. Different users can also have varying levels of access to the same database, allowing for greater control over who has access to sensitive information. Furthermore, understanding how to set up privileges for each individual user will help ensure that only authorized users are able to make changes or view data on the server. In this article, we will explore the different types of user accounts and privileges in MySQL so you can set up your own secure database server. Three different types of users on a MySQL database server are root user, normal user (system administrator) and anonymous user.
Learn How to Create New user in MSQL/MariaDB Server.
A. MySQL/MariaDB Root User
The database ‘root’ account is so powerful that it should not be used by most users unless they are very well-versed in their server administration skills. This type of username creates the most privilege and power in your server.
MySQL root user and roles are an important part of any database system with full privileges to manage databases, users, and other aspects of the system. MSQL root user is the user with the highest level of privileges and access to a MSQL database. It is created when the database is first initialized, and it has complete control over all other users in the system. The root user can create, modify, delete, or grant privileges to any other user in the system. This means that it has complete control over all aspects of the MSQL database and can make changes to any part of it without permission from anyone else. As such, it is important for database administrators to understand how this root user differs from other users in order to ensure that they are using their privileges responsibly.
The root user is entirely different from a standard user in the following ways:
- The root user cannot be deleted.
- Root users have no default privilege assigned to them.
- Root users can modify and change the privileges of all other users on the system without needing to know what privilege they are modifying or changing.
Example command line actions that a MySQL root user can perform as an administrator:
1. Connect to the MySQL server:
# mysql -u root -p
2. Create a new database:
# CREATE DATABASE mydatabase;
3. Create a new user:
# CREATE USER 'linodelinux'@'localhost' IDENTIFIED BY 'mypassword';
4. Grant privileges to a user:
# GRANT ALL PRIVILEGES ON myDB.* TO 'linodelinux'@'localhost';
5. Show all databases:
# SHOW DATABASES;
6. Show all users:
# SELECT User, Host FROM mysql.user;
7. Show the status of the MySQL server:
# SHOW STATUS;
8. Optimize tables:
# OPTIMIZE TABLE mytable;
9. Backup a database:
# mysqldump -u root -p mydatabase > mydatabase.sql
Examples of the administrative tasks that a MySQL root user can perform and it is important to use these commands with caution, as a root user has full access to the MySQL server and can make changes that can affect its performance and stability.
B. MySQL System administrator or Standard user or Normal user
MySQL System administrator do not provide as much power as a root account but can still be used for individual tasks. This type of user can still have full privileges for a specific database only, but does not have full authority for the entire administration of the MySQL database.
Example command line actions that a MySQL normal user can perform as an administrator with limited access to a specific database:
1. Connect to the MySQL server as a normal user:
# mysql -u linode -p
2. Show the tables in a specific database:
USE mydatabase; SHOW TABLES;
3. Describe a table structure:
DESCRIBE mytable;
4. Select data from a table:
SELECT * FROM mytable;
5. Insert data into a table:
INSERT INTO mytable (mycolumn_1, mycolumn_2, ...) VALUES (value_1, value_2, ...);
6. Update data in a table:
UPDATE mytable SET mycolumn_1 = value_1 WHERE condition;
7. Delete data from a table:
DELETE FROM mytable WHERE condition;
Example of asks that a MySQL normal user with limited access can perform via the command line. The specific privileges that a normal user has will depend on the grants given to them by the root user or another administrator.
C. Anonymous Users
Anonymous user accounts do not require a username or password in order to connect to the MySQL database server. This means an anonymous user is accessible without any authentication or authorization required by setting up an anonymous account with no privileges or restrictions set. It should be noted that anonymous users have no real account in the sense that they do not need to log in or out of their account. It is also possible to set up an anonymous account with a password so it can be used to authenticate connections from other servers or clients.Any user whose name is not specified by adding a username when logging into MySQL is automatically assigned as an anonymous user, meaning no passwords are required for them any more than for anyone else who has access to the server.
Example command line actions that a MySQL anonymous user can perform:
1. Connect to the MySQL server as an anonymous user:
# mysql -u anonymous
2. Show the available databases:
# SHOW DATABASES;
3. Use a specific database:
# USE mydatabase;
4. Show the available tables in a specific database:
# SHOW TABLES;
5. Select data from a table:
# SELECT * FROM mytable;
It’s important to note that the specific privileges that an anonymous user has will depend on the configuration of the MySQL server. By default, anonymous users are not granted any privileges and will not be able to access or modify any data.
Conclusion
With the increasing use of technology in businesses, it is essential to ensure that user accounts and access privileges are set up securely to protect your data. Data security is a key priority for any business, and setting up secure user accounts and access privileges can help prevent unauthorized access.