用户权限管理主要有以下作用:
1. 可以限制用户访问哪些库, 哪些表
2. 可以限制用户对哪些表执行 SELECT,CREATE,DELETE,DELETE,ALTER 等操作
3. 可以限制用户登录的 IP 或域名
4. 可以限制用户自己的权限是否可以授权给别的用户
一, 用户授权
MySQL> grant all privileges on *.* to 'yangxin'@'%' identified by 'yangxin123456' with grant option;
all privileges: 表示将所有权限授予给用户. 也可指定具体的权限, 如: SELECT,CREATE,DROP 等.
on: 表示这些权限对哪些数据库和表生效, 格式: 数据库名. 表名, 这里写 "*" 表示所有数据库, 所有表. 如果我要指定将权限应用到 test 库的 user 表中, 可以这么写: test.user
to: 将权限授予哪个用户. 格式:"用户名"@"登录 IP 或域名".% 表示没有限制, 在任何主机都可以登录. 比如:"yangxin"@"192.168.0.%", 表示 yangxin 这个用户只能在 192.168.0IP 段登录
identified by: 指定用户的登录密码
with grant option: 表示允许用户将自己的权限授权给其它用户
可以使用 GRANT 给用户添加权限, 权限会自动叠加, 不会覆盖之前授予的权限, 比如你先给用户添加一个 SELECT 权限, 后来又给用户添加了一个 INSERT 权限, 那么该用户就同时拥有了 SELECT 和 INSERT 权限.
用户详情的权限列表请参考 MySQL 官网说明: http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html
二, 刷新权限
对用户做了权限变更之后, 一定记得重新加载一下权限, 将权限信息从内存中写入数据库.
MySQL> flush privileges;
三, 查看用户权限
- MySQL> grant select,create,drop,update,alter on *.* to 'yangxin'@'localhost' identified by 'yangxin0917' with grant option;
- MySQL> show grants for 'yangxin'@'localhost';
四, 回收权限
删除 yangxin 这个用户的 create 权限, 该用户将不能创建数据库和表.
- MySQL> revoke create on *.* from '[email protected]';
- MySQL> flush privileges;
五, 删除用户
- MySQL> select host,user from user;
- +---------------+---------+
- | host | user |
- +---------------+---------+
- | % | root |
- | % | test3 |
- | % | yx |
- | 192.168.0.% | root |
- | 192.168.0.% | test2 |
- | 192.168.0.109 | test |
- | ::1 | yangxin |
- | localhost | yangxin |
- +---------------+---------+
- 8 rows in set (0.00 sec)
- MySQL> drop user 'yangxin'@'localhost';
六, 用户重命名
shell> rename user 'test3'@'%' to 'test1'@'%';
七, 修改密码
1> 更新 MySQL.user 表
- MySQL> use MySQL;
- # mysql5.7 之前
- MySQL> update user set password=password('123456') where user='root';
- # mysql5.7 之后
- MySQL> update user set authentication_string=password('123456') where user='root';
- MySQL> flush privileges;
2> 用 set password 命令
语法: set password for '用户名'@'登录地址'=password('密码')
- MySQL> set password for 'root'@'localhost'=password('123456');
- 3> mysqladmin
语法: mysqladmin -u 用户名 -p 旧的密码 password 新密码
MySQL> mysqladmin -uroot -p123456 password 1234abcd
注意: mysqladmin 位于 MySQL 安装目录的 bin 目录下
八, 忘记密码
1> 添加登录跳过权限检查配置
修改 my.cnf, 在 mysqld 配置节点添加 skip-grant-tables 配置
- [mysqld]
- skip-grant-tables
2> 重新启动 MySQL 服务
shell> service mysqld restart
3> 修改密码
此时在终端用 MySQL 命令登录时不需要用户密码, 然后按照修改密码的第一种方式将密码修改即可.
注意: MySQL 库的 user 表, 5.7 以下版本密码字段为 password,5.7 以上版本密码字段为 authentication_string
4> 还原登录权限跳过检查配置
将 my.cnf 中 mysqld 节点的 skip-grant-tables 配置删除, 然后重新启动服务即可.
MySQL 有多个个权限? 经常记不住, 今天总结一下, 看后都能牢牢的记在心里啦!!
很明显总共 28 个权限: 下面是具体的权限介绍: 转载的, 记录一下:
一.权限表
MySQL 数据库中的 3 个权限表: user ,db, host
权限表的存取过程是:
1)先从 user 表中的 host, user, password 这 3 个字段中判断连接的 IP, 用户名, 密码是否存在表中, 存在则通过身份验证;
2)通过权限验证, 进行权限分配时, 按照 useràdbàtables_privàcolumns_priv 的顺序进行分配. 即先检查全局权限表 user, 如果 user 中对应的权限为 Y, 则此用户对所有数据库的权限都为 Y, 将不再检查 db, tables_priv,columns_priv; 如果为 N, 则到 db 表中检查此用户对应的具体数据库, 并得到 db 中为 Y 的权限; 如果 db 中为 N, 则检查 tables_priv 中此数据库对应的具体表, 取得表中的权限 Y, 以此类推.
二.MySQL 各种权限(共 27 个)
- (以下操作都是以 root 身份登陆进行 grant 授权, 以 [email protected] 身份登陆执行各种命令.)
- 1. usage
连接 (登陆) 权限, 建立一个用户, 就会自动授予其 usage 权限(默认授予).
MySQL> grant usage on *.* to 'p1′@'localhost'identified by'123′;
该权限只能用于数据库登陆, 不能执行任何操作; 且 usage 权限不能被回收, 也即 REVOKE 用户并不能删除用户.
2. select
必须有 select 的权限, 才可以使用 select table
- MySQL> grant select on pyt.* to 'p1′@'localhost';
- MySQL> select * from shop;
- 3. create
必须有 create 的权限, 才可以使用 create table
- MySQL> grant create on pyt.* to 'p1′@'localhost';
- 4. create routine
必须具有 create routine 的权限, 才可以使用{create |alter|drop} {procedure|function}
MySQL> grant create routine on pyt.* to 'p1′@'localhost';
当授予 create routine 时, 自动授予 EXECUTE, ALTER ROUTINE 权限给它的创建者:
- MySQL> show grants for 'p1′@'localhost';
- +-------------------------+
- Grants for [email protected]
- +-------------------------+
| GRANT USAGE ON *.* TO 'p1′@'localhost'IDENTIFIED BY PASSWORD'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257′ |
- | GRANT SELECT, CREATE, CREATE ROUTINE ON `pyt`.* TO 'p1′@'localhost'|
- | GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `pyt`.`pro_shop1` TO 'p1′@'localhost' |
- +-----------------------------+
- 5. create temporary tables(注意这里是 tables, 不是 table)
必须有 create temporary tables 的权限, 才可以使用 create temporary tables.
- MySQL> grant create temporary tables on pyt.* to 'p1′@'localhost';
- [[email protected] ~]$ MySQL -h localhost -u p1 -p pyt
- MySQL> create temporary table tt1(id int);
- 6. create view
必须有 create view 的权限, 才可以使用 create view
- MySQL> grant create view on pyt.* to 'p1′@'localhost';
- MySQL> create view v_shop as select price from shop;
- 7. create user
要使用 CREATE USER, 必须拥有 MySQL 数据库的全局 CREATE USER 权限, 或拥有 INSERT 权限.
MySQL> grant create user on *.* to 'p1′@'localhost';
或: MySQL> grant insert on *.* to [email protected];
8. insert
必须有 insert 的权限, 才可以使用 insert into ..... values....
9. alter
必须有 alter 的权限, 才可以使用 alter table
- alter table shop modify dealer char(15);
- 10. alter routine
必须具有 alter routine 的权限, 才可以使用{alter |drop} {procedure|function}
- MySQL>grant alter routine on pyt.* to 'p1′@' localhost ';
- MySQL> drop procedure pro_shop;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> revoke alter routine on pyt.* from 'p1′@'localhost';
- [[email protected] ~]$ MySQL -h localhost -u p1 -p pyt
- MySQL> drop procedure pro_shop;
- ERROR 1370 (42000): alter routine command denied to user 'p1′@'localhost'for routine'pyt.pro_shop'
- 11. update
必须有 update 的权限, 才可以使用 update table
- MySQL> update shop set price=3.5 where article=0001 and dealer='A';
- 12. delete
必须有 delete 的权限, 才可以使用 delete from ....where....(删除表中的记录)
13. drop
必须有 drop 的权限, 才可以使用 drop database db_name; drop table tab_name;
- drop view vi_name; drop index in_name;
- 14. show database
通过 show database 只能看到你拥有的某些权限的数据库, 除非你拥有全局 SHOW DATABASES 权限.
对于 [email protected] 用户来说, 没有对 MySQL 数据库的权限, 所以以此身份登陆查询时, 无法看到 MySQL 数据库:
- MySQL> show databases;
- +-------+
- | Database |
- +-------+
- | information_schema|
- | pyt |
- | test |
- +-------+
- 15. show view
必须拥有 show view 权限, 才能执行 show create view.
- MySQL> grant show view on pyt.* to [email protected];
- MySQL> show create view v_shop;
- 16. index
必须拥有 index 权限, 才能执行[create |drop] index
- MySQL> grant index on pyt.* to [email protected];
- MySQL> create index ix_shop on shop(article);
- MySQL> drop index ix_shop on shop;
- 17. excute
执行存在的 Functions,Procedures
- MySQL> call pro_shop1(0001,@a);
- +---+
- | article |
- +---+
- | 0001 |
- | 0001 |
- +---+
- MySQL> select @a;
- +--+
- | @a |
- +--+
- | 2 |
- +--+
- 18. lock tables
必须拥有 lock tables 权限, 才可以使用 lock tables
- MySQL> grant lock tables on pyt.* to [email protected];
- MySQL> lock tables a1 read;
- MySQL> unlock tables;
- 19. references
有了 REFERENCES 权限, 用户就可以将其它表的一个字段作为某一个表的外键约束.
20. reload
必须拥有 reload 权限, 才可以执行 flush [tables | logs | privileges]
- MySQL> grant reload on pyt.* to [email protected];
- ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
- MySQL> grant reload on *.* to 'p1′@'localhost';
- Query OK, 0 rows affected (0.00 sec)
- MySQL> flush tables;
- 21. replication client
拥有此权限可以查询 master server,slave server 状态.
- MySQL> show master status;
- ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation
- MySQL> grant Replication client on *.* to [email protected];
或: MySQL> grant super on *.* to [email protected];
- MySQL> show master status;
- +------+----+-----+------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------+----+-----+------+
- | MySQL-bin.000006 | 2111 | | |
- +------+----+-----+------+
- MySQL> show slave status;
- 22. replication slave
拥有此权限可以查看从服务器, 从主服务器读取二进制日志.
- MySQL> show slave hosts;
- ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation
- MySQL> show binlog events;
- ERROR 1227 (42000): Access denied; you need the REPLICATION SLAVE privilege for this operation
- MySQL> grant replication slave on *.* to [email protected];
- MySQL> show slave hosts;
- Empty set (0.00 sec)
- MySQL>show binlog events;
- +-----+---+------+----+-----+-----+
- | Log_name | Pos | Event_type | Server_id| End_log_pos|Info |
- +-----+---+-----+----+-----+-----+
- | MySQL-bin.000005 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log, Binlog ver: 4 | |MySQL-bin.000005|98|Query|1|197|use `mysql`; create table a1(i int)engine=myisam|
- ..........................................
- 23. Shutdown
关闭 MySQL:
[[email protected] ~]$ mysqladmin shutdown
重新连接:
- [[email protected] ~]$ MySQL
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/tmp/MySQL.sock' (2)
- [[email protected] ~]$ cd /u01/MySQL/bin
- [[email protected] bin]$ ./mysqld_safe &
- [[email protected] bin]$ MySQL
- 24. grant option
拥有 grant option, 就可以将自己拥有的权限授予其他用户(仅限于自己已经拥有的权限)
- MySQL> grant Grant option on pyt.* to [email protected];
- MySQL> grant select on pyt.* to [email protected];
- 25. file
拥有 file 权限才可以执行 select ..into outfile 和 load data infile... 操作, 但是不要把 file, process, super 权限授予管理员以外的账号, 这样存在严重的安全隐患.
- MySQL> grant file on *.* to [email protected];
- MySQL> load data infile '/home/mysql/pet.txt' into table pet;
- 26. super
这个权限允许用户终止任何查询; 修改全局变量的 SET 语句; 使用 CHANGE MASTER,PURGE MASTER LOGS.
MySQL> grant super on *.* to [email protected];
MySQL> purge master logs before 'MySQL-bin.000006′;
27. process
通过这个权限, 用户可以执行 SHOW PROCESSLIST 和 KILL 命令. 默认情况下, 每个用户都可以执行 SHOW PROCESSLIST 命令, 但是只能查询本用户的进程.
- MySQL> show processlist;
- +--+--+----+--+---+--+---+------+
- | Id | User | Host | db | Command | Time | State | Info |
- +--+--+----+--+---+--+---+------+
- | 12 | p1 | localhost | pyt | Query | 0 | NULL | show processlist |
- +--+--+----+--+---+--+---+------+
另外,
管理权限 (如 super, process, file 等) 不能够指定某个数据库, on 后面必须跟 *.*
- MySQL> grant super on pyt.* to [email protected];
- ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
- MySQL> grant super on *.* to [email protected];
- Query OK, 0 rows affected (0.01 sec)
- ---------------------
作者: anzhen0429
来源: http://www.bubuko.com/infodetail-3101376.html