1、创建用户:
语法:
create user 用户1@主机名 identified by ‘123‘|password ‘‘ 123:*23AE809DDACAF96AF0FD78ED04B6A265E05AA257
同时创建用户liu1 123 ,明文
name 123 password
例1:
create user name1@localhost identified by ‘123‘, name2@localhost identified by password ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257‘;
例2:
对用户进行重命名:
把用户name1@localhost改成name3@localhost
rename user name1@localhost to name3@localhost;
例3:
把用户name3的密码改成456;
set password[for user] =password(str)|=‘加密后的字符串‘
方法一:
set password for name3@localhost =password(‘456‘);
方法2:
set password for name3@localhost=‘*531E182E2F72080AB0740FE2F2D689DBE0146E04‘; 456:*531E182E2F72080AB0740FE2F2D689DBE0146E04
例4:
drop user name3@localhost,name2@localhost;
分配权限:
grant
语法:
grant 权限(all,select/update/insert/create user,select(studentno,studenname))
on *//当前库的所有的表的权限
on *.*//所有库的所有表的权限
on db_school.tb_student//具体的库的具体的某个表
on db_school.*//db_school库的所有表
on tb_student//当前的库里的tb_student;
on db_name.routine_name//某个库的存储过程或者是函数
to zhangsan1@localhost
identified by ‘456‘;
例:
授予用户zhangsan在数据库db_school的表tb_student上拥有对列studentno的select权限;
grant select(studentno)on db_school.tb_student to zhangsan@localhost;
例2;
授予用户lisi在数据库db_school中所有表select、update和insert的权限,并且指定口令为456;
grant select,update,insert on db_school.* to lisi@localhost identified by‘456‘;
grant select,update,insert on db_school.* to lisi@localhost identified by ‘456‘;
例:
给李明分配所有数据库的所有表的所有权限;
all grant all on *.* to liming@localhost;
grant create user on *.* to liming@localhost;
with grant option max_queries_per_hour count//限制每小时可以查询数据库的次数 max_update_per_hour count//限制每小时可以更新数据库的次数 max_connections_per_hour count//限制每小时可以连接数据库的次数 max_user_connections count//限制同时连接MySQL的最大用户数;
例:
限制用户kkk@localhost每小时只能更新数据库db_school的tb_student表一次;
grant update on db_school.tb_student to kkk@localhost identified by ‘123‘ with max_updates_per_hour 2;
回收权限
revoke select on *.* from ‘name‘@‘%‘; revoke all privileges,grant option from ‘liu‘@‘%‘;