MySQL基本授权操作

浅浅的花香味﹌ 2023-07-19 05:51 24阅读 0赞

本文介绍MySQL权限的基本操作。

# 授权 #

MySQL用户包括user和host两部分。

user与host是一起出现的，即权限指的是某个用户在某个主机或某些主机上的权限。

首先，创建用户：

mysql> CREATE USER 'root'@'%' IDENTIFIED  by 'mysql123456';

接着，授权权限：

mysql> GRANT ALL on *.* to 'root'@'%';
    Query OK, 0 rows affected (0.00 sec)

具体权限，是指某个DB下某个table的权限。  
这里授权`'root'@'%'` 操作所有DB所有tabe的权限。

# 查看授权 #

查看’root’@’%'的授权：

mysql> show grants for 'root'@'%';
    +-------------------------------------------------------------+
    | Grants for root@%                                           |
    +-------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
    +-------------------------------------------------------------+
    1 row in set (0.00 sec)

## 查看授权用户 ##

mysql> select * from mysql.user;

查看指定用户

mysql> select * from mysql.user where user='root'\G
    *************************** 1. row ***************************
                      Host: localhost
                      User: root
    ... ...
    
             max_questions: 0
               max_updates: 0
           max_connections: 0
      max_user_connections: 0
                    plugin: mysql_native_password
     authentication_string: *xxx
          password_expired: N
     password_last_changed: 2018-10-10 14:42:12
         password_lifetime: NULL
            account_locked: N
    *************************** 2. row ***************************
                      Host: %
                      User: root
    ... ...
               max_updates: 0
           max_connections: 0
      max_user_connections: 0
                    plugin: mysql_native_password
     authentication_string: xxx
          password_expired: N
     password_last_changed: 2018-10-10 14:47:32
         password_lifetime: NULL
            account_locked: N
    2 rows in set (0.01 sec)

# 撤销权限 #

撤销某个用户的授权：

REVOKE ALL on orchestrator.* FROM 'orchestrator_server'@'10.23.211.199';

删除用户：

delete from mysql.user where user='orchestrator_server' and host='10.23.211.199' ;
    flush privileges ;