Mysql之用户的创建
1.Create User
语法:CREATE USER ‘username‘@‘host‘ IDENTIFIED BY ‘password‘;
eg:
CREATE USER ‘pig‘@‘192.168.1.101‘ IDENDIFIED BY ‘123456‘; // 这里限定了可访问的主机的ip为192.168.1.101
CREATE USER ‘pig‘@‘%‘ IDENTIFIED BY ‘123456‘; //表明任何主机都可以访问
create user jss; //可以从任意安装了mysql客户端,并能够访问目标服务器的机器上创建连接,无须密码
2.Grant语句
语法:grant 权限1,权限2,...权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令‘;
eg:
grant select,insert,update,delete,create,drop on mydb.user to [email protected] identified by ‘123‘;//给来自192.168.1.101的用户admin分配可对数据库mydb的user表进行select,insert,update,delete,create,drop等操作的权限,并设定口令为123。
grant all privileges on mydb.* to [email protected] identified by ‘123‘;//给来自192.168.1.101的用户admin分配可对数据库mydb所有表进行所有操作的权限,并设定口令为123
grant all privileges on *.* to [email protected] identified by ‘123‘;//给来自192.168.1.101‘的用户admin分配可对所有数据库的所有表进行所有操作的权限,并设定口令为123
3.直接向表中插入数据
insert into user (host,user,password) values (‘%‘,‘admin‘,password(‘123‘));
flush privileges; //刷新系统权限表
4.修改密码
4.1.使用mysqladmin
语法:mysqladmin -u用户名 -p旧密码 password 新密码
eg:
mysqladmin -u root -p 123 password 456;
4.2.直接修改user表的用户口令
语法:update mysql.user set password=password(‘新密码‘) where User="phplamp" and Host="localhost";
eg:
update user set password=password(‘54netseek‘) where user=‘root‘;
flush privileges; //刷新一下系统权限表
4.3.使用SET PASSWORD语句修改密码
语法:SET PASSWORD FOR ‘username‘@‘host‘ = PASSWORD(‘newpassword‘);
如果是当前用户则去掉FOR ‘username‘@‘host‘
5.取消账户和其授权
Drop USER user;
drop user username@‘%‘
drop user username@localhost
6.去掉权限
语法:REVOKE privilege ON databasename.tablename FROM ‘username‘@‘host‘;
eg:
REVOKE SELECT ON test.user FROM ‘pig‘@‘%‘;
revoke all on user.* from ‘admin‘@‘%‘;
SHOW GRANTS FOR ‘pig‘@‘%‘; //查看授权
7.删除账户
语法: Delete from user where user = "user_name" and host = "host_name" ;
eg:
delete from user where user=‘admin‘ and host=‘localhost‘;
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。