MySQL is well known Database that used to store, maintain and retrieve data in certain format in which we possible need them to present it in various platforms.
Over time it evolve very well in various levels and has various features that almost cover all points that any advanced Database should have. In this post we will how we can create User in MySQL and provide it privileges to work on database and its tables.

How to create User

Like we have one database web_crawler and we to create one user who can access this database, so that we can use this user through our application.

mysql> create user 'web_crawler'@'localhost' identified by 'passw0rd';
Query OK, 0 rows affected (0.00 sec)

With above command we created one user name web_crawler that can only login through localhost (same machine) and has no access to any database except default one (information_schema).
So will this user can login from local machine, yes it can through below way.

Locally access

ssirohi@srv2:~$ mysql -u web_crawler -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

So in above command, we can see. User web_crawler is login through same machine and can only able to access information_schema. But can we allow it to access from outside machine. Sure, but how , let’s see below commands for it.

Remote Access

mysql> create user 'web_crawler'@'%' identified by 'passw0rd';
Query OK, 0 rows affected (0.00 sec)

Through above command, Now user has ability to access MySQL through any machine until network allow network for it. Basically we used percentage sign which means like asterisk/wildcard from any machine, earlier we just used localhost.

ssirohi@jarvis:~$ mysql -u web_crawler -h srv2 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

How To Grant User Permissions

We also need to provide privileges to database, so that we can able to access (read-only) on database.

mysql> GRANT select ON web_crawler.* TO 'web_crawler'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Above we just provide select permission for user, which give access as read-only through user can able to read database tables and its content. We can specific tables as well in below way.

mysql> GRANT ALL PRIVILEGES on web_crawler.urls TO 'web_crawler'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

In above command we provide ALL PRIVILEGES to urls table of same database to web_crawler user.

Note: I suggest, please run FLUSH PRIVILEGES command after every privileges command

MySQL have many type of permission, which gave different type access on database and its tables to users.

  • ALL PRIVILEGES – This would allow a MySQL user full access to database or if no database is selected, global access across the MySQL databases
  • CREATE -Allows User to create new tables or database
  • DROP – User could delete tables or databases
  • DELETE – Allows user to delete rows from tables
  • INSERT – User can insert rows into tables
  • SELECT – Can use the SELECT command to read through databases
  • UPDATE – Allow Update table rows
  • GRANT OPTION – User can grant or remove other users’ privileges

In case you like to see users grants in MySQL, you can use below command.

Show grants for user;

show grants for 'user'@'localhost'

Let’s test with same user used above in many commands.

mysql> SHOW GRANTS for 'web_crawler'@'localhost';
+--------------------------------------------------------------+
| Grants for web_crawler@localhost                             |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'web_crawler'@'localhost'              |
| GRANT SELECT ON `web_crawler`.* TO 'web_crawler'@'localhost' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

We could revoke grants from user from MySQL database and its tables.

mysql> revoke privileges on database from user

Let’s test same with web_crawler user in below way.

mysql> SHOW GRANTS for 'web_crawler'@'localhost';
+--------------------------------------------------------------+
| Grants for web_crawler@localhost                             |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'web_crawler'@'localhost'              |
| GRANT SELECT ON `web_crawler`.* TO 'web_crawler'@'localhost' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke select on web_crawler.* from 'web_crawler'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS for 'web_crawler'@'localhost';
+-------------------------------------------------+
| Grants for web_crawler@localhost                |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 'web_crawler'@'localhost' |
+-------------------------------------------------+
1 row in set (0.00 sec)