User Tools

Site Tools


mysql:grant

Creating a database user

When creating a database in mysql, I always prefer to create a user with all the permisions to this database to handling it. Moreover, if you want to use this database through internet, it is advisable to have a second user with more restricted permissions.

First, here are the instructions to create a database user:

mysql> GRANT ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, UPDATE ON  DATABASE_NAME.* TO DATABASE_USERNAME@'%';
Query OK, 0 ROWS affected (0.00 sec)

The '%' means “from all locations”: it gives permissions regardless from where the user is connecting to the database. However, if your database is in your server, and you don't have plans to connect from other locations, you can use DATABASE_USERNAME@localhost as well.

Second, the more restricted user:

mysql> GRANT DELETE, INSERT, LOCK TABLES, SELECT, UPDATE ON  DATABASE_NAME.* TO DATABASE_USERNAME@'%';
Query OK, 0 ROWS affected (0.00 sec)
mysql/grant.txt · Last modified: 2014/12/24 10:42 (external edit)