mysql创建用户并赋权

查看所有用户

select host,user from mysql.user;

创建用户test,密码123456789

create user 'test' identified by '123456789';

重命名用户test为wangpandong

rename user test to wangpandong;

修改用户wangpandong的密码为987654321

set password for wangpandong = password('987654321');

查看用户wangpandong权限

show grants for wangpandong;

删除用户wangpandong

drop user wangpandong;

为用户wangpandong授权

grant select, update, delete, insert on mysql.*  to wangpandong;

grant create,  alter, drop on testdb.* to wangpandong;

取消对用户wangpandong的授权

revoke  select on testdb.*  from  wangpandong;

授于创建、修改、删除 mysql 数据表结构权限

grant create,  alter, drop on testdb.* to wangpandong;

授于操作 mysql 外键权限

grant references on testdb.* to wangpandong;

授于操作 mysql 临时表权限

grant create temporary tables on testdb.* to wangpandong;

授于操作 mysql 索引权限

grant index on testdb.* to wangpandong;

授于操作 mysql 视图、查看视图源代码 权限

grant create view on testdb.* to wangpandong;

grant show view on testdb.* to wangpandong;

授于操作 mysql 存储过程、函数 权限

grant create routine on testdb.* to wangpandong; 

grant alter routine on testdb.* to wangpandong;

grant execute on testdb.* to wangpandong;

授于wangpandong管理某个 mysql 数据库的权限。

grant all privileges on testdb to wangpandong;

授于wangpandong管理 mysql 中所有数据库的权限

grant all on *.* to wangpandong;

grant 作用在整个 mysql 服务器上：

grant select on *.* to wangpandong; 

grant all on *.* to wangpandong; 

grant 作用在单个数据库上：

grant all on test.* to wangpandong;

grant 作用在单个数据表上：

grant select, insert, update, delete on test to wangpandong;

grant 作用在表中的列上：

grant select(学号,姓名) on test to wangpandong;

刷新所有修改:

flush privileges;