MySQL创建用户

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 by456;
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 123with max_updates_per_hour 2;

 回收权限

revoke select on *.* from ‘name@%;

revoke all privileges,grant option from liu@%;

 

MySQL创建用户

上一篇:MySQL多表数据记录查询(一)


下一篇:Linux下安装MySQL