MySQL-用户与权限

目录

DB权限表

新建普通用户

创建新用户(create user)

创建新用户(grant)

删除普通用户

修改用户密码

Root用户修改自己的密码

Root用户修改普通用户密码

普通用户修改密码

Root用户密码丢失的解决办法

权限管理

赋予权限

收回权限

查看权限

---

博客主页：大虾好吃吗的博客

MySQL专栏：MySQL专栏地址

1. 用户列user表的用户列包括 Host、 User、password，分别表示主机名、用户名和密码。其中 user和 Host为user表的联合主踺。当用户与服务器之间建立连接时，输入的账户信息中的用户名称、主机名和密码必须匹配user表中对应的字段，只有3个值都匹配的时候，才允许连接的建立。这3 个字段的值就是创建账户时保存的账户信息。修改用户密码时，实际就是修改user表的password字段的值。

2. 权限列 权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。包括查询权限、修改权限等普通权限，还包括了关闭服务器、超级权限和加载用户等高级权限。普通权限用于操作数据库；高级权限用于数据库管理。 user表中对应的权限是针对所有用户数据库的。这些字段值的类型为 ENUM,可以取的值只能为 Y和 N, Y表示该用户有对应的权限； N表示用户没有对应的权限。查看user表的结构可以看到，这些字段的值默认都是 N。如果要修改权限，可以使用 GRANT语句或 UPDATE语句更改 user表的这些字段来修改用户对应的权限。

3. 安全列 安全列只有6个字段，其中两个是 SSI相关的，2个是 x509相关的，另外2个是授权插件相关的。SSI用于加密； X509标准可用于标识用户： Plugin字段标识可以用于验证用户身份的插件，如果该字段为空，服务器使用内建授权验证机制验证用户身份。可以通过 SHOW VARIABLES LIKE ‘ have_openssl’语句来查询服务器是否支持 SSI功能。

4. 资源控制列 资源控制列的字段用来限制用户使用的资源，包含4个字段，分别为： 1）Max_questions—用户每小时允许执行的查询操作次数。 2）Max_updates一用户每小时允许执行的更新操作次数。 3）Max_connections—用户每小时允许执行的连接操作次数。4）Max_user_connections—用户允许同时建立的连接次数。

5. 一个小时内用户查询或者连接数量超过资源控制限制，用户将被锁定，直到下一个小时，才可以在此执行对应的操作。可以使用 GRANT语句更新这些字段的值。

DB权限表

DB表和host表 db表和 host表是 MYSQL数据中非常重要的权限表。 db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。 host表中存储了某个主机对数据库的操作权限，配合db权限表对给定主机上数据库级操作权限做更细致的控制。这个权限表不受 GRANT和 REVOKE语句的影响。 db表比较常用， host表一般很少使用。 db表和 host表结构相似，字段大致可以分为两类：用户列和权限列。

新建普通用户

创建新用户(create user)

CREATE USER user_specification [,user_specification] ... user_specification: user@host [ IDENTIFIED BY[PASSWORD] ‘password’ | IDENTIFIED WITH auth_plugin [AS ‘auth_string’]] 

User:表示创建的用户的名称；host：表示允许登陆的用户主机名称；IDENTIFIED BY：表示用来设置用户的密码；[PASSWORD] ：表示使用哈希值设置密码;‘password’:表示用户登陆时使用的普通明文密码；IDENTIFIED WITH：表示用户指定一个身份验证插件；auth_plugin：是插件的名称；‘auth_string’是可选的字符串，解释插件的意义。

1. 查看MySQL的用户

mysql> select * from mysql.user\G

2. 创建用户Tom

mysql> create user 'tom'@'localhost' identified by '123';       #创建本地用户tom密码为123Query OK, 0 rows affected (0.02 sec)​mysql> create user 'z3'@'localhost';                            #创建本地用户z3密码为空Query OK, 0 rows affected (0.00 sec)

3. 哈希加密

mysql> select password('123');+-------------------------------------------+| password('123')              |+-------------------------------------------+| *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |+-------------------------------------------+1 row in set, 1 warning (0.00 sec)​mysql> create user 'l4'@'localhost' identified by password '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257';Query OK, 0 rows affected, 1 warning (0.00 sec)

创建新用户(grant)

GRANT privileges ON db.table To user@host [identified by ‘password’] [,user[identified by ‘password’]][with grant option]; 

Privileges:表示赋予用户的权限类型； db.table：表示用户的权限所作用的数据库中的表； identified by关键字用来设置密码； ‘password’用户的密码； [with grant option]可选项，表示对新建立的用户赋予GRANT权限。

1. 创建用户并授权

mysql> grant select,update on bbs.g1_tab to 'g1'@'localhost' identiified by '123';Query OK, 0 rows affected, 1 warning (0.00 sec)

2. 查看用户

mysql> select host,user,select_priv,update_priv from mysql.user where user='g1';+-----------+------+-------------+-------------+| host    | user | select_priv | update_priv |+-----------+------+-------------+-------------+| localhost | g1  | N      | N      |+-----------+------+-------------+-------------+1 row in set (0.00 sec)

