mysql> CREATE USER ‘francis‘@‘localhost‘ IDENTIFIED BY ‘frank‘
-> WITH MAX_QUERIES_PER_HOUR 20
-> MAX_UPDATES_PER_HOUR 10
-> MAX_CONNECTIONS_PER_HOUR 5
-> MAX_USER_CONNECTIONS 2;
mysql> ALTER USER ‘francis‘@‘localhost‘ WITH MAX_QUERIES_PER_HOUR 100;
mysql> ALTER USER ‘francis‘@‘localhost‘ WITH MAX_CONNECTIONS_PER_HOUR 0;
mysql> CREATE USER ‘jeffrey‘@‘localhost‘ IDENTIFIED BY ‘mypass‘;
mysql> ALTER USER ‘jeffrey‘@‘localhost‘ IDENTIFIED BY ‘mypass‘;
mysql> SET PASSWORD FOR ‘jeffrey‘@‘localhost‘ = PASSWORD(‘mypass‘);
mysql> GRANT USAGE ON *.* TO ‘jeffrey‘@‘localhost‘ IDENTIFIED BY ‘mypass‘;
shell> mysqladmin -u user_name -h host_name password "new_password"
mysql> ALTER USER USER() IDENTIFIED BY ‘mypass‘;
mysql> SET PASSWORD = PASSWORD(‘mypass‘);
default_password_lifetime=180 设置180天过期
default_password_lifetime=0 设置密码不过期
ALTER USER ‘jeffrey‘@‘localhost‘ PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER ‘jeffrey‘@‘localhost‘ PASSWORD EXPIRE NEVER; 密码不过期
ALTER USER ‘jeffrey‘@‘localhost‘ PASSWORD EXPIRE DEFAULT; 默认过期策略
ALTER USER ‘jeffrey‘@‘localhost‘ PASSWORD EXPIRE;
mysql> SELECT 1;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
mysql> ALTER USER USER() IDENTIFIED BY ‘new_password‘;
Query OK, 0 rows affected (0.01 sec)
mysql> create role app_readonly;
Query OK, 0 rows affected (0.09 sec)
#创建一个role叫app_readonly只读
mysql> create user app1@localhost identified by ‘mysql‘;
Query OK, 0 rows affected (0.01 sec)
#创建一个用户app1
mysql> grant select on *.* to app_readonly;
Query OK, 0 rows affected (0.02 sec)
#给app_readonly这个角色一个select权限
mysql> grant app_readonly to app1@localhost;
Query OK, 0 rows affected (0.10 sec)
#把用户和角色绑定
mysql> show grants for app1@localhost;
+------------------------------------------------+
| Grants for app1@localhost |
+------------------------------------------------+
| GRANT USAGE ON *.* TO `app1`@`localhost` |
| GRANT `app_readonly`@`%` TO `app1`@`localhost` |
+------------------------------------------------+
#查询用户的权限
mysql> show grants for app1@localhost using app_readonly;
+------------------------------------------------+
| Grants for app1@localhost |
+------------------------------------------------+
| GRANT SELECT ON *.* TO `app1`@`localhost` |
| GRANT `app_readonly`@`%` TO `app1`@`localhost` |
+------------------------------------------------+
2 rows in set (0.00 sec)
#查询用户的权限
mysql> create role app_readwrite;
Query OK, 0 rows affected (0.04 sec)
mysql> create user app2@localhost identified by ‘mysql‘;
Query OK, 0 rows affected (0.04 sec)
mysql> grant select,insert,delete,update on *.* to app_readwrite;
Query OK, 0 rows affected (0.10 sec)
mysql> grant app_readwrite to app2@localhost;
Query OK, 0 rows affected (0.07 sec)
mysql> show grants for app2@localhost;
+-------------------------------------------------+
| Grants for app2@localhost |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `app2`@`localhost` |
| GRANT `app_readwrite`@`%` TO `app2`@`localhost` |
+-------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for app2@localhost using app_readwrite;
+-------------------------------------------------------------------+
| Grants for app2@localhost |
+-------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `app2`@`localhost` |
| GRANT `app_readwrite`@`%` TO `app2`@`localhost` |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke app_readonly from app1@localhost;
Query OK, 0 rows affected (0.10 sec)
#解除绑定关系
mysql> show grants for app1@localhost using app_readonly;
ERROR 3530 (HY000): `app_readonly`@`%` is not granted to `app1`@`localhost`
mysql> show grants for app1@localhost;
+------------------------------------------+
| Grants for app1@localhost |
+------------------------------------------+
| GRANT USAGE ON *.* TO `app1`@`localhost` |
+------------------------------------------+
原文：https://www.cnblogs.com/xuewenlong/p/12882064.html