(三)用户授权
由于root可以对所有表进行操作,所以会产生安全问题,若在程序设计中,每个表都赋予root权限给与用户操作,可以会影响其他表的数据;所以应该对用户权限进行限制(即用户仅可以对某个表进行 增删改查读 中的某些权限)
1、用户管理
在MySQL的默认数据库 mysql
中的user
表中存储着所有的账户信息(含账户、权限等)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hw |
| mysql |
| performance_schema |
| sys |
| test_sql |
| testdb1 |
| user_system |
+--------------------+
8 rows in set (0.00 sec)
mysql> use mysql;
Database changed
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
+---------------+-------------------------------------------+-----------+
3 rows in set (0.00 sec)
-- host 可以设定用户使用哪个ip地址才能链接上数据库
通过desc user;
可以看到账号除了user和host
外还有很多权限,所以说若创建账户一一对应输入信息会显得非常麻烦
1.1、创建和删除用户
使用简单语句创建账户:
create user '用户名'@'连接者的ip地址' identified by '密码'; -- '' 可以省略,为了统一,建议使用,@之间没用空格
使用简单语句删除账户:
drop user '用户名'@'连接者的ip地址';
如:
create user 'user_joywon'@'127.0.0.1' identified by 'root'; -- 创建用户:use_joywon,密码:root,只能使用ip地址为:127.0.0.1 链接数据库
drop user 'user_joywon'@'127.0.0.1'; -- 删除用户user_joywon
-- 使用通配符赋予用户多ip链接数据库
create user 'user_joywon'@'127.0.0.%' identified by 'root'; -- 该用户可在ip为127.0.0.xxx下链接数据库
drop user 'user_joywon'@'127.0.0.%';
create user 'user_joywon'@'%' identified by 'root'; -- 该用户可在任何ip下链接数据库
drop user 'user_joywon'@'%';
-
修改用户(或ip)
rename user '用户名'@'ip地址' to '新用户名'@'ip'; -- 只修改用户名 rename user '用户名'@'ip地址' to '用户名'@'新ip'; -- 只修改用ip rename user '用户名'@'ip地址' to '新用户名'@'新ip'; -- 修改用户名和ip
mysql> select user,host from user; +---------------+-----------+ | user | host | +---------------+-----------+ | user_joywon | 127.0.0.% | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 4 rows in set (0.00 sec) mysql> rename user 'user_joywon'@'127.0.0.%' to 'new_joywon'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from user; +---------------+-----------+ | user | host | +---------------+-----------+ | new_joywon | 127.0.0.1 | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 4 rows in set (0.00 sec)
-
修改密码
set password for '用户名'@'ip地址' = password('新密码');
set password for 'new_joywon'@'127.0.0.1' = Password('joywon');
mysql> select user,authentication_string,host from user; +---------------+-------------------------------------------+-----------+ | user | authentication_string | host | +---------------+-------------------------------------------+-----------+ | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | localhost | | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost | | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost | | user_joywon | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | % | +---------------+-------------------------------------------+-----------+ 4 rows in set (0.00 sec) mysql> set password for 'user_joywon'@'%' = password('123456'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select user,authentication_string,host from user; +---------------+-------------------------------------------+-----------+ | user | authentication_string | host | +---------------+-------------------------------------------+-----------+ | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | localhost | | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost | | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost | | user_joywon | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | % | +---------------+-------------------------------------------+-----------+ 4 rows in set (0.00 sec)
1.2、用户授权
-
授权
grant 权限 on 数据库.表 to '用户名'@'ip地址';
-- 除了 grant 外的所有权限用 all privileges grant all privileges on *.* to 'user_joywon'@'%'; -- 用户user_joy拥有所有数据库的所有权限 flush privileges; -- 将数据读取到内存中,从而立即生效 grant all privileges on test_sql.* to 'joy'@'%'; -- 用户joy拥有test_sql这个数据库下的所有数据表的所有权限 flush privileges; -- 将数据读取到内存中,从而立即生效 grant all privileges on test_sql.info to 'joy'@'%'; -- 用户joy拥有test_sql这个数据库下info数据表的所有权限 flush privileges; -- 将数据读取到内存中,从而立即生效 -- 仅查询权限用 select privileges grant select privileges on test_sql.info to 'joy'@'%'; -- 用户joy拥有test_sql这个数据库下info数据表的查询权限 flush privileges; -- 将数据读取到内存中,从而立即生效 -- 查询和修改权限用 select,insert privileges grant select,insert on test_sql.* to 'joy'@'%'; -- 用户joy拥有test_sql这个数据库下所有数据表的查询和插入权限 flush privileges; -- 将数据读取到内存中,从而立即生效 -- 其他权限参考网络教程
1.2.1、查看用户授权
show grants for '用户名'@'ip地址';
mysql> show grants for 'joy'@'%';
+---------------------------------------------------+
| Grants for joy@% |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'joy'@'%' |
| GRANT ALL PRIVILEGES ON `test_sql`.* TO 'joy'@'%' |
+---------------------------------------------------+
2 rows in set (0.00 sec)
1.2.2、取消用户授权
revoke 权限 on 数据库.表 from '用户'@'ip地址';
flush privileges; -- 将数据读取到内存中,从而立即生效
revoke all privileges on test_sql.* from 'joy'@'%';
flush privileges; -- 将数据读取到内存中,从而立即生效
mysql> show grants for 'joy'@'%';
+---------------------------------------------------+
| Grants for joy@% |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'joy'@'%' |
| GRANT ALL PRIVILEGES ON `test_sql`.* TO 'joy'@'%' |
+---------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke all privileges on test_sql.* from 'joy'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'joy'@'%';
+---------------------------------+
| Grants for joy@% |
+---------------------------------+
| GRANT USAGE ON *.* TO 'joy'@'%' |
+---------------------------------+
1 row in set (0.00 sec)