直接操作MySQL用户表

mysql> insert into user(host,user,authentication_string,ssl_cipher,x509_issuer,x509_subject) values('localhost','g2',PASSWORD('123'),,'','','');Query OK, 1 row affected, 1 warning (0.00 sec)​mysql> flush privileges;                        #刷新权限表Query OK, 0 rows affected (0.02 sec)​mysql> exitBye[root@localhost ~]# mysql -ug2 -p123

需要注意的是5.7以前的版本authentication_string字段修改为password。

新添加的用户还无法使用账号和密码登陆MySQL，需要使用FLUSH告诉服务器重新加载授权。

提示：INSERT 需要使用PASSWORD（）函数加密密码；GRANT语句会自动将密码加密后存入user表，因此不需要password（）。

删除普通用户

1. 使用DROP USER语句删除

mysql> drop user z3@localhost;Query OK, 0 rows affected (0.00 sec)

2. 使用delete语句删除用户

mysql> delete from mysql.user where host='localhost' and user='tom';Query OK, 1 row affected (0.00 sec)

修改用户密码

Root用户修改自己的密码

1. 使用mysqladmin命令在命令行指定新密码

Mysqladmin -u username -h localhost-p password “newpassword” Username:要修改的用户名 -h：需要修改那个主机 -p：输入当前的密码“newpassword”：新密码

[root@localhost ~]# mysqladmin -uroot -p password "456"#456为新密码Enter password: #此处注意，需要填写旧密码mysqladmin: [Warning] Using a password on the command line interface can be insecure.Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

2. 修改mysql数据库的user表

需要注意的是5.7以前的版本authentication_string字段修改为password

mysql> update mysql.user set authentication_string=password('456') where user='root' and host='localhost';Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 1mysql> flush privileges;#刷新权限表Query OK, 0 rows affected (0.00 sec)

3. 使用SET语句修改root用户的密码

mysql> set password=password('123.com');#修改root密码为123.comQuery OK, 0 rows affected, 1 warning (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)mysql> exitBye[root@localhost ~]# mysql -uroot -p123.com#重新登录测试密码登录成功

Root用户修改普通用户密码

1. 使用SET语句来修改普通用户的密码

mysql> set password for 'l4'@'localhost'=password('123.com');#修改l4密码为123.comQuery OK, 0 rows affected, 1 warning (0.00 sec)

2. 使用UPDATE语句修改普通用户密码

mysql> update mysql.user set authentication_string=password('123') where user='t1' and host='localhost';Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 1mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

3. 使用GRANT语句修改普通用户密码

mysql> grant usage on *.* to 'g1'@'localhost' identified by '123.com';Query OK, 0 rows affected, 1 warning (0.00 sec)

普通用户修改密码

先登录普通用户，使用set命令修改密码。

[root@localhost ~]# mysql -ut1 -p123mysql> set password=password('456');Query OK, 0 rows affected, 1 warning (0.00 sec)

Root用户密码丢失的解决办法

使用–skip-grant-tables选项启动mysql服务

[root@localhost bin]# cd /usr/local/mysql/bin[root@localhost bin]# mysqld_safe --skip-grant-tables user=mysql2023-03-10T04:13:58.885526Z mysqld_safe Logging to '/usr/local/mysql/data/mysql.log'.2023-03-10T04:13:58.922275Z mysqld_safe A mysqld process already exists[root@localhost bin]# /etc/init.d/mysqld restart --skip-grant-tablesShutting down MySQL....                                    [  确定  ]Starting MySQL..                                           [  确定  ]

权限管理

权限管理主要是对登陆到MySQL的用户进行权限验证。所有用户的权限都存储在MySQL的权限表中，不合理的权限规划给MySQL服务器带来安全隐患。MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并赋予该用户在数据库上SELECT\INSERT\UPDATE和DELETE权限。账户权限信息被存储在MySQL数据库的user、db、tables_priv、columns_priv和procs_priv表中。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。

1. CREATE和 DROP权限，可以创建新数据库和表，或删除（移掉）己有数据库和表。如果将 MySQL数据库中的 DROP权限授予某用户，用户可以删掉 MySQL访问权限保存的数据库。

2. SELECT、 INSERT、 UPDATE和DELETE权限允许在一个数据库现有的表上实施操作。

3. SELECT权限只有在它们真正从一个表中检索行时才被用到。

4. INDEX权限允许创建或删除索引， INDEX适用己有表。如果具有某个表的 CREATE权限，可以在CREATE TABLE语句中包括索引定义。

5. ALTER权限，可以使用 ALTER TABLE来更改表的结构和重新命名表。

6. CREATE ROUTINE权限来创建保存的程序（函数和程序）， ALTER ROUTINE权限用来更改和删除保存的程序， EXECUTE权限用来执行保存的程序。

7. GRANT权限允许授权给其他用户。可用于数据库、表和保存的程序。

