Let's see how to create a MySQL user and grant permissions to the database. All through the command line only.
First, you need to login to MySQL server to create users and assigning privileges.
Login to MySQL Server
Once you are in Linux / Ubuntu command line enter below command to access MySQL server.
shell> mysql --user=root mysqlThe above command connects you to MySQL server interface with root user. If you have assigned a password to the root account, you must also supply a --password or -p option.
shell> mysql --user=root mysql -pAfter connecting to MySQL server successfully, create a new user for MySQL.
How to Create a MySQL user?
Let's setup a new user with MySQL command.
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
You have created a new user without any permissions. The new user can't access any MySQL databases or execute queries unless you assign some permissions.
So let's add some permissions or privileges to new user.
Technically, It's called granting permissions .
How to Grant Permissions to a MySQL User?
The basic command syntax for granting permissions to MySQL user is as follows:
mysql> GRANT ALL PRIVILEGES ON dbtest. * TO 'newuser'@'localhost';Let's see the meaning of above command piece by piece.
GRANT - This is the command used to create users and grant rights to databases, tables, etc. ALL PRIVILEGES - This tells it the user will have all standard privileges.
dbtest.* - This instructions MySQL to apply these rights for the use onto the full dbtest database. You can replace the * with specific table names or store routines if you wish.
'newuser'@'hostname' - 'newuser' is the of the user account you created. Note: You must have the single quotes in there. 'hostname' tells MySQL what hosts the user can connect from. If you only want it from the same machine, use localhost
​
To provide a specific user with a permission, you can use this below syntax.
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;
List of commands used for MySQL permissions :
- ALL – Allow complete access to a specific database. If a database is not specified, then allow complete access to the entirety of MySQL.
- CREATE – Allow a user to create databases and tables.
- DELETE – Allow a user to delete rows from a table.
- DROP – Allow a user to drop databases and tables.
- EXECUTE – Allow a user to execute stored routines.
- GRANT OPTION – Allow a user to grant or remove another user’s privileges.
- INSERT – Allow a user to insert rows from a table.
- SELECT – Allow a user to select data from a database.
- SHOW DATABASES- Allow a user to view a list of all databases.
- UPDATE – Allow a user to update rows in a table.
After executing permissions changes, It's good practice to reload the privileges of MySQL server using below command.
mysql> flush privileges;
How to View MySQL User Permissions?
Finally, to see the privileges for an account, use SHOW GRANTS command.
mysql> SHOW GRANTS FOR 'newuser'@'localhost';
We hope that above instructions helped you in learning how to create a mysql user and grant permissions in Linux.
TIP: There are several third-party software programs available for creating mysql users using GUI tools such as MySQL Workbench and most popular phpmyadmin.
​