MySQL用户权限

用户权限控制

可以限制用户访问特定数据库或表，或限制特定操作，如select. insert和update。 需要用户GRANT权限，才能为其他用户授予权限。可以使用root用户管理权限，也可以创建管理员账号来管理权限

授予权限

• 将READ ONLY(select)权限授予lisi用户

$ grant select on employees.* to 'lisi'@'localhost';

• 授予zhangsan用户仅能对employees表有查询权限:

# 授予用户权限之前要创建一个用户：
$ CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'Com.123456'
$ grant select on employees.employees to 'zhangsan'@'localhost';

• 授予zhangsan用户仅能对employees表的first. name和last_ name列有查询权限

$ grant select(first_name,last_name) on employees to 'zhangsan'@'localhost';

• 扩展权限。可以通过执行新授权来扩展权限。将权限扩展到zhangsan用户,以访问薪资(salaries)表中的薪水:

$ grant select(salary) on employees.salaries to 'zhangsan'@'localhost';

• 创建超级(SUPER)用户。需要一个管理员账号来管理该服务器。ALL表示除GRANT权限之外的所有权限。

  $ create user 'dbadmin'@'%' identified with mysql_native_password by 'Com.123456';
  
  $ grant all on *.* to 'dbadmin'@'%';
  

• 授予GRANT权限。 用户拥有GRANT OPTION权限才能授予其他用户权限。

$ grant grant option on *.* to 'dbadmin'@'%';

检查权限

可以检查所有用户的权限。

• 检查zhangsan用户的授权：

  $ show grants for 'zhangsan'@'localhost'\G
  

• 检查dbadmin用户的权限，可以看到其拥有的所有权限：

$ show grants for 'dbadmin'@'%'\G

撤销权限

撤销权限与创建权限的语法相同。向用户授权限用TO,撤销用户的权限用FROM。

• 撤销‘lisi’@'localhost’用户的DELETE访问权限

$ revoke delete on employees.*from 'lisi'@'localhost';

• 撤销zhangsan用户对薪水列的访问权限：

$ revoke select(salary) on employees.salaries from 'zhangsan'@'localhost';

修改mysql.user表

所有用户信息及权限都存储在mysql.user表中。如果你有权访问mysql.user表, 则可以直接通过修改mysql.user表来创建用户并授予权限。如果使用GRANT、REVOKE、 SET PASSWORD或RENAME USER 等账户管理语句间接修改授权表，会立即生效,如果是直接修改mysql.user表,不会立即生效，除非重启服务器或者重新加载表，可以通过FLUSH PRIVILEGES语句来完成GRANT表的重新加载。

• 查找mysql.user表以找出dbadmin用户的所有条目:

$ select * from mysql.user where User='dbadmin'\G

• 可以看到dbadmin用户可以从任意主机(%)。 访问数据库，更新mysql.user表, 令其只能从localhost访问数据库。

$ update mysql.user set Host='localhost' where User='dbadmin';

$ flush privileges;

设置用户密码有效期

可以设置一段时间作为用户密码的有效期, 过期之后用户则需要更改密码。

当应用程序开发人员要求访问数据库时，可以设置默认密码创建该账户，并将其设置为过期状态,则其必须更改密码才能继续使用MySQL。

创建所有用户并设置其密码过期日期等于default, password. lifetimne变量的值,默认情况下用户被禁用。

• 创建一个具有过期密码的用户。第一次登陆时会报错: 1820 (HY000) ,这时候需要使用ALTER USER语句重置密码:

$ create user 'devlope'@'%' identified with mysql_native_password by 'Com.123456' password expire;

开发人员登录：

[root@localhost ~]# mysql -udevlope -pCom.123456

• 开发人员更改密码（在root用户下更改）：

$ alter user 'devlope'@'%' identified with mysql_native_password by '123456.Com';

• 登录devlope查看：

$ mysql  -udevlope -p123456.Com
$ show databases;

• 手动设置用户过期：

$ alter user 'devlope'@'%' password expire;

• 要求用户每隔90天更改一次密码：

$ alter user 'devlope'@'%' password expire interval 90 day;

锁定用户

如果发现账户有任何问题，可以将其锁定。使用CREATE USER或ALTER USER 锁定用户。

• 通过将ACCOUNT LOCK子句添加到ALTER USER语句来锁定账户:

$ ALTER USER 'devlope'@'%' ACCOUNT LOCK;

# 账户登陆后会提示账户被锁定：
$ mysql -udevlope -p123456.Com
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3118 (HY000): Access denied for user 'devlope'@'localhost'. Account is locked.

# 解除锁定

$ ALTER USER 'devlope'@'%' ACCOUNT UNLOCK;

为用户创建角色

MySQL的角色是一个权限的集合。 与用户-样,角色的权限可以被授予和撤销。用户账号被授予角色后,该角色会将其拥有的权限授予该账户。

# 创建角色

$ create role 'app_read_only','app_writes'，'app_developer';

# 使用GRANT语句为角色分配权限：

$ grant select on employees.* to 'app_read_only';

$ grant insert,update,delete on employees.* to 'app_writes';

$ grant all on employees.* to 'app_developer';

• 创建用户。如果不指定主机，则将采用% （任意主机）：

mysql> create user emp_read_only identified by 'Com.123456';
Query OK, 0 rows affected (0.00 sec)

mysql> create user emp_writes identified by 'Com.123456';
Query OK, 0 rows affected (0.01 sec)

mysql> create user emp_developer identified by 'Com.123456';
Query OK, 0 rows affected (0.01 sec)

mysql> create user emp_read_write identified by 'Com.123456';
Query OK, 0 rows affected (0.01 sec)

• 使用GRANT语句为用户分配角色。你可以为用户分配多个角色。

mysql> grant 'app_read_only' to 'emp_read_only'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant 'app_writes'to 'emp_writes'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant 'app_developer' to 'emp_developer'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> grant 'app_read_only' to 'emp_read_write'@'%';
Query OK, 0 rows affected (0.01 sec)

• mysql库中多了两张表：

mysql> use mysql;

mysql> show tables like '%role%';

mysql> select * from role_edges;