首页   快速返回

MySQL5.7.26用户管理     所属分类 mysql
创建用户 

CREATE USER test IDENTIFIED BY 'Test123$';
CREATE USER test@'localhost' IDENTIFIED BY 'Test123$';

默认为 %

mysql> show grants for test;
+----------------------------------+
| Grants for test@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' |
+----------------------------------+
1 row in set (0.00 sec)

create user 默认权限为 USAGE ,只能连库 不能做任何操作

授权 
GRANT all privileges ON test.* TO 'test'@'%';



创建用户并授权
GRANT ALL privileges ON test.* TO 'admin'@'%' IDENTIFIED BY 'Admin123$';

'admin'@'localhost'
localhost 只能本地登录

'admin'@'%'  可以在任何机器上登录


GRANT SELECT, INSERT ON test.* TO 'dog'@'localhost';

flush privileges;

密码太简单会报错 
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements


常见错误

java.sql.SQLException: null,  message from server: "Host '10.57.240.25' is not allowed to connect to this MySQL server"


CREATE USER 'test'@'localhost' IDENTIFIED BY 'Test123$';

mysql -h 10.57.31.22 -u test -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'test'@'10.57.240.25' (using password: YES)


GRANT ALL privileges ON test.* TO 'test'@'%' IDENTIFIED BY 'Test123$';

修改密码
 set password for test = password('Test1234$');
 
 
查看权限

show grants for test;

mysql> show grants for test;
+------------------------------------------------+
| Grants for test@%                              |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%'               |
| GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'%' |
+------------------------------------------------+
2 rows in set (0.00 sec)



create table t1(id int primary key,name varchar(32));
insert into t1(id,name) values(1,'cat');
insert into t1(id,name) values(2,'dog');

回收权限
REVOKE delete ON test.* FROM test
REVOKE delete ON test.* FROM test@'%'


test 用户退出 重新登录后权限才能生效
delete from t1 where id=3;

ERROR 1142 (42000): DELETE command denied to user 'test'@'10.57.240.25' for table 't1'

查看所有用户
select user,host from mysql.user;


删除用户
drop user test;

mysql> drop user test;
ERROR 1396 (HY000): Operation DROP USER failed for 'test'@'%'
mysql> drop user test@'localhost';
Query OK, 0 rows affected (0.00 sec)

上一篇     下一篇
aerospike备份

aerospike恢复

客户的游艇在哪里摘录

mysql5.7.26安装

centos7防火墙配置

mysql5.7 JSON 数据类型测试