MySQL用户权限设置

MySQL用户权限设置

1.登陆mysql

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 127
Server version: 5.7.28 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]>

2.创建用户

创建新用户，名为hxy，密码为12345，且可以从其他服务器登录

MySQL [(none)]> create user 'hxy'@'%' identified by '12345';
Query OK, 0 rows affected (0.01 sec)
 
MySQL [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

只允许本机登录
create user 'hxy'@'%' identified by '12345';

3.授权用户

给hxy用户授权，让hxy能给tester_pro数据库中的system_testperson表实行select、insert和update

MySQL [(none)]> grant select,insert,update on tester_pro.system_testperson TO 'hxy'@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
MySQL [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

给hxy授权，使hxy能给所有库实行所有的权利
MySQL [(none)]> grant all on *.* to 'hxy'@'%';
Query OK, 0 rows affected (0.01 sec)
 
MySQL [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4.撤销用户权限

撤销用户insert的权限

MySQL [(none)]> revoke insert on *.* from 'hxy'@'%';
Query OK, 0 rows affected (0.00 sec)
 
MySQL [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

5.删除用户

MySQL [(none)]> drop user 'hxy'@'%';
Query OK, 0 rows affected (0.00 sec)