MySQL用户权限设置

缺乏、安全感 2024-03-17 15:08 103阅读 0赞

> **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)