mysql中的用户与权限管理

mysql中的用户与权限管理

我就直接po代码了

#mysql中的用户与权限管理

/*
用户要连接mysql数据库服务器
分为2个阶段
1.你有没有权连接上来
2.你有没有权执行此操作(如SELECT,UPDATE等等)
对于1，服务器如何判断用户有没有权连接上来？
依据3个参数
host 你从哪儿来？
user 你是谁？
password 你的密码是多少？
用户的这3个信息，存储在mysql.user表中(即mysql库的user表)
*/

/*

注意:
grant(授予)/revoke(收回)用户权限后，该用户需要重新登录/连接mysql数据库，权限才能生效

*/


#
SELECT host, user, password FROM mysql.`user`;
SELECT * FROM mysql.`user`;
#
SELECT PASSWORD('root'), PASSWORD('666666');

#冲刷权限(可以理解成刷新一下内存)
FLUSH PRIVILEGES;

#当前所在的库
SELECT DATABASE();
#当前的账户
SELECT CURRENT_USER();
SELECT USER();

#
USE mysql;
#
SHOW TABLES;

#
SELECT host, user, password FROM mysql.user;
#数据库级权限
SELECT * from mysql.db;
#表级权限
SELECT * from mysql.tables_priv;
#列级权限
SELECT * from mysql.columns_priv;
#
SELECT host, user, password FROM mysql.user;

#修改host，使ip可以连接
update user set host = '192.168.6.%' where user = 'root';  
FLUSH PRIVILEGES; #冲刷权限

#修改用户的密码
UPDATE mysql.user SET password = PASSWORD('root') WHERE user = 'root';
FLUSH PRIVILEGES; #冲刷权限

#新增一个用户
/*
GRANT [权限1, 权限2, 权限3....] ON 库名.表名 TO 用户名@'ip地址即host' IDENTIFIED BY '密码';
常用权限有 ALL, CREATE, DROP, INSERT, DELETE, UPDATE, SELECT
*/
GRANT ALL ON *.* TO jack@'192.168.10.%' IDENTIFIED BY '6666';
GRANT ALL PRIVILEGES ON *.* TO lily@'192.%.%.%' IDENTIFIED BY '9999';
GRANT SELECT ON *.* TO root@'%';
GRANT ALL ON *.* TO root@'%';
#privileges关键字可以省略
GRANT ALL ON myemployees.employees TO lucy@'192.168.%.%' IDENTIFIED BY '8888';

#子句"WITH GRANT OPTION" 表示该用户可以为其他用户分配权限。
GRANT ALL ON *.* TO tom@'%' IDENTIFIED BY '6666' WITH GRANT OPTION;
GRANT ALL ON girls.* TO tom@'%' IDENTIFIED BY '6666' WITH GRANT OPTION;
SELECT host, user, password FROM mysql.user;
SELECT * FROM mysql.user;
SELECT * FROM mysql.user where user = 'tom';

#
GRANT ALL ON *.* TO zhangwuji@'%' IDENTIFIED BY '6666';
#收回权限
REVOKE ALL ON *.* FROM zhangwuji@'%';
REVOKE ALL ON *.* FROM jack@'192.168.10.%';
FLUSH PRIVILEGES;


#库及表级授权与收回

#针对某个库做授权
#把myemployees这个库授权给zhangwuji这个用户
GRANT ALL ON myemployees.* TO zhangwuji@'%';
FLUSH PRIVILEGES;
#收回权限
REVOKE ALL ON myemployees.* FROM zhangwuji@'%';
FLUSH PRIVILEGES;
#

#针对表做授权
GRANT SELECT, INSERT ON myemployees.book TO zhangwuji@'%';
FLUSH PRIVILEGES;
#经过测试，用户zhangwuji对myemployees库的book表，确实只能做SELECT查询和INSERT新增操作
#收回权限
REVOKE SELECT, INSERT ON myemployees.book FROM zhangwuji@'%';
FLUSH PRIVILEGES;
#经过测试，此时用户zhangwuji对myemployees库的book表，已经没有SELECT和INSERT的权限了

/*
mysql数据库的权限控制，可以精确到列
*/

#针对表中某些列做授权
GRANT SELECT(bname, price) ON myemployees.book TO zhangwuji@'%';
FLUSH PRIVILEGES;

#
SELECT * FROM mysql.user;
SELECT * FROM mysql.host;
#数据库级权限
SELECT * from mysql.db;
#表级权限
SELECT * from mysql.tables_priv;
#列级权限
SELECT * from mysql.columns_priv;

#
SHOW GRANTS FOR tom;
SHOW GRANTS;

#删除用户DROP USER username@localhost;
SELECT host, user, password from mysql.user;
DROP user lily@'192.%.%.%'; #删除用户
SELECT host, user, password from mysql.user;

#
SELECT host, user, password FROM mysql.user;
DROP user weixiaobao@'%'; #删除用户


#
GRANT ALL ON *.* TO linghuchong@'%' IDENTIFIED BY '6666' WITH GRANT OPTION;
FLUSH PRIVILEGES;
#
REVOKE ALL ON *.* FROM linghuchong@'%';
FLUSH PRIVILEGES;
#
GRANT ALL ON myemployees.* TO linghuchong@'%' IDENTIFIED BY '6666' WITH GRANT OPTION;
FLUSH PRIVILEGES;
#
REVOKE ALL ON myemployees.* FROM linghuchong@'%';
FLUSH PRIVILEGES;


#
GRANT ALL ON *.* TO weixiaobao@'%' IDENTIFIED BY '6666';
FLUSH PRIVILEGES;
#如下写法，不能将weixiaobao用户的myemployees库的权限收回来
REVOKE ALL ON *.* FROM weixiaobao@'%';
FLUSH PRIVILEGES;
#
GRANT ALL ON myemployees.* TO weixiaobao@'%' IDENTIFIED BY '6666';
FLUSH PRIVILEGES;
#
SELECT * from mysql.db;
#如下写法，才能将weixiaobao用户的myemployees库的权限收回来
REVOKE ALL ON myemployees.* FROM weixiaobao@'%';
FLUSH PRIVILEGES;

#
show grants for linghuchong;
show grants for weixiaobao;