To create a new MySQL user account run the following command:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';
To grant access from another host change the hostname part (localhost
) with the remote machine IP. 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';
The most commonly used privileges are:
ALL PRIVILEGES
– grants all privileges to a user account.CREATE
– user account is allowed to create databases and tables.DROP
– user account is allowed to drop databases and tables.DELETE
– user account is allowed to delete rows from a specific table.INSERT
– user account is allowed to insert rows into a specific table.SELECT
– user account is allowed to read a database.UPDATE
– user account is allowed to update table rows.
Here are some examples:
- Grand all privileges to a user account over a specific database:
GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
- Grand all privileges to a user account over all databases:
GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';
- Grand all privileges to a user account over a specific table from a database:
GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';
- Grant multiple privileges to a user account over a specific database:
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:
SHOW GRANTS FOR 'database_user'@'localhost';
+---------------------------------------------------------------------------+
| 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.
For example to revoke all privileges from a user account over a specific database, use the following command:
REVOKE ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
Remove an Existing MySQL User Account
To delete a MySQL user account use the DROP USER
statement:
DROP USER 'user'@'localhost'