MySQL 账户和访问控制管理

1、 MySQL 提供的权限

Privilege Grant Table Column Context
ALL [PRIVILEGES] Synonym for “all privileges” Server administration
ALTER Alter_priv Tables
ALTER ROUTINE Alter_routine_priv Stored routines
CREATE Create_priv Databases, tables, or indexes
CREATE ROUTINE Create_routine_priv Stored routines
CREATE TABLESPACE Create_tablespace_priv Server administration
CREATE TEMPORARY TABLES Create_tmp_table_priv Tables
CREATE USER Create_user_priv Server administration
CREATE VIEW Create_view_priv Views
DELETE Delete_priv Tables
DROP Drop_priv Databases, tables, or views
EVENT Event_priv Databases
EXECUTE Execute_priv Stored routines
FILE File_priv File access on server host
GRANT OPTION Grant_priv Databases, tables, or stored routines
INDEX Index_priv Tables
INSERT Insert_priv Tables or columns
LOCK TABLES Lock_tables_priv Databases
PROCESS Process_priv Server administration
PROXY See proxies_priv table Server administration
REFERENCES References_priv Databases or tables
RELOAD Reload_priv Server administration
REPLICATION CLIENT Repl_client_priv Server administration
REPLICATION SLAVE Repl_slave_priv Server administration
SELECT Select_priv Tables or columns
SHOW DATABASES Show_db_priv Server administration
SHOW VIEW Show_view_priv Views
SHUTDOWN Shutdown_priv Server administration
SUPER Super_priv Server administration
TRIGGER Trigger_priv Tables
UPDATE Update_priv Tables or columns
USAGE Synonym for “no privileges” Server administration

权限详情：https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

2、 权限管理数据库 mysql / 授权表，权限级别

MySQL 数据库本身会有一些系统的数据库，其中有一个mysql库，里边包含几个关于用户和权限的表。

有以下权限表：

user：用户帐户，全局权限和其他非权限列。

db：数据库级权限。

tables_priv：表级权限。

columns_priv：列级权限。

procs_priv：存储过程和函数权限。

proxies_priv：代理用户权限

每个权限表都包含作用域列和权限列。
作用域列就是指定该用户的用户名 和 连接客户端，包含 Host 列 和 User 列，还有Db 列。 Host 是主机名，User是用户名，Db是数据库名。表示哪台主机能够通过哪个用户 连接到 server ，操作哪个数据库。
权限列就是说明 该用户是否具有该权限。

MySQL 不推荐用 INSERT，UPDATE或DELETE之类的语句直接修改权限表，而是通过 CREATE USER，GRANT和SET PASSWORD之类的 SQL 语句创建用户和授权

https://dev.mysql.com/doc/refman/5.7/en/grant-tables.html

3、创建用户、授权、修改

3.1 账户语法

MySQL账户由 用户名和主机名构成，以@连接：

# 如果用户名和主机名是合法的，则无需用引号引起来；如果包含特殊字符(例如空格或-)，则需要引号。 引号可以是 反引号(``'')，单引号(')或双引号(")。
#主机可以是主机名或者是ip，也可以使用通配符
'user-name'@'host-name'

#仅由用户名组成的帐户名等效于 主机名是 %
'user-name'
# 等效
'user_name'@'%'

'user_name'@'%'   # 表示允许任何客户端连接server

'test-user'@'%.com'  # 表示允许以 .com 结尾的主机名 客户端连接

'user-name'@'198.51.100.%'  # 匹配子网上的每个主机

'david'@'198.51.100.0/255.255.255.0'  # 对于指定为 IPv4 地址的主机值，可以给出网络掩码以指示要用于网络号的地址位。

'localhost' 表示本机， IP 地址 '127.0.0.1' 表示 IPv4 回环地址。 IP 地址 '::1' 表示 IPv6回环地址。

https://dev.mysql.com/doc/refman/5.7/en/account-names.html

3.2 账户 和 授权

CREATE USER和DROP USER创建和删除帐户
GRANT和REVOKE为帐户分配特权并撤消其特权
SHOW GRANTS显示帐户权限分配

3.2.1创建用户

create user 语法：

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

# 说明

# 用户账号
user:
    'user-name'@'host-name'

# 认证选项： 身份验证插件 和 密码
auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | IDENTIFIED BY PASSWORD 'auth_string'
}

# 安全连接选项
tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

# 资源限制选项：每小时最大查询数，更新数，连接数，用户连接数。默认都是0，表示没有限制
resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

