mysql 123456_MySQL基本授权操作

本文介绍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 ;