mysql Adding users and assigning access rights

mysql> create user 'user1'@'locahost' identified by 'password';

mysql> select user,host from mysql.user;

+------------------+-----------+

| user             | host      |

+------------------+-----------+

| root             | 127.0.0.1 |

| root             | ::1       |

| user1            | locahost  |

| debian-sys-maint | localhost |

| root             | localhost |

| root             | ubuntu    |

+------------------+-----------+

6 rows in set (0.00 sec)

mysql> grant all privileges on *.* to 'user1'@'localhost' with grant option;

Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'user1'@'localhost';

+----------------------------------------------------------------------+

| Grants for user1@localhost                                           |

+----------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' WITH GRANT OPTION |

+----------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> update mysql.user set password=PASSWORD('qweqwe_1');

Query OK, 6 rows affected (0.01 sec)

Rows matched: 8  Changed: 6  Warnings: 0

ysql> select version();

+-------------------------+

| version()               |

+-------------------------+

| 5.5.53-0ubuntu0.14.04.1 |

+-------------------------+

1 row in set (0.01 sec)

mysql> show variables;

SHOW VARIABLES LIKE "%version%";

mysql> select @@log;

+-------+

| @@log |

+-------+

|     0 |

+-------+

1 row in set (0.00 sec)

To limit privileges to certain tasks, mention specific privileges in a grant statement:

mysql> grant select, insert, update, delete, create
    -> on dbname.* to ‘dbuser’@’localhost’;

The preceding statement will grant select, insert, update, delete, and create privileges on any table under the dbname database.

There’s more…

Similar to preceding add user example, other user management tasks can be performed with SQL queries as follows:

REMOVING USER ACCOUNTS

You can easily remove a user account with the drop statement, as follows:

mysql> drop user ‘dbuser’@’localhost’;

SETTING RESOURCE LIMITS

MySQL allows setting limits on individual accounts:

mysql> grant all on dbname.* to ‘dbuser’@’localhost’
    ->     with max_queries_per_hour 20
    ->          max_updates_per_hour 10
    ->          max_connections_per_hour 5
    ->          max_user_connections 2;

See also

• MySQL user account management at https://dev.mysql.com/doc/refman/5.6/en/user-account-management.html