Mysql 创建新用户并分配权限

创建新用户

➜ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and   others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE USER username IDENTIFIED BY 'password'
Query OK, 0 rows affected (0.01 sec)

给用户授权

mysql> GRANT select,update,otherprivileges ON databasename.tablename TO username

示例：

mysql> GRANT all ON database_test.* TO user_test #给user_test用户所有在数据库database_test上的权限。
Query OK, 0 rows affected (0.00 sec)

成功，然后就可以登陆了。

其他问题

有时候新用户登录会出现如下错误

mysql>ERROR 1045 (28000): Access denied for user 'test@localhost'(using password: YES)

这是因为数据库中存在空用户，登录时User字段为空的匿名用户将占先，解决方案：删掉所有用户名为空的记录，或者把空用户名的用户改为其他名字.

mysql> delete from user where User is NULL
或者
mysql> update user set user='mytest' where User is NULL

如果mysql.user表里面没有可以访问的用户，也会出现 ERROR 1045 (28000): Access denied for user 'test@localhost'(using password: YES) 的错误，此时可以使用以下解决方案:

➜ service mysql stop
Shutting down MySQL...[  OK  ]
➜ mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
➜ mysql -uroot
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and   others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from user;
Empty set (0.01 sec) #发现没有用户
mysql> INSERT INTO user(host, user, password, select_priv, insert_priv, update_priv) VALUES ('localhost', 'username', PASSWORD(‘yourpassword'), 'Y', 'Y','Y');
Query OK, 1 row affected, 3 warnings (0.00 sec)

成功。