博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5786507.html
以下操作来自MySQL 8.0GA版本
1、创建用户和授权
(root@localhost:)[(none)]> CREATE USER 'dbawatcher'@'%' identified by 'dbawatcher';
Query OK, 0 rows affected (0.04 sec)
(root@localhost:)[(none)]> GRANT ALL PRIVILEGES ON *.* TO 'dbawatcher'@'%' with GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
(root@localhost:)[(none)]> flush privileges;
查看创建用户的ddl语句：
(root@localhost:)[(none)]> show create user dbawatcher@'%'\G
*************************** 1. row ***************************
CREATE USER for dbawatcher@%: CREATE USER 'dbawatcher'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*221DE9E297A08B5BA06E105D95582593491E3A7D' REQUIRE
NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT1 row in set (0.00 sec)
(2)MySQL有哪些权限呢，创建个all privileges的用户可以来看看：
(root@localhost:)[(none)]> show grants for dbayang@'%'\G
*************************** 1. row ***************************
Grants for dbayang@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES,
SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `dbayang`@`%` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for dbayang@%: GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLI
CATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `dbayang`@`%` WITH GRANT OPTION2 rows in set (0.00 sec)
例如：
(root@localhost:)[(none)]> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'password';
(root@localhost:)[(none)]> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON bankaccount.*
-> TO 'custom'@'localhost';
(root@localhost:)[(none)]> CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'password';
(root@localhost:)[(none)]> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON expenses.*
-> TO 'custom'@'host47.example.com';
(root@localhost:)[(none)]> CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'password';
(root@localhost:)[(none)]> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON customer.*
-> TO 'custom'@'%.example.com';
2、密码管理
(1)修改密码
(root@localhost:)[(none)]> alter user `dev_lili`@`localhost` identified by 'lili123';
Query OK, 0 rows affected (0.11 sec)
匿名用户密码修改：
ALTER USER USER() IDENTIFIED BY 'password';
(2)设置密码过期
(root@localhost:)[(none)]> ALTER USER 'dev_lili'@'localhost' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.11 sec)
[root@dbayang ~]# mysql -udev_lili -plili123 test
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 8.0.11
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(dev_lili@localhost:)[test]> show tables;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
(dev_lili@localhost:)[test]>
(dev_lili@localhost:)[test]>
(dev_lili@localhost:)[test]> select * from sales_info;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
密码历史重用
(root@localhost:)[(none)]> ALTER USER 'dev_lili'@'localhost' PASSWORD HISTORY 5;
Query OK, 0 rows affected (0.07 sec)
密码历史时期：
CREATE USER 'dev_lili'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'dev_lili'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
3、用户资源限制
限制客户端使用MySQL服务器资源的一种方法是将全局 max_user_connections系统变量设置为非零值。
虽然这限制了任何给定帐户可以进行的同时连接的数量，但对连接后客户端可以执行的操作没有限制。
另外，设置 max_user_connections不能管理个人账户。这两种类型的控制都是MySQL管理员感兴趣的。
为了解决这些问题，MySQL允许个人用户使用这些服务器资源的限制：
一个用户每小时可以发出的查询次数，对应参数
MAX_QUERIES_PER_HOUR
一个用户每小时可以发布的更新次数，对应参数
MAX_UPDATES_PER_HOUR
用户每小时可以连接到服务器的次数，对应参数
MAX_CONNECTIONS_PER_HOUR
一个用户户同时连接到服务器的数量，对应参数
MAX_USER_CONNECTIONS
任何客户可以发出的声明都会违反查询限制。只有修改数据库或表的语句会违反更新限制。
CREATE USER 'lili'@'localhost' IDENTIFIED BY 'frank'
WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5
MAX_USER_CONNECTIONS 2;
修改：
ALTER USER 'lili'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;
删除：
要删除限制，将其值设置为零就行了
ALTER USER 'lili'@'localhost' WITH MAX_QUERIES_PER_HOUR 0;
--The end