# 密码过期设置，5.7.11之后版本默认为 0，表示永不过期；5.7.11之前默认为360
password_option: {
    PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

# 账号状态
lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

使用CREATE USER 的用户 必须具有全局CREATE USER权限或mysql系统数据库的INSERT权限。启用read_only系统变量后，CREATE USER还需要SUPER权限。
如果您尝试创建一个已经存在的帐户，则会发生错误。如果给出了IF NOT EXISTS子句，则该语句将为每个已存在的命名帐户生成警告，而不是错误

示例：

shell> mysql -u root -p
Enter password: (enter root password here)

CREATE USER 'finley'@'localhost'  IDENTIFIED BY 'password';


CREATE USER
  'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password
                                   BY 'new_password1',
  'jeanne'@'localhost' IDENTIFIED WITH sha256_password
                                  BY 'new_password2'
  REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 60
  PASSWORD EXPIRE NEVER
  ACCOUNT LOCK;

3.2.2 删除用户

语法：

DROP USER [IF EXISTS] user [, user] ...

执行DROP USER 的用户需要具有全局CREATE USER权限或mysql系统数据库的DELETE权限。启用read_only系统变量后，DROP USER还需要SUPER权限
如果尝试删除一个不存在的帐户，则会发生错误。如果给出了IF EXISTS子句，则该语句将为每个不存在的命名用户生成警告，而不是错误。

示例：

DROP USER 'jeffrey'@'localhost';

3.2.3 修改账户

1） alter user

ALTER USER [IF EXISTS]
    user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

# 如果修改的是当前活动账号
ALTER USER [IF EXISTS]
    USER() IDENTIFIED BY 'auth_string'

user:
    (see Section6.2.4, "Specifying Account Names")

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

ALTER USER语句修改 MySQL 帐户。它允许为现有帐户修改身份验证，SSL/TLS，资源限制和密码 Management 属性。它也可以用于锁定和解锁帐户。
要使用ALTER USER，您必须具有mysql系统数据库的全局CREATE USER特权或UPDATE特权。启用read_only系统变量后，ALTER USER还需要SUPER特权。
默认情况下，如果您尝试修改不存在的用户，则会发生错误。如果给出了IF EXISTS子句，则该语句将为每个不存在的命名用户生成警告，而不是错误

2）如果只是修改 用户名 可使用 Rename

RENAME USER old_user TO new_user
    [, old_user TO new_user] ...


RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';
  1. 如果只是修改密码，可以使用 set password，但从 MySQL 5.7.6 开始不推荐使用。
SET PASSWORD [FOR user] = password_option

password_option: {
    'auth_string'
  | PASSWORD('auth_string')
}

SET PASSWORD FOR 'jeffrey'@'localhost' = 'auth_string';

没有FOR user子句，该语句设置当前用户的密码

3.2.4 账户授权

# 授权
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

# 授权代理
GRANT PROXY ON user
    TO user [, user] ...
    [WITH GRANT OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

# 权限级别
priv_level: {
    *
  | *.*         # 全局权限： 可以操作任何数据库下的表
  | db_name.*   # 数据库级别权限：只能操作该数据库下的表 
  | db_name.tbl_name   #表级别权限： 只能操作该数据库的特定表
  | tbl_name
  | db_name.routine_name
}

user:
    (see Section6.2.4, "Specifying Account Names")

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
  | IDENTIFIED BY PASSWORD 'auth_string'
}

tls_option: {
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'
}

resource_option: {
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

要使用GRANT授予特权，您必须具有GRANT OPTION特权，并且您必须具有要授予的特权


# 全局权限
GRANT ALL ON *.* TO 'someuser'@'somehost';
# 数据库级权限
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
# 表级权限
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
# 授予列级权限
GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost';

# WITH GRANT OPTION子句使目标用户拥有对其他用户进行授予与该用户拥有的相同级别权限的权限。
# 下面例子表示，'someuser'@'somehost' 也能对其他用户授予 全局级别的INSERT权限
GRANT INSERT ON *.* TO 'someuser'@'somehost' WITH GRANT OPTION;

ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

3.2.5 撤销授权

##1撤销部分权限
REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

##2 要撤消所有权限
REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user [, user] ...

## 3 撤销代理权限
REVOKE PROXY ON user
    FROM user [, user] ...

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

3.2.6 查询用户 拥有的权限

SHOW GRANTS [FOR user]

mysql> SHOW GRANTS FOR 'jeffrey'@'localhost';
+------------------------------------------------------------------+
| Grants for jeffrey@localhost                                     |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jeffrey`@`localhost`                      |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `jeffrey`@`localhost` |
+------------------------------------------------------------------+

https://dev.mysql.com/doc/refman/5.7/en/creating-accounts.html
https://dev.mysql.com/doc/refman/5.7/en/create-user.html
https://dev.mysql.com/doc/refman/5.7/en/drop-user.html
https://dev.mysql.com/doc/refman/5.7/en/alter-user.html
https://dev.mysql.com/doc/refman/5.7/en/rename-user.html
https://dev.mysql.com/doc/refman/5.7/en/set-password.html
https://dev.mysql.com/doc/refman/5.7/en/grant.html
https://dev.mysql.com/doc/refman/5.7/en/revoke.html
https://dev.mysql.com/doc/refman/5.7/en/show-grants.html

posted @ 2021-12-07 21:53  zhanglw  阅读(167)  评论(0)    收藏  举报