MYSQL用户管理

MySQL权限级别

全局性的管理权限，作用于整个MySQL实例级别

数据库级别的权限，作用于某个指定的数据库上或者所有的数据库上

数据库对象级别的权限，作用于指定的数据库对象上(表、视图等)或 者所有的数据库对象上

权限存储在mysql库的user, db, tables_priv, columns_priv, and procs_priv这几个系统表中，待MySQL实例启动后就加载到内存中

MYSQL创建用户的基本语法：

创建一个test测试用户

mysql> create user test identified by 'test';

Query OK, 0 rows affected (0.04 sec)

创建用户后测试下能否登录

[root@redhat ~]# mysql -utest -ptest -P3306 -S/u01/mysql/mysql3306/tmp/mysql_3306.sock

mysql: [Warning] Using a password on the commandline interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 11

Server version: 5.7.20-log MySQL Community Server(GPL)

Copyright (c) 2000, 2017, Oracle and/or itsaffiliates. All rights reserved.

Oracle is a registered trademark of OracleCorporation and/or its

affiliates. Other names may be trademarks of theirrespective

owners.

Type 'help;' or '\h' for help. Type '\c' to clearthe current input statement.

mysql> show databases;

+--------------------+

| Database          |

+--------------------+

| information_schema |

+--------------------+

1 row in set (0.00 sec)

mysql> create database no1;

ERROR 1044 (42000): Access denied for user'test'@'%' to database 'no1'

mysql> show grants ;

+----------------------------------+

| Grants for test@%                |

+----------------------------------+

| GRANT USAGE ON *.* TO 'test'@'%' |

+----------------------------------+

仅仅是登录使用并没有其他的权限

使用root用户继续授权给test用户

mysql> grant all privileges on *.* to 'test'@'%';

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:   12

Current database: *** NONE ***

Query OK, 0 rows affected (0.06 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.02 sec)

all privileges 表示将所有的权限都授予了test测试用户

mysql> show grants;

+-------------------------------------------+

| Grants for test@%                         |

+-------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' |

+-------------------------------------------+

1 row in set (0.00 sec)

正常情况下是给普通用户授权是不允许全部数据库全部权限

对权限回收从新授权

mysql> revoke all PRIVILEGES on *.* from'test'@'%' ;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.04 sec)

mysql> grant all privileges on test.* to'test'@'%';

Query OK, 0 rows affected (0.04 sec)

对测试用户授予mysq数据库的查询权限(可授予的权限在文章结尾）

mysql> grant all PRIVILEGES on mysql.* to'test'@'%' ;

Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.03 sec)

使用test用户好看mysql数据库下的user表

mysql> select * from mysql.user limit 1 \G

*************************** 1. row***************************

                 Host: localhost

                 User: root

          Select_priv: Y

          Insert_priv: Y

          Update_priv: Y

.

.

.

 max_user_connections: 0

               plugin: mysql_native_password

 authentication_string:*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA

     password_expired: N

 password_last_changed: 2018-04-27 22:13:00

    password_lifetime: NULL

       account_locked: N

1 row in set (0.00 sec)

可以观察到我们在授权的时候都是 user@host 模式授权的因为格式是根据mysql数据库中user表授权的，可以根据不同的host地址授予权限。

授予的时候可以指定某个网段 或者所有Ip地址 user@'192.168.1.%' 或者user@'%'

GRANT 语法:

GRANT privileges (columns)

ON what

TO user IDENTIFIED BY "password"

WITH GRANT OPTION

权限列表:

ALTER: 修改表和索引。

CREATE: 创建数据库和表。

DELETE: 删除表中已有的记录。

DROP: 抛弃(删除)数据库和表。

INDEX: 创建或抛弃索引。

INSERT: 向表中插入新行。

REFERENCE: 未用。

SELECT: 检索表中的记录。

UPDATE: 修改现存表记录。

FILE: 读或写服务器上的文件。

PROCESS: 查看服务器中执行的线程信息或杀死线程。

