MySQL 默认管理员用户为 root, 但是这个用户权限太大, 一般只在管理数据库时候才用. 为了 MySQL 的安全性, 在实际应用中用户权限要最小化, 应该只分配使用到的权限.
1 MySQL 授权表
MySQL 服务器通过 MySQL 权限表来控制用户对数据库的访问, MySQL 权限表存放在 MySQL 数据库里, 由 mysql_install_db 脚本初始化. 这些 MySQL 权限表分别 user,db,table_priv,columns_priv 和 host. 下面分别介绍一下这些表的结构和内容:
user 权限表: 记录允许连接到服务器的用户帐号信息, 里面的权限是全局级的.
db 权限表: 记录各个帐号在各个数据库上的操作权限.
table_priv 权限表: 记录数据表级的操作权限. 在这里指定的一个权限适用于一个表的所有列.
columns_priv 权限表: 记录数据列级的操作权限; 这里指定的权限适用于一个表的特定列;
host 权限表: 配合 db 权限表对给定主机上数据库级操作权限作更细致的控制. 这个权限表不受 GRANT 和 REVOKE 语句的影响.
2 用户及密码管理
2.1 创建用户
命令:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
新用户创建完成暂时无法登陆, 因为我们还没有为这个用户分配相应权限,
说明:
username: 你将创建的用户名
host: 指定该用户在哪些主机上可以登陆, 本地用户用 localhost, 如果想让该用户可以从任意远程主机登陆, 可以使用通配符 %
password: 该用户的登陆密码, 密码可以为空, 如果为空则该用户可以不需要密码登陆服务器
不管是授权, 还是撤销授权, 都要指定响应的 host(即 @ 符号后面的内容), 因为以上及格命令实际上都是在操作 MySQL 数据库中的 user 表;
例:
- CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
- CREATE USER 'pig'@'192.168.1.101' IDENDIFIED BY '123456';
- CREATE USER 'pig'@'192.168.1.%' IDENTIFIED BY '123456';
- CREATE USER 'pig'@'%' IDENTIFIED BY '';
- CREATE USER 'pig'@'%';
2.2 密码设置
设置 root 密码:
设置密码: password1
mysqladmin -uroot -password password1
修改 root 密码:
由 password1 修改为 password2
mysqladmin -uroot -p password1 password password2
管理员设置其它用户密码
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
设置当前登陆用户 (自己) 密码:
SET PASSWORD = PASSWORD("newpassword");
2.3 查看当前用户
SELECT USER();
2.4 删除用户
DROP USER 'username'@'host';
2.5 查看 MySQL 所有用户及对应的 host:
SELECT User,Host FROM MySQL.user;
2.6 数据表结构:
DESCRIBE table1;
3 权限
3.1 授权 GRANT
GRANT 语法:
- GRANT privileges (columns)
- ON what
- TO user IDENTIFIED BY "password"
- WITH GRANT OPTION
权限列表:
CREATE: 创建数据库和表.
INSERT: 插入; 向表中插入新行.
DELETE: 删除表中已有的记录.
DROP: 抛弃 (删除) 数据库和表.
ALTER: 修改表和索引.
INDEX: 创建或抛弃索引.
REFERENCE: 未用.
SELECT: 检索表中的记录.
UPDATE: 修改现存表记录.
FILE: 读或写服务器上的文件.
PROCESS: 查看服务器中执行的线程信息或杀死线程.
RELOAD: 重载授权表或清空日志, 主机缓存或表缓存.
SHUTDOWN: 关闭服务器.
ALL: 所有权限, ALL PRIVILEGES 同义词.
USAGE: 特殊的 "无权限" 权限.
用户账户包括 "username" 和 "host" 两部分, 后者表示该用户被允许从何地接入. tom@'%' 表示任何地址, 默认可以省略. 还可以是 "tom@192.168.1.%","tom@%.abc.com" 等. 数据库格式为 db@table, 可以是 "test." 或 ".*", 前者表示 test 数据库的所有表, 后者表示所有数据库的所有表.
子句 WITH GRANT OPTION 表示该用户可以为其他用户分配权限.
grant, revoke 用户权限后, 该用户只有重新连接 MySQL 数据库, 权限才能生效.
只分配权限: 和 CREATE USER 一起使用;
GRANT all PRIVILEGES ON databasename.tablename TO 'username'@'host';
说明:
all: 要授予用户的操作权限, 如 SELECT,INSERT,UPDATE 等, 如果要授予所的权限则使用 ALL;
databasename: 数据库名;
tablename: 表名, 如果要授予该用户对所有数据库和表的相应操作权限则可用表示, 如.*
username: 用户;
host:IP 范围;
新建账户并分配权限: grant
GRANT all PRIVILEGES ON databasename.tablename TO 'username'@'host' IDENTIFIED BY 'password';
说明:
其它同上;
password: 密码;
例子:
- GRANT ALL ON *.* TO 'pig'@'%';
- GRANT ALL ON maindataplus.* TO 'pig'@'%';
- GRANT SELECT,INSERT ON test.user TO 'pig'@'%';
新建账户并授予用户管理其它用户的权限
GRANT PRIVILEGES ON databasename.tablename TO 'username'@'localhost' WITH GRANT OPTION;
说明:
其它同上;
WITH GRANT OPTION: 授予用户管理其它用户的权限;
WITH GRANT OPTION 一般配置的用户的 host 配 localhost, 即只能本地登陆;
常用操作
grant 查询, 插入, 更新, 删除 MySQL 数据表结构权限.
grant select,insert,update,delete on databasename.tablename to 'username'@'host';
grant 创建, 卸载 MySQL 数据表的权限.
grant create,drop on databasename.tablename to 'username'@'host';
grant 作用在表中的列上:
grant select(id, se, rank) on databasename.tablename to 'username'@'host';
grant 操作 MySQL 外键权限.
grant references on databasename.tablename to 'username'@'host';
grant 操作 MySQL 临时表权限.
grant create temporary tables on databasename.tablename to 'username'@'host';
grant 操作 MySQL 索引权限.
grant index on databasename.tablename to 'username'@'host';
grant 操作 MySQL 视图, 查看视图源代码 权限.
- grant create view on databasename.tablename to 'username'@'host';
- grant show view on databasename.tablename to 'username'@'host';
grant 操作 MySQL 存储过程, 函数 权限.
- grant create routine on databasename.tablename to 'username'@'host';
- grant alter routine on databasename.tablename to 'username'@'host';
- grant execute on databasename.tablename to 'username'@'host';
- grant execute on procedure testdb.pr_add to 'dba'@'localhost';
- grant execute on function testdb.fn_add to 'dba'@'localhost';
grant 普通 DBA 管理某个 MySQL 数据库的权限.
grant all privileges on databasename.* to dba@'localhost';
grant 高级 DBA 管理 MySQL 中所有数据库的权限.
grant all on *.* to dba@'localhost'
关键字 privileges 如果省略的话, 后面需要刷新权限.
3.2 刷新权限
修改完权限以后 一定要刷新服务, 或者重启服务:
FLUSH PRIVILEGES;
3.3 查看用户权限
SHOW GRANTS [FOR 'username'@'host'];
默认查看自己的权限, 加 for 查看指定用户的权限;
3.4 撤销权限: revoke
如果此时发现刚刚给的权限太大了, 如果我们只是想授予它在某个数据库上的权限, 那么需要切换到 root 用户撤销刚才的权限, 重新授权:
REVOKE ALL PRIVILEGES ON databasename.tablename FROM 'username'@'host';
说明:
privilege, databasename, tablename: 同授权部分
注意:
假如你在给用户'pig'@'%'授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO 'pig'@'%', 则在使用 REVOKE SELECT ON . FROM 'pig'@'%'; 命令并不能撤销该用户对 test 数据库中 user 表的 SELECT 操作.
相反, 如果授权使用的是 GRANT SELECT ON . TO 'pig'@'%'; 则 REVOKE SELECT ON test.user FROM 'pig'@'%'; 命令也不能撤销该用户对 test 数据库中 user 表的 Select 权限.
具体信息可以用命令 SHOW GRANTS FOR 'pig'@'%'; 查看.
4 摘要
4.1 grant 和 revoke 可以在几个层次上控制访问权限
整个服务器, 使用 grant ALL 和 revoke ALL
整个数据库, 使用 on database.*
特定表, 使用 on database.table
特定的列
特定的存储过程
4.2 user 表中 host 列的值的意义
%: 匹配所有主机
localhost: localhost 不会被解析成 IP 地址, 直接通过 UNIXsocket 连接
127.0.0.1: 会通过 TCP/IP 协议连接, 并且只能在本机访问;
::1 ::1: 就是兼容支持 ipv6 的, 表示同 ipv4 的 127.0.0.1
5 参考
https://www.cnblogs.com/SQL888/p/5748824.html
官方文档: 用户
官方文档: 权限
来源: http://www.bubuko.com/infodetail-2879584.html