MySQL command

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';

Copy

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';

    Copy

  • Grand all privileges to a user account over all databases: 
    GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';

    Copy

  • 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';

    Copy

  • Grant multiple privileges to a user account over a specific database: 
    GRANT SELECT, INSERT, DELETE ON database_name.* TO database_user@'localhost';

    Copy

To find the privilege(s) granted to a specific MySQL user account use the SHOW GRANTS statement:

SHOW GRANTS FOR 'database_user'@'localhost';

Copy

+---------------------------------------------------------------------------+
| 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)

Copy

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';

Copy

To delete a MySQL user account use the DROP USER statement:

DROP USER 'user'@'localhost'