8. FILE权限给予用户使用 LOAD DATA INFILE和 SELECT… INTO OUTFILE语句读或写服务器上的文件，任何被授予 FILE权限的用户都能读或写 MySQL服务器上的任何文件。（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。 FILE权限允许用户在 MySQL服务器具有写权限的目录下创建新文件，但不能覆盖己有文件。

赋予权限

授权就是为某个用户授予权限。合理的授权可以保证数据库的安全。MySQL中可以使用GRANT语句为用户授予权限。 授予的权限可以分为多个层级：

1. 全局层级 全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON . 和REVOKE ALL ON . 只授予和撤销全局权限。

2. 数据库层级 数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db 和mysql.host 表中。GRANTALL ON db_name 和 REVOKE ALL ON db_name.* 只授予和撤销数据库权限。

3. 表层级 表权限适用于一个给定表中的所有列。这些权限存储在mysql.tables_priv表中。GRANT ALL ON db_name.tb1_name 和 REVOKE ALLON db_name.tb1_name只授予和撤销表权限。

4. 列层级 列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，必须指定与被授权列相同的列。

5. 子程序层级 CREATEROUTINE、 ALTER ROUTINE、 EXCUTE和 GRANT权限适用于己存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予子程序层级，并存储在 mysql.procs_priv表中。 在 MySQL中，必须是拥有 GRANT权限的用户才可以执行 GRANT语句。要使用 GRANT或 REVOKE,必须拥有 GRANT OPTION权限，并且必须用于正在授予或撤销的权限。

GRANT的语法如下：

GRANT priv_type[(columns)] [,priv_type[(columns)]]... ON [object_type] table1,table2... TO user [IDENTIFIED BY [password] ’password’] [with grant option] Object_type=TABLE | FUNCTION | PROCEDURE GRANT OPTION取值： |MAX_QUERIES_PER_HOUR count|MAX_UPDATE_PER_HOUR count |MAX_CONNECTIONS_PER_HOUR count |MAX_USER_PER_HOUR count

GRANT OPTION的取值有5个意义：

1. GRANT OPTION将自己的权限赋予其他的用户

2. MAX_QUERIES_PER_HOUR count设置每个小时可以执行count次查询

3. MAX_UPDATE_PER_HOUR count设置每个小时可以执行count次更新

4. MAX_CONNECTIONS_PER_HOUR count设置每个小时可以建立count个连接

5. MAX_USER_PER_HOUR count 设置单个用户可以同时建立count个连接

创建权限并查看

授予tom用户select和insert权限（也可以说此刻在创建一个tom用户，密码为’123‘并赋予select、insert权限）

mysql> grant select,insert on *.* to 'tom'@'localhost' identified by '123';Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> select host,user,select_priv,insert_priv,grant_priv from mysql.user where user='tom';+-----------+------+-------------+-------------+------------+| host      | user | select_priv | insert_priv | grant_priv |+-----------+------+-------------+-------------+------------+| localhost | tom  | Y           | Y           | N          |+-----------+------+-------------+-------------+------------+1 row in set (0.00 sec)

收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用REVOKE语句取消用户的某些权限，使用REVOKE收回权限之后，用户账号的记录将从db,host,user,tables_priv和columns_priv表中删除，但是用户账号记录仍然在user表中保存（删除user表中的账户记录，用DROP USER语句）

REVOKE语句有两种用法

1. 第一种语法是收回所有用户的所有权限，此语法用于取消对已命名的用户的所有全局层级，数据库层级，表层级和列层级的权限。

REVOKE ALL PRIVILEGES GRANT OPTION FROM ‘user’@‘localhost’，‘user’@‘localhost’...

2. 第二种语法是长格式的REVOKE语句

REVOKE priv_type [(columns)],priv_type[(columns)]... ON table1,table2... FROM ‘user’@’localhost’;

撤销tom用户的insert权限

mysql> revoke insert on *.* from 'tom'@'localhost';Query OK, 0 rows affected (0.00 sec)mysql> select host,user,select_priv,insert_priv,grant_priv from mysql.user where user='tom';+-----------+------+-------------+-------------+------------+| host      | user | select_priv | insert_priv | grant_priv |+-----------+------+-------------+-------------+------------+| localhost | tom  | Y           | N           | N          |+-----------+------+-------------+-------------+------------+1 row in set (0.00 sec)

查看权限

SHOW GRANTS语句可以显示指定用户的权限信息 SHOW GRANTS FOR ‘user’@‘host’

mysql> show grants for 'tom'@'localhost'\G*************************** 1. row ***************************Grants for tom@localhost: GRANT SELECT ON *.* TO 'tom'@'localhost'1 row in set (0.00 sec)

用select语句查看user表中的各个权限字段以确定用户的权限信息，语法格式如下：

SELECT privileges_list FROM user WHEREuser=’username’,host=’hostname’;mysql> select host,user,select_priv,insert_priv,grant_priv from mysql.user where user='tom';+-----------+------+-------------+-------------+------------+| host      | user | select_priv | insert_priv | grant_priv |+-----------+------+-------------+-------------+------------+| localhost | tom  | Y           | N           | N          |+-----------+------+-------------+-------------+------------+1 row in set (0.00 sec)