Can't create user in MySQL 5.6
I want to create a user, which will be an admin in my classicmodels database. I want that my admin user can create other user accounts and assign them only to classicmodels database. I have already done the following steps:
GRANT USAGE ON *.*
TO 'admin'@'%' IDENTIFIED BY PASSWORD
GRANT ALL PRIVILEGES ON classicmodels.* TO 'admin'@'%' WITH GRANT OPTION
Now logged in as admin:
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
I don't see, where I'm doing mistake. I've given all privileges to admin in step 3, when I was logged in as root. Thanks in advance.
You have given all privileges for a database (schema) in specific and not globally.
Run the following queries and check the results.
SELECT * FROM `mysql`.`db` WHERE `User` = 'admin' AND `Host` = '%';
SELECT * FROM `mysql`.`user` WHERE `User` = 'admin' AND `Host` = '%';
13.7.1.2 CREATE USER Syntax
...
... To use this statement, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. ...
...
UPDATE
13.7.1.4 GRANT Syntax
...
The CREATE TABLESPACE, CREATE USER, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privileges are administrative and can only be granted globally.
...
You need to do the following:
GRANT CREATE USER ON *.* TO 'admin'@'%';
FLUSH PRIVILEGES;
mysql> SHOW GRANTS FOR 'admin'@'%';
+------------------------------------------------------------------------------------------------------------+
| Grants for admin@% |
+------------------------------------------------------------------------------------------------------------+
| GRANT CREATE USER ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*AA1420F182E88B9E5F874F6FBE7459291E8F4601' |
| GRANT ALL PRIVILEGES ON `classicmodels`.* TO 'admin'@'%' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0,00 sec)
链接地址: http://www.djcxy.com/p/69278.html
上一篇: MySQL授予权限访问拒绝
下一篇: 无法在MySQL 5.6中创建用户