Grant Privileges To User in MySQL

Overview

If you would like to add a user to MySQL and give them permissions to view on or multiple databases, then continue reading. It is a fairly simple process and can be very powerful in keeping your database secure as well as giving people the access to the database they need.

Login To MySQL Server

In order to run these sql commands (sql queries), login to your sql database so we can take a look at the format of the MySQL command.

mysql -u <user> -p

I recommend you don’t enter your password above as people viewing your bash history would see your password. Once you hit enter you will be prompted to enter your password.

Grant Privileges To User in MySQL

Now the format of the grant appears as follows

GRANT <privilege-type> ON <database> TO <user>@`<ip-or-domain>` IDENTIFIED BY '<new-password>'

Here is the breakdown of what each of the variables means.

  • privilege-type: type of privileges to give such as INSERT, DELETE, ALTER, DROP, ALL PRIVILEGES, etc.
  • database: the database/table combination your are giving the user access to (see below for examples).
  • user: username of your choice that the user will use to login to view their databases.
  • ip-or-domain: the location where the user is access the database FROM.
  • new-password: password of your choice that the user will use to login to view their databases.

 

MySQL GRANT Privileges Examples

Example 1: Gives the user `poweruser` full access to all databases and tables. The *.* means all databases and all tables.

GRANT ALL PRIVILEGES ON *.* TO poweruser@`1.2.3.4` IDENTIFIED BY 'some-pass';

 

Example 2: Create a user with INSERT, DELETE and UPDATE permissions to all tables under the exampledatabase database. They can also only login if they come from the location somedomain.example.com.

GRANT INSERT,DELETE,UPDATE ON exampledatabase.* TO `editoruser`@`somedomain.example.com` IDENTIFIED BY 'any-pass';

 

Example 3: Only give delete to a user and they can only access the reports database and the temptable table. The % for their location means they can access it from anywhere and not just a single ip or domain.

GRANT DELETE ON reports.temptable TO `cleanupuser`@`%` IDENTIFIED BY 'their-pass';

 

Example 4: Gives the root user full access but only from the same location as the database. So anyone outside of the server won’t be able to login. Useful if your website is running on the same server as the database and very secure.

GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY 'difficult-pass';