MySQL5.7.26用户管理
所属分类 mysql
浏览量 1272
创建用户
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 数据类型测试