Set MySQL user permissions using Linux/Ubuntu command-line (CLI) statement

mysql is a program for connecting to a MySQL server. For the purposes of this tutorial, I'll assume you are connecting to a MySQL server that's running on the same machine as the 'mysql' program you will be running (i.e. "localhost").

SQL statements can be used outside the MySQL command-line program. If just need want the relevant SQL statement(s), you can disregard steps 1 and 2 and skip to step 3.

1. Connect to mysql server

Syntax

mysql -u [username] -p
  • -u -- This flag must preceed the username. Without this flag, mysql will assume that the MySQL username makes the OS username of the logged in user running the program.
  • [username] -- MySQL username.*
  • -p -- Prompts user to provide password. Without this flag, mysql will search for a setting file that contains relevant password.

  • You may have a MySQL username that's identical to your Linux/OS username and they may even share the same password. But it's imporant to understand that these are different user accounts--much like using the same username and password on multiple web sites. For example, Linux has a default user called "root" and MySQL also has a default user called "root."

Example

mysql -u root -p

2. When prompted, enter your password

Remember: You are entering the username for the MySQL username you specified.

3. Enter "GRANT" statement

Syntax

GRANT [privilege]([columns]) ON [database].[table] TO '[user]'@'[server]';
  • [columns] -- (Optional) Grant permission for a particular set of table columns (separated by commas).
  • [privilege] -- Specifc permission that will be granted to the user(s)--select, update, insert, delete, all. You can enter multiple, comma seperated, privileges.
  • [database] -- MySQL database name. Use wildcard ("*") to represent "all tables."
  • [table] -- MySQL database name. Use wildcard ("*") to represent "all tables."
  • [user] -- The MySQL username of the user that is being granted permission.
  • [server] -- MySQL server address (IP address, domain name or "localhost").*

Example

GRANT ALL ON *.* TO 'wp_user'@'localhost';

4. Flush privileges

Update database to reflect changes.

FLUSH PRIVILEGES;