07-mysql数据库权限管理

用户管理

用户登录

-- -h参数 hostname为主机，hostIP为主机IP。
-- -P参数 MySQL服务的默认端口是3306，
-- -u参数 后面接用户名，username为用户名。
-- -p参数 会提示输入密码。DatabaseName参数 指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库。
-- -e参数 后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器。
-- 直接选择 test数据库执行命令
mysql -uroot -proot test -e "select * from book";

创建用户

-- CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
CREATE USER zhang3 IDENTIFIED BY '123123'; # 默认host是 %
CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';

-- 查询用户
select user,host from user;

修改用户

update user set user='zhangsan1' where user='zhangsan';
FLUSH PRIVILEGES;

删除用户

drop user lisi@'localhost';

修改密码

alter user 'zhangsan1' identified by '1234';

权限管理

-- 查看所有权限
 show privileges;

权限	上下文	描述
Alter	Tables	改变表格
Alter routine	Functions,Procedures	更改或删除存储的功能/程序
Create	Databases,Tables,Indexes	创建新的数据库和表
Create routine	Databases	To use CREATE FUNCTION/PROCEDURE
Create temporary tables	Databases	To use CREATE TEMPORARY TABLE
Create view	Tables	创建视图
Create user	Server Admin	创建用户
Delete	Tables	删除行
Drop	Databases,Tables	To drop databases, tables, and views
Event	Server Admin	创建、更改、删除和执行事件
Execute	Functions,Procedures	执行存储的例程
File	File access on server	在服务器上读取和写入文件
Grant option	Databases,Tables,Functions,Procedures	To give to other users those privileges you possess
Index	Tables	To create or drop indexes
Insert	Tables	To insert data into tables
Lock tables	Databases	To use LOCK TABLES (together with SELECT privilege)
Process	Server Admin	To view the plain text of currently executing queries
Proxy	Server Admin	To make proxy user possible
References	Databases,Tables	To have references on tables
Reload	Server Admin	To reload or refresh tables, logs and privileges
Replication client	Server Admin	To ask where the slave or master servers are
Replication slave	Server Admin	To read binary log events from the master
Select	Tables	To retrieve rows from table
Show databases	Server Admin	To see all databases with SHOW DATABASES
Show view	Tables	To see views with SHOW CREATE VIEW
Shutdown	Server Admin	To shut down the server
Super	Server Admin	To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
Trigger	Tables	To use triggers
Create tablespace	Server Admin	To create/alter/drop tablespaces
Update	Tables	To update existing rows
Usage	Server Admin	No privileges - allow connect only

-- 授予权限
GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
GRANT SELECT,INSERT,DELETE,UPDATE ON db1.* TO li4@localhost ;

-- 查看当前用户权限
SHOW GRANTS;
-- 查看某个用户权限g
SHOW GRANTS FOR 'user'@'主机地址' ;
show grants for zhangsan1@'%';

-- 收回权限
#收回全库全表的所有权限 
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%'; 
#收回mysql库下的所有表的插删改查权限 
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;