MySQL is the most popular open-source relational database management system. MySQL server allows us to create numerous user accounts and grant appropriate privileges so that the users can access and manage databases.
This tutorial describes how to create MySQL user accounts and grant privileges.
Before you Begin
We assume you already have MySQL or MariaDB server installed on your system.
All commands are executed inside the MySQL shell as root or administrative users. The minimum privileges required to create user accounts and define their privileges are CREATE USER and GRANT.
To access the MySQL shell type the following command and enter your MySQL root user password when prompted:
mysql -u root -pIf you have MySQL version 5.7 or later that uses the auth_socket plugin login as root by typing:
sudo mysqlCreate a new MySQL User Account
A user account in MySQL consists of user name and hostname.
To create a new MySQL user account, run the following command:
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';
KEY TAKEAWAYS
- A credit score is a number that depicts a consumer’s creditworthiness. FICO scores range from 300 to 850.
- Factors used to calculate your credit score include repayment history, types of loans, length of credit history, debt utilization, and whether you’ve applied for new accounts.
- A credit score plays a key role in a lender’s decision to offer credit and for what terms.
- The three main U.S. credit bureaus (Equifax, Experian, and TransUnion) may each calculate your FICO score differently.
In the example above, the hostname part is set to localhost, which means that the user can connect to the MySQL server only from the localhost (i.e. from the system where MySQL Server runs).
Change the hostname part with the remote machine IP to grant access from another host. For example, to grant access from a machine with IP 10.8.0.5 you would run:
CREATE USER 'newuser'@'10.8.0.5' IDENTIFIED BY 'user_password';To create a user that can connect from any host, use the '%' wildcard as a host part:
CREATE USER 'newuser'@'%' IDENTIFIED BY 'user_password';Grant Privileges to a MySQL User Account
Multiple types of privileges can be granted to a user account. You can find a complete list of privileges supported by MySQL here.
The most commonly used privileges are:
ALL PRIVILEGES– Grants all privileges to a user account.CREATE– The user account is allowed to create databases and tables.DROP– The user account is allowed to drop databases and tables.DELETE– The user account is allowed to delete rows from a specific table.INSERT– The user account is allowed to insert rows into a specific table.SELECT– The user account is allowed to read a database.UPDATE– The user account is allowed to update table rows.
To grant specific privileges to a user account, use the following syntax:
GRANT permission1, permission2 ON database_name.table_name TO 'database_user'@'localhost';Here are some examples:
Grant all privileges to a user account over a specific database:
mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
Grant all privileges to a user account on all databases:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';
Grant all privileges to a user account over a specific table from a database:
mysql> GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';
Grant multiple privileges to a user account over a specific database:
mysql> GRANT SELECT, INSERT, DELETE ON database_name.* TO database_user@'localhost';
Display MySQL User Account Privileges
To find the privilege(s) granted to a specific MySQL user account, use the SHOW GRANTS statement:
mysql> SHOW GRANTS FOR 'database_user'@'localhost';The output will look something like the below:
+---------------------------------------------------------------------------+
| Grants for database_user@localhost |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'database_user'@'localhost' |
| GRANT ALL PRIVILEGES ON `database_name`.* TO 'database_user'@'localhost' |
+---------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Revoke Privileges from a MySQL User Account
The syntax to revoke one or more privileges from a user account is almost identical as when granting privileges.
To revoke all privileges from a user account over a specific database, run the following command:
mysql> REVOKE ALL PRIVILEGES ON database_name.* FROM 'database_user'@'localhost';Remove an Existing MySQL User Account
To delete a MySQL user account use the DROP USER statement:
mysql> DROP USER 'user'@'localhost'The command above will remove the user account and its privileges.
Conclusion
This tutorial covers only the basics, but it should be a good start for anyone who wants to learn how to create new MySQL user accounts and grant privileges.
If you have any questions or feedback, feel free to leave a comment.