RELOAD: 重载授权表或清空日志、主机缓存或表缓存。

SHUTDOWN: 关闭服务器。

ALL: 所有权限，ALL PRIVILEGES同义词。

USAGE: 特殊的 "无权限" 权限。

用 户账户包括 "username" 和 "host" 两部分，后者表示该用户被允许从何地接入。tom@'%'表示任何地址，默认可以省略。还可以是 "tom@192.168.1.%"、"tom@%.abc.com" 等。数据库格式为 db@table，可以是 "test.*" 或 "*.*"，前者表示 test 数据库的所有表，后者表示所有数据库的所有表。子句 "WITHGRANT OPTION" 表示该用户可以为其他用户分配权限。 

All/All Privileges权限代表全局或者全数据库对象级别的所有权限

Alter权限代表允许修改表结构的权限，但必须要求有create和insert权 限配合。如果是rename表名，则要求有alter和drop原表，create和 insert新表的权限

Alter routine权限代表允许修改或者删除存储过程、函数的权限

Create权限代表允许创建新的数据库和表的权限

Createroutine权限代表允许创建存储过程、函数的权限

Createtablespace权限代表允许创建、修改、删除表空间和日志组的权 限

Create temporary tables权限代表允许创建临时表的权限

Createuser权限代表允许创建、修改、删除、重命名user的权限

Createview权限代表允许创建视图的权限

Delete权限代表允许删除行数据的权限

Drop权限代表允许删除数据库、表、视图的权限，包括truncatetable命令

Event权限代表允许查询，创建，修改，删除MySQL事件

Execute权限代表允许执行存储过程和函数的权限

File权限代表允许在MySQL可以访问的目录进行读写磁盘文件操作，可使用的命令包括load data infile,select ... into outfile,load file()函数

Grant option权限代表是否允许此用户授权或者收回给其他用户你给予的权 限

Index权限代表是否允许创建和删除索引

Insert权限代表是否允许在表里插入数据，同时在执行analyzetable,optimize table,repair table语句的时候也需要insert权限

Lock权限代表允许对拥有select权限的表进行锁定，以防止其他链接对此表的读或写

Process权限代表允许查看MySQL中的进程信息，比如执行showprocesslist,

Reference权限是在5.7.6版本之后引入，代表是否允许创建外键

Reload权限代表允许执行flush命令，指明重新加载权限表到系统内存中， refresh命令代表关闭和重新开启日志文件并刷新所有的表

Replication client权限代表允许执行showmaster status,show slave status,show binary logs命令

Replication slave权限代表允许slave主机通过此用户连接master以便建立主从 复制关系

Select权限代表允许从表中查看数据，某些不查询表数据的select执行则不需要此权限，如Select 1+1，Select PI()+2;而且select权限在执行update/delete 语句中含有where条件的情况下也是需要的

Showdatabases权限代表通过执行showdatabases命令查看所有的数据库名

Show view权限代表通过执行show create view命令查看视图创建的语句mysqladmin processlist, show engine等命令

Shutdown权限代表允许关闭数据库实例，执行语句包括mysqladminshutdown

Super权限代表允许执行一系列数据库管理命令，包括kill强制关闭某个连接命令，change master to创建复制关系命令，以及create/alter/dropserver等命 令

Trigger权限代表允许创建，删除，执行，显示触发器的权限

Update权限代表允许修改表中的数据的权限

Usage权限是创建一个用户之后的默认权限，其本身代表连接登录权限

MySQL授权用户

MySQL的授权用户由两部分组成:用户名和登录主机名

表达用户的语法为‘user_name’@‘host_name’

单引号不是必须，但如果其中包含特殊字符则是必须的

 ‘’@‘localhost’代表匿名登录的用户

Host_name可以使主机名或者ipv4/ipv6的地址。Localhost代表本机，127.0.0.1代表ipv4的 本机地址，::1代表ipv6的本机地址

Host_name字段允许使用%和_两个匹配字符，比如’%’代表所有主机，’%.mysql.com’代表 来自mysql.com这个域名下的所有主机，‘192.168.1.%’代表所有来自192.168.1网段的主机

