Mysql⽤户权限管理--从xxxcommanddeniedtouserxxx
今天遇到⼀个mysql 权限的问题，即标题所述  xxx command denied to user xxx，⼀般mysql 这种报错，基本都属于当前⽤户没有进⾏该操作的权限，需要 root ⽤户授权才能解决，从⽹上了⼀些资料，感觉这篇写得不错，分享⼀下：
可以⽤ CREATE USER 或 GRANT 创建⽤户，后者还同时分配相关权限。⽽ REVOKE 则⽤于删除⽤户权限，DROP USER 删除账户。
$ mysql -u root -p
password:
mysql> create database test; # 创建数据库
Query OK, 1 row affected (0.00 sec)
mysql> show databases; # 查看数据库是否创建成功
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mysql              |
| test              |
+--------------------+
3 rows in set (0.00 sec)
mysql> grant all on test.* to user1@'%' identified by '123456' with grant option; # 创建特权管理⽤户
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user; # 查看⽤户创建是否成功
+------------------+-----------+
| user            | host      |
+------------------+-----------+
| user1            | %        |
| root            | 127.0.0.1 |
| debian-sys-maint | localhost |
| root            | localhost |
| root            | server    |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql> show grants for user1; # 查看⽤户权限
+--------------------------------------------------------------------------------------------------+
| Grants for user1@%                                                                              |
+--------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'%' IDENTIFIED BY PASSWORD '*2CA2AD9'                        |
| GRANT ALL PRIVILEGES ON `test`.* TO 'user1'@'%' WITH GRANT OPTION                                |
+--------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
GRANT 语法:
GRANT privileges (columns)
ON what
TO user IDENTIFIED BY "password"
WITH GRANT OPTION
权限列表:
ALTER: 修改表和索引。
CREATE: 创建数据库和表。
DELETE: 删除表中已有的记录。
DROP: 抛弃(删除)数据库和表。
INDEX: 创建或抛弃索引。
INSERT: 向表中插⼊新⾏。
REFERENCE: 未⽤。
SELECT: 检索表中的记录。
UPDATE: 修改现存表记录。
FILE: 读或写服务器上的⽂件。
PROCESS: 查看服务器中执⾏的线程信息或杀死线程。
RELOAD: 重载授权表或清空⽇志、主机缓存或表缓存。
SHUTDOWN: 关闭服务器。
ALL: 所有权限，ALL PRIVILEGES同义词。
USAGE: 特殊的 "⽆权限" 权限。
drop table if exists user⽤户账户包括 "username" 和 "host" 两部分，后者表⽰该⽤户被允许从何地接⼊。user1@'%' 表⽰任何地址，默认可以省略。还可以是"user1@192.168.1.%"、"user1@%.abc" 等。数据库格式为 db@table，可以是 "test.*" 或 "*.*"，前者表⽰ test 数据库的所有表，后者表⽰所有数据库的所有表。
⼦句 "WITH GRANT OPTION" 表⽰该⽤户可以为其他⽤户分配权限。
我们⽤ root 再创建⼏个⽤户，然后由 test 数据库的管理员 user1 为他们分配权限。mysql> create user user2 identified by '123456', user3 identified by 'abcd';
Query OK, 0 rows affected (0.00 sec)
mysql> select user, host from mysql.user;
+------------------+-----------+
| user            | host      |
+------------------+-----------+
| user1            | %        |
| user2            | %        |
| user3            | %        |
| root            | 127.0.0.1 |
| debian-sys-maint | localhost |
| root            | localhost |
| root            | server    |
+------------------+-----------+
7 rows in set (0.00 sec)
好了，我们退出改⽤ user1 登录并针对 test 数据库进⾏操作。
mysql> quit # 退出
Bye
$ mysql -u user1 -p123456 test # 使⽤新⽤户登录
mysql> select database(); # 确认当前⼯作数据库
+------------+
| database() |
+------------+
| test      |
+------------+
1 row in set (0.00 sec)
mysql> select current_user(); # 确认当前⼯作账户
+----------------+
| current_user() |
+----------------+
| user1@%        |
+----------------+
1 row in set (0.00 sec)
继续，创建⼀个数据表。
mysql> create table table1 # 创建表
-
> (
->    name varchar(50),
->    age integer
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> show tables; # 查看表是否创建成功
+----------------+
| Tables_in_test |
+----------------+
| table1        |
+----------------+
1 row in set (0.00 sec)
mysql> describe table1; # 查看表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | YES  |    | NULL    |      |
| age  | int(11)    | YES  |    | NULL    |      |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into table1 values('Tom', 20); # 插⼊记录
Query OK, 1 row affected (0.00 sec)
mysql> select * from table1; # 查询记录
+------+------+
| name | age  |
+------+------+
| Tom  |  20 |
+------+------+
1 row in set (0.00 sec)
接下来我们为 user2, user3 分配权限。
mysql> grant select on test.* to user2; # 为 user2 分配 SELECT 权限。
Query OK, 0 rows affected (0.00 sec)
mysql> grant select on test.* to user3; # 为 user3 分配 SELECT 权限。
Query OK, 0 rows affected (0.00 sec)
mysql> grant insert, update on test.* to user2; # 再为 user2 增加 INSERT, UPDATE 权限。
Query OK, 0 rows affected (0.00 sec)
好了，我们退出，切换成 user2 操作看看。
$ mysql -u user2 -p123456
mysql> use test; # 切换⼯作数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select database(); # 验证当前⼯作数据库
+------------+
| database() |
+------------+
| test      |
+------------+
1 row in set (0.00 sec)
mysql> select user(); # 验证当前账户
+-----------------+
| user()          |
+-----------------+
| user2@localhost |
+-----------------+
1 row in set (0.00 sec)
mysql> show grants for user2; # 查看当前⽤户权限，显然后来添加的 INSERT, UPDATE 被添加了。
+--------------------------------------------------------------------------------------------------+
| Grants for user2@%                                                                              |
+--------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'%' IDENTIFIED BY PASSWORD '*2C9'                        | | GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'user2'@'%'                                          |
+--------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
进⾏操作测试。
mysql> insert into table1 values("Jack", 21); # INSERT 操作成功
Query OK, 1 row affected (0.00 sec)
mysql> update table1 set age=22 where name='Jack'; # UPDATE 操作成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from table1; # SELECT 操作成功
+------+------+
| name | age  |
+------+------+
| Tom  |  20 |
| Jack |  22 |
+------+------+
2 rows in set (0.00 sec)
mysql> delete from table1 where age=22; # DELETE 操作⽆权限
ERROR 1142 (42000): DELETE command denied to user 'user2'@'localhost' for table 'table1'
我们切换回 user1 管理账户，移除 user2 的 UPDATE 权限看看。
$ mysql -u user1 -p123456 test
mysql> revoke update on test.* from user2; # 移除 UPDATE 权限
Query OK, 0 rows affected (0.00 sec)
再次切换回 user2。
$ mysql -u user2 -p123456 test
mysql> show grants for user2; # UPDATE 权限被移除
+--------------------------------------------------------------------------------------------------+
| Grants for user2@%                                                                              |
+--------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'%' IDENTIFIED BY PASSWORD '*6B...2AD9'                            |
| GRANT SELECT, INSERT ON `test`.* TO 'user2'@'%'                                                  |
+--------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> update table1 set age=23 where name='Jack'; # 不在拥有 UPDATE 权限
ERROR 1142 (42000): UPDATE command denied to user 'user2'@'localhost' for table 'table1'
好了，到此我们基本完成了创建⽤户和分配权限的操作。接下来，我们回到 root 进⾏修改⽤户密码和删除⽤户操作。$ mysql -u root -p123456
mysql> set password for user3=password('abcabc'); # 修改⽤户 user3 密码
Query OK, 0 rows affected (0.00 sec)
mysql>flush privileges; # 刷新权限表(通常只在直接修改相关管理数据表后需要该操作)
Query OK, 0 rows affected (0.00 sec)
mysql> revoke all on *.* from user2; # 移除 user2 在所有数据库上的权限
Query OK, 0 rows affected (0.00 sec)
mysql> drop user user2; # 删除 user2 账户
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user; # 验证删除结果
+------------------+-----------+
| user            | host      |
+------------------+-----------+
| user1            | %        |
| user3            | %        |
| root            | 127.0.0.1 |
| debian-sys-maint | localhost |
| root            | localhost |
| root            | server    |
+------------------+-----------+
6 rows in set (0.00 sec)
⽤户 user2 ⽆法再次使⽤。
$ mysql -u user2 -p123456 test
ERROR 1045 (28000): Access denied for user 'user2'@'localhost' (using password: YES)
试试 user3。
$ mysql -u user3 -pabc test # 连接失败！哦，对了，我们修改了密码。
ERROR 1045 (28000): Access denied for user 'user3'@'localhost' (using password: YES)
$ mysql -u user3 -pabcabc test # 新密码成功
mysql> select * from table1; # SELECT 操作成功
+------+------+
| name | age  |
+------+------+
| Tom  |  20 |
| Jack |  22 |
+------+------+
2 rows in set (0.00 sec)
要修改⾃⼰的密码直接执⾏ "set password = password('new_password');" 即可。
-
------ 摘要 --------------------------------------
创建⽤户:
GRANT insert, update ON testdb.* TO user1@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
CREATE USER user2 IDENTIFIED BY 'password';
分配权限:
GRANT select ON testdb.* TO user2;
查看权限:
SHOW GRANTS FOR user1;
修改密码:
SET PASSWORD FOR user1 = PASSWORD('newpwd'); SET PASSWORD = PASSWORD('newpwd');
移除权限:
REVOKE all ON *.* FROM user1;
删除⽤户:
DROP USER user1;
数据库列表:
SHOW DATABASES;
数据表列表:
SHOW TABLES;
当前数据库:
SELECT DATABASE();
当前⽤户:
SELECT USER();
数据表结构:
DESCRIBE table1;
刷新权限:
FLUSH PRIVILEGES;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。