用户管理
用户登录
mysql -uroot -proot test -e "select * from book";
创建用户
CREATE USER zhang3 IDENTIFIED BY '123123';
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;
SHOW GRANTS FOR 'user'@'主机地址' ;
show grants for zhangsan1@'%';
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;