MySQL修改权限的生效

执行Grant,revoke,setpassword,renameuser命令修改权限之后，MySQL会自动

将修改后的权限信息同步加载到系统内存中

如果执行insert/update/delete操作上述的系统权限表之后，则必须再执行刷新权限命令才能同步到系统内存中，刷新权限命令包括:flush privileges/mysqladminflush-privileges/mysqladmin reload

如果是修改tables和columns级别的权限，则客户端的下次操作新权限就会生 效

如果是修改database级别的权限，则新权限在客户端执行use database命令后 生效

如果是修改global级别的权限，则需要重新创建连接新权限才能生效

--skip-grant-tables可以跳过所有系统权限表而允许所有用户登录，只在特殊 情况下暂时使用

设置MySQL用户资源限制

通过设置全局变量max_user_connections可以限制所有用户在同一时间连接MySQL实例的数量，但此参数无法对每个用户区别对待，所以 MySQL提供了对每个用户的资源限制管理

MAX_QUERIES_PER_HOUR:一个用户在一个小时内可以执行查询的次数(基本包含所有语句)

MAX_UPDATES_PER_HOUR:一个用户在一个小时内可以执行修改的次数(仅包含修改数据库或表的语句)

MAX_CONNECTIONS_PER_HOUR:一个用户在一个小时内可以连接 MySQL的时间

MAX_USER_CONNECTIONS:一个用户可以在同一时间连接MySQL实例 的数量

从5.0.3版本开始，对用户‘user’@‘%.example.com’的资源限制是指所有通过example.com域名主机连接user用户的连接，而不是分别指从 host1.example.com和host2.example.com主机过来的连接

通过执行createuser/alteruser设置/修改用户的资源限制

mysql> alter user 'test'@'%' withMAX_QUERIES_PER_HOUR 100;

Query OK, 0 rows affected (0.00 sec)

mysql>create user 'test2'@'%' identified by 'test2'WITH MAX_QUERIES_PER_HOUR 20MAX_UPDATES_PER_HOUR 10 MAX_CONNECTIONS_PER_HOUR 5 MAX_USER_CONNECTIONS 2;

需要对用户资源不进行限制将值改为0

mysql> alter user 'test'@'%' with MAX_QUERIES_PER_HOUR 0;

Query OK, 0 rows affected (0.04 sec)

用户修改密码的方式

使用超级管理员用户进行修改：

方法一： mysql> alter user'test'@'%' identified by 'mysql';

        Query OK, 0 rows affected(0.02 sec)

方法二：mysql> set password for'test'@'%' =password('test');

       Query OK, 0 rows affected, 1warning (0.03 sec)

方法三： mysqladmin -utest -ptestpassword "mysql"

用户自己修改密码

方法四：mysql> alter user user()identified by 'test';

Query OK, 0 rows affected (0.07 sec)

方法四：mysql> set password = password('mysql');

      Query OK, 0 rows affected, 1warning (0.02 sec)

设置MySQL用户密码过期策略

设置系统参数default_password_lifetime作用于所有的用户账户

 default_password_lifetime=180 设置180天过期

 default_password_lifetime=0 设置密码不过期

 如果为每个用户设置了密码过期策略，则会覆盖上述系统参数

设置90天后密码过期

mysql> alter user 'test'@'%' password EXPIREINTERVAL 90 DAY;

Query OK, 0 rows affected (0.04 sec)

设置用户密码不过期

mysql> alter user 'test'@'%' password expirenever;

Query OK, 0 rows affected (0.04 sec)

如果不指定密码过期策略默认使用系统密码过期策略

锁定用户和解锁用户

创建jaye用户

mysql> create user 'jaye'@'%' identified by'jaye' account lock;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:   23

Current database: *** NONE ***

Query OK, 0 rows affected (0.09 sec)

登录jaye 报错

[root@redhat ~]# mysql -ujaye -pjaye -P3306

mysql: [Warning] Using a password on the commandline interface can be insecure.

ERROR 3118 (HY000): Access denied for user'jaye'@'localhost'. Account is locked.

对用户进行解锁

mysql> alter user 'jaye'@'%' account unlock;

Query OK, 0 rows affected (0.01 sec)

登录成功

[root@redhat ~]# mysql -ujaye -pjaye -P3306

mysql: [Warning] Using a password on the commandline interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 25

Server version: 5.7.20-log MySQL Community Server(GPL)

Copyright (c) 2000, 2017, Oracle and/or itsaffiliates. All rights reserved.

Oracle is a registered trademark of OracleCorporation and/or its

affiliates. Other names may be trademarks of theirrespective

owners.

Type 'help;' or '\h' for help. Type '\c' to clearthe current input statement.

mysql>

可以查看用户到底拥有什么权限可以使用

mysql> show grants for test;

+-------------------------------------------------+

| Grants for test@%                               |

+-------------------------------------------------+

| GRANT USAGE ON *.* TO 'test'@'%'                |

| GRANT ALL PRIVILEGES ON `test`.* TO'test'@'%'  |

| GRANT ALL PRIVILEGES ON `mysql`.* TO 'test'@'%'|

+-------------------------------------------------+

mysql> select * from mysql.user whereuser='test' \G;

*************************** 1. row***************************

                 Host: %

                 User: test

          Select_priv: N

           Insert_priv: N

          Update_priv: N

          Delete_priv: N

          Create_priv: N

            Drop_priv: N

          Reload_priv: N

        Shutdown_priv: N

         Process_priv: N

            File_priv: N

           Grant_priv: N

      References_priv: N

           Index_priv: N

           Alter_priv: N

         Show_db_priv: N

           Super_priv: N

 Create_tmp_table_priv: N

     Lock_tables_priv: N

         Execute_priv: N

      Repl_slave_priv: N

     Repl_client_priv: N

     Create_view_priv: N

       Show_view_priv: N

  Create_routine_priv: N

   Alter_routine_priv: N

     Create_user_priv: N

           Event_priv: N

         Trigger_priv: N

Create_tablespace_priv: N

             ssl_type:

           ssl_cipher:

          x509_issuer:

         x509_subject:

        max_questions: 0

          max_updates: 0

      max_connections: 0

 max_user_connections: 0

               plugin: mysql_native_password

 authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA

     password_expired: Y

 password_last_changed: 2018-05-03 09:53:55

    password_lifetime: 0

       account_locked: N

1 row in set (0.00 sec)

ERROR:

No query specified

这个查出来的是相对于超级权限的查询

mysql> grant select on *.* to 'test'@'%';

Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.user whereuser='test' \G;

*************************** 1. row***************************

                 Host: %

                 User: test

           Select_priv: Y

          Insert_priv: N

          Update_priv: N

          Delete_priv: N

          Create_priv: N

            Drop_priv: N

          Reload_priv: N

        Shutdown_priv: N

          Process_priv:N

            File_priv: N

           Grant_priv: N

      References_priv: N

           Index_priv: N

           Alter_priv: N

         Show_db_priv: N

           Super_priv: N

 Create_tmp_table_priv: N

     Lock_tables_priv: N

          Execute_priv: N

      Repl_slave_priv: N

     Repl_client_priv: N

     Create_view_priv: N

       Show_view_priv: N

  Create_routine_priv: N

   Alter_routine_priv: N

     Create_user_priv: N

           Event_priv: N

         Trigger_priv: N

Create_tablespace_priv: N

             ssl_type:

           ssl_cipher:

          x509_issuer:

         x509_subject:

        max_questions: 0

          max_updates: 0

      max_connections: 0

 max_user_connections: 0

               plugin: mysql_native_password

 authentication_string:*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA

     password_expired: Y

 password_last_changed: 2018-05-03 09:53:55

    password_lifetime: 0

       account_locked: N

1 row in set (0.00 sec)

ERROR:

No